Command Builder & DataTable

Command Builder

The ADO.NET object model not only allows you to define your own updating logic, but it also provides a dynamic updating logic generation similar to that of the ADO cursor engine, using the CommandBuilder object.  If you instantiate a CommandBuilder object and associate it with a DataAdapter object, the CommandBuilder will attempt to generate updating logic based on the query contained in the DataAdapter object’s SelectCommand.

The CommandBuilder can generate updating logic if all the following are true:

·          Your query returns data from only one table

·          That table has a primary key

·          The primary key is included in the results of your query

The disadvantages with using the CommandBuilder are: 

 ·          It doesn’t offer the best possible run-time performance.

·          You can supply your own updating logic in code in less time than it takes the CommandBuilder to request and process the metadata required to generate similar updating logic.

·          The CommandBuilder doesn’t offer options to let you control the updating logic that is generated.

·          You can’t specify the type of optimistic concurrency you want to use. 

A CommandBuilder will not help you submit updates using stored procedures.

Command Builder Example:

public static DataSet SelectSqlRows(string connectionString,

    string queryString, string tableName)

{

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        SqlDataAdapter adapter = new SqlDataAdapter();

        adapter.SelectCommand = new SqlCommand(queryString, connection);

        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        connection.Open();

        DataSet dataSet = new DataSet();

        adapter.Fill(dataSet, tableName);

        //code to modify data in DataSet here

        builder.GetUpdateCommand();

        //Without the SqlCommandBuilder this line would fail

        adapter.Update(dataSet, tableName);

        return dataSet;

    }

}

DataTable Object

A DataSet object is made up of a collection of tables, relationships, and constraints.  In ADO.NET, DataTable objects are used to represent the tables in a DataSet object.  A DataTable object represents one table of in-memory relational data.  The data is local to the .NET application in which it resides, however, can be populated from a data source such as SQL Server or VFP using a DataAdapter.

You can create and use a DataTable independently or as a member of a DataSet object.  DataTable objects can then be used by other .NET Framework objects, including the DataView object.  Access the collection of tables in a DataSet object through the DataSet object’s Tables property.

The schema, or structure, of a table is represented by columns and constraints.  Define the schema of a DataTable object using DataColumn objects, as well as ForeignKeyConstraint and UniqueConstraint objects.  The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values.

If you populate a DataTable object from a database, it will inherit the constraints from the database so you do not have to do all of the work manually.  A DataTable object must also have rows in which to contain and order the data.  The DataRow class represents the actual data contained in the table.  As you access and change the data within a row, the DataRow object maintains both its current and original state.

You can create parent/child relationships between tables within a database, like SQL Server and VFP, using one or more related columns in the tables.  You can create a relationship between DataTable objects using a DataRelation object, which may then be used to return a row’s related child or parent rows.

DataTable Example:

  // Create a DataTable with 5 columns.

    //

    DataTable table = new DataTable();

    table.Columns.Add("Weight", typeof(int));

    table.Columns.Add("Name", typeof(string));

    table.Columns.Add("Breed", typeof(string));

    table.Columns.Add("Size", typeof(char));

    table.Columns.Add("Date", typeof(DateTime));

    //

    // Add data to the DataTable. [This will be dyanmically generated from your app.]

    //

    AddDogRow(table, 57, "Koko", "Shar Pei");

    AddDogRow(table, 130, "Fido", "Bullmastiff");

    AddDogRow(table, 92, "Alex", "Anatolian Shepherd Dog");

    AddDogRow(table, 25, "Charles", "Cavalier King Charles Spaniel");

    AddDogRow(table, 7, "Candy", "Yorkshire Terrier");

/// <summary>

/// Add dog data to the DataTable.

/// </summary>

static DataRow AddDogRow(DataTable table, int weight, string name, string breed)

{

    //

    // This method uses custom code to generate the size type.

    //

    return table.Rows.Add(weight, name, breed, GetSizeChar(weight), DateTime.Now);

}

/// <summary>

/// Get size code for dogs by weight.

/// </summary>

static char GetSizeChar(int weight)

{

    //

    // Custom method for getting size code.

    //

    if (weight > 100)

    {

       return 'B';

    }

    else if (weight > 50)

    {

       return 'M';

    }

    else

    {

       return 'S';

    }

}