Using Stored Procedures to Add, Modify, and Remove Rows from the Database phần 2

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:8

Thêm vào BST

Báo xấu

119
lượt xem 22
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Setting the InsertCommand Property of a DataAdapter The following example creates a SqlCommand object named myInsertCommand that contains a call to the AddProduct4() stored procedure

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Using Stored Procedures to Add, Modify, and Remove Rows from the Database phần 2

Setting the InsertCommand Property of a DataAdapter The following example creates a SqlCommand object named myInsertCommand that contains a call to the AddProduct4() stored procedure: SqlCommand myInsertCommand = mySqlConnection.CreateCommand(); myInsertCommand.CommandText = "EXECUTE @MyProductID = AddProduct4 @MyProductName, @MyUnitPrice"; myInsertCommand.Parameters.Add( "@MyProductID", SqlDbType.Int, 0, "ProductID"); myInsertCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; myInsertCommand.Parameters.Add( "@MyProductName", SqlDbType.NVarChar, 40, "ProductName"); myInsertCommand.Parameters.Add( "@MyUnitPrice", SqlDbType.Money, 0, "UnitPrice"); As you can see from the previous code, the direction of the @MyProductID parameter is set to ParameterDirection.Output, which indicates that this parameter is an output parameter. Also, the maximum length of the @MyProductID and @MyUnitPrice parameters is set to 0 in the third parameter to the Add() method. Setting them to 0 is fine because the maximum length doesn't apply to fixed length types such as numbers, only to types such as strings. Next, the following example sets the InsertCommand property of mySqlDataAdapter to myInsertCommand: mySqlDataAdapter.InsertCommand = myInsertCommand; Setting the UpdateCommand Property of a DataAdapter The following example creates a SqlCommand object named myUpdateCommand that contains a call to the UpdateProduct() stored procedure and sets the UpdateCommand property of mySqlDataAdapter to myUpdateCommand: SqlCommand myUpdateCommand = mySqlConnection.CreateCommand(); myUpdateCommand.CommandText = "EXECUTE UpdateProduct @OldProductID, @NewProductName, " + "@NewUnitPrice, @OldProductName, @OldUnitPrice"; myUpdateCommand.Parameters.Add( "@OldProductID", SqlDbType.Int, 0, "ProductID"); myUpdateCommand.Parameters.Add( "@NewProductName", SqlDbType.NVarChar, 40, "ProductName");
myUpdateCommand.Parameters.Add( "@NewUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myUpdateCommand.Parameters.Add( "@OldProductName", SqlDbType.NVarChar, 40, "ProductName"); myUpdateCommand.Parameters.Add( "@OldUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myUpdateCommand.Parameters["@OldProductID"].SourceVersion = DataRowVersion.Original; myUpdateCommand.Parameters["@OldProductName"].SourceVersion = DataRowVersion.Original; myUpdateCommand.Parameters["@OldUnitPrice"].SourceVersion = DataRowVersion.Original; mySqlDataAdapter.UpdateCommand = myUpdateCommand; Setting the DeleteCommand Property of a DataAdapter The following example creates a SqlCommand object named myDeleteCommand that contains a call to the DeleteProduct() stored procedure and sets the DeleteCommand property of mySqlDataAdapter to myDeleteCommand: SqlCommand myDeleteCommand = mySqlConnection.CreateCommand(); myDeleteCommand.CommandText = "EXECUTE DeleteProduct @OldProductID, @OldProductName, @OldUnitPrice"; myDeleteCommand.Parameters.Add( "@OldProductID", SqlDbType.Int, 0, "ProductID"); myDeleteCommand.Parameters.Add( "@OldProductName", SqlDbType.NVarChar, 40, "ProductName"); myDeleteCommand.Parameters.Add( "@OldUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myDeleteCommand.Parameters["@OldProductID"].SourceVersion = DataRowVersion.Original; myDeleteCommand.Parameters["@OldProductName"].SourceVersion = DataRowVersion.Original; myDeleteCommand.Parameters["@OldUnitPrice"].SourceVersion = DataRowVersion.Original; mySqlDataAdapter.DeleteCommand = myDeleteCommand; This completes the setup of the DataAdapter object. Adding a DataRow to a DataTable
In this section, you'll learn how to add a DataRow to a DataTable. First, the following code creates a DataSet object named myDataSet and populates it by calling mySqlDataAdapter.Fill(): DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numOfRows = mySqlDataAdapter.Fill(myDataSet, "Products"); mySqlConnection.Close(); The int returned by the Fill() method is the number of rows retrieved from the database and copied to myDataSet. The myDataSet object now contains a DataTable named Products, which contains the rows retrieved by the following SELECT statement set earlier in the SelectCommand property of mySqlDataAdapter: SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID To add a new row to a DataTable object, you use the same four steps as shown earlier in the section "Modifying a DataRow in a DataTable." The following method, named AddDataRow(), uses those steps to add a new row to a DataTable: public static int AddDataRow( DataTable myDataTable, SqlDataAdapter mySqlDataAdapter, SqlConnection mySqlConnection ) { Console.WriteLine("\nIn AddDataRow()"); // step 1: use the NewRow() method of the DataTable to // create a new DataRow Console.WriteLine("Calling myDataTable.NewRow()"); DataRow myNewDataRow = myDataTable.NewRow(); Console.WriteLine("myNewDataRow.RowState = " + myNewDataRow.RowState); // step 2: set the values for the DataColumn objects of // the new DataRow myNewDataRow["ProductName"] = "Widget"; myNewDataRow["UnitPrice"] = 10.99;
// step 3: use the Add() method through the Rows property // to add the new DataRow to the DataTable Console.WriteLine("Calling myDataTable.Rows.Add()"); myDataTable.Rows.Add(myNewDataRow); Console.WriteLine("myNewDataRow.RowState = " + myNewDataRow.RowState); // step 4: use the Update() method to push the new // row to the database Console.WriteLine("Calling mySqlDataAdapter.Update()"); mySqlConnection.Open(); int numOfRows = mySqlDataAdapter.Update(myDataTable); mySqlConnection.Close(); Console.WriteLine("numOfRows = " + numOfRows); Console.WriteLine("myNewDataRow.RowState = " + myNewDataRow.RowState); DisplayDataRow(myNewDataRow, myDataTable); // return the ProductID of the new DataRow return (int) myNewDataRow["ProductID"]; } Notice that no value for the ProductID DataColumn is set in step 2. This is because the ProductID is automatically generated by the database when the new row is pushed to the database by the Update() method in step 4. When the Update() method is called, the AddProduct4() stored procedure is run to add the new row to the Products table. The database then generates a new ProductID for the row, which is then returned by the AddProduct4() stored procedure. You can then read the new ProductID using myNewDataRow["ProductID"], which now contains the new ProductID. This ProductID is then returned at the end of the AddDataRow() method. The output from AddDataRow() and its call to DisplayDataRow() are as follows: In AddDataRow() Calling myDataTable.NewRow() myNewDataRow.RowState = Detached Calling myDataTable.Rows.Add() myNewDataRow.RowState = Added Calling mySqlDataAdapter.Update() numOfRows = 1 myNewDataRow.RowState = Unchanged
In DisplayDataRow() ProductID = 180 ProductName = Widget UnitPrice = 10.99 As you can see, after myDataTable.NewRow() is called to create myNewDataRow its RowState is Detached, which indicates myNewDataRow isn't yet part of myDataTable. Next, myDataTable.Rows.Add() is called to add myNewDataRow to myDataTable. This causes the RowState of myNewDataRow to change to Added, which indicates myNewDataRow has been added to myDataTable. Finally, mySqlDataAdapter.Update() is called to push the new row to the database. The AddProduct4() stored procedure is run to add the new row to the Products table, and the RowState of myNewDataRow changes to Unchanged. Modifying a DataRow in a DataTable The following method, named ModifyDataRow(), uses four steps to modify a DataRow in a DataTable object. Notice that the ProductID to modify is passed as a parameter: public static void ModifyDataRow( DataTable myDataTable, int productID, SqlDataAdapter mySqlDataAdapter, SqlConnection mySqlConnection ) { Console.WriteLine("\nIn ModifyDataRow()"); // step 1: set the PrimaryKey property of the DataTable myDataTable.PrimaryKey = new DataColumn[] { myDataTable.Columns["ProductID"] }; // step 2: use the Find() method to locate the DataRow // in the DataTable using the primary key value DataRow myEditDataRow = myDataTable.Rows.Find(productID); // step 3: change the DataColumn values of the DataRow myEditDataRow["ProductName"] = "Advanced Widget";
myEditDataRow["UnitPrice"] = 24.99; Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState); Console.WriteLine("myEditDataRow[\" ProductID\", " + "DataRowVersion.Original] = " + myEditDataRow["ProductID", DataRowVersion.Original]); Console.WriteLine("myEditDataRow[\" ProductName\", " + "DataRowVersion.Original] = " + myEditDataRow["ProductName", DataRowVersion.Original]); Console.WriteLine("myEditDataRow[\" UnitPrice\", " + "DataRowVersion.Original] = " + myEditDataRow["UnitPrice", DataRowVersion.Original]); Console.WriteLine("myEditDataRow[\" ProductName\", " + "DataRowVersion.Current] = " + myEditDataRow["ProductName", DataRowVersion.Current]); Console.WriteLine("myEditDataRow[\" UnitPrice\", " + "DataRowVersion.Current] = " + myEditDataRow["UnitPrice", DataRowVersion.Current]); // step 4: use the Update() method to push the update // to the database Console.WriteLine("Calling mySqlDataAdapter.Update()"); mySqlConnection.Open(); int numOfRows = mySqlDataAdapter.Update(myDataTable); mySqlConnection.Close(); Console.WriteLine("numOfRows = " + numOfRows); Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState); DisplayDataRow(myEditDataRow, myDataTable); } Notice this method displays the original values for the ProductID, ProductName, and UnitPrice DataColumn objects using the DataRowVersion.Original constant. These are the DataColumn values before they are changed. The method also displays the current values for the ProductName and UnitPrice DataColumn objects using the DataRowVersion.Current constant. These are the DataColumn values after they are changed. When the Update() method is called in step 4, the UpdateProduct() stored procedure is run behind the scenes to perform the update. The output from ModifyDataRow() and its call to DisplayDataRow() is as follows: In ModifyDataRow()
myEditDataRow.RowState = Modified myEditDataRow["ProductID", DataRowVersion.Original] = 180 myEditDataRow["ProductName", DataRowVersion.Original] = Widget myEditDataRow["UnitPrice", DataRowVersion.Original] = 10.99 myEditDataRow["ProductName", DataRowVersion.Current] = Advanced Widget myEditDataRow["UnitPrice", DataRowVersion.Current] = 24.99 Calling mySqlDataAdapter.Update() numOfRows = 1 myEditDataRow.RowState = Unchanged In DisplayDataRow() ProductID = 180 ProductName = Advanced Widget UnitPrice = 24.99 Notice that the RowState property of myEditDataRow changes to Modified after it is changed, and then to Unchanged after mySqlDataAdapter.Update() is called. Removing a DataRow from a DataTable The following method, named RemoveDataRow(), uses four steps to remove a DataRow from a DataTable. Notice that the ProductID to modify is passed as a parameter: public static void RemoveDataRow( DataTable myDataTable, int productID, SqlDataAdapter mySqlDataAdapter, SqlConnection mySqlConnection ) { Console.WriteLine("\nIn RemoveDataRow()"); // step 1: set the PrimaryKey property of the DataTable myDataTable.PrimaryKey = new DataColumn[] { myDataTable.Columns["ProductID"] }; // step 2: use the Find() method to locate the DataRow DataRow myRemoveDataRow = myDataTable.Rows.Find(productID); // step 3: use the Delete() method to remove the DataRow
Console.WriteLine("Calling myRemoveDataRow.Delete()"); myRemoveDataRow.Delete(); Console.WriteLine("myRemoveDataRow.RowState = " + myRemoveDataRow.RowState); // step 4: use the Update() method to push the delete // to the database Console.WriteLine("Calling mySqlDataAdapter.Update()"); mySqlConnection.Open(); int numOfRows = mySqlDataAdapter.Update(myDataTable); mySqlConnection.Close(); Console.WriteLine("numOfRows = " + numOfRows); Console.WriteLine("myRemoveDataRow.RowState = " + myRemoveDataRow.RowState); } The output from RemoveDataRow() is as follows: In RemoveDataRow() Calling myRemoveDataRow.Delete() myRemoveDataRow.RowState = Deleted Calling mySqlDataAdapter.Update() numOfRows = 1 myRemoveDataRow.RowState = Detached Notice that the RowState property of myRemoveDataRow is set to Deleted after myRemoveData .Delete() is called, and then to Detached after mySqlDataAdapter.Update() is called. When the Update() method is called in step 4, the DeleteProduct() stored procedure is run behind the scenes to perform the delete. Note You'll find a complete program named PushChangesUsingProcedures.cs in the ch11 directory that illustrates the use of the AddDataRow(), ModifyDataRow(), and RemoveDataRow() methods. This listing is omitted from this book for brevity.