Persisting DataSet Changes C# Help

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 riic, 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 defined 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 previously, which produces an example that uses 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[“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 I -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 rQWstate:

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: 
3 Northern: 
4 Southern: 
5 North West: 

Look at the last row in the DataTable, The RegionID had been set in code to 999, but after executing the Region lnsert 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 UpdatedRvwSource property set to updaterowsource .Output parameters:

What this means is that whenever a data adapter issue this command, the output parameters should be mapped to the source of the row, which in this Instance was a row in   Datatable, The flag states what data should be updated – the stored procedure has an output parameter that is mapped to Data Row The column it applies to is Region because this is defined within the command definition.

The following table shows the values for UpdateRowSource.

Updating an Existing Row

Updating an existing row within the DataTable is just a case dusing the DataRow classes indexer with either a column name or column number, as shown in the following code:

r[‘RegionOescription’]=’North West England’;
r[l] = ‘North Wast England’;

Both of these statements are equivalent (in this example):

Changed RegionlD 5 description

1 Eastern: Unchanged
2 Western: 
3 Northern: 
4 Southern: 
5 North West England:

Prior to updating the database, the row updated has it 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 Oat Row because-they are no longer valid, When the adaptor’s Update () method is called, all deleted row will use the DeleteCommandand, which in this instance execute the Region Delete stored procedure.

Writing XML Output

As you have seen already, the Data set 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  XML Document.

The DataSet write Xml () method enables you to output various part of the data stored within DataSet You can elect to output just the data, or the and th schema, The following code shows an example of both for the region show earlier.

The closing tag on RegionDescription isover to the right of the page because the database column is defined as NCHAR (50) ,which isa 50-character stringpadded with spaces, The output produced in the With Schema .xml file includes the XML schema for the DataSet as well as the data itself:

Note the use in this file of the msdata schema, which defines extra attributes for columns within a DataSet, such as Autolncrement and AutolncrementSeed – these attributes correspond directly with the properties definable on a DataColoumn class.

Posted on October 30, 2015 in Data Access

Share the Story

Back to Top