Home
C#
SqlCommandBuilder Example: GetInsertCommand
Updated Dec 24, 2024
Dot Net Perls
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).
DataTable
Detail We generate an insert command with the GetInsertCommand method. We then assign the first parameter.
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.
Info 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.
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.
Dot Net Perls is a collection of pages with code examples, which are updated to stay current. Programming is an art, and it can be learned from examples.
Donate to this site to help offset the costs of running the server. Sites like this will cease to exist if there is no financial support for them.
Sam Allen is passionate about computer languages, and he maintains 100% of the material available on this website. He hopes it makes the world a nicer place.
This page was last updated on Dec 24, 2024 (simplify).
Home
Changes
© 2007-2025 Sam Allen