Zurück

Performing Transactional Updates with and without Data Sets

Contents

1.   Introduction
2.   Locking and Transaction Isolation
3.   Simple Transactional Updates without a DataSet
4.   Transactional DataSet Updates using DataAdapter.Update()
 5.   Transactional DataSet Updates using a DataReader

 
1.  Introduction

Summary

Datasets are a fundamental part of the ADO.NET architecture, providing both high-performance data access as well as scalability. Datasets store data in a disconnected cache. The structure of a dataset is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, and columns. In addition, it contains constraints and relationships defined for the dataset. However, to perform simple updates it's not necessary to create all the overhead of a Dataset, you can use the ExecuteNonQuery() method to process any SQL Statement.

If you need the full control over your transactions, then you can perform your own transaction logic, mostly without the implicit AcceptChanges() method of the DataAdapter.Update() using a DataReader.

This mechanism can also be used to retrieve Identity or Autonumber Values back into Datasets.

2.  Locking and Transaction Isolation

One of the key functions of a database management system (DBMS) is to ensure that multiple users can read and write records in the database without reading inconsistent sets of records due to in-progress changes and without overwriting each other's changes inadvertently.

Oracle and SQL Server approach this task with different locking and isolation strategies. You must consider these differences when you convert an application from Oracle to SQL Server or the resulting application may scale poorly to high numbers of users.

Oracle uses a multiversion consistency model for all SQL statements that read data, either explicitly or implicitly. In this model, data readers, by default, neither acquire locks nor wait for other locks to be released before reading rows of data. When a reader requests data that has been changed but not yet committed by other writers, Oracle re-creates the old data by using its rollback segments to reconstruct a snapshot of rows.

Data writers in Oracle request locks on data that is updated, deleted, or inserted. These locks are held until the end of a transaction, and they prevent other users from overwriting uncommitted changes.

SQL Server, in contrast, uses shared locks to ensure that data readers only see committed data. These readers take and release shared locks as they read data. These shared locks do not affect other readers. A reader waits for a writer to commit the changes before reading a record. A reader holding shared locks also blocks a writer trying to update the same data.

Releasing locks quickly for applications that support high numbers of users is more important in SQL Server than in Oracle. Releasing locks quickly is usually a matter of keeping transactions short. If possible, a transaction should neither span multiple round-trips to the server nor wait for the user to respond. You also need to code your application to fetch data as quickly as possible because unfetched data scans can hold share locks at the server and thus block updaters.

The data affected by a transaction is called volatile. When you create a transaction, you can specify the isolation level that applies to the transaction. The isolation level of a transaction determines what level of access other transactions have to volatile data before a transaction completes.

The lowest isolation level, ReadUncommitted, allows many transactions to operate on a data store simultaneously and provides no protection against data corruption due to interruptive transactions. The highest isolation level, Serializable, provides a high degree of protection against interruptive transactions, but requires that each transaction complete before any other transactions are allowed to operate on the data.

ReadCommitted Volatile data cannot be read during the transaction, but can be modified.
ReadUncommitted Volatile data can be read and modified during the transaction.
RepeatableRead Volatile data can be read but not modified during the transaction. New data can be added during the transaction.
Serializable Volatile data can be read but not modified, and no new data can be added during the transaction.

Snapshot Volatile data can be read. Before a transaction modifies data, it verifies if another transaction has changed the data after it was initially read. If the data has been updated, an error is raised. This allows a transaction to get to the previously committed value of the data.
When you try to promote a transaction that was created with this isolation level, an InvalidOperationException is thrown with the error message "Transactions with IsolationLevel Snapshot cannot be promoted".

3.  Simple Transactional Updates without a DataSet

You can use the ExecuteNonQuery to perform SQL operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

Example

using System;
using System.Diagnostics;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteNonQuery
{
    // Simple Non DataSet Update using ExecuteNonQuery
    class ExecuteNonQuery
    {
        private static string conn = "server=xeon;database=northwind;uid=sa;pwd=manager";

        // The main entry point for the application.
        [STAThread]
        static void Main(string[] args)
        {
            RunSqlTransaction(conn);
        }

        // Perform Update using a Transaction
        public static void RunSqlTransaction(string myConnString)
        {
            SqlConnection myConnection = new SqlConnection(myConnString);
            myConnection.Open();

            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            // Start a local transaction
            using (myTrans = myConnection.BeginTransaction(
                  IsolationLevel.RepeatableRead, "SampleTransaction"))
            {

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                myCommand.Connection = myConnection;
                myCommand.Transaction = myTrans;

                // Create Command and Execute the Transactions
                try
                {
                    int num_row;
                    myCommand.CommandText = "DELETE FROM Region WHERE RegionID IN (100,101)";
                    myCommand.ExecuteNonQuery();

                    myCommand.CommandText = "INSERT INTO Region
                             (RegionID, RegionDescription) VALUES (100, 'Region-100')";
                    num_row = myCommand.ExecuteNonQuery();

                    myCommand.CommandText = "INSERT INTO Region
                             (RegionID, RegionDescription) VALUES (101, 'Region-101')";
                    num_row += myCommand.ExecuteNonQuery();

                    myCommand.CommandText = "UPDATE Region SET RegionDescription = 'Other Region'
                             WHERE RegionID = 100";
                    myCommand.ExecuteNonQuery();

                    myTrans.Commit();
                    string msg = num_row.ToString() + " Records written to database.";
                    Console.WriteLine(msg);
                    Console.Read();
                }
                catch (Exception e)
                {
                    try
                    {
                        myTrans.Rollback("SampleTransaction");
                    }
                    catch (SqlException ex)
                    {
                        if (myTrans.Connection != null)
                        {
                            Console.WriteLine("An exception of type " + ex.GetType() +
                                " was encountered while attempting to roll back the transaction.");
                        }
                    }

                    Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
                    Console.WriteLine("Neither record was written to database.");
                }
                finally
                {
                    myConnection.Close();
                }
            }
        }
    }
}

Release of Memory with USING

C#, though the .NET Framework common language runtime (CLR) automatically releases the memory used to store objects that are no longer required. The release of memory is non-deterministic; memory is released whenever the CLR decides to perform garbage collection. However, it is usually best to release limited resources such as file handles and network connections as quickly as possible.

The using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object's resources.

A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.

4.   Transactional DataSet Updates using DataAdapter.Update()

Two Step Process

Updating a data source via a dataset is a two-step process. The first step is to update the dataset with new information — new records, changed records, or deleted records.

If you are updating a data source (such as a database), the second step is to send the changes from the dataset to the original data source. That is, the process of updating the dataset does not also write the changes through to an underlying data source; you must explicitly perform this second step.

After changes have been made in a dataset, you can transmit the changes to a data source. Most commonly, you do this by calling the Update method of a DataAdapter. The method loops through each record in a data table, determines what type of update is required (update, insert, or delete), if any, and then executes the appropriate command.

How an Update Is Transmitted to the Data Source

As an illustration of how updates are made, suppose your application uses a dataset containing a single data table. The application fetches two rows from the database. After the retrieval, the in-memory data table looks like this:

(RowState)     FirstName         LastName
 (Unchanged)    Nancy             Buchanan
 (Unchanged)    James             Wilson

Your application changes "James" to "Jim". As a result of this change, the value of the DataRow.RowState property for that row changes from Unchanged to Modified. The value of the RowState property for the other row remains Unchanged. The data table now looks like this:

(RowState)     FirstName         LastName
 (Unchanged)    Nancy             Buchanan
