Adding, Updating, and Deleting Related Rows In this section, you'll learn how to make changes in

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

Thêm vào BST

Báo xấu

93
lượt xem 6
download

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

Adding, Updating, and Deleting Related Rows In this section, you'll learn how to make changes in DataTable objects that store rows from the Customers and Orders tables

Chủ đề:

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

Lưu

Nội dung Text: Adding, Updating, and Deleting Related Rows In this section, you'll learn how to make changes in

Adding, Updating, and Deleting Related Rows In this section, you'll learn how to make changes in DataTable objects that store rows from the Customers and Orders tables. These tables are related through the CustomerID foreign key. As you'll see, you must push changes to the underlying database tables in a specific order. If you don't, your program will throw an exception. Note You'll find all the code examples shown in this section in the ModifyingRelatedData.cs program. Setting Up the DataAdapter Objects You'll need two DataAdapter objects: • One to work with the Customers table, which will be named customersDA. • One to work with the Orders table, which will be named ordersDA. Let's take a look at setting up these two DataAdapter objects. Setting Up the customersDA DataAdapter The following code creates and sets up a DataAdapter named customersDA that contains the necessary SELECT, INSERT, UPDATE, and DELETE statements to access the Customers table: SqlDataAdapter customersDA = new SqlDataAdapter(); // create a SqlCommand object to hold the SELECT SqlCommand customersSelectCommand = mySqlConnection.CreateCommand(); customersSelectCommand.CommandText = "SELECT CustomerID, CompanyName " + "FROM Customers"; // create a SqlCommand object to hold the INSERT SqlCommand customersInsertCommand = mySqlConnection.CreateCommand(); customersInsertCommand.CommandText = "INSERT INTO Customers (" + " CustomerID, CompanyName " + ") VALUES (" + " @CustomerID, @CompanyName" + ")"; customersInsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar,
5, "CustomerID"); customersInsertCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); // create a SqlCommand object to hold the UPDATE SqlCommand customersUpdateCommand = mySqlConnection.CreateCommand(); customersUpdateCommand.CommandText = "UPDATE Customers " + "SET " + " CompanyName = @NewCompanyName " + "WHERE CustomerID = @OldCustomerID " + "AND CompanyName = @OldCompanyName"; customersUpdateCommand.Parameters.Add("@NewCompanyName", SqlDbType.NVarChar, 40, "CompanyName"); customersUpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID"); customersUpdateCommand.Parameters.Add("@OldCompanyName", SqlDbType.NVarChar, 40, "CompanyName"); customersUpdateCommand.Parameters["@OldCustomerID"].SourceVersion = DataRowVersion.Original; customersUpdateCommand.Parameters["@OldCompanyName"].SourceVersion = DataRowVersion.Original; // create a SqlCommand object to hold the DELETE SqlCommand customersDeleteCommand = mySqlConnection.CreateCommand(); customersDeleteCommand.CommandText = "DELETE FROM Customers " + "WHERE CustomerID = @OldCustomerID " + "AND CompanyName = @OldCompanyName"; customersDeleteCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID"); customersDeleteCommand.Parameters.Add("@OldCompanyName", SqlDbType.NVarChar, 40, "CompanyName"); customersDeleteCommand.Parameters["@OldCustomerID"].SourceVersion = DataRowVersion.Original; customersDeleteCommand.Parameters["@OldCompanyName"].SourceVersion = DataRowVersion.Original; // set the customersDA properties // to the SqlCommand objects previously created customersDA.SelectCommand = customersSelectCommand; customersDA.InsertCommand = customersInsertCommand; customersDA.UpdateCommand = customersUpdateCommand;
customersDA.DeleteCommand = customersDeleteCommand; Notice that the UPDATE statement modifies only the CompanyName column value; it doesn't modify the CustomerID primary key column value. You'll learn about the issues involved with updating a primary key column value later in the section "Issues When Updating the Primary Key of a Parent Row." The ModifyingRelatedData.cs program contains a method named SetupCustomersDA() that performs the previous code. Setting Up the ordersDA DataAdapter The following code creates and sets up a DataAdapter object named ordersDA that contains the necessary SELECT, INSERT, UPDATE, and DELETE statements to access the Orders table; notice that the ordersInsertCommand contains both an INSERT statement and a SELECT statement to retrieve the new OrderID column, which is an identity column that has a value automatically generated by the database: SqlDataAdapter ordersDA = new SqlDataAdapter(); // create a SqlCommand object to hold the SELECT SqlCommand ordersSelectCommand = mySqlConnection.CreateCommand(); ordersSelectCommand.CommandText = "SELECT OrderID, CustomerID, ShipCountry " + "FROM Orders"; // create a SqlCommand object to hold the INSERT SqlCommand ordersInsertCommand = mySqlConnection.CreateCommand(); ordersInsertCommand.CommandText = "INSERT INTO Orders (" + " CustomerID, ShipCountry " + ") VALUES (" + " @CustomerID, @ShipCountry" + ");" + "SELECT @OrderID = SCOPE_IDENTITY();"; ordersInsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); ordersInsertCommand.Parameters.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry"); ordersInsertCommand.Parameters.Add("@OrderID", SqlDbType.Int, 0, "OrderID"); ordersInsertCommand.Parameters["@OrderID"].Direction = ParameterDirection.Output;
// create a SqlCommand object to hold the UPDATE SqlCommand ordersUpdateCommand = mySqlConnection.CreateCommand(); ordersUpdateCommand.CommandText = "UPDATE Orders " + "SET " + " ShipCountry = @NewShipCountry " + "WHERE OrderID = @OldOrderID " + "AND CustomerID = @OldCustomerID " + "AND ShipCountry = @OldShipCountry"; ordersUpdateCommand.Parameters.Add("@NewShipCountry", SqlDbType.NVarChar, 15, "ShipCountry"); ordersUpdateCommand.Parameters.Add("@OldOrderID", SqlDbType.Int, 0, "OrderID"); ordersUpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID"); ordersUpdateCommand.Parameters.Add("@OldShipCountry", SqlDbType.NVarChar, 15, "ShipCountry"); ordersUpdateCommand.Parameters["@OldOrderID"].SourceVersion = DataRowVersion.Original; ordersUpdateCommand.Parameters["@OldCustomerID"].SourceVersion = DataRowVersion.Original; ordersUpdateCommand.Parameters["@OldShipCountry"].SourceVersion = DataRowVersion.Original; // create a SqlCommand object to hold the DELETE SqlCommand ordersDeleteCommand = mySqlConnection.CreateCommand(); ordersDeleteCommand.CommandText = "DELETE FROM Orders " + "WHERE OrderID = @OldOrderID " + "AND CustomerID = @OldCustomerID " + "AND ShipCountry = @OldShipCountry"; ordersDeleteCommand.Parameters.Add("@OldOrderID", SqlDbType.Int, 0, "OrderID"); ordersDeleteCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID"); ordersDeleteCommand.Parameters.Add("@OldShipCountry", SqlDbType.NVarChar, 15, "ShipCountry"); ordersDeleteCommand.Parameters["@OldOrderID"].SourceVersion = DataRowVersion.Original; ordersDeleteCommand.Parameters["@OldCustomerID"].SourceVersion = DataRowVersion.Original; ordersDeleteCommand.Parameters["@OldShipCountry"].SourceVersion =
DataRowVersion.Original; // set the ordersDA properties // to the SqlCommand objects previously created ordersDA.SelectCommand = ordersSelectCommand; ordersDA.InsertCommand = ordersInsertCommand; ordersDA.UpdateCommand = ordersUpdateCommand; ordersDA.DeleteCommand = ordersDeleteCommand; The ModifyingRelatedData.cs program contains a method named SetupOrdersDA() that performs the previous code. Creating and Populating a DataSet Next, the following example creates and populates a DataSet named myDataSet with the rows from the Customers and Orders tables using customersDA and ordersDA: DataSet myDataSet = new DataSet(); mySqlConnection.Open(); customersDA.Fill(myDataSet, "Customers"); ordersDA.Fill(myDataSet, "Orders"); mySqlConnection.Close(); DataTable customersDT = myDataSet.Tables["Customers"]; DataTable ordersDT = myDataSet.Tables["Orders"]; Notice that the DataTable objects are named customersDT and ordersDT. The following examples set the PrimaryKey properties of customersDT and ordersDT: customersDT.PrimaryKey = new DataColumn[] { customersDT.Columns["CustomerID"] }; ordersDT.PrimaryKey = new DataColumn[] { ordersDT.Columns["OrderID"] }; The following example sets up the OrderID DataColumn of ordersDT as an identity:
ordersDT.Columns["OrderID"].AllowDBNull = false; ordersDT.Columns["OrderID"].AutoIncrement = true; ordersDT.Columns["OrderID"].AutoIncrementSeed = -1; ordersDT.Columns["OrderID"].AutoIncrementStep = -1; ordersDT.Columns["OrderID"].ReadOnly = true; ordersDT.Columns["OrderID"].Unique = true; The final example adds a DataRelation to myDataSet that specifies a relationship between customersDT and ordersDT using the CustomerID DataColumn: DataRelation customersOrdersDataRel = new DataRelation( "CustomersOrders", customersDT.Columns["CustomerID"], ordersDT.Columns["CustomerID"] ); myDataSet.Relations.Add( customersOrdersDataRel ); The ModifyingRelatedData.cs program performs the previous code in the Main() method. Adding DataRow Objects to customersDT and ordersDT The following example adds a DataRow named customerDR to customersDT; notice that the CustomerID is set to J6COM: DataRow customerDR = customersDT.NewRow(); customerDR["CustomerID"] = "J6COM"; customerDR["CompanyName"] = "J6 Company"; customersDT.Rows.Add(customerDR); The next example adds a DataRow named orderDR to ordersDT; notice that the CustomerID is also set to J6COM, indicating that this is the child DataRow for the previous DataRow in customersDT: DataRow orderDR = ordersDT.NewRow(); orderDR["CustomerID"] = "J6COM"; orderDR["ShipCountry"] = "England"; ordersDT.Rows.Add(orderDR);
Because the OrderID DataColumn of ordersDT is set up as an identity, it will automatically be assigned the initial value of -1. When this DataRow is pushed to the database, the SELECT statement in ordersDA will set the OrderID to the identity value generated by the database for the new row in the Orders table. You'll see how to push the changes to the database shortly in the section "Pushing Changes in customersDT and ordersDT to the Database." The ModifyingRelatedData.cs program performs the previous code in the Main() method. Updating DataRow Objects in customersDT and ordersDT The following example updates the CompanyName in customerDR to Widgets Inc.: customerDR["CompanyName"] = "Widgets Inc."; The next example updates the ShipCountry in orderDR to USA: orderDR["ShipCountry"] = "USA"; The ModifyingRelatedData.cs program performs the previous code in the Main() method. Deleting DataRow Objects from customersDT and ordersDT The following example deletes the customerDR DataRow from the customersDT DataTable: customerDR.Delete(); Earlier in the section "Examining the Constraints Created by the DataRelation," you saw that a ForeignKeyConstraint is added to the child DataTable by default when a DataRelation object is added to a DataSet. You also saw that this ForeignKeyConstraint object's DeleteRule property is set to Cascade by default. This means that when the DataRow in the parent DataTable is deleted, so are the corresponding DataRow objects in the child DataTable. Therefore, in the previous example, when customerDR is deleted from customersDT, so is orderDR in ordersDT. The ModifyingRelatedData.cs program performs the previous code in the Main() method. Pushing Changes in customersDT and ordersDT to the Database In this section, you'll learn how to push the changes previously made in the customersDT and ordersDT DataTable objects to the database. When pushing changes to the database, you have to apply them in an order that satisfies the foreign key constraints in the related tables.
For example, a row in the Customers table with a CustomerID of J6COM must exist before a row with that CustomerID can be added to the Orders table. Similarly, you can't delete the row with a CustomerID of J6COM while there are rows with that CustomerID in the Orders table. Finally, of course, you can update only rows that already exist in a table. Follow these steps when pushing the changes from customersDT and ordersDT to the database: 1. Push the DataRow objects added to customersDT to the Customers table. 2. Push the DataRow objects added to ordersDT to the Orders table. 3. Push the DataRow objects updated in customersDT to the Customers table. 4. Push the DataRow objects updated in ordersDT to the Orders table. 5. Delete the DataRow objects removed from ordersDT from the Orders table. 6. Delete the DataRow objects removed from customersDT from the Customers table. To get the DataRow objects that have been added, updated, or deleted, you use the Select() method of a DataTable. The Select() method was covered in the previous chapter, and one of the overloaded versions of this method is DataRow[] Select(string filterExpression, string sortExpression, DataViewRowState myDataViewRowState) where • filterExpression specifies the rows to select. • sortExpression specifies how the selected rows are to be ordered. • myDataViewRowState specifies the row state of the rows to select. You can see the members of the DataViewRowState enumeration in Table 11.8 of the previous chapter. To get the DataRow objects that have been added to the customersDT DataTable, you can use the following code that calls the Select() method: DataRow[] newCustomersDRArray = customersDT.Select("", "", DataViewRowState.Added); Notice the use of the Added constant from the DataViewRowState enumeration. This indicates that only the newly added DataRow objects in customersDT are to be returned and stored in newCustomersDRArray.
You can then push the DataRow objects in newCustomersDRArray to the Customers table in the database using the following call to the Update() method of the customersDA DataAdapter: int numOfRows = customersDA.Update(newCustomersDRArray); The numOfRows int is the number of rows added to the Customers table. The following code uses the six steps shown earlier to push all the changes to the database; notice the different constants used from the DataViewRowState enumerator to get the required DataRow objects: mySqlConnection.Open(); // push the new rows in customersDT to the database Console.WriteLine("Pushing new rows in customersDT to database"); DataRow[] newCustomersDRArray = customersDT.Select("", "", DataViewRowState.Added); int numOfRows = customersDA.Update(newCustomersDRArray); Console.WriteLine("numOfRows = " + numOfRows); // push the new rows in ordersDT to the database Console.WriteLine("Pushing new rows in ordersDT to database"); DataRow[] newOrdersDRArray = ordersDT.Select("", "", DataViewRowState.Added); numOfRows = ordersDA.Update(newOrdersDRArray); Console.WriteLine("numOfRows = "+ numOfRows); // push the modified rows in customersDT to the database Console.WriteLine("Pushing modified rows in customersDT to database"); DataRow[] modifiedCustomersDRArray = customersDT.Select("", "", DataViewRowState.ModifiedCurrent); numOfRows = customersDA.Update(modifiedCustomersDRArray); Console.WriteLine("numOfRows = " + numOfRows); // push the modified rows in ordersDT to the database Console.WriteLine("Pushing modified rows in ordersDT to database"); DataRow[] modifiedOrdersDRArray = ordersDT.Select("", "", DataViewRowState.ModifiedCurrent); numOfRows = ordersDA.Update(modifiedOrdersDRArray); Console.WriteLine("numOfRows = " + numOfRows); // push the deletes in ordersDT to the database Console.WriteLine("Pushing deletes in ordersDT to database");
DataRow[] deletedOrdersDRArray = ordersDT.Select("", "", DataViewRowState.Deleted); numOfRows = ordersDA.Update(deletedOrdersDRArray); Console.WriteLine("numOfRows = " + numOfRows); // push the deletes in customersDT to the database Console.WriteLine("Pushing deletes in customersDT to database"); DataRow[] deletedCustomersDRArray = customersDT.Select("", "", DataViewRowState.Deleted); numOfRows = customersDA.Update(deletedCustomersDRArray); Console.WriteLine("numOfRows = " + numOfRows); mySqlConnection.Close(); The ModifyingRelatedData.cs program contains a method named PushChangesToDatabase() that uses the previous code. One thing you'll notice about ModifyingRelatedData.cs is that it calls PushChangesToDatabase() immediately after performing the following steps in the Main() method: 1. Adding DataRow objects to customersDT and ordersDT. 2. Updating the new DataRow objects. 3. Deleting the new DataRow objects. PushChangesToDatabase() is immediately called after each of these steps so that you can see the database activity as the program progresses. I could have simply called PushChangesToDatabase() once at the end of the three steps-but then you wouldn't see any changes to the database, because the new rows would have been deleted in step 3 prior to PushChangesToDatabase() being called. Tip In your own programs, you're likely add, update, and delete many different rows in your DataTable objects, and so pushing the changes once at the end will be more efficient.