Absolutely no Fixed Bid Contracts - Ever!

I got a job from a client and it was a fixed bid.  We pay you x dollars and you finish the work.

BUT...

• The scope was ill-defined




• the "spec" for the project was "replace our existing system", not an objective set of criteria.  Therefore any variation between their old system and the new one could be considered incorrect.


• If there are features that we cant see from the 300 screen shots we have, then we'll have to do them all to be "finished"


• because there is no objective spec, this will end up where all fixed bid contracts end up




• the client will expect us to "finish" the project forever, because if they ever accept the work, then they will have to pay more for changes.


• the client will always have a giant list of 'fixes' whenever we show them the completed work.


• we will continue to push them to go live, and they will have every inclination to manufacture more and more changes, even if they are not in the original project.






• The estimate was based on a very simplified version of the work




• we showed the client a mockup with all the bells and whistles, and gave them an estimate.


• the client liked it, but wanted the work much, much cheaper.


• we offered a simplified approach to lower the cost.


• they didn't really listen and kept expecting everything at the lower cost.

This week I will finish the project.  I will build it to the demo server and the client will look at it.  We have exhausted ALL the hours assigned to the work.  They are going to have a list of things they want "fixed" before going live.  At that point we will want more money, but as far as they are concerned, it's a fixed bid.  They get whatever they want for the agreed price. 



Welcome to slavery.  Working free forever and never getting paid again.



Unless a project is less than 10 hours, NEVER accept a fixed bid contract.  It's a no-win scenario.



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

How to Check for any Changes in Table Data.

I am having a problem where some sneak process is changing the wrong


data in my inventory table. I wanted to discover how and when this was


happening, so I created a copy of my inventory table (on my local


machine, like this:



Select * from [dbo].[Inventory] into [dbo].[InventoryBASELINE]



Now I can check for discrepancies, like this...



select * from [dbo].[InventoryBASELINE] IB where CHECKSUM(*) <>


isnull((select CHECKSUM(*) from [dbo].[Inventory] I where IB.ID=I.ID),0)


union all


select * from [dbo].[Inventory] I where CHECKSUM(*) <> isnull((select


CHECKSUM(*) from [dbo].[InventoryBASELINE] IB where IB.ID=I.ID),0)


order by ID



This will show me any rows that are different, missing, or added between


my baseline table and my current table.


If I only cared about a few columns, I could use CHECKSUM(Customer, Qty,


Style) any columns I care about in all 4 checksum functions.

Issuing Direct SQL from MVC5

Recently I needed to add a quick updater that added a value to Table1


when someone copied data from Table2.




Now, the normal way to do this with Entity Framework is to load the


Entity, and change the value, then save the changed entity.




But I am a SQL guy from way back, and rather than fetch the entire


record and write it all back through Entity Framework, I figured it


would be Way more efficient if I just sent the database an update


directly. Turns out that for us old SQL developers, it's terribly easy


to do just that.




Here is my example subroutine, for your dining pleasure.

        /// 

        /// Updates a Toolkit with the number of the Assignment ID Created from it. If you are trying to remove an assignment id from a toolkit, use RemoveAssignmentFromToolkit()
        /// 
        /// The toolkit ID to be updated
        /// The AssignmentID to attach
        public static void UpdateToolkitWithAssignmentID(int ToolkitID, int AssignmentID)
        {
            using (ORM db = new ORM())
            {
                try
                {
                    db.Database.ExecuteSqlCommand(
                        "UPDATE [dbo].[ToolkitRequests] SET[AssignmentID] = @AID Where[UniqueId] = @ID",
                        new SqlParameter("@AID", AssignmentID),
                        new SqlParameter("@ID", ToolkitID)
                        );
                }
                finally
                {
                    db.Dispose();
                }
            }
        }

That's it! It's super fast, and there is no mucking about in Entity


Framework. NOTE that all the EF validity checking is skipped when you


do this, so use it in cases where it's a simple SQL and you can ensure


data integrity yourself. In my case, there is no way to get in this


routine without valid values in both params, so I know I won't get


surprised with a stray NULL.






---


This email has been checked for viruses by Avast antivirus software.


https://www.avast.com/antivirus

Data to MVC in 10 minutes.

Database

Assuming you created your table in SSMS already, It should look something like this:

Now drag select the 3 columns like and control+C copy them to the clipboard:

…and the clipboard…

ID              uniqueidentifier     Unchecked

International   bit                  Unchecked

Name            nvarchar(20)         Unchecked

Description     nvarchar(200)        Checked

BarPercent      int                  Unchecked

IncludesSteps   varchar(MAX)         Unchecked

Model

Go to Visual Studio and in your Solution Explorer, right click Models and Add, then Class

Make it easy on yourself:  Name it the same as your table.

Make the code look like this…

namespace SA.DS._0._2.Models  //this should match your other models, or leave it how Visual Studio created it

{

        using System;

        using System.Collections.Generic;

        using System.ComponentModel.DataAnnotations;

        using System.ComponentModel.DataAnnotations.Schema;

        using System.Data.Entity.Spatial;

        [Table("Lookups.StatusBarTypes")]  //This is the name of your table.  If the schema is left out, it assumes [dbo]

        public partial class StatusBarTypes

        {

                [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

                //a default constructor

                public StatusBarTypes()

                {

                }

        }

}

Now, we'll paste in the list from the clipboard.

                //a default constructor

                public StatusBarTypes()

                {

                }

                ID      uniqueidentifier        Unchecked

International   bit     Unchecked

Name    nvarchar(20)    Unchecked

Description     nvarchar(200)   Checked

BarPercent      int     Unchecked

IncludesSteps   varchar(MAX)    Unchecked

                Unchecked

        }

}

