VistaDB 5
Gibraltar VistaDB > Developer's Guide > How To Perform Common Tasks > How To - Use the VistaDBCommandBuilder
How To - Use the VistaDBCommandBuilder

Are you sick and tired of writing INSERT/UPDATE/DELETE statements all the time?

The CommandBuilder object was designed to do this for you! I think this object is probably one of the most underused tools in the Dot Net database programmers tool belt. Most books do not seem to discuss it for some reason, or if they do they don't explain WHY you would use it so novice programmers skip it.

The CommandBuilder object was built to automate the tedious and error prone task of writing manual INSERT / UPDATE / DELETE statements. You could do all of these by hand, but then every time the schema of your database changed you would have to remember to edit all these commands also. Using the CommandBuilder can save you from this task.

Look in the NUnit tests for example usage of the CommandBuilder object.

Remarks

Ordering can sometimes be important! Take this example with AutoIncrementSeed and Step entries on a DataSet.
The use of these in the incorrect order can lead to a very subtle bug that we spent a few days tracking down with a customer. I could not believe the subtlety of the bug so I felt a strong need to document this for others to hopefully find.

AutoIncrement information from MSDN

This is from that article in case the link does not work.

When modifying the AutoIncrement* properties of a column after the table has been filled, it is important to set the AutoIncrementStep first, before the AutoIncrementSeed. This is because the effect of setting the AutoIncrementSeed changes based on the value of AutoIncrementStep.

For example, a common pattern is to set AutoIncrementSeed and AutoIncrementStep to -1, so that there is no chance of new rows conflicting with existing rows. However, if you set the AutoIncrementSeed first, and then the AutoIncrementStep, the value of AutoIncrementSeed is ignored. This is the progression that illustrates the problem:

  1. The table is created during a call to DbAdapter.Fill. At this point, the AutoIncrementSeed is 0 and the AutoIncrementStep is 1.
  2. As rows are added to the table during the fill, the value of an internal variable called autoIncrementCurrent is incremented so that it is always at least 1 greater than the greatest existing value in the column.
  3. AutoIncrementSeed is set to -1. At this point, the column compares the new value of AutoIncrementSeed to the value of autoIncrementCurrent. Since autoIncrementCurrent is greater than AutoIncrementSeed, AND AutoIncrementStep is greater than 0, the column assumes that the generated values have already passed the seed value, and so it does NOT reset autoIncrementCurrent.
  4. AutoIncrementStep is set to -1.
  5. A new row is inserted. The value of the identity column is set to autoIncrementCurrent + AutoIncrementStep, which will be equal to the greatest value currently in the table, causing a constraint violation.

By setting the AutoIncrementStep to -1 first, when the AutoIncrementSeed is set, the column sees that the new seed value is "farther along" (i.e. more negative) than autoIncrementCurrent, and so it resets autoIncrementCurrent to the new value of AutoIncrementSeed.