C# SqlCommandBuilder Example: GetInsertCommandUse SqlCommandBuilder from System.Data to create a command for use with a database.
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.
Important SqlCommandBuilder takes the SELECT command you specify, and generates the SQL commands for SQL Server automatically.
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).
GetInsertCommand We generate an insert command with the GetInsertCommand method. We then assign the first parameter.
C# program that uses SqlCommandBuilder
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.
SqlDataAdapater The data adapter here is used in the SqlCommandBuilder to update the table. You must fill the adapter with Fill.
Notes, SqlCommandBuilder. A new SqlCommandBuilder is instantiated. It writes its own SQL commands from the knowledge in the SqlDataAdapter.
Finally The example calls the Update method on the data adapter. This actually performs the SQL insertion of data.
Note Thanks to Andrew Dennison for pointing out the original code example did not work.
Using statement. You should always use "using," as it ensures that the resources are disposed properly. You can nest "using" statements without added extra curly brackets.
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.