Fill any TableAdapter Using Any of Your Select Methods

• Download source code - 106 KB

Introduction

Here, we will see how to fill any TableAdapter DataTable and avoid the dreaded:

Server Error in '/REDB' Application.

Failed to enable constraints. One or more rows contain values 
       violating non-null, unique, or foreign-key constraints.

Background

First, let's get an overview of the TableAdapter. You know what one is and how to use it, or you wouldn't even be here.

• We know the TableAdapter is not a .NET framework object. (It is a VS Designer created object, so don't try to browse for a TableAdapter .NET object.)
• Inherits from System.ComponentModel.Component not DataAdapter.
• Encapsulates a DataAdapter, i.e., SQLDataAdapter, etc.
• Encapsulates a SQLConnection.
• Encapsulates a SQL CommandCollection.

private global::System.Data.SqlClient.SqlDataAdapter _adapter;
private global::System.Data.SqlClient.SqlConnection _connection;
private global::System.Data.SqlClient.SqlCommand[] _commandCollection; 

Using the code

Create a web project. Name it. Save it. Create a DataSet referencing the venerable Northwind database. Allow the DataSet to be saved in the App_Code folder.

Create a ProductsTableAdapter using the Products table. Let's use a sample TableAdapter named ProductsTableAdapter which belongs to the venerable NorthwindDataSet we have already created. (Create it now, I'll wait.)

Create your custom Get and Fill methods. Name one GetProductNames and the other FillProductNames.

When you attempt to call one of your custom Fill methods created in your ProductsTableAdapter that does not include every DataColumn found in the default Select method's DataTable, you end up with this interesting dialog warning message:

This warning does not translate to much until you try to invoke your custom GetProductNames() method that only returns a list of product names.

When you invoke this method using the Configure and Preview provided by Sir Wizard, everything looks great. All the product DataTable columns show up empty except the product name. Great! This is exactly what we are looking for. (Actually, I think most of us expected a list of product names, with no other columns of data.)

Well, we can live with it for now. Let's just fill this thing with data so we can at least use the product names list.

Most of us do something like this:

NorthwindDataSetTableAdapters.ProductsTableAdapter Adaptr = 
new ProductsTableAdapter();  
NorthwindDataSet.ProductsDataTable tbl = 
   new NorthwindDataSet.ProductsDataTable();

Just for good measure, we clear any table constraints that may prevent our table from filling.

tbl.Constraints.Clear();

Now, we invoke the fill method:

tbl=Adaptr.GetProductNames();
//FILL the table and herein lies the problem. 

The dreaded error:

Server Error in '/REDB' Application.

Failed to enable constraints. One or more rows contain values 
violating non-null, unique, or foreign-key constraints.

What in the world just happened? We know it worked in Preview Data. (You are going to scream when I state the obvious, but first, a brief analysis.)

Did we clobber our tbl object with the object returned by the GetProductNames() call? Nope, because that call failed in the Fill method call. Let's review the call stack.

Line 9506:  this.Adapter.SelectCommand = this.CommandCollection[1];

Line 9507:  NorthwindDataSet.ProductsDataTable dataTable = 
            new NorthwindDataSet.ProductsDataTable();
Line 9508:  this.Adapter.Fill(dataTable);
Line 9509:  return dataTable;

Line 9510:

We never made it to the return object.

Also, notice the CommandCollection called the second item in its collection (more barely related information on that later).

Our tbl object was created earlier as a new ProductsDataTable. I did this on purpose. Normally, we would declare a new object and assign to it all in one line of code:

ProductsDataTable tbl = Adaptr.GetProductNames();

VB:

Dim tbl AS ProductsDataTable = Adaptr.GetProductNames()

In either case, the call would have failed.

Since we have our tbl object instantiated as a new ProductsDataTable (the only way we can start working with it), let's fix the most obvious issues:

columns = tbl.Columns; //Get reference to the Columns Collection

foreach (DataColumn dc in columns) {dc.AllowDBNull = true;}

This is the only thing you need to do, because we will now pass in our tbl object to the fill a DataTable method, not the get a DataTable method. ( I warned you would hate the simplicity of it.)

Adaptr.FillProductNames(tbl); //No Errors. Yes!

Console.Write("Row Count is " tbl.Rows.Count.ToString());
Response.Write("Row Count is " tbl.Rows.Count.ToString());

A great article on Dynamic SQL for TableAdapters is available on CodeProject.

Points of Interest

tbl.Constraints.Clear() is not the solution to the problem. AllowDBNull is the actual solution in every case. It took me a few seconds to realize, I almost always returned a DataTable, and never tried to pass in a DataTable to fill. That's when I realized the usefulness of the fill DataTable custom methods.

You can always try to change the schema or create separate TableAdapters to produce a schema consistent with your data, but why bother? This method works very well.

The first Select query you create is the schema basis for every other query. It becomes the default query and its method is decorated with the DataObjectMethodAttribute and Select is set to true. Now, all other queries must use this default schema. This is why you get the warning dialog as shown above.

Notice the check mark next to the SQL. This is the first and default Select query in the TableAdapter. The second query becomes CommandCollection[1] and so on.

Thank you, Yyu've been a wonderful audience.

RickIsWright - www.rickiswright.com.

History

• Published: 5-11-2009.