Maintaining Information About Dataset Changes

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Overview

Information about the changes in a dataset is maintained in two ways: by flagging the row that indicates whether it has been changed (RowState), and by keeping multiple copies of a record (DataRowVersion). By using this information, processes can determine what has changed in the dataset and can send appropriate updates to the data source.

The RowState property of a DataRow object is a value that provides information about the status of a particular row of data.

The following table details the possible values of the DataRowState enumeration:

DataRowState Value

Description

Added

The row has been added as an item to a DataRowCollection. (A row in this state does not have a corresponding original version since it did not exist at the time the last AcceptChanges method was called).

Deleted

The row was deleted using the DataRow.Delete Method of a DataRow object.

Detached

The row has been created but is not part of any DataRowCollection. A DataRow object is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection.

Modified

A column value in the row has been changed in some way.

Unchanged

The row has not changed since AcceptChanges was last called.

Datasets maintain multiple versions of records. The DataRowVersion enumeration of a DataRow object is a value that can be used to return a specific version of a DataRow object.

The following table details the possible values of the DataRowVersion enumeration:

DataRowVersion Value

Description

Current

The current version of a record contains all modifications performed on the record since the last time AcceptChanges has been called. If the row has been deleted there is no current version.

Default

The default value of a record, as defined by the dataset schema or data source.

Original

The original version of a record is a copy of the record as it was the last time changes were committed in the dataset. In practical terms, this is typically the version of a record as read from a data source.

Proposed

The proposed version of a record that is available temporarily, while you are in the middle of an update — that is, between the time you called the BeginEdit method and the EndEdit method. You typically access the proposed version of a record in a handler for an event such as RowChanging. Invoking the CancelEdit method reverses the changes and deletes the proposed version of the data row.

The original and current versions are useful when update information is transmitted to a data source. Typically, when an update is sent to the data source, the new information for the database is in the current version of a record, and information from the original version is used to locate the record to update. For example, in a case where the primary key of a record is changed, you must have a way to locate the proper record in the data source, in order to update the changes. If no original version existed, the record would most likely be appended to the data source resulting not only in an extra unwanted record, but in one record that is inaccurate and out of date. The two versions are also used in concurrency control; you can compare the original version against a record in the data source to determine if the record has changed since it was loaded into the dataset.

The proposed version is useful when you need to perform validation before actually committing the changes to the dataset.

Even if records have changed, there are not always original or current versions of that row. When you insert a new row into the table, there is no original version, only a current version. Similarly, if you delete a row by calling the table's Delete method, there is an original version, but no current version.

You can test to see if a specific version of a record exists by querying a data row's HasVersion property. You can access either version of a record by passing a DataRowVersion enumeration value as an optional argument when you request the value of a column.

Example

This ADO.NET example will demonstrate using a DataSet and a DataAdapter to retrieve data from SQL Server, and post changes such as inserts, updates, and deletes through the adapter. During these changes the RowState Property will be shown.

It's worth noting here than when creating commands to commit changes for the DataAdapter, it is important to indicate the source column. You can do that by setting the SourceColumn property on the Parameter object, or by passing it as the argument immediately following the parameter's data type size.

For example, the parameter, @RegionDescription, when the DataAdapter attempted to invoke the Update command, couldn't decipher on its own that the value to be supplied for @RegionDescription was the RegionDescription column. By supplying the SourceColumn to the Parameters.Add() method on our own, we guarantee that the adapter will supply the right column values for the right arguments.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace AdoNetSample
{
    public class AdoNetSample : System.Windows.Forms.Form
    {
        public System.Windows.Forms.RichTextBox richTextBox;
        private System.ComponentModel.Container components = null;
        public string msg;

        public AdoNetSample()
        {
            InitializeComponent();

            DataSet SampleDS = new DataSet("SampleDS");
            string ConnectionString = "Data Source=XEON; "+
                "Initial Catalog=Northwind; User id=sa; Password=manager;";

            SqlConnection Connection = new SqlConnection(ConnectionString);

            SqlDataAdapter SampleDA = new SqlDataAdapter(
                "SELECT RegionID,RegionDescription FROM Region", Connection);

            // Now lets provide SQL statements that support the rest of the
            // UPDATE, DELETE, and INSERT functionality.

            // It's worth noting here than when creating commands to commit
            // changes for the DataAdapter, it is important to indicate the source column.
            // You can do that by setting the SourceColumn property on the Parameter object,
            // or by passing it as the argument immediately following the parameter's data type size.

            // For example, the parameter, @RegionDescription, when the DataAdapter
            // attempted to invoke the Update command, couldn't decipher on its own that the value
            // to be supplied for @RegionDescription was the RegionDescription column.
            // By supplying the SourceColumn to the Parameters.Add() method on our own,
            // we guarantee that the adapter will supply the right column values for the right arguments.

            // -- UPDATE --
            SqlCommand UpdateCommand = new SqlCommand(
                "UPDATE Region SET RegionDescription = @RegionDescription " +
                "WHERE RegionID = @RegionID", Connection);

            SqlParameter UpdateKeyParam = new SqlParameter("@RegionID",SqlDbType.Int);
            UpdateKeyParam.SourceColumn = "RegionID";
            UpdateKeyParam.SourceVersion = DataRowVersion.Original;

            UpdateCommand.Parameters.Add("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription");
            UpdateCommand.Parameters.Add(UpdateKeyParam);
            SampleDA.UpdateCommand = UpdateCommand;

            // -- DELETE --
            SqlCommand DeleteCommand = new SqlCommand(
                "DELETE Region WHERE RegionID = @RegionID", Connection);

            SqlParameter DeleteKeyParam = new SqlParameter("@RegionID",SqlDbType.Int);
            DeleteKeyParam.SourceColumn = "RegionID";
            DeleteKeyParam.SourceVersion = DataRowVersion.Original;

            DeleteCommand.Parameters.Add(DeleteKeyParam);
            SampleDA.DeleteCommand = DeleteCommand;

            // -- INSERT --
            SqlCommand InsertCommand = new SqlCommand(
                "INSERT INTO Region(RegionDescription, RegionID) "+
                "VALUES(@RegionDescription, @RegionID)", Connection);

            InsertCommand.Parameters.Add("@RegionDescription", SqlDbType.NChar, 50,"RegionDescription");
            InsertCommand.Parameters.Add("@RegionID", SqlDbType.Int, 4, "RegionID");
            SampleDA.InsertCommand = InsertCommand;

           // Now load the dataset with the results of our SQL Query.
            // notice that we're not explicitly opening our conection.
            // Our DataAdapter is doing all that work for us, closing
            // it as soon as it completes its task.
            SampleDA.Fill(SampleDS, "Region");

            // Create a new row the Region table doesn't have an
            // autonumbering identity, so we have to supply our own region ID.
            DataRow NewRow;
            NewRow = SampleDS.Tables["Region"].NewRow();
            NewRow["RegionDescription"] = "Central";
            NewRow["RegionID"] = 5;

            msg = "New Row Created using NewRow(), RowState is: " + NewRow.RowState.ToString() + "\n";
            SampleDS.Tables["Region"].Rows.Add(NewRow);
            msg += "New Row Added to Table RowState is: " + NewRow.RowState.ToString() + "\n";

            // Modify the first row
            DataRow TempRow;
            TempRow = SampleDS.Tables["Region"].Rows[0];

            msg += "Modifying First Row, Pre-Change State is: " + TempRow.RowState.ToString() + "\n";
            TempRow["RegionDescription"] = "The wonderful Region of Berne";
            msg += "Modifying First Row, Post-Change State is: " + TempRow.RowState.ToString() + "\n";

            // Call the update method to save the new row and update the first
            msg += "Calling Update() to Commit New Row and First Row Change.\n";
            SampleDA.Update(SampleDS, "Region");

            // Delete the second row
            msg += "Deleting Our New Row, Post-Delete State is: " + NewRow.RowState.ToString() + "\n";
            NewRow.Delete();
            msg += "Deleting Our New Row, Post-Delete State is: " + NewRow.RowState.ToString() + "\n";

            // Now call the update method.
            msg += "Calling Update() - this will post ALL of our changes, not just the update\n";

            SampleDA.Update(SampleDS, "Region");
            msg += "Region Table after Update()\n------------------------------\n";

            foreach (DataRow tRow in SampleDS.Tables["Region"].Rows)
            {
                msg += tRow["RegionDescription"].ToString() + "\n";
            }
            richTextBox.Text = msg;

            SampleDS.Dispose();
            SampleDA.Dispose ();
        }

        [STAThread]
        static void Main()
        {
            Application.Run(new AdoNetSample());
        }
    }
}