ADO.NET 3.5 Cookbook, 2nd Edition

3.1. Executing a Query That Does Not Return a Result Set

Problem

You need to execute a query or command that does not return a result set.

Solution

Use the ExecuteNonQuery() method of the Command object.

The T-SQL statement to create the table ExecuteQueryNoResultSet used in this solution is shown in Example 3-1.

Example 3-1. Stored Procedure: Create table ExecuteQueryNoResultSet

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

And here is the T-SQL statement to add sample data to the table ExecuteQueryNoResultSet:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO Execute QueryNoResultSet
	    VALUES (1, 'Field 1.1', 'Field 2.1');
	INSERT INTO ExecuteQueryNoResultSet
	    VALUES (2, 'Field 1.2', 'Field 2.2');
	INSERT INTO ExecuteQueryNoResultSet
	    VALUES (3, 'Field 1.3', 'Field 2.3');

The solution creates a connection and executes a T-SQL statement using the ExecuteNonQuery() method to delete a single row from the table ExecuteQueryNoResultSet. The number of rows affected is output to the console.

The C# code in Program.cs in the project ExecuteQueryNoResultSet is shown in Example 3-2.

Example 3-2. File: Program.cs for ExecuteQueryNoResultSet solution

using System; using System.Data; using System.Data.SqlClient; namespace ExecuteQueryNoResultSet { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);Integrated security=SSPI;" + "Initial Catalog=AdoDotNet35Cookbook;"; ...