Creating DataAdapter Objects in Visual Studio .NET

Questions That Should Be Asked More Frequently

• Q. Of my three options for creating DataTable objects, which should I use?

• Creating them using code before populating them using a DataAdapter

• Creating them implicitly by calling DataAdapter.Fill

• Creating them by calling DataAdapter.FillSchema

• A. I strongly recommend the first option. As of this writing, creating tables using code is about 20 times faster than calling the FillSchema method of the DataAdapter.

• Q. I created my DataTable objects in code, as you recommended, but now the code that fills my DataTable objects runs much more slowly. Why is that?

• A. I'm so glad you asked. You'll see this slowdown if your DataTable objects have constraints. As you retrieve data from your data store and add rows to your DataTable objects, ADO.NET will validate each new row based on these constraints. Also, constraints, such as primary keys and unique constraints, require ADO.NET to examine your DataTable to ensure that each new row you create does not violate these constraints. This means that you will pay a greater performance penalty as you add more rows to the DataTable.

  Generally speaking, the constraints you create in your DataTable objects also exist in your database. Assuming that's the case, your database has already validated the data that you're going to pull into your DataTable objects. There's no reason to validate the data again. Is there some sort of middle ground that lets you create constraints on your DataTable objects but not pay the performance penalty that comes with validating the data you retrieve using DataAdapter.Fill?

  You could build your DataTable objects without constraints and then populate them using DataAdapter.Fill and then add your constraints, but that's an inelegant solution at best.

  Ah, but the ADO.NET development team anticipated this scenario and provided a more elegant solution. The DataSet class has an EnforceConstraints property. By default, it's set to True, which means that ADO.NET will enforce the constraints in the DataSet. However, you can set this property to False just before you call the Fill method on your DataAdapter objects and then set the property back to True, as shown here:

Visual Basic .NET

ds.EnforceConstraints = False da1.Fill(ds.Tables("Table1")) da2.Fill(ds.Tables("Table2")) ds.EnforceConstraints = True

Visual C# .NET

ds.EnforceConstraints = false; da1.Fill(ds.Tables["Table1"]); da2.Fill(ds.Tables["Table2"]); ds.EnforceConstraints = true;

Now the Fill method will retrieve data as quickly as if you had no constraints on your DataTable objects.