ADO.NET 3.5 Cookbook, 2nd Edition

5.10. Inserting Multiple Rows into a Database Table Using T-SQL Row Constructors

Problem

You need to insert multiple rows into a table in a SQL Server 2008 database using a single T-SQL statement.

Solution

Use T-SQL row constructors.

The solution needs a table named RowConstructor in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE RowConstructor(
	    ID int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution creates a T-SQL INSERT statement that inserts three records into the RowConstructor table in the AdoDotNet35Cookbook database. The contents of that table are loaded into a DataTable before and after the insert operation and the contents output to the console.

The C# code in Program.cs in the project InsertMultipleRows is shown in Example 5-17.

Example 5-17. File: Program.cs for InsertMultipleRows solution

using System; using System.Data; using System.Data.SqlClient; namespace InsertMultipleRows { class Program { static void Main(string[] args) { string sqlConnectString = @"Data Source=(local); Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;"; string sqlSelect = "SELECT * FROM RowConstructor"; string sqlInsert = @"INSERT INTO RowConstructor VALUES (1, 'Field1.1', 'Field2.1'), (2, 'Field1.2', 'Field2.2'), (3, 'Field1.3', 'Field2.3')"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); Console.WriteLine("---INITIAL---"); ...