Then doctor it into object properties.  DO NOT rename any fields!  Make sure anything that says "Checked" is nullable.

Keep tabs on the string lengths and whether the columns are required or not. (not null means Requred)

                [Key]

                public Guid ID { get; set; }

                [Required]

                public bool International { get; set; }

                [Required]

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string? Description { get; set; }

                [Required]

                public int BarPercent { get; set; }

                public int IncludesSteps { get; set; }

If you want, you can add display info, like prettier column names.

                [Key]

                public Guid ID { get; set; }

                [Required]

                [Display(Name="Intl")]

                public bool International { get; set; }

                [Required]             

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string Description { get; set; }

                [Required]

                [Display(Name="Bar%")]

                public int BarPercent { get; set; }

                [Display(Name="Incl Steps")]

                public int IncludesSteps { get; set; }

OK save it and BUILD.

If all went well, we can…

Controller and View

In Controllers, right click Add, then Controller

Pick MVC5 Controller with Views, Using Entity Framework

Click Add.

Pick your new Model Class.

Make sure it's your correct Database, pick a layout page, and Add.

Note that it autogenerates your controller…

…and your CRUD views.

These Views are ALL WIRED UP and ready to use.  Open Index.cshtml in Visual Studio and run it.

Your empty data table awaits!  Add some records, edit them, delete them, modify them!

Select a List of Column Names in SQL Server

This gives a list of all the tables and columns in the tables in your database.  The TABLES table was included to exclude views.

Select
    COLUMNS.TABLE_SCHEMA, 
    COLUMNS.TABLE_NAME,
    COLUMNS.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
inner join
    INFORMATION_SCHEMA.TABLES
    on Tables.TABLE_CATALOG = Columns.TABLE_CATALOG
        and Tables.TABLE_SCHEMA = Columns.TABLE_SCHEMA
        and Tables.TABLE_NAME = COLUMNS.TABLE_NAME
        and Tables.TABLE_TYPE = 'BASE TABLE'
 

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Dramatically Speed Up Stored Procedures using Temp Tables

If you're wondering how to create a list, or temp table in a SQL Server Stored Procedure, look here.



But what if you're joining to an in-memory table and you experience performance issues?  I had a multiple join against such a table, and found that the query was taking over 12 seconds to complete - causing a timeout ocassionally.  Here was the temp table declaration:




    declare @tempIDs TABLE
    (
        pick_list_id integer

    )
Simple enough, right? Just a long list of integers.  But like I said the entire stored procedure was taking over 12 seconds to execute.  In a database table, my first approach would be to make an index on the column.  So that's what I did in my stored procedure.  It turned out to be incredibly simple and improved performance from 12 seconds to 30 milliseconds.  Looky!




    declare @tempIDs TABLE
    (
        pick_list_id integer not null PRIMARY KEY
    )
That's it! The rest of the stored procedure is exactly the same.  That's a 40000% speed boost!  Not bad for one modified line of code!





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Column Modification Checklist

This is one of those things that I always forget part of, so because I just went through this, I thought I would document what needs to be done to sync your application whenever you modify any column in your database.



This tutorial is designed for:

• MS SQL Server


• Visual Studio (for web or desktop)

Quick Checklist:

1. In SQL Server Management Studio (SSMS)




1. Check for source and destination columns (for instance if widening "Address1" from 40 to 50, make sure all the columns in the order table, address book, Shipping and Billing etc are all the same)


2. Update All Views that depend on this column. (SQL Server does not do this automatically)


3. Update all stored procedures that operate on this column (for instance in and out parameters that access the changed column)




2. In your Desktop App:




1. Check all dataset xsd files to ensure the result column maxlengths are updated.


2. Check all dataset xsd files to ensure the query parameter maxlengths are updated.


3. Ensure all databound textboxes are set to the correct MaxLength.


4. Ensure all DataGridView Columns are set to the correct MaxInputLength.

Updating Views:



There is actually a stored procedure for updating views.  Once you have found a dependent view, just run...

EXECUTE sp_refreshview 'dbo.v_myViewName';

That will take care of it.  Of course if your view is no longer valid because of the change, you'll get an appropriately misleading error message from Microsoft.



Updating Stored Procs:



Your stored procs have a header much like this:




ALTER PROCEDURE [dbo].[StoredProcName]
    -- Add the parameters for the stored procedure here
    @customer varchar(10),
    @PurchaseOrder varchar(15),
    @Address1 varchar(40)
AS

These header parameters and any internally declared variables must be changed to match any column changes.







...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.