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
Nội dung trích xuất từ tài liệu:
Using Stored Procedures to Add, Modify, and Remove Rows from the Database phần 2Setting the InsertCommand Property of a DataAdapterThe following example creates a SqlCommand object named myInsertCommand thatcontains 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 isset to ParameterDirection.Output, which indicates that this parameter is an outputparameter. Also, the maximum length of the @MyProductID and @MyUnitPriceparameters is set to 0 in the third parameter to the Add() method. Setting them to 0 is finebecause the maximum length doesnt apply to fixed length types such as numbers, only totypes such as strings.Next, the following example sets the InsertCommand property of mySqlDataAdapter tomyInsertCommand:mySqlDataAdapter.InsertCommand = myInsertCommand;Setting the UpdateCommand Property of a DataAdapterThe following example creates a SqlCommand object named myUpdateCommand thatcontains a call to the UpdateProduct() stored procedure and sets the UpdateCommandproperty 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 DataAdapterThe following example creates a SqlCommand object named myDeleteCommand thatcontains a call to the DeleteProduct() stored procedure and sets the DeleteCommandproperty 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 DataTableIn this section, youll learn how to add a DataRow to a DataTable. First, the followingcode creates a DataSet object named myDataSet and populates it by callingmySqlDataAdapter.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 databaseand copied to myDataSet. The myDataSet object now contains a DataTable namedProducts, which contains the rows retrieved by the following SELECT statement setearlier in the SelectCommand property of mySqlDataAdapter:SELECT ProductID, ProductName, UnitPriceFROM ProductsORDER BY ProductIDTo add a new row to a DataTable object, you use the same four steps as shown earlier inthe section Modifying a DataRow in a DataTable. The following method, namedAddDataRow(), 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 Pr ...