C# SqlCommandBuilder Example

SqlCommandBuilder helps UPDATE SQL Server tables.

It eliminates the need to write the commands. It reduces the likelihood of errors. It can be placed in a using-acquisition statement to eliminate possible memory usage problems.

Tip: SqlCommandBuilder is effective when used with DataTable and SqlConnection instances.

Intro. First, SqlCommandBuilder takes the SELECT command you specify, and generates the SQL commands for SQL Server automatically. This means that instead of writing custom SQL commands, you can simply create an instance of SqlCommandBuilder.

Get started. Before we can use the SqlDataAdapter and SqlCommandBuilder, we need to create a database table with the required columns. These columns will be automatically updated.

Creating the DataTable. In the code example below, a DataTable is created with its parameterless constructor. Five columns are added to the DataTable. The names of these are used by SqlCommandBuilder to generate its SQL.

Here: The example has five fields, which are pictured in the first image. Please see the screenshot at the top.

Method that uses SqlCommandBuilder: C#

static void Main()
{
    //
    // Make sure table is prepared. (You won't need this.)
    //
    Prepare();

    //
    // 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 dynamically 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");

    //
    // Create new SqlConnection, SqlDataAdapter, and builder.
    //
    using (var con = new SqlConnection(
	ConsoleApplication1.Properties.Settings.Default.masterConnectionString))
    using (var adapter = new SqlDataAdapter("SELECT * FROM Dogs2", con))
    using (new SqlCommandBuilder(adapter))
    {
	//
	// Fill the DataAdapter with the values in the DataTable.
	//
	adapter.Fill(table);
	//
	// Open the connection to the SQL database.
	//
	con.Open();
	//
	// Insert the data table into the SQL database.
	//
	adapter.Update(table);
    }
}

Using SqlConnection. Here, we create a new SqlConnection using the connection string, which was automatically generated. For your data-driven web applications, this will be specified as an external source to the SQL Server.

Using SqlDataAdapater. The data adapter here is used in the SqlCommandBuilder to update the table. You must fill the adapter with Fill. This populates the adapter with the data in the DataTable.

In the third using statement, a new SqlCommandBuilder is instantiated. It writes its own SQL commands from the knowledge in the SqlDataAdapter. The SqlCommandBuilder just needs to be created. It does not need to used.

Finally: The example calls the Update method on the data adapter. This actually performs the SQL insertion of data.

And: The five Dog rows, created previously and stored in the DataTable, are now inside your database.

The benefits of using the DataTable in the example are that you do not need to loop over an INSERT command, and use the SqlTransaction. The results of the operation are the same, but there is less chance for error here.

Add data logic. We also use some logic to generate the DataRows and add them to the DataTable. This is not core to the SqlCommandBuilder, but it makes the example more realistic and practical. It improves the overall code quality.

Note: Your C# code will almost certainly use some kind of logic before INSERTing into the DataTable.

We see how the DataTable's rows are added. The second method above simply applies a conditional logic structure and returns a CHAR that we want to add to the DataTable, and later the database server.

Method that uses Rows.Add: C#

/// <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';
    }
}

Using statement. Many excellent resources on the Internet and in print, such as MSDN, do not consistently use the using statement with the "Sql" classes. You should always use "using," as it ensures that the resources are disposed properly.

Note: In the example, we also see how you can nest "using" statements without added extra { } curly brackets.

Using

Example. Below we see the complete program. You will need to set up the correct connection string in this program before it will work. This is a good starting point for using SqlCommandBuilder.

Complete program: C#

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
	//
	// Make sure table is prepared. (You won't need this.)
	//
	Prepare();

	//
	// Create a DataTable with five 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 dynamically 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");

	//
	// Create new SqlConnection, SqlDataAdapter, and builder.
	//
	using (var con = new SqlConnection(
	    ConsoleApplication1.Properties.Settings.Default.masterConnectionString))
	using (var adapter = new SqlDataAdapter("SELECT * FROM Dogs2", con))
	using (new SqlCommandBuilder(adapter))
	{
	    //
	    // Fill the DataAdapter with the values in the DataTable.
	    //
	    adapter.Fill(table);
	    //
	    // Open the connection to the SQL database.
	    //
	    con.Open();
	    //
	    // Update the SQL database table with the values.
	    //
	    adapter.Update(table);
	}
    }

    /// <summary>
    /// This method attempts to create the Dogs2 SQL table.
    /// </summary>
    static void Prepare()
    {
	//
	// Make sure Dogs2 table is created.
	//
	using (SqlConnection con = new SqlConnection(
	    ConsoleApplication1.Properties.Settings.Default.masterConnectionString))
	using (SqlCommand command = new SqlCommand(
		   "CREATE TABLE Dogs2 (Weight INT, Name TEXT, Breed TEXT, Size CHAR(1), Date DATETIME)",
		   con))
	{
	    con.Open();
	    try
	    {
		command.ExecuteNonQuery();
	    }
	    catch
	    {
		Console.WriteLine("Could not create table.");
	    }
	}
    }

    /// <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';
	}
    }
}

Summary. We used the SqlCommandBuilder with other ADO.NET data objects. We saw an example of how you can add data conditionally to your DataTable, finally INSERTing the data—without ever writing an INSERT command.

Finally: We saw correct disposal of the system database resources. This may reduce bugs in your code.

Related Links