SqlCommandBuilder

This System.Data.SqlClient type helps update SQL Server tables. It eliminates the need to write the commands. It reduces the likelihood of errors.

As with other SQL types, it can be placed in a using-acquisition statement. We get methods like GetInsertCommand on this type. SqlCommandBuilder is used with SqlConnection instances.

Get started

Before we can use the SqlDataAdapter and SqlCommandBuilder, we need to create a database table with the required columns. We use just 1 column for this example (Weight).

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

class Program
{
    static void Main()
    {
        Prepare();

        // Use SqlCommandBuilder.
        using (var con = new SqlConnection( ConsoleApp5.Properties.Settings.Default.test123ConnectionString))
        using (var adapter = new SqlDataAdapter("SELECT * FROM Dogs2", con))
        using (var builder = new SqlCommandBuilder(adapter))
        {
            // Open connection.
            con.Open();

            // Insert with command built from SqlCommandBuilder.
            var insertCommand = builder.GetInsertCommand();
            Debug.WriteLine(insertCommand.CommandText);
            Debug.WriteLine(insertCommand.Parameters);
            // Set first parameter.
            insertCommand.Parameters[0] = new SqlParameter("@p1", 60);
            // Execute.
            insertCommand.ExecuteNonQuery();

            // Display the contents of our database.
            using (var command = new SqlCommand("SELECT * FROM Dogs2", con))
            {
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int weight = reader.GetInt32(0);
                    Debug.WriteLine("Weight = {0}", weight);
                }
            }
        }
    }

    static void Prepare()
    {
        using (SqlConnection con = new SqlConnection( ConsoleApp5.Properties.Settings.Default.test123ConnectionString))
        using (SqlCommand command = new SqlCommand("CREATE TABLE Dogs2 (Weight INT)", con))
        {
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {
                Console.WriteLine("Could not create table.");
            }
        }
    }
}
INSERT INTO [Dogs2] ([Weight]) VALUES (@p1)
System.Data.SqlClient.SqlParameterCollection
Weight = 60

Using SqlConnection

We create a new SqlConnection using the connection string, which was automatically generated. You can use Visual Studio to generate a connection string.

Notes, SqlCommandBuilder

A new SqlCommandBuilder is instantiated. It writes its own SQL commands from the knowledge in the SqlDataAdapter.

Summary

We used the SqlCommandBuilder with other ADO.NET data objects. We added conditionally to a DataTable, inserting the data—without ever writing an INSERT command.