(Modified)     Jim               Wilson

You application now calls the Update method to transmit the dataset to the database. The method inspects each row in turn. For the first row, the method transmits no SQL statement to the database, because that row has not changed since it was originally fetched from the database.

For the second row, however, the Update method automatically invokes the proper data command and transmits it to the database. The specific syntax of the SQL statement depends on the dialect of SQL supported by the underlying data store. But the following general traits of the transmitted SQL statement are noteworthy:

  • The transmitted SQL statement is an UPDATE statement. The DataAdapter know to use an UPDATE statement because the value of the RowState property is Modified.
  • The transmitted SQL statement includes a WHERE clause. The information for the WHERE clause is derived from the original version of the record (DataRowVersion.Original), in case values required to identify the row have been changed.
  • The transmitted SQL statement includes the SET clause, to set the new values of the modified columns.

Example

When using Update, the order of execution is as follows:

  1. The values in the DataRow are moved to the parameter values.

  2. The OnRowUpdating event is raised.

  3. The command executes.

  4. If the command is set to FirstReturnedRecord, the first returned result is placed in the DataRow.

  5. If there are output parameters, they are placed in the DataRow.

  6. The OnRowUpdated event is raised.

  7. AcceptChanges is called.

The following Example uses the DataAdapter.Update() Method, the Primary Key is not automatically created in this Example (see later Examples).

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

namespace Akadia.CustomerTransactions
{
   public class DataSetWithAcceptChanges : System.Windows.Forms.Form
   {
      // Visual Controls
      private System.ComponentModel.Container components = null;
      private TextBox txtPhone;
      private TextBox txtContactTitle;
      private TextBox txtZip;
      private TextBox txtCity;
      private TextBox txtAddress;
      private TextBox txtContactName;
      private TextBox txtCompanyName;
      private TextBox txtCompanyID;
      private Label lblCompanyID;
      private Button btnNew;
      private TextBox txtCustomerName;
      private Button btnUpdate;
      private Button btnDelete;
      private Label lblCity;
      private Label lblZip;
      private Label lblContactTitle;
      private Label lblPhone;
      private Label lblAddress;
      private Label lblCompanyName;
      private Label lblContactName;
      private Label lblNewCustomerName;
      private ListBox listBoxCustomers;
      private GroupBox grpBoxUpdateCustomer;
      private GroupBox grpBoxNewCustomer;
      private Label lblMessage;

      // The DataSet, DataAdapter, and DataTable are members
      // so that we can access them from any member method.
      private SqlDataAdapter _dataAdapter;
      private DataSet _dataSet;
      private DataTable _dataTable;

      // **** Constructor
      public DataSetWithAcceptChanges()
      {
         // Create the visual Controls
         InitializeComponent();

         // Setup DB Connection **** ADJUST IT FOR YOUR USE ****
         string connectionString = "server=xeon;" +
            "uid=sa; pwd=manager; database=northwind";
         string commandString = "Select * from Customers";
         _dataAdapter =  new SqlDataAdapter(commandString, connectionString);

         // Create INSERT, UPDATE, DELETE "Template" Commands
         InitializeCommands();

         // Populate DataSet
         _dataSet = new DataSet();
         _dataAdapter.Fill(_dataSet,"Customers");

         // Fill the list box with columns from the Dataset
         this.PopulateListBox();
      }

      // *** Bind parameters to appropriate columns.
      // Note: The params keyword lets you specify a method
      // parameter that takes an argument where the number
      // of arguments is variable.
      private void AddParms(SqlCommand cmd, params string[] cols)
      {
         // Add each parameter. Note that each colum in
         // table "Customers" is of type VARCHAR !
         foreach (String column in cols)
         {
            cmd.Parameters.Add("@" + column, SqlDbType.Char, 0, column);
         }
      }

      // *** Setup DataAdapter with expicit INSERT, UPDATE,
      // DELETE "Template" Commands.
      private void InitializeCommands()
      {
         // Reuse the SelectCommand's Connection.
         SqlConnection con =
            (SqlConnection) _dataAdapter.SelectCommand.Connection;

         // Create an explicit, reusable INSERT command
         _dataAdapter.InsertCommand = con.CreateCommand();
         _dataAdapter.InsertCommand.CommandText =
            "INSERT INTO customers " +
            "(CustomerId, CompanyName, ContactName, ContactTitle, " +
            "Address, City, PostalCode, Phone) " +
            "VALUES(@CustomerId, @CompanyName, @ContactName, " +
            "@ContactTitle, @Address, @City, @PostalCode, @Phone)";

         // Bind parameters to appropriate columns for INSERT command
         this.AddParms(_dataAdapter.InsertCommand,
            "CustomerId", "CompanyName", "ContactName", "ContactTitle",
            "Address", "City", "PostalCode", "Phone");

         // Create an explicit UPDATE command
         _dataAdapter.UpdateCommand = con.CreateCommand();
         _dataAdapter.UpdateCommand.CommandText =
            "UPDATE customers " +
            "SET CompanyName = @CompanyName " +
            "WHERE CustomerID = @CustomerId";

         // Bind parameters to appropriate columns for UPDATE command
         this.AddParms(_dataAdapter.UpdateCommand, "CompanyName", "CustomerID");

         // Create an explicit DELETE command
         _dataAdapter.DeleteCommand = con.CreateCommand();
         _dataAdapter.DeleteCommand.CommandText =
            "DELETE FROM customers " +
            "WHERE customerID = @CustomerId";

         // Bind parameters to appropriate columns for DELETE command
         AddParms(_dataAdapter.DeleteCommand, "CustomerID");
      }

      // *** Fill the list box with columns from the Dataset
      private void PopulateListBox()
      {
         // Get the only one Table from the Dataset
         _dataTable = _dataSet.Tables[0];
         listBoxCustomers.Items.Clear();

         // Loop through the Dataset and add each Row
         // to the ListBox
         foreach (DataRow dataRow in _dataTable.Rows)
         {
            listBoxCustomers.Items.Add(
               dataRow["CustomerId"] + ", " +
               dataRow["CompanyName"] + ", " +
               dataRow["ContactName"] + ", " +
               dataRow["ContactTitle"] + ", " +
               dataRow["Address"] + ", " +
               dataRow["City"] + ", " +
               dataRow["PostalCode"] + ", " +
               dataRow["Phone"] + ")" );
         }
      }

      // **** Set all the text fields to empty strings
      private void ClearFields()
      {
         txtCompanyID.Text = "";
         txtCompanyName.Text = "";
         txtContactName.Text = "";
         txtContactTitle.Text = "";
         txtAddress.Text = "";
         txtCity.Text = "";
         txtZip.Text = "";
         txtPhone.Text = "";
      }

