Persisting DataSet Changes
 | |||||||||||
| Chapter 9 - Data Access with .NET | |
| bySimon Robinsonet al. | |
| Wrox Press 2002 | |
After editing data within a DataSet , it is probably necessary to persist these changes. The most common example would be selecting data from a database, displaying it to the user , and returning those updates back to the database.
In a less "connected" application, changes might be persisted to an XML file, transported to a middle- tier application server, and then processed to update several data sources.
A DataSet can be used for either of these examples, and what's more it's really easy to do.
Updating with Data Adapters
In addition to the SelectCommand that an SqlDataAdapter most likely includes, you can also define an InsertCommand , UpdateCommand, and DeleteCommand . As these names imply, these objects are instances of SqlCommand (or OleDbCommand for the OleDbDataAdapter ), so any of these commands could be straight SQL or a stored procedure.
With this level of flexibility, you are free to tune the application by judicious use of stored procedures for frequently used commands (say SELECT and INSERT ), and use straight SQL for less commonly used commands such as DELETE .
For the example in this section I have resurrected the stored procedure code from the Calling Stored Procedures section for inserting, updating, and deleting Region records, coupled these with the RegionSelect procedure written above, and produced an example utilizes each of these commands to retrieve and update data in a DataSet . The main body of code is shown below; the full sourcecode is available in the 12_DataAdapter2 directory.
Inserting a New Row
There are two ways to add a new row to a DataTable . The first way is to call the NewRow() method, which returns a blank row that you then populate and add to the Rows collection, as follows :
DataRow r = ds.Tables["Region"].NewRow(); r["RegionID"]=999; r["RegionDescription"]="North West"; ds.Tables["Region"].Rows.Add(r);
The second way to add a new row would be to pass an array of data to the Rows.Add() method as shown in the following code:
DataRow r = ds.Tables["Region"].Rows.Add (new object [] { 999 , "North West" });
Each new row within the DataTable will have its RowState set to Added . The example dumps out the records before each change is made to the database, so after adding the following row (either way) to the DataTable , the rows will look something like the following. Note that the right-hand column shows the row state.
New row pending inserting into database 1 Eastern Unchanged 2 Western Unchanged 3 Northern Unchanged 4 Southern Unchanged 999 North West Added
To update the database from the DataAdapter , call one of the Update() methods as shown below:
da.Update(ds , "Region"); For the new row within the DataTable , this will execute the stored procedure (in this instance RegionInsert ), and subsequently I dump the records in the DataTable again.
New row updated and new RegionID assigned by database 1 Eastern Unchanged 2 Western Unchanged 3 Northern Unchanged 4 Southern Unchanged 5 North West Unchanged
Look at the last row in the DataTable . I had set the RegionID in code to 999 , but after executing the RegionInsert stored procedure the value has been changed to 5 . This is intentional - the database will often generate primary keys for you, and the updated data in the DataTable is due to the fact that the SqlCommand definition within our sourcecode has the UpdatedRowSource property set to UpdateRowSource.OutputParameters :
SqlCommand aCommand = new SqlCommand("RegionInsert" , conn); aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.Add(new SqlParameter("@RegionDescription" , SqlDbType.NChar , 50 , "RegionDescription")); aCommand.Parameters.Add(new SqlParameter("@RegionID" , SqlDbType.Int, 0 , ParameterDirection.Output , false , 0 , 0 , "RegionID" , // Defines the SOURCE column DataRowVersion.Default , null)); aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters; What this means is that whenever a data adapter issues this command, the output parameters should be mapped back to the source of the row, which in this instance was a row in a DataTable . The flag states what data should be updated - the stored procedure has an output parameter that is mapped back into the DataRow . The column it applies to is RegionID , as this is defined within the command definition.
The values for UpdateRowSource are as follows:
| UpdateRowSource Value | Description |
|---|---|
| Both | A stored procedure may return output parameters and also a complete database record. Both of these data sources are used to update the source row. |
| FirstReturnedRecord | This infers that the command returns a single record, and that the contents of that record should be merged into the original source DataRow . This is useful where a given table has a number of default (or computed) columns , as after an INSERT statement these need to be synchronized with the DataRow on the client. An example might be ' INSERT (columns) INTO (table) WITH (primarykey)', then ' SELECT (columns) FROM (table) WHERE (primarykey)'. The returned record would then be merged into the original row. |
| None | All data returned from the command is discarded. |
| OutputParameters | Any output parameters from the command are mapped onto the appropriate column(s) in the DataRow . |
Updating an Existing Row
Updating a row that already exists within the DataTable is just a case of utilizing the DataRow class's indexer with either a column name or column number, as shown in the following code:
r["RegionDescription"]="North West England"; r[1] = "North East England";
Both of these statements are equivalent (in this example):
Changed RegionID 5 description 1 Eastern Unchanged 2 Western Unchanged 3 Northern Unchanged 4 Southern Unchanged 5 North West England Modified
Prior to updating the database, the row updated has its state set to Modified as shown above.
Deleting a Row
Deleting a row is a matter of calling the Delete() method:
r.Delete(); A deleted row has its row state set to Deleted , but you cannot read columns from the deleted DataRow as these are no longer valid. When the adaptor's Update() method is called, all deleted rows will utilize the DeleteCommand , which in this instance executes the RegionDelete stored procedure.
Writing XML Output
As you have seen already, the DataSet has great support for defining its schema in XML, and as you can read data from an XML document, you can also write data to an XML document.
The DataSet.WriteXml() method permits you to output various parts of the data stored within the DataSet . You can elect to output just the data, or the data and the schema. The following code shows an example of both for the Region example shown above:
ds.WriteXml(".\WithoutSchema.xml"); ds.WriteXml(".\WithSchema.xml" , XmlWriteMode.WriteSchema);
The first file, WithoutSchema.xml is shown below:
<?xml version="1.0" standalone="yes"?> <NewDataSet> <Region> <RegionID>1</RegionID> <RegionDescription>Eastern </RegionDescription> </Region> <Region> <RegionID>2</RegionID> <RegionDescription>Western </RegionDescription> </Region> <Region> <RegionID>3</RegionID> <RegionDescription>Northern </RegionDescription> </Region> <Region> <RegionID>4</RegionID> <RegionDescription>Southern </RegionDescription> </Region> </NewDataSet>
The closing tag on RegionDescription is over to the right of the page as the database column is defined as NCHAR(50) , which is a 50 character string padded with spaces.
The output produced in the WithSchema.xml file includes, not surprisingly, the XML schema for the DataSet as well as the data itself:
<?xml version="1.0" standalone="yes"?> <NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Region"> <xs:complexType> <xs:sequence> <xs:element name="RegionID" msdata:AutoIncrement="true" msdata:AutoIncrementSeed="1" type="xs:int" /> <xs:element name="RegionDescription" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <Region> <RegionID>1</RegionID> <RegionDescription>Eastern </RegionDescription> </Region> <Region> <RegionID>2</RegionID> <RegionDescription>Western </RegionDescription> </Region> <Region> <RegionID>3</RegionID> <RegionDescription>Northern </RegionDescription> </Region> <Region> <RegionID>4</RegionID> <RegionDescription>Southern </RegionDescription> </Region> </NewDataSet>
Note the use in this file of the msdata schema, which defines extra attributes for columns within a DataSet , such as AutoIncrement and AutoIncrementSeed - these attributes correspond directly with the properties definable on a DataColumn .
EAN: 2147483647
Pages: 244