Export (0) Print
Expand All

Modifying Data in a Database

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 Connection (and any required Parameters), and use 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.

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
                                                   "Initial Catalog=northwind")
nwindConn.Open()

Dim insertStr As String = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"

Dim insertCMD As SqlCommand = New SqlCommand(insertStr, nwindConn)
Dim recordsAffected As Int32 = insertCMD.ExecuteNonQuery()
[C#]SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;" +
                                            "Initial Catalog=northwind");
nwindConn.Open();

string insertStr = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";

SqlCommand insertCMD = new SqlCommand(insertStr, nwindConn);
Int32 recordsAffected = insertCMD.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 the OleDbCommand object, the ReturnValue parameter must be added to the Parameters collection first.

Dim insertCatCMD As SqlCommand = New SqlCommand("InsertCategory" , nwindConn)
insertCatCMD.CommandType = CommandType.StoredProcedure

Dim workParm As SqlParameter

workParm = insertCatCMD.Parameters.Add("@RowCount", SqlDbType.Int)
workParm.Direction = ParameterDirection.ReturnValue

workParm = insertCatCMD.Parameters.Add("@CategoryName", SqlDbType.NChar, 15)

workParm = insertCatCMD.Parameters.Add("@Identity", SqlDbType.Int)
workParm.Direction = ParameterDirection.Output

insertCatCMD.Parameters("@CategoryName").Value = "New Category"
insertCatCMD.ExecuteNonQuery()

Dim catID As Int32 = CInt(insertCatCMD.Parameters("@Identity").Value)
Dim rowCount As Int32 = CInt(insertCatCMD.Parameters("@RowCount").Value) 
[C#]SqlCommand insertCatCMD = new SqlCommand("InsertCategory" , nwindConn);
insertCatCMD.CommandType = CommandType.StoredProcedure;

SqlParameter workParm;

workParm = insertCatCMD.Parameters.Add("@RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue;

workParm = insertCatCMD.Parameters.Add("@CategoryName", SqlDbType.NChar, 15);

workParm = insertCatCMD.Parameters.Add("@Identity", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;

insertCatCMD.Parameters["@CategoryName"].Value = "New Category";
insertCatCMD.ExecuteNonQuery();

Int32 catID = (Int32)insertCatCMD.Parameters["@Identity"].Value;
Int32 rowCount = (Int32)insertCatCMD.Parameters["@RowCount"].Value;

See Also

Using .NET Framework Data Providers to Access Data | Performing Database Operations and Modifying Data | OleDbCommand Class | OleDbConnection Class | OdbcCommand Class | OdbcConnection Class | SqlCommand Class | SqlConnection Class

Show:
© 2014 Microsoft