      // **** Create the visual Controls, let the Designer do
      // this and DO NOT CHANGE MANUALLY.
      private void InitializeComponent()
      {
            this.txtCustomerName = new System.Windows.Forms.TextBox();
            this.txtCity = new System.Windows.Forms.TextBox();
            this.txtCompanyID = new System.Windows.Forms.TextBox();
            this.btnUpdate = new System.Windows.Forms.Button();
            this.txtContactName = new System.Windows.Forms.TextBox();
            this.txtZip = new System.Windows.Forms.TextBox();
            this.btnDelete = new System.Windows.Forms.Button();
            this.txtContactTitle = new System.Windows.Forms.TextBox();
            this.txtAddress = new System.Windows.Forms.TextBox();
            this.txtCompanyName = new System.Windows.Forms.TextBox();
            this.lblCity = new System.Windows.Forms.Label();
            this.lblZip = new System.Windows.Forms.Label();
            this.lblContactTitle = new System.Windows.Forms.Label();
            this.lblPhone = new System.Windows.Forms.Label();
            this.lblNewCustomerName = new System.Windows.Forms.Label();
            this.lblAddress = new System.Windows.Forms.Label();
            this.listBoxCustomers = new System.Windows.Forms.ListBox();
            this.txtPhone = new System.Windows.Forms.TextBox();
            this.btnNew = new System.Windows.Forms.Button();
            this.lblCompanyID = new System.Windows.Forms.Label();
            this.lblCompanyName = new System.Windows.Forms.Label();
            this.lblContactName = new System.Windows.Forms.Label();
            this.grpBoxUpdateCustomer = new System.Windows.Forms.GroupBox();
            this.grpBoxNewCustomer = new System.Windows.Forms.GroupBox();
            this.lblMessage = new System.Windows.Forms.Label();
            this.grpBoxUpdateCustomer.SuspendLayout();
            this.SuspendLayout();
            //
            // txtCustomerName
            //
            this.txtCustomerName.Location = new System.Drawing.Point(26, 170);
            this.txtCustomerName.Name = "txtCustomerName";
            this.txtCustomerName.Size = new System.Drawing.Size(275, 20);
            this.txtCustomerName.TabIndex = 4;
            //
            // txtCity
            //
            this.txtCity.Location = new System.Drawing.Point(326, 266);
            this.txtCity.Name = "txtCity";
            this.txtCity.Size = new System.Drawing.Size(160, 20);
            this.txtCity.TabIndex = 15;
            //
            // txtCompanyID
            //
            this.txtCompanyID.Location = new System.Drawing.Point(111, 237);
            this.txtCompanyID.Name = "txtCompanyID";
            this.txtCompanyID.Size = new System.Drawing.Size(160, 20);
            this.txtCompanyID.TabIndex = 7;
            //
            // btnUpdate
            //
            this.btnUpdate.Location = new System.Drawing.Point(308, 169);
            this.btnUpdate.Name = "btnUpdate";
            this.btnUpdate.Size = new System.Drawing.Size(179, 23);
            this.btnUpdate.TabIndex = 0;
            this.btnUpdate.Text = "Update selected Customer Name";
            this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
            //
            // txtContactName
            //
            this.txtContactName.Location = new System.Drawing.Point(111, 295);
            this.txtContactName.Name = "txtContactName";
            this.txtContactName.Size = new System.Drawing.Size(160, 20);
            this.txtContactName.TabIndex = 11;
            //
            // txtZip
            //
            this.txtZip.Location = new System.Drawing.Point(326, 295);
            this.txtZip.Name = "txtZip";
            this.txtZip.Size = new System.Drawing.Size(160, 20);
            this.txtZip.TabIndex = 17;
            //
            // btnDelete
            //
            this.btnDelete.Location = new System.Drawing.Point(494, 76);
            this.btnDelete.Name = "btnDelete";
            this.btnDelete.Size = new System.Drawing.Size(54, 23);
            this.btnDelete.TabIndex = 2;
            this.btnDelete.Text = "Delete";
            this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
            //
            // txtContactTitle
            //
            this.txtContactTitle.Location = new System.Drawing.Point(111, 324);
            this.txtContactTitle.Name = "txtContactTitle";
            this.txtContactTitle.Size = new System.Drawing.Size(160, 20);
            this.txtContactTitle.TabIndex = 12;
            //
            // txtAddress
            //
            this.txtAddress.Location = new System.Drawing.Point(326, 237);
            this.txtAddress.Name = "txtAddress";
            this.txtAddress.Size = new System.Drawing.Size(160, 20);
            this.txtAddress.TabIndex = 13;
            //
            // txtCompanyName
            //
            this.txtCompanyName.Location = new System.Drawing.Point(111, 266);
            this.txtCompanyName.Name = "txtCompanyName";
            this.txtCompanyName.Size = new System.Drawing.Size(160, 20);
            this.txtCompanyName.TabIndex = 9;
            //
            // lblCity
            //
            this.lblCity.Location = new System.Drawing.Point(271, 270);
            this.lblCity.Name = "lblCity";
            this.lblCity.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblCity.Size = new System.Drawing.Size(49, 13);
            this.lblCity.TabIndex = 14;
            this.lblCity.Text = "City";
            //
            // lblZip
            //
            this.lblZip.Location = new System.Drawing.Point(271, 299);
            this.lblZip.Name = "lblZip";
            this.lblZip.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblZip.Size = new System.Drawing.Size(49, 13);
            this.lblZip.TabIndex = 16;
            this.lblZip.Text = "Zip";
            //
            // lblContactTitle
            //
            this.lblContactTitle.Location = new System.Drawing.Point(24, 327);
            this.lblContactTitle.Name = "lblContactTitle";
            this.lblContactTitle.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblContactTitle.Size = new System.Drawing.Size(82, 13);
            this.lblContactTitle.TabIndex = 28;
            this.lblContactTitle.Text = "Contact Title";
            //
            // lblPhone
            //
            this.lblPhone.Location = new System.Drawing.Point(278, 327);
            this.lblPhone.Name = "lblPhone";
            this.lblPhone.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblPhone.Size = new System.Drawing.Size(42, 13);
            this.lblPhone.TabIndex = 20;
            this.lblPhone.Text = "Phone";
            //
            // lblNewCustomerName
            //
            this.lblNewCustomerName.Location = new System.Drawing.Point(27, 154);
            this.lblNewCustomerName.Name = "lblNewCustomerName";
            this.lblNewCustomerName.RightToLeft = System.Windows.Forms.RightToLeft.No;
            this.lblNewCustomerName.Size = new System.Drawing.Size(147, 14);
            this.lblNewCustomerName.TabIndex = 22;
            this.lblNewCustomerName.Text = "Update Customer Name";
            //
            // lblAddress
            //
            this.lblAddress.Location = new System.Drawing.Point(271, 241);
            this.lblAddress.Name = "lblAddress";
            this.lblAddress.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblAddress.Size = new System.Drawing.Size(49, 13);
            this.lblAddress.TabIndex = 26;
            this.lblAddress.Text = "Address";
            //
            // listBoxCustomers
            //
            this.listBoxCustomers.HorizontalScrollbar = true;
            this.listBoxCustomers.Location = new System.Drawing.Point(24, 31);
            this.listBoxCustomers.Name = "listBoxCustomers";
            this.listBoxCustomers.Size = new System.Drawing.Size(463, 108);
            this.listBoxCustomers.Sorted = true;
            this.listBoxCustomers.TabIndex = 3;
            this.listBoxCustomers.SelectedIndexChanged +=
            new System.EventHandler(this.listBoxCustomers_SelectedIndexChanged);
            //
            // txtPhone
            //
            this.txtPhone.Location = new System.Drawing.Point(326, 324);
            this.txtPhone.Name = "txtPhone";
            this.txtPhone.Size = new System.Drawing.Size(160, 20);
            this.txtPhone.TabIndex = 18;
            //
            // btnNew
            //
            this.btnNew.Location = new System.Drawing.Point(503, 284);
            this.btnNew.Name = "btnNew";
            this.btnNew.Size = new System.Drawing.Size(54, 23);
            this.btnNew.TabIndex = 25;
            this.btnNew.Text = "New";
            this.btnNew.Click += new System.EventHandler(this.btnNew_Click);
            //
            // lblCompanyID
            //
            this.lblCompanyID.Location = new System.Drawing.Point(24, 241);
            this.lblCompanyID.Name = "lblCompanyID";
            this.lblCompanyID.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblCompanyID.Size = new System.Drawing.Size(82, 13);
            this.lblCompanyID.TabIndex = 6;
            this.lblCompanyID.Text = "Company ID";
            //
            // lblCompanyName
            //
            this.lblCompanyName.Location = new System.Drawing.Point(18, 269);
            this.lblCompanyName.Name = "lblCompanyName";
            this.lblCompanyName.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblCompanyName.Size = new System.Drawing.Size(88, 13);
            this.lblCompanyName.TabIndex = 8;
            this.lblCompanyName.Text = "Company Name";
            //
            // lblContactName
            //
            this.lblContactName.Location = new System.Drawing.Point(24, 298);
            this.lblContactName.Name = "lblContactName";
            this.lblContactName.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblContactName.Size = new System.Drawing.Size(82, 13);
            this.lblContactName.TabIndex = 10;
            this.lblContactName.Text = "Contact Name";
            //
            // grpBoxUpdateCustomer
            //
            this.grpBoxUpdateCustomer.Controls.Add(this.btnDelete);
            this.grpBoxUpdateCustomer.ImeMode = System.Windows.Forms.ImeMode.On;
            this.grpBoxUpdateCustomer.Location = new System.Drawing.Point(10, 7);
            this.grpBoxUpdateCustomer.Name = "grpBoxUpdateCustomer";
            this.grpBoxUpdateCustomer.Size = new System.Drawing.Size(567, 194);
            this.grpBoxUpdateCustomer.TabIndex = 29;
            this.grpBoxUpdateCustomer.TabStop = false;
            this.grpBoxUpdateCustomer.Text = "Update selected Customer Name or delete selected Customer";
            //
            // grpBoxNewCustomer
            //
            this.grpBoxNewCustomer.Location = new System.Drawing.Point(10, 218);
            this.grpBoxNewCustomer.Name = "grpBoxNewCustomer";
            this.grpBoxNewCustomer.Size = new System.Drawing.Size(566, 145);
            this.grpBoxNewCustomer.TabIndex = 30;
            this.grpBoxNewCustomer.TabStop = false;
            this.grpBoxNewCustomer.Text = "New Customer";
            //
            // lblMessage
            //
            this.lblMessage.Location = new System.Drawing.Point(13, 374);
            this.lblMessage.Name = "lblMessage";
            this.lblMessage.Size = new System.Drawing.Size(556, 13);
            this.lblMessage.TabIndex = 31;
            //
            // DataSetWithAcceptChanges
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(584, 396);
            this.Controls.Add(this.txtCustomerName);
            this.Controls.Add(this.lblNewCustomerName);
            this.Controls.Add(this.lblMessage);
            this.Controls.Add(this.txtPhone);
            this.Controls.Add(this.lblPhone);
            this.Controls.Add(this.txtContactTitle);
            this.Controls.Add(this.lblContactTitle);
            this.Controls.Add(this.txtZip);
            this.Controls.Add(this.lblZip);
            this.Controls.Add(this.txtCity);
            this.Controls.Add(this.lblCity);
            this.Controls.Add(this.txtAddress);
            this.Controls.Add(this.lblAddress);
            this.Controls.Add(this.txtContactName);
            this.Controls.Add(this.lblContactName);
            this.Controls.Add(this.txtCompanyName);
            this.Controls.Add(this.lblCompanyName);
            this.Controls.Add(this.txtCompanyID);
            this.Controls.Add(this.lblCompanyID);
            this.Controls.Add(this.btnNew);
            this.Controls.Add(this.btnUpdate);
            this.Controls.Add(this.listBoxCustomers);
            this.Controls.Add(this.grpBoxUpdateCustomer);
            this.Controls.Add(this.grpBoxNewCustomer);
            this.Name = "DataSetWithAcceptChanges";
            this.Text = "DataSetWithAcceptChanges";
            this.grpBoxUpdateCustomer.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

      }

