Executing Commands that Modify Information in the Database

Executing Commands that Modify Information in the Database You can use the ExecuteNonQuery() method of a Command object to execute any command that doesnt return a result set from the database
Nội dung trích xuất từ tài liệu:
Executing Commands that Modify Information in the DatabaseExecuting Commands that Modify Information in the DatabaseYou can use the ExecuteNonQuery() method of a Command object to execute anycommand that doesnt return a result set from the database. In this section, youll learnhow to use the ExecuteNonQuery() method to execute commands that modifyinformation in the database.You can use the ExecuteNonQuery() method to execute SQL INSERT, UPDATE, andDELETE statements. You can also use the ExecuteNonQuery() method to call storedprocedures that dont return a value, or issue Data Definition Language (DDL) statementssuch as CREATE TABLE and CREATE INDEX. (DDL was covered in Chapter 3,Introduction to the Structured Query Language.) Table 8.8 summarizes theExecuteNonQuery() method. Table 8.8: THE ExecuteNonQuery() METHODMETHOD RETURN DESCRIPTION TYPEExecuteNonQuery() int Used to execute SQL statements that dont return a result set, such as INSERT, UPDATE, and DELETE statements, DDL statements, or stored procedure calls that dont return a result set. The int value returned is the number of database rows affected by the command, if any.Youll learn how to execute INSERT, UPDATE, and DELETE statements, and how toexecute DDL statements in this section. Youll learn how to execute stored procedurecalls later in the Executing SQL Server Stored Procedures section.Executing INSERT, UPDATE, and DELETE Statements Using theExecuteNonQuery() MethodLets take a look at an example that executes an INSERT statement using theExecuteNonQuery() method. First, a Command object is needed:SqlCommand mySqlCommand = mySqlConnection.CreateCommand();Next, you set the CommandText property of your Command object to the INSERTstatement. The following example sets the CommandText property of mySqlCommand toan INSERT statement that adds a row to the Customers table:mySqlCommand.CommandText = INSERT INTO Customers ( + CustomerID, CompanyName + ) VALUES ( + J2COM, Jason Price Corporation + );Finally, you execute the INSERT statement using the ExecuteNonQuery() method:int numberOfRows = mySqlCommand.ExecuteNonQuery();The ExecuteNonQuery() method returns an int value that indicates the number of rowsaffected by the command. In this example, the value returned is the number of rowsadded to the Customers table, which is 1 since one row was added by the INSERTstatement.Lets take a look at an example that executes an UPDATE statement to modify the newrow just added. The following code sets the CommandText property of mySqlCommandto an UPDATE statement that modifies the CompanyName column of the new row, andthen calls the ExecuteNonQuery() method to execute the UPDATE:mySqlCommand.CommandText = UPDATE Customers + SET CompanyName = New Company + WHERE CustomerID = J2COM;numberOfRows = mySqlCommand.ExecuteNonQuery();The ExecuteNonQuery() method returns the number of rows modified by the UPDATEstatement, which is 1 since one row was modified.Finally, lets take a look at an example that executes a DELETE statement to remove thenew row:mySqlCommand.CommandText = DELETE FROM Customers + WHERE CustomerID = J2COM;numberOfRows = mySqlCommand.ExecuteNonQuery();ExecuteNonQuery() returns 1 again because only one row was removed by the DELETEstatement.Listing 8.7 illustrates the use of the ExecuteNonQuery() method to execute the INSERT,UPDATE, and DELETE statements shown in this section. This program features aprocedure named DisplayRow() that retrieves and displays the details of a specified rowfrom the Customers table. DisplayRow() is used in the program to show the result of theINSERT and UPDATE statements.Listing 8.7: EXECUTEINSERTUPDATEDELETE.CS/* ExecuteInsertUpdateDelete.cs illustrates how to use the ExecuteNonQuery() method to run INSERT, UPDATE, and DELETE statements*/using System;using System.Data;using System.Data.SqlClient;class ExecuteInsertUpdateDelete{ public static void DisplayRow( SqlCommand mySqlCommand, string CustomerID ) { mySqlCommand.CommandText = SELECT CustomerID, CompanyName + FROM Customers + WHERE CustomerID = + CustomerID + ; SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { Console.WriteLine(mySqlDataReader[ CustomerID] = + mySqlDataReader[CustomerID]); Console.WriteLine(mySqlDataReader[ CompanyName] = + mySqlDataReader[CompanyName]); } mySqlDataReader.Close(); } public static void Main() { SqlConnection mySqlConnection = new SqlConnection( server=localhost;database=Northwind;uid=sa;pwd=sa );// create a SqlCommand object an ...