Xem mẫu
Data Access with ADO.NET
We can also add a UniqueConstraint to the ConstraintCollection to ensure that the primary key of a column is unique. This is a very similar process to setting the primary key shown earlier.
` Declare a ConstraintCollection. Dim myCKey As ConstraintCollection
Dim myColumn As DataColumn
` Get the column we want to place a unique constraint on. myColumn = myDataSet.Tables("Customers").Columns("CustomerID")
` Add the constraint to the constraint collection. myCKey.Add("MyConstraint", myColumn, True)
` Add the constraint collection to the table`s constraint collection. myDataSet.Tables("Customers").Constraints.Add(mCKey)
This code starts with declarations for a ConstraintCollection and a DataColumn object to hold the column we want to set the constraint for (in this case the CustomerID column). We use the Add method of the ConstraintCollection, passing in the name of a constraint, the DataColumn object, and a Boolean parameter indicating if this is a primary key. We then call the Add method of the Tables.Constraints property and pass in the ConstraintCollection object.
There are many objects that work together to make up the DataSet object. They all work hand and hand to build the tables` schema as well as manage the data.
Next we will see how to save changes that are made to a DataSet on the server.
Updating the Database
Once changes are made to a DataSet how do we update those changes back to the database? Earlier we showed how calling the AcceptChanges method updates only local records and not the database proper. These methods we use next assume you haven`t called AcceptChanges on any of your modified records before you post changes to the server. We have a couple of methods for updating our database. To update a DataSet call the Update method of the DataAdapter. This can take a DataSet, DataTables, or an array of DataRowobjects, and examines the RowState property to determine which rows have changed. Then Insert, Update, or Delete is executed depending on the state of the changed row.
When you call a DataAdapter`s Update method, you update your changes on the server. As you can see from the illustration below, we follow a similar process as with the AcceptChanges method:
27
Data Access with ADO.NET
We can control which updates are completed first if we need to. By default, the DataAdapter will update each row depending on its order, one row could be deleted and the row after it added. We can use the GetChanges method of a DataSet or DataTable to control which updates we want to occur first. GetChanges returns a DataSetcontaining changes that match the RowState parameter you specify, thus allowing us to retrieve only records marked as modified, deleted, or inserted, as the code below shows:
Dim myDataSetChanges As New DataSet()
` Gets all changes.
myDataSetChanges = myCustomerDataSet.GetChanges()
` Get records that have been modified only.
myDataSetChanges = myCustomerDataSet.GetChanges(DataRowState.Modified)
` Get records that have been deleted only.
myDataSetChanges = myCustomerDataSet.GetChanges(DataRowState.Deleted)
` Get records that have been added only.
myDataSetChanges = myCustomerDataSet.GetChanges(DataRowState.Inserted)
` Update changes back to actual database. myDataAdapter.Update(myDataSetChanges)
Once we have a DataSet with only records that have changed, we call the DataAdapter`s Update method passing in this DataSet as shown above.
This allows you to perform updates where you have referential integrity. Let`s say you have a Customer table and an Address table and the Address table contains the primary key of the customer. You would want to delete the addresses before the customer otherwise you could experience an error. If we were to delete the customer first, we would have a record that points to another record that doesn`t exist. This would leave "orphaned" records in the database that referential integrity prohibits.
A related method is the Merge method, which merges one DataSet into another keeping all the original DataSet`s row state information:
` Create a new dataset with only the modified records. Dim myDataSetChanges As New DataSet()
myDataSetChanges = myCustomerDataSet.GetChanges(DataRowState.Modified)
` Merge the changes back into our local dataset. myNewDataSet.Merge(myDataSet)
` Commit the changes. myNewDataSet.AcceptChanges()
When we call the Merge method passing in myDataSet, any records in the DataSet are added to myNewDataSet that now has the records that were in myDataSet along with the row state information of each record. This is useful when we get data from an outside source or a source not part of our system that we want to merge into our current set of data. Once we call the AcceptChanges method of myNewDataSet, we no longer have a modified RowState and we lose any changes we may have had.
29
Chapter 6
As a final note, you must ensure you have updated your changes back to the database by calling the Updatemethod of your DataAdapter, otherwise the changes will be lost. This must be done before the AcceptChangesmethod is called since AcceptChanges clears any and all row states. The Update needs the row state information to determine which records need updating.
DataSet Sample
Next, we will expand the customer application created in the last chapter to add the capability to insert, update, and delete data. As it currently stands, our application provides a read-only view of the database. This is only a quick sample: we will develop a more complex application in later chapters.
Here is what our final result will look like:
Try It Out – Reconfiguring the DataAdapters
1. Open the project from the previous chapter called CustomerApp.
We need to re-configure the data adapter for our customer table so we can perform Insert, Update, and Deletecommands on the table. Right-click on SqlDataAdpter1 and select Configure Data Adapter.
30
Data Access with ADO.NET
2. The Data Adapter Configuration Wizard dialog will appear prompting for a connection to a database. The name of our previous connection should appear which will be listed as Servername.Northwind SQL.dbo where Servername is the name of your particular computer. If the connection doesn`t appear, create a new connection by clicking on the New Connection button.
Click Next when you`re finished.
3. The Use SQL statements option should already be selected. Ensure that it is and click Next.
31
...
- tailieumienphi.vn
nguon tai.lieu . vn