      // **** Handle the NEW button click
      protected void btnNew_Click (object sender, System.EventArgs e)
      {
         // Create a new row, populate it with entered
         // Data from the ListBox
         DataRow newRow = _dataTable.NewRow();
         newRow["CustomerID"]   = txtCompanyID.Text;
         newRow["CompanyName"]  = txtCompanyName.Text;
         newRow["ContactName"]  = txtContactName.Text;
         newRow["ContactTitle"] = txtContactTitle.Text;
         newRow["Address"]      = txtAddress.Text;
         newRow["City"]         = txtCity.Text;
         newRow["PostalCode"]   = txtZip.Text;
         newRow["Phone"]        = txtPhone.Text;

         // Add the new row to the table in the dataset
         _dataTable.Rows.Add(newRow);

         // Update the database, any Error is catched in catch block
         try
         {
            // Prepared UPDATE Command is executed
            _dataAdapter.Update(_dataSet,"Customers");

            // Inform the user and repaint Form
            lblMessage.Text = "Updated!";
            Application.DoEvents();

            // Repopulate the list box
            PopulateListBox();

            // Clear all the text fields
            ClearFields();
         }
         catch (SqlException ex)
         {
            _dataSet.RejectChanges();
            MessageBox.Show(ex.Message);
         }
      }

      // **** Handle the UPDATE button click
      protected void btnUpdate_Click (object sender, System.EventArgs e)
      {
         int  index;

         // Get the selected row from the ListBox
         index = listBoxCustomers.SelectedIndex;
         if (index == (-1))
         {
            lblMessage.Text = "Please select a Customer";
            return;
         }
         else
         {
            lblMessage.Text = "";
         }
         DataRow targetRow  = _dataTable.Rows[index];

         // Inform the user
         lblMessage.Text = "Updating: " +  targetRow["CompanyName"];
         Application.DoEvents();

         // Edit the row, no validation is fired between
         // BeginEdit() and EndEdit(). The underlying Dataset
         // will no mark the Row as an UPDATE!
         targetRow.BeginEdit();
         targetRow["CompanyName"] = txtCustomerName.Text;
         targetRow.EndEdit();

         // Use the GetChanges method to create a second DataSet
         // object that is then used to update a data source.
         DataSet dataSetChanged =
            _dataSet.GetChanges(DataRowState.Modified);

         // Test to make sure all the changed rows are without errors
         bool okayFlag = true;
         if (dataSetChanged.HasErrors)
         {
            okayFlag = false;
            string msg = "Error in row with customer ID ";

            // Examine each table in the changed DataSet
            foreach (DataTable theTable in dataSetChanged.Tables)
            {
               // If any table has errors, find out which rows
               if (theTable.HasErrors)
               {
                  // Get the rows with errors
                  DataRow[] errorRows = theTable.GetErrors();

                  // iterate through the errors and correct
                  // (in our case, just identify)
                  foreach (DataRow theRow in errorRows)
                  {
                     msg = msg + theRow["CustomerID"];
                  }
               }
            }
            lblMessage.Text = msg;
         }
         // If we have no errors
         if (okayFlag)
         {
            // Update the database on second dataset
            _dataAdapter.Update(dataSetChanged,"Customers");

            // Inform the user
            lblMessage.Text = "Updated " +  targetRow["CompanyName"];
            Application.DoEvents();

            // Repopulate the list box
            PopulateListBox();
         }
         else
         {  // If we had errors, reject the changes
            _dataSet.RejectChanges();
         }
      }

