C# SqlCommandBuilder Example: GetInsertCommand

Use 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).DataTable Debug.Write

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."); } } } } Output 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.

SqlDataAdapter

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.Using

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.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# SqlCommandBuilder Example: GetInsertCommand

Related Links:

.Net

Java

Related Links