Persisting DataSet Changes


After editing data within a DataSet, it is usually necessary to persist these changes. The most common example is selecting data from a database, displaying it to the user, and returning those updates 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 class can be used for either of these examples; what’s more, it’s really easy to do.

Updating with Data Adapters

In addition to the SelectCommand that a SqlDataAdapter most likely includes, you can also define an InsertCommand, UpdateCommand, and DeleteCommand. As these names imply, these objects are instances of the command object appropriate for your provider such as SqlCommand and OleDbCommand.

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. In general it is recommended to provide stored procedures for all database interaction, because it is faster and easier to tune.

This example uses the stored procedure code from the “Calling Stored Procedures” section for inserting, updating, and deleting Region records, coupled with the RegionSelect procedure written above, which produces an example that utilizes each of these commands to retrieve and update data in a DataSet class. The main body of code is shown in the following section.

Inserting a New Row

You can add a new row to a DataTable in two ways. 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 a 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 here:

  da.Update(ds , "Region"); 

For the new row within the DataTable, this executes the stored procedure (in this instance RegionInsert). The example then dumps the state of the data so you can see that changes have been made to the database.

 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. The RegionID had been set 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 the source code 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 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 to the DataRow. The column it applies to is RegionID, because this is defined within the command definition.

The following table shows the values for UpdateRowSource.

Open table as spreadsheet

UpdateRowSource Value

Description

Both

A stored procedure might 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, because 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 an existing row 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.

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, because they are no longer valid. When the adaptor’s Update() method is called, all deleted rows will use the DeleteCommand, which in this instance executes the RegionDelete stored procedure.

Writing XML Output

As you have seen already, the DataSet class has great support for defining its schema in XML, and just as you can read data from an XML document, you can also write data to an XML document.

The DataSet.WriteXml() method enables 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 earlier:

  ds.WriteXml(".\\WithoutSchema.xml"); ds.WriteXml(".\\WithSchema.xml" , XmlWriteMode.WriteSchema); 

The first file, WithoutSchema.xml, is shown here:

  <?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, because 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 the XML schema for the DataSet as well as the data itself:

 <?xml version="1.0" standalone="yes"?> <NewDataSet>    <xs:schema  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 class.




Professional C# 2005 with .NET 3.0
Professional C# 2005 with .NET 3.0
ISBN: 470124725
EAN: N/A
Year: 2007
Pages: 427

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net