      // **** Handle the DELETE button click
      protected void btnDelete_Click (object sender, System.EventArgs e)
      {
         // Get the selected row
         DataRow targetRow  = _dataTable.Rows[listBoxCustomers.SelectedIndex];

         // Prepare message for user
         string msg = targetRow["CompanyName"] + " deleted. ";

         // Delete the selected row
         targetRow.Delete();

         // Update the database
         try
         {
            _dataAdapter.Update(_dataSet,"Customers");

            // Repopulate the list box without the deleted record
            PopulateListBox();

            // Inform the user
            lblMessage.Text = msg;
            Application.DoEvents();
         }
         catch (SqlException ex)
         {
            _dataSet.RejectChanges();
            MessageBox.Show(ex.Message);
         }

      }

      // **** Get the selected row and show Customer Name in TextBox
      private void listBoxCustomers_SelectedIndexChanged(object sender, System.EventArgs e)
      {
         DataRow targetRow  = _dataTable.Rows[listBoxCustomers.SelectedIndex];
         txtCustomerName.Text = targetRow["CompanyName"].ToString();
      }

      // **** Cleanup when exiting
      protected override void Dispose( bool disposing )
      {
         if( disposing )
         {
            if (components != null)
            {
               components.Dispose();
            }
         }
         base.Dispose( disposing );
      }

      // **** Entry Point
      public static void Main(string[] args)
      {
         Application.Run(new DataSetWithAcceptChanges());
      }
   }
}

5.   Transactional DataSet Updates using a DataReader

The next Example offers the most possible flexibility over the Update Process. We want to avoid the implizit call to AcceptChanges() withimh the DataAdapter.Update(), so we can re-read the just inserted row, to get the automatically created @@IDENTITY value for a primary key.

  1. Needed Steps
  2. Begin a Database Transaction
  3. Store Data from Input Fields in Dialog to the DataSetRow
  4. Adds a Reference to the Transactions Object
  5. Setup of Command Object
  6. Add the Parameter Values
  7. Execute the Command and read back the new Values
  8. Commit Transaction
  9. Commit DataSet

First create a Table with an auto created Primary Key

CREATE TABLE [dbo].[Customer] (
    [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
    [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
    CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED
    (
        [CustomerID]
    )  ON [PRIMARY]
GO

Here is the full Code

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

namespace Akadia.DataSetWithoutAcceptChanges
{
    // Shows how to INSERT, UPDATE, DELETE with Begin/End Transaction
    // and without AcceptChanges() using a DataReader(). This DataReader()
    // is also used to read back the IDENTITY Value when inserting a new Row.
    public class DataSetWithoutAcceptChanges : System.Windows.Forms.Form
    {
        #region Fields
        // Visual Controls
        private System.ComponentModel.Container components = null;
        private TextBox txtPhone;
        private TextBox txtContactTitle;
        private TextBox txtZip;
        private TextBox txtCity;
        private TextBox txtAddress;
        private TextBox txtContactName;
        private TextBox txtCompanyName;
        private Button btnNew;
        private TextBox txtCustomerName;
        private Button btnUpdate;
        private Button btnDelete;
        private Label lblCity;
        private Label lblZip;
        private Label lblContactTitle;
        private Label lblPhone;
        private Label lblAddress;
        private Label lblCompanyName;
        private Label lblContactName;
        private Label lblNewCustomerName;
        private ListBox listBoxCustomer;
        private GroupBox grpBoxUpdateCustomer;
        private GroupBox grpBoxNewCustomer;
        private Label lblMessage;

        // The DataSet, DataAdapter, and DataTable are members
        // so that we can access them from any member method.
        private SqlDataAdapter _sqlda = null;
        private SqlConnection _sqlcon = null;
        private SqlTransaction _sqltran = null;
        private DataSet _dataSet;
        private DataTable _dataTable;
        private DataView _dataView;
        private string _origCompanyName;
        private string _customerID;
        #endregion

        #region Management Methods
        // **** Constructor
        public DataSetWithoutAcceptChanges()
        {
            // Create the visual Controls
            InitializeComponent();

            // Setup DB Connection **** ADJUST IT FOR YOUR USE ****
            string connectionString = "server=xeon;" +
                "uid=sa; pwd=manager; database=northwind";

            // Establish the connection
            _sqlcon = new SqlConnection(connectionString);
            _sqlcon.Open();

            // Create the DataAdapter
            _sqlda = new SqlDataAdapter();

            // Create INSERT, UPDATE, DELETE "Template" Commands
            InitializeCommands();

            // Populate DataSet
            _dataSet = new DataSet();
            _sqlda.TableMappings.Add("Table", "Customer");
            _sqlda.Fill(_dataSet);

            // Fill the list box with columns from the Dataset
            this.PopulateListBox();
        }

        // **** Cleanup when exiting
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose(disposing);
        }
        #endregion

        #region Methods
        // *** Bind parameters to appropriate columns.
        // Note: The params keyword lets you specify a method
        // parameter that takes an argument where the number
        // of arguments is variable.
        private void AddParms(SqlCommand cmd, params string[] cols)
        {
            // Add each parameter. Note that each colum in
            // table "Customer" is of type VARCHAR !
            foreach (String column in cols)
            {
                cmd.Parameters.Add("@" + column, SqlDbType.Char, 0, column);
            }
        }

        // *** Setup DataAdapter with expicit INSERT, UPDATE,
        // DELETE "Template" Commands.
        private void InitializeCommands()
        {
            // SELECT command
            SqlCommand cmdSelect = new SqlCommand();
            cmdSelect.Connection = _sqlcon;
            cmdSelect.CommandText = "SELECT * FROM Customer";
            _sqlda.SelectCommand = cmdSelect;

            // Create an explicit, reusable INSERT command (Note that
            // CustomerId is autocreated by SQL Server). Here we read
            // back the just inserted Row to get the IDENTITY Value
            // for CustomerId.
            SqlCommand cmdInsert = new SqlCommand();
            cmdInsert.Connection = _sqlcon;
            _sqlda.InsertCommand = cmdInsert;
            _sqlda.InsertCommand.CommandText =
                "INSERT INTO Customer " +
                "(CompanyName, ContactName, ContactTitle, " +
                "Address, City, PostalCode, Phone) " +
                "VALUES(@CompanyName, @ContactName, " +
                "@ContactTitle, @Address, @City, @PostalCode, @Phone); "
                + "SELECT * FROM Customer WHERE CustomerId = @@IDENTITY";

            // Bind parameters to appropriate columns for INSERT command
            this.AddParms(_sqlda.InsertCommand,
                "CompanyName", "ContactName", "ContactTitle",
                "Address", "City", "PostalCode", "Phone");

            // Create an explicit UPDATE command
            SqlCommand cmdUpate = new SqlCommand();
            cmdUpate.Connection = _sqlcon;
            _sqlda.UpdateCommand = cmdUpate;
            _sqlda.UpdateCommand.CommandText =
                "UPDATE Customer " +
                "SET CompanyName = @CompanyName " +
                "WHERE CustomerID = @CustomerId";

            // Bind parameters to appropriate columns for UPDATE command
            this.AddParms(_sqlda.UpdateCommand, "CompanyName", "CustomerID");

            // Create an explicit DELETE command
            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = _sqlcon;
            _sqlda.DeleteCommand = cmdDelete;
            _sqlda.DeleteCommand.CommandText =
                "DELETE FROM Customer " +
                "WHERE customerID = @CustomerId";

            // Bind parameters to appropriate columns for DELETE command
            AddParms(_sqlda.DeleteCommand, "CustomerID");
        }

        // *** Fill the list box with columns from the Dataset
        private void PopulateListBox()
        {
            // Get the only one Table from the Dataset
            _dataTable = _dataSet.Tables["Customer"];
            _dataTable.DefaultView.Sort = "CompanyName DESC";

            // Loop through the Dataset and add each Row
            // to the ListBox
            listBoxCustomer.Items.Clear();
            foreach (DataRow dataRow in _dataTable.Rows)
            {
                listBoxCustomer.Items.Add(
                    dataRow["CompanyName"] + ", " +
                    dataRow["ContactName"] + ", " +
                    dataRow["ContactTitle"] + ", " +
                    dataRow["Address"] + ", " +
                    dataRow["City"] + ", " +
                    dataRow["PostalCode"].ToString() + ", " +
                    dataRow["Phone"].ToString() + ", " +
                    dataRow["CustomerId"].ToString());
            }
        }

        // **** Set all the text fields to empty strings
        private void ClearFields()
        {
            txtCompanyName.Text = "";
            txtContactName.Text = "";
            txtContactTitle.Text = "";
            txtAddress.Text = "";
            txtCity.Text = "";
            txtZip.Text = "";
            txtPhone.Text = "";
        }

        // **** Update to Database without AcceptChanges()
        private void UpdateWithoutAcceptChanges(DataRow pDataRow)
        {
            // Get the Command object depending on the type of the update
            // for the specified DataAdapter.
            SqlCommand sqlcomm = null;
            DataRowVersion drowversion = DataRowVersion.Default;
            switch (pDataRow.RowState)
            {
                case DataRowState.Unchanged:
                    {
                        return;
                    }
                case DataRowState.Modified:
                    {
                        sqlcomm = _sqlda.UpdateCommand;
                        drowversion = DataRowVersion.Default;
                        break;
                    }
                case DataRowState.Added:
                    {
                        sqlcomm = _sqlda.InsertCommand;
                        drowversion = DataRowVersion.Default;
                        break;
                    }
                case DataRowState.Deleted:
                    {
                        sqlcomm = _sqlda.DeleteCommand;
                        drowversion = DataRowVersion.Original;
                        break;
                    }
            }

            // Add the Values to the Parameters
            for (int i = 0; i < pDataRow.Table.Columns.Count; i++)
            {
                string strParameterName = "@" + pDataRow.Table.Columns[i].ColumnName;
                if (sqlcomm.Parameters.Contains(strParameterName))
                {
                    object oValue = pDataRow[i, drowversion];
                    sqlcomm.Parameters[strParameterName].Value = oValue;
                }
            }

            // Execute the Command and read back the new Values
            // for INSERT with autogenerated Primary Key IDENTITY
            SqlDataReader sqlReader = null;
            try
            {
                sqlReader = sqlcomm.ExecuteReader();
                if (sqlReader != null)
                {
                    // For INSERTs, read the IDENTITY Value back into the DataRow
                    if (sqlReader.Read())
                    {
                        CopyRow(sqlReader, pDataRow);
                    }
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    sqlReader.Close();
                }
            }
        }

        //  For INSERTs, read the IDENTITY Value back into the DataRow
        private void CopyRow(SqlDataReader pReader, DataRow pDataRow)
        {
            pDataRow.BeginEdit();
            for (int i = 0; i < pDataRow.Table.Columns.Count; i++)
            {
                pDataRow[i] = pReader.GetValue(i);
            }
            pDataRow.EndEdit();
        }

        // Adds a Reference to the Transactions Object of the Command Object
        //  if a Transacton is open. Returns true, if the action is part of a TRX.
        private bool ManageTrx()
        {
            // Transaction is open
            if (_sqltran != null)
            {
                if (_sqlda.SelectCommand != null)
                {
                    _sqlda.SelectCommand.Transaction = _sqltran;
                }
                if (_sqlda.UpdateCommand != null)
                {
                    _sqlda.UpdateCommand.Transaction = _sqltran;
                }
                if (this._sqlda.InsertCommand != null)
                {
                    _sqlda.InsertCommand.Transaction = _sqltran;
                }
                if (this._sqlda.DeleteCommand != null)
                {
                    _sqlda.DeleteCommand.Transaction = _sqltran;
                }
                return true;
            }
            // There is no Transaction
            return false;
        }

        // **** Update DataRow to the Database
        private bool Update(DataRow pDataRow)
        {
            // Associate Transaction to Command Object
            bool bTrx = ManageTrx();

            // Writes the Changes to the Database
            try
            {
                // No Transaction: Update the Row using the DataAdapter's
                // Update() Method which will call AcceptChanges internally.
                if (bTrx == false)
                {
                    DataRow[] arDataRow = { pDataRow };
                    _sqlda.Update(arDataRow);
                    return false;
                }

                // Existing Transaction: Update the Row using the DataReader's
                // ExecuteReader() Command.
                UpdateWithoutAcceptChanges(pDataRow);
                return true;
            }
            catch (SqlException sqlex)
            {
                // Handle data access exception condition
                // Log specific exception details
                LogException(sqlex);
                return true;
            }
            catch (Exception ex)
            {
                // Handle generic exception condition
                throw ex;
                return true;
            }
        }

        // Helper routine that logs SqlException details to the
        // Application event log
        private void LogException(SqlException sqlex)
        {
            EventLog el = new EventLog();
            el.Source = "Exception from ADO.NET Update Tutorial";
            string strMessage;
            strMessage = "Exception Number : " + sqlex.Number +
                         "(" + sqlex.Message + ") has occurred";
            el.WriteEntry(strMessage);

            foreach (SqlError sqle in sqlex.Errors)
            {
                strMessage = "Message: " + sqle.Message +
                             " Number: " + sqle.Number +
                             " Procedure: " + sqle.Procedure +
                             " Server: " + sqle.Server +
                             " Source: " + sqle.Source +
                             " State: " + sqle.State +
                             " Severity: " + sqle.Class +
                             " LineNumber: " + sqle.LineNumber;
                el.WriteEntry(strMessage);
            }
        }
        #endregion

        #region InitializeComponent
        // **** Create the visual Controls, let the Designer do
        // this and DO NOT CHANGE MANUALLY.
        private void InitializeComponent()
        {
            this.txtCustomerName = new System.Windows.Forms.TextBox();
            this.txtCity = new System.Windows.Forms.TextBox();
            this.btnUpdate = new System.Windows.Forms.Button();
            this.txtContactName = new System.Windows.Forms.TextBox();
            this.txtZip = new System.Windows.Forms.TextBox();
            this.btnDelete = new System.Windows.Forms.Button();
            this.txtContactTitle = new System.Windows.Forms.TextBox();
            this.txtAddress = new System.Windows.Forms.TextBox();
            this.txtCompanyName = new System.Windows.Forms.TextBox();
            this.lblCity = new System.Windows.Forms.Label();
            this.lblZip = new System.Windows.Forms.Label();
            this.lblContactTitle = new System.Windows.Forms.Label();
            this.lblPhone = new System.Windows.Forms.Label();
            this.lblNewCustomerName = new System.Windows.Forms.Label();
            this.lblAddress = new System.Windows.Forms.Label();
            this.listBoxCustomer = new System.Windows.Forms.ListBox();
            this.txtPhone = new System.Windows.Forms.TextBox();
            this.btnNew = new System.Windows.Forms.Button();
            this.lblCompanyName = new System.Windows.Forms.Label();
            this.lblContactName = new System.Windows.Forms.Label();
            this.grpBoxUpdateCustomer = new System.Windows.Forms.GroupBox();
            this.grpBoxNewCustomer = new System.Windows.Forms.GroupBox();
            this.lblMessage = new System.Windows.Forms.Label();
            this.grpBoxUpdateCustomer.SuspendLayout();
            this.SuspendLayout();
            //
            // txtCustomerName
            //
            this.txtCustomerName.Location = new System.Drawing.Point(26, 170);
            this.txtCustomerName.Name = "txtCustomerName";
            this.txtCustomerName.Size = new System.Drawing.Size(275, 20);
            this.txtCustomerName.TabIndex = 4;
            //
            // txtCity
            //
            this.txtCity.Location = new System.Drawing.Point(326, 268);
            this.txtCity.Name = "txtCity";
            this.txtCity.Size = new System.Drawing.Size(160, 20);
            this.txtCity.TabIndex = 15;
            //
            // btnUpdate
            //
            this.btnUpdate.Location = new System.Drawing.Point(308, 169);
            this.btnUpdate.Name = "btnUpdate";
            this.btnUpdate.Size = new System.Drawing.Size(179, 23);
            this.btnUpdate.TabIndex = 0;
            this.btnUpdate.Text = "Update selected Customer Name";
            this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
            //
            // txtContactName
            //
            this.txtContactName.Location = new System.Drawing.Point(111, 268);
            this.txtContactName.Name = "txtContactName";
            this.txtContactName.Size = new System.Drawing.Size(160, 20);
            this.txtContactName.TabIndex = 11;
            //
            // txtZip
            //
            this.txtZip.Location = new System.Drawing.Point(326, 297);
            this.txtZip.Name = "txtZip";
            this.txtZip.Size = new System.Drawing.Size(160, 20);
            this.txtZip.TabIndex = 17;
            //
            // btnDelete
            //
            this.btnDelete.Location = new System.Drawing.Point(494, 76);
            this.btnDelete.Name = "btnDelete";
            this.btnDelete.Size = new System.Drawing.Size(54, 23);
            this.btnDelete.TabIndex = 2;
            this.btnDelete.Text = "Delete";
            this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
            //
            // txtContactTitle
            //
            this.txtContactTitle.Location = new System.Drawing.Point(111, 297);
            this.txtContactTitle.Name = "txtContactTitle";
            this.txtContactTitle.Size = new System.Drawing.Size(160, 20);
            this.txtContactTitle.TabIndex = 12;
            //
            // txtAddress
            //
            this.txtAddress.Location = new System.Drawing.Point(326, 239);
            this.txtAddress.Name = "txtAddress";
            this.txtAddress.Size = new System.Drawing.Size(160, 20);
            this.txtAddress.TabIndex = 13;
            //
            // txtCompanyName
            //
            this.txtCompanyName.Location = new System.Drawing.Point(111, 239);
            this.txtCompanyName.Name = "txtCompanyName";
            this.txtCompanyName.Size = new System.Drawing.Size(160, 20);
            this.txtCompanyName.TabIndex = 9;
            //
            // lblCity
            //
            this.lblCity.Location = new System.Drawing.Point(271, 271);
            this.lblCity.Name = "lblCity";
            this.lblCity.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblCity.Size = new System.Drawing.Size(49, 13);
            this.lblCity.TabIndex = 14;
            this.lblCity.Text = "City";
            //
            // lblZip
            //
            this.lblZip.Location = new System.Drawing.Point(271, 300);
            this.lblZip.Name = "lblZip";
            this.lblZip.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblZip.Size = new System.Drawing.Size(49, 13);
            this.lblZip.TabIndex = 16;
            this.lblZip.Text = "Zip";
            //
            // lblContactTitle
            //
            this.lblContactTitle.Location = new System.Drawing.Point(24, 300);
            this.lblContactTitle.Name = "lblContactTitle";
            this.lblContactTitle.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblContactTitle.Size = new System.Drawing.Size(82, 13);
            this.lblContactTitle.TabIndex = 28;
            this.lblContactTitle.Text = "Contact Title";
            //
            // lblPhone
            //
            this.lblPhone.Location = new System.Drawing.Point(278, 328);
            this.lblPhone.Name = "lblPhone";
            this.lblPhone.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblPhone.Size = new System.Drawing.Size(42, 13);
            this.lblPhone.TabIndex = 20;
            this.lblPhone.Text = "Phone";
            //
            // lblNewCustomerName
            //
            this.lblNewCustomerName.Location = new System.Drawing.Point(27, 154);
            this.lblNewCustomerName.Name = "lblNewCustomerName";
            this.lblNewCustomerName.RightToLeft = System.Windows.Forms.RightToLeft.No;
            this.lblNewCustomerName.Size = new System.Drawing.Size(147, 14);
            this.lblNewCustomerName.TabIndex = 22;
            this.lblNewCustomerName.Text = "Update Customer Name";
            //
            // lblAddress
            //
            this.lblAddress.Location = new System.Drawing.Point(271, 242);
            this.lblAddress.Name = "lblAddress";
            this.lblAddress.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblAddress.Size = new System.Drawing.Size(49, 13);
            this.lblAddress.TabIndex = 26;
            this.lblAddress.Text = "Address";
            //
            // listBoxCustomer
            //
            this.listBoxCustomer.HorizontalScrollbar = true;
            this.listBoxCustomer.Location = new System.Drawing.Point(24, 31);
            this.listBoxCustomer.Name = "listBoxCustomer";
            this.listBoxCustomer.Size = new System.Drawing.Size(463, 108);
            this.listBoxCustomer.Sorted = true;
            this.listBoxCustomer.TabIndex = 3;
            this.listBoxCustomer.SelectedIndexChanged +=
            new System.EventHandler(this.listBoxCustomer_SelectedIndexChanged);
            //
            // txtPhone
            //
            this.txtPhone.Location = new System.Drawing.Point(326, 325);
            this.txtPhone.Name = "txtPhone";
            this.txtPhone.Size = new System.Drawing.Size(160, 20);
            this.txtPhone.TabIndex = 18;
            //
            // btnNew
            //
            this.btnNew.Location = new System.Drawing.Point(503, 284);
            this.btnNew.Name = "btnNew";
            this.btnNew.Size = new System.Drawing.Size(54, 23);
            this.btnNew.TabIndex = 25;
            this.btnNew.Text = "Create";
            this.btnNew.Click += new System.EventHandler(this.btnNew_Click);
            //
            // lblCompanyName
            //
            this.lblCompanyName.Location = new System.Drawing.Point(18, 242);
            this.lblCompanyName.Name = "lblCompanyName";
            this.lblCompanyName.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblCompanyName.Size = new System.Drawing.Size(88, 13);
            this.lblCompanyName.TabIndex = 8;
            this.lblCompanyName.Text = "Company Name";
            //
            // lblContactName
            //
            this.lblContactName.Location = new System.Drawing.Point(24, 271);
            this.lblContactName.Name = "lblContactName";
            this.lblContactName.RightToLeft = System.Windows.Forms.RightToLeft.Yes;
            this.lblContactName.Size = new System.Drawing.Size(82, 13);
            this.lblContactName.TabIndex = 10;
            this.lblContactName.Text = "Contact Name";
            //
            // grpBoxUpdateCustomer
            //
            this.grpBoxUpdateCustomer.Controls.Add(this.btnDelete);
            this.grpBoxUpdateCustomer.ImeMode = System.Windows.Forms.ImeMode.On;
            this.grpBoxUpdateCustomer.Location = new System.Drawing.Point(10, 7);
            this.grpBoxUpdateCustomer.Name = "grpBoxUpdateCustomer";
            this.grpBoxUpdateCustomer.Size = new System.Drawing.Size(567, 194);
            this.grpBoxUpdateCustomer.TabIndex = 29;
            this.grpBoxUpdateCustomer.TabStop = false;
            this.grpBoxUpdateCustomer.Text = "Update selected Customer Name or delete selected Customer";
            //
            // grpBoxNewCustomer
            //
            this.grpBoxNewCustomer.Location = new System.Drawing.Point(10, 218);
            this.grpBoxNewCustomer.Name = "grpBoxNewCustomer";
            this.grpBoxNewCustomer.Size = new System.Drawing.Size(566, 145);
            this.grpBoxNewCustomer.TabIndex = 30;
            this.grpBoxNewCustomer.TabStop = false;
            this.grpBoxNewCustomer.Text = "New Customer";
            //
            // lblMessage
            //
            this.lblMessage.Location = new System.Drawing.Point(13, 374);
            this.lblMessage.Name = "lblMessage";
            this.lblMessage.Size = new System.Drawing.Size(556, 13);
            this.lblMessage.TabIndex = 31;
            //
            // CustTrans
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(584, 396);
            this.Controls.Add(this.txtCustomerName);
            this.Controls.Add(this.lblNewCustomerName);
            this.Controls.Add(this.lblMessage);
            this.Controls.Add(this.txtPhone);
            this.Controls.Add(this.lblPhone);
            this.Controls.Add(this.txtContactTitle);
            this.Controls.Add(this.lblContactTitle);
            this.Controls.Add(this.txtZip);
            this.Controls.Add(this.lblZip);
            this.Controls.Add(this.txtCity);
            this.Controls.Add(this.lblCity);
            this.Controls.Add(this.txtAddress);
            this.Controls.Add(this.lblAddress);
            this.Controls.Add(this.txtContactName);
            this.Controls.Add(this.lblContactName);
            this.Controls.Add(this.txtCompanyName);
            this.Controls.Add(this.lblCompanyName);
            this.Controls.Add(this.btnNew);
            this.Controls.Add(this.btnUpdate);
            this.Controls.Add(this.listBoxCustomer);
            this.Controls.Add(this.grpBoxUpdateCustomer);
            this.Controls.Add(this.grpBoxNewCustomer);
            this.Name = "CustTrans";
            this.Text = "Update Data without AcceptChanges() using a DataReader";
            this.grpBoxUpdateCustomer.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

        }
        #endregion

        #region Event Handling
        // **** Get the selected row and show Customer Name in TextBox
        private void listBoxCustomer_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            object item = listBoxCustomer.Items[listBoxCustomer.SelectedIndex];
            string str = item.ToString();
            txtCustomerName.Text = str.Substring(0, str.IndexOf(","));

            // Store CompanyName and CustomerID for INSERT and DELETE
            _origCompanyName = txtCustomerName.Text;
            _customerID = str.Substring(str.LastIndexOf(",") + 1).Trim();
        }

        // **** Handle the CREATE button click
        protected void btnNew_Click(object sender, System.EventArgs e)
        {
            // Create a new row, populate it with entered
            // Data from the ListBox
            DataRow newRow = _dataTable.NewRow();
            newRow["CompanyName"] = txtCompanyName.Text;
            newRow["ContactName"] = txtContactName.Text;
            newRow["ContactTitle"] = txtContactTitle.Text;
            newRow["Address"] = txtAddress.Text;
            newRow["City"] = txtCity.Text;
            newRow["PostalCode"] = txtZip.Text;
            newRow["Phone"] = txtPhone.Text;

            // Add the new row to the table in the dataset
            _dataTable.Rows.Add(newRow);

            // Begin the Transaction
            _sqltran = _sqlcon.BeginTransaction();

            // Update the Row
            bool bAcceptChanges = Update(newRow);

            // COMMIT Transaction
            _sqltran.Commit();

            // Accept the changes
            if (bAcceptChanges)
            {
                _dataSet.AcceptChanges();
            }

            // Repopulate the list box
            PopulateListBox();

            // Inform the user and repaint Form
            lblMessage.Text = "Inserted!";
            Application.DoEvents();

            // Clear all the text fields
            ClearFields();
        }

        // **** Handle the UPDATE button click
        protected void btnUpdate_Click(object sender, System.EventArgs e)
        {
            // Get the selected row from the ListBox
            int index = listBoxCustomer.SelectedIndex;
            if (index == (-1))
            {
                lblMessage.Text = "Please select a Customer";
                return;
            }
            else
            {
                lblMessage.Text = "";
            }

            // Get the Row from the DataTable which must be updated
            DataRow customerRow = null;
            for (int i = 0; i < _dataTable.Rows.Count; i++)
            {
                if (_dataTable.Rows[i]["CompanyName"].ToString() == _origCompanyName)
                {
                    customerRow = _dataTable.Rows[i];
                    break;
                }
            }

            // Inform the user
            lblMessage.Text = "Updating: " + customerRow["CompanyName"];
            Application.DoEvents();

            // Begin the Transaction
            _sqltran = _sqlcon.BeginTransaction();

            // Insert the modified CompanyName from the GUI to
            // the DataRow. In this example, only the CompanyName
            // can be modified for simplicity.
            // Edit the row, no validation is fired between
            // BeginEdit() and EndEdit(). The underlying Dataset
            // will no mark the Row as an UPDATE!
            customerRow.BeginEdit();
            customerRow["CompanyName"] = txtCustomerName.Text;
            customerRow.EndEdit();

            // Update the Row
            bool bAcceptChanges = Update(customerRow);

            // COMMIT Transaction
            _sqltran.Commit();

            // Accept the changes
            if (bAcceptChanges)
            {
                _dataSet.AcceptChanges();
            }

            // Repopulate the list box
            PopulateListBox();
        }

        // **** Handle the DELETE button click
        protected void btnDelete_Click(object sender, System.EventArgs e)
        {
            // Get the Row from the DatatTable which must be deleted
            DataRow customerRow = null;
            for (int i = 0; i < _dataTable.Rows.Count; i++)
            {
                if (_dataTable.Rows[i]["CustomerId"].ToString() == _customerID)
                {
                    customerRow = _dataTable.Rows[i];
                    break;
                }
            }

            // Delete Row from DataTable
            customerRow.Delete();

            // Begin the Transaction
            _sqltran = _sqlcon.BeginTransaction();

            // Update the Row
            bool bAcceptChanges = Update(customerRow);

            // COMMIT Transaction
            _sqltran.Commit();

            // Accept the changes
            if (bAcceptChanges)
            {
                _dataSet.AcceptChanges();
            }

            // Repopulate the list box
            PopulateListBox();
            txtCustomerName.Text = "";

            // Inform the user and repaint Form
            lblMessage.Text = "Deleted!";
            Application.DoEvents();
        }
        #endregion

        // **** Entry Point
        public static void Main(string[] args)
        {
            Application.Run(new DataSetWithoutAcceptChanges());
        }
    }
}