Modifying Data in a Data Source
SQL statements that modify data (such as INSERT, UPDATE, or DELETE) do not return rows. Similarly, many stored procedures perform an action but do not return rows. To execute commands that do not return rows, create a Command object with the appropriate SQL command and a Connection, including any required Parameters. Execute the command with the ExecuteNonQuery method of the Command object.
The ExecuteNonQuery method returns an integer that represents the number of rows affected by the statement or stored procedure that was executed. If multiple statements are executed, the value returned is the sum of the records affected by all of the statements executed.
The following code example executes an INSERT statement to insert a record into a database using ExecuteNonQuery.
// Assumes connection is a valid SqlConnection. connection.Open(); string queryString = "INSERT INTO Customers " + "(CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"; SqlCommand command = new SqlCommand(queryString, connection); Int32 recordsAffected = command.ExecuteNonQuery();
The following code example executes the stored procedure created by the sample code in Performing Catalog Operations. No rows are returned by the stored procedure, so the ExecuteNonQuery method is used, but the stored procedure does receive an input parameter and returns an output parameter and a return value.
For theobject, the ReturnValue parameter must be added to the Parameters collection first.
// Assumes connection is a valid SqlConnection. SqlCommand command = new SqlCommand("InsertCategory" , connection); command.CommandType = CommandType.StoredProcedure; SqlParameter parameter = command.Parameters.Add( "@RowCount", SqlDbType.Int); parameter.Direction = ParameterDirection.ReturnValue; parameter = command.Parameters.Add( "@CategoryName", SqlDbType.NChar, 15); parameter = command.Parameters.Add("@Identity", SqlDbType.Int); parameter.Direction = ParameterDirection.Output; command.Parameters["@CategoryName"].Value = "New Category"; command.ExecuteNonQuery(); Int32 categoryID = (Int32) command.Parameters["@Identity"].Value; Int32 rowCount = (Int32) command.Parameters["@RowCount"].Value;