Export (0) Print
Expand All
2 out of 2 rated this helpful - Rate this topic

Maintaining Database Integrity

This chapter is excerpted from ADO.NET 3.5 Cookbook, Second Edition by Bill Hamilton, published by O'Reilly Media

Introduction

Transactions allow a system to maintain integrity when interacting with multiple data sources. If an update to one data source fails, all changes are rolled back to a known good state. This chapter focuses on using transactions from ADO.NET, maintaining database integrity, and resolving conflicts and concurrency problems.

.NET supports both manual and automatic transactions with classes in the System.Transaction namespace. Manual transactions use the transactional capabilities of the data source. The Microsoft Distributed Transaction Coordinator (DTC) manages automatic transactions.

In a manual transaction, a transaction object is associated with a connection to a data source. Multiple commands against the data source can be associated with the transaction, grouping the commands together as a single transaction. Manual transactions can also be controlled using SQL commands in a stored procedure. Manual transactions are significantly faster than automatic transactions because they do not require interprocess communication (IPC) with the DTC. Manual transactions are limited to performing transactions against a single data source. the section called "Using Manual Transactions" shows how to work with manual transactions.

Automatic transactions are easier to program, can span multiple data sources, and can use multiple resource managers. They are significantly slower than manual transactions. Distributed transactions are demonstrated in the section called "Using Distributed Transactions".

Concurrency problems occur when multiple users attempt to modify unlocked data. Possible problems include lost updates, dirty reads, nonrepeatable reads, and phantom reads. Isolation levels specify transaction locking behavior. Locking data ensures database consistency by controlling how changes made to data within an uncommitted transaction can be used by concurrent transactions. Higher isolation levels increase data accuracy at the expense of data availability. the section called "Using Transaction Isolation Levels to Protect Data" shows how to use transaction isolation levels. the section called "Specifying Locking Hints in a SQL Server Database" shows how to use pessimistic concurrency implemented using SQL Server database locks.

Even in well-designed applications, concurrency violations often occur by design. The ADO.NET DataAdapter raises events that can be handled programmatically to resolve concurrency violations as required by application requirements. the section called "Checking for Concurrency Violations" shows how to use a timestamp to check for concurrency violations, while the section called "Resolving Data Conflicts" shows how to resolve concurrency violations with DataAdapter event handlers.

A DataSet can contain both foreign key and unique constraints as well as relationships between tables to define and maintain data and referential integrity. The order in which DataRow changes from a DataSet containing hierarchical data are updated back to the data source is important to avoid referential integrity errors during the update process. ADO.NET allows data changes of a certain type-inserts, deletes, and updates-to be identified so that they can be processed separately as required. the section called "Avoiding Referential Integrity Problems when Updating Data in Related Tables" shows how to update a DataSet containing hierarchical data back to the data source.

Problem

You need to create a distributed transaction-a transaction that spans two or more data sources.

Solution

Use the TransactionScope class within the System.Transactions namespace.

The solution uses a table named SystemTransaction in the AdoDotNet35Cookbook database. Create the database in the AdoDotNet35Cookbook database in both SQL Server and SQL Server Express instances by executing the following T-SQL statement.

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE SystemTransaction(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )
Tip
You need to configure the Microsoft Distributed Transaction Coordinator (MS DTC) in Windows to let applications enlist resources in a distributed transaction. Follow these steps to fully enable MS DTC:
  1. Select Control Panel→Administrative Tools→Component Services to open the Component Services dialog.

  2. Expand Console Root→Component Services→Computers→My Computer in the tree view.

  3. Right-click My Computer and select Properties from the context menu.

  4. Select the MSDTC tab in the My Computer Properties dialog.

  5. Click the Security Configuration button to open the Security Configuration dialog.

  6. Ensure that all of the checkboxes are checked.

  7. Ensure that the DTC Logon Account name is NT Authority\NetworkService.

  8. Click OK to close the Security Configuration dialog.

  9. Click OK to close the My Computer Properties dialog.

  10. Close the Component Services dialog.

The solution successfully inserts a record into the SystemTransaction table in the AdoDotNet35Cookbook database in both a SQL Server and SQL Server Express instance. The Complete() method of the TransactionScope class is called in the try block to indicate that all operations within the transaction completed successfully. Next, the solution attempts to insert a record into the SystemTransaction table in the AdoDotNet35Cookbook database in both a SQL Server and SQL Server Express instance. The second insert into the SQL Server Express table fails because of a duplicate primary key. The error is written to the console in the catch block and the transaction is closed once the TransactionScope object is closed when its using block ends. The contents of the SystemTransaction table are output to the console at the beginning and after each transaction.

The solution needs a reference to the System.Transactions assembly.

The C# code in Program.cs in the project SystemTransaction is shown in Example 7.1, "File: Program.cs for SystemTransaction solution".

Example 7.1. File: Program.cs for SystemTransaction solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace SystemTransaction
{
    class Program
    {
        private static string sqlConnectString1 = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        private static string sqlConnectString2 = @"Data Source=(local)\SQLExpress;" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        static void Main(string[] args)
        {
            OutputData( );

            // Insert records into both SQL Server and SQL Server Express databases
            string sqlInsert1 = "INSERT INTO SystemTransaction " +
                "VALUES (1, 'Field1.1a', 'Field2.1a')";
            string sqlInsert2 = "INSERT INTO SystemTransaction " +
                "VALUES (1, 'Field1.1b', 'Field2.1b')";
            using (TransactionScope ts = new TransactionScope( ))
            {
                try
                {
                    Console.WriteLine("\n=> Execute: {0}", sqlInsert1);
                    SqlConnection connection1 = new SqlConnection(sqlConnectString1);
                    SqlCommand command1 = new SqlCommand(sqlInsert1, connection1);
                    connection1.Open( );
                    int rowsAff1 = command1.ExecuteNonQuery( );
                    connection1.Close( );

                    Console.WriteLine("=> Execute: {0}", sqlInsert2);
                    SqlConnection connection2 = new SqlConnection(sqlConnectString2);
                    SqlCommand command2 = new SqlCommand(sqlInsert2, connection2);
                    connection2.Open( );
                    int rowsAff2 = command2.ExecuteNonQuery( );
                    connection2.Close( );

                    Console.WriteLine("\nTransactionScope.Complete( )");
                    ts.Complete( );
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\nEXCEPTION: {0}", ex.Message);
                }
            }

            OutputData( );

            // Insert records into both SQL Server and SQL Server Express databases
            sqlInsert1 = "INSERT INTO SystemTransaction " +
                "VALUES (2, 'Field1.2a', 'Field2.2a')";
            // Insert to Express has an "error" in -- Id value 1 already exists
            sqlInsert2 = "INSERT INTO SystemTransaction " +
                "VALUES (1, 'Field1.2b', 'Field2.2b')";
            using (TransactionScope ts = new TransactionScope( ))
            {
                try
                {
                    Console.WriteLine("\n=> Execute: {0}", sqlInsert1);
                    SqlConnection connection1 = new SqlConnection(sqlConnectString1);
                    SqlCommand command1 = new SqlCommand(sqlInsert1, connection1);
                    connection1.Open( );
                    int rowsAff1 = command1.ExecuteNonQuery( );
                    connection1.Close( );

                    Console.WriteLine("=> Execute: {0}", sqlInsert2);
                    SqlConnection connection2 = new SqlConnection(sqlConnectString2);
                    SqlCommand command2 = new SqlCommand(sqlInsert2, connection2);
                    connection2.Open( );
                    int rowsAff2 = command2.ExecuteNonQuery( );
                    connection2.Close( );

                    Console.WriteLine("\nTransactionScope.Complete( )");
                    ts.Complete( );
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\nEXCEPTION: {0}", ex.Message);
                }
            }

            OutputData( );

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }

        static void OutputData( )
        {
            // Output data from table in each SQL Server and SQL Server Express
            string sqlSelect1 = "SELECT * FROM SystemTransaction";
            SqlDataAdapter da1 = new SqlDataAdapter(sqlSelect1, sqlConnectString1);
            DataTable dt1 = new DataTable( );
            da1.Fill(dt1);

            string sqlSelect2 = "SELECT * FROM SystemTransaction";
            SqlDataAdapter da2 = new SqlDataAdapter(sqlSelect2, sqlConnectString2);
            DataTable dt2 = new DataTable( );
            da2.Fill(dt2);

            Console.WriteLine("\n---{0}---", da1.SelectCommand.Connection.DataSource);
            Console.WriteLine("Id\tField1\t\tField2");
            Console.WriteLine("--\t------\t\t------");
            if (dt1.Rows.Count == 0)
                Console.WriteLine("[Empty]");
            else
                foreach (DataRow row in dt1.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t{2}",
                        row["Id"], row["Field1"], row["Field2"]);
                }

            Console.WriteLine("\n---{0}---", da2.SelectCommand.Connection.DataSource);
            Console.WriteLine("Id\tField1\t\tField2");
            Console.WriteLine("--\t------\t\t------");
            if (dt2.Rows.Count == 0)
                Console.WriteLine("[Empty]");
            else
                foreach (DataRow row in dt2.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t{2}",
                        row["Id"], row["Field1"], row["Field2"]);
                }
        }
    }
}

The output is shown in Figure 7.1, "Output for SystemTransaction solution".

Discussion

The System.Transaction namespace introduced in .NET Framework 2.0 contains classes that let you create and participate in a local or distributed transaction. Transactions initiated in SQL Server, ADO.NET, MSMQ, and MSDTC are supported. It provides an explicit programming model based on the Transaction class and an explicit programming model based on the TransactionScope class. Transactions using the TransactionScope class are automatically managed.

A transaction scope is automatically started when a TransactionScope object is created. Call the Complete() method of the TransactionScope object to inform the transaction manager that the transaction should be committed. Failing to call the Complete() method aborts and rolls back the transaction.

Figure 7.1. Output for SystemTransaction solution

Output for SystemTransaction solution

The TransactionScope class provides eight overloaded constructors that define behavior of the transaction:

	TransactionScope( )
	TransactionScope(Transaction trans)
	TransactionScope(TransactionScopeOption transScopeOption)
	TransactionScope(Transaction trans, TimeSpan timeSpan)
	TransactionScope(TransactionScopeOption transScopeOption,
	    TimeSpan timeSpan)
	TransactionScope(TransactionScopeOption transScopeOption,
	    TransactionOptions transOptions)
	TransactionScope(Transaction trans, TimeSpan timeSpan,
	    EnterpriseServicesInteropOption enterpriseServicesInteropOption)
	TransactionScope(TransactionScopeOption transScopeOption,
	    TransactionOptions transOptions,
	    EnterpriseServicesInteropOption enterpriseServicesInteropOption)

Where:

trans

Transaction to be used.

transScopeOption

A value from the TransactionScopeOptions enumeration specifying transaction requirements for the transaction scope. One of Required, RequiresNew, or Suppress.

timeSpan

Time after which the transaction scope times out and aborts.

transOptions

Transaction options to use if a new transaction is created. The TransactionOptions structure encapsulates timeout and isolation level parameters as a simple way to pass these parameters into a transaction.

enterpriseServicesInteropOption

A value from the EnterpriseServicesInteropOption enumeration that specifies how the transaction interacts with COM+ transactions. One of None, Automatic, or Full.

For more information about using the TransactionScope to implement implicit transactions, see "Implementing Implicit Transactions using Transaction Scope" in MSDN.

Problem

You need to explicitly begin, control, and end a transaction from within a .NET application.

Solution

Use the Connection object with structured exceptions (try-catch-finally).

The solution uses a table named ManualTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL in Example 7.2, "Create table ManualTransaction" to create the table.

Example 7.2. Create table ManualTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE ManualTransaction (
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL )

The solution starts a transaction, executes two commands that successfully insert records into the ManualTransaction table, and commits the transaction within a try block. Next, the solution starts a transaction and executes two commands that insert records into the ManualTransaction table. The second insert fails because the Id value is null and the transaction is rolled back within a catch block. The contents of the ManualTransaction table are output to the console after both transactions are completed.

The C# code in Program.cs in the project ManualTransaction is shown in Example 7.3, "File: Program.cs for ManualTransaction solution".

Example 7.3. File: Program.cs for ManualTransaction solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace ManualTransaction
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlSelect = "SELECT * FROM ManualTransaction";
            string sqlInsert = "INSERT INTO ManualTransaction VALUES " +
                "(@Id, @Field1, @Field2)";

            // Create and open a connection
            SqlConnection connection = new SqlConnection(sqlConnectString);
            connection.Open( );

            // Create the parameterized insert command
            SqlCommand command = new SqlCommand(sqlInsert, connection);
            command.Parameters.Add("@Id", SqlDbType.Int);
            command.Parameters.Add("@Field1", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@Field2", SqlDbType.NVarChar, 50);
            // Begin a new transaction and assign it to the command
            SqlTransaction tran = connection.BeginTransaction( );
            command.Transaction = tran;

            // try...catch block that succeeds
            try
            {
                Console.WriteLine("=> Add row with Id = 1");
                command.Parameters["@Id"].Value = 1;
                command.Parameters["@Field1"].Value = "field 1.1";
                command.Parameters["@Field2"].Value = "field 2.1";
                command.ExecuteNonQuery( );

                Console.WriteLine("=> Add row with Id = 2");
                command.Parameters["@Id"].Value = 2;
                command.Parameters["@Field1"].Value = "field 1.2";
                command.Parameters["@Field2"].Value = "field 2.2";
                command.ExecuteNonQuery( );

                // If OK to here, commit the transaction.
                tran.Commit( );
                Console.WriteLine("\nTRANSACTION COMMIT.\n");
            }
            catch (Exception ex)
            {
                tran.Rollback( );
                Console.WriteLine("\nTRANSACTION ROLLBACK.\n{0}\n", ex.Message);
            }

            // Begin a new transaction and assign it to the command
            tran = connection.BeginTransaction( );
            command.Transaction = tran;

            // try...catch block that fails
            try
            {
                Console.WriteLine("=> Add row with Id = 3");
                command.Parameters["@Id"].Value = 3;
                command.Parameters["@Field1"].Value = "field 1.3";
                command.Parameters["@Field2"].Value = "field 2.3";
                command.ExecuteNonQuery( );

                Console.WriteLine("=> Add row with Id = null");
                command.Parameters["@Id"].Value = DBNull.Value;
                command.Parameters["@Field1"].Value = "field 1.4";
                command.Parameters["@Field2"].Value = "field 2.4";
                command.ExecuteNonQuery( );

                // If OK to here, commit the transaction.
                tran.Commit( );
                Console.WriteLine("\nTRANSACTION COMMIT.\n");
            }
            catch (Exception ex)
            {
                tran.Rollback( );
                Console.WriteLine("\nTRANSACTION ROLLBACK.\n{0}\n", ex.Message);
            }

            connection.Close( );

            // Retrieve and output the contents of the table
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            Console.WriteLine("---TABLE ManualTransaction---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }
    }
}

The output is shown in Figure 7.2, "Output for ManualTransaction solution".

Figure 7.2. Output for ManualTransaction solution

Output for ManualTransaction solution

Discussion

Manual transactions allow control over the transaction boundary through explicit commands to start and end the transaction. There is no built-in support for distributed transactions spanning multiple resources with manual transactions.

.NET data providers make available objects to enable manual transactions. The Connection object has a BeginTransaction() method that is used to start a transaction. If successful, the method returns a Transaction object that is used to perform all subsequent actions associated with the transaction, such as committing or aborting. Calling the BeginTransaction() method does not implicitly cause all subsequent commands to execute within the transaction. The Transaction property of the Command object must be set to a transaction that has already been started for the command to execute within the transaction.

Once started, the transaction remains in a pending state until it is explicitly committed or rolled back using the Commit() or Rollback() methods of the Transaction object. The Commit() method of the Transaction is used to commit the database transaction. The Rollback() method of the Transaction is used to roll back a database transaction from a pending state. An InvalidOperationException will be raised if Rollback() is called after Commit() has been called.

The isolation level of the transaction can be specified through an overload of the BeginTransaction() method and if it is not specified, the default isolation level ReadCommitted is used.

Unlike automatic transactions, manual transactions must be explicitly committed or rolled back using the Commit() or Rollback() method. If possible, use the .NET data provider transaction management exclusively; avoid using other transaction models, such as the one provided by SQL Server. If this is necessary for any reason, the section called "Nesting Manual Transactions with the SQL Server .NET Data Provider" discusses using the SQL Server transaction model together with the .NET SQL Server data provider transaction management.

The IDbTransaction interface is implemented by .NET data providers that access relational databases. Applications create an instance of the class implementing the IDbTransaction interface rather than creating an instance of the interface directly. Classes that inherit IDbTransaction must implement the inherited members and typically define provider-specific functionality by adding additional members.

The SQL .NET data provider allows savepoints to be defined allowing a transaction to be partially rolled back to a point in the transaction other than its beginning. The OLE DB .NET data provider allows nested transactions to be started within the parent transaction; the parent transaction cannot commit until all of its nested transactions have committed.

Problem

You need to create a nested transaction using the SQL Server .NET data provider, but the Begin() command that you need is only available with the OLEDB .NET data provider. The SQL Server data provider appears to provide no built-in support for nested transactions. You want to nest transactions when using it.

Solution

Simulate nested transactions with savepoints when using the SQL Server .NET data provider, manage and control the lifetime of the SqlTransaction class, and create the required exception handling.

The solution requires a table named NestManualTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL code in Example 7.4, "Create table NestManualTransaction" to create the table.

Example 7.4. Create table NestManualTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE Nest ManualTransaction (
    Id int NOT NULL,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL,
  CONSTRAINT PK_Nest ManualTransaction PRIMARY KEY CLUSTERED
    ( Id ASC )
)

The solution creates a parameterized insert command that inserts a single record into the NestManualTransaction table. A transaction is started and a record inserted with Id = 1. A savepoint named SavePoint1 is created. Next, an attempt is made to insert two records with Id = 2 and Id = null within a try-catch block. The insert of the Id = null record fails and the transaction is rolled back to SavePoint1 in a catch block and the transaction is committed. The contents of the NestManualTransaction table are retrieved and output to the console, showing the inserted record with Id = 1.

The C# code in Program.cs in the project NestManualTransaction is shown in Example 7.5, "File: Program.cs for NestManualTransaction solution".

Example 7.5. File: Program.cs for NestManualTransaction solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace NestManualTransaction
{
    class Program
    {
        private static string savePointName = "SavePoint1";

        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlSelect = "SELECT * FROM NestManualTransaction";
            string sqlInsert = "INSERT INTO NestManualTransaction VALUES " +
                "(@Id, @Field1, @Field2)";

            // Create and open a connection
            SqlConnection connection = new SqlConnection(sqlConnectString);
            connection.Open( );
            // Begin a new transaction
            SqlTransaction tran = connection.BeginTransaction( );

            // Create the parameterized insert command
            SqlCommand command = new SqlCommand(sqlInsert, connection, tran);
            command.Parameters.Add("@Id", SqlDbType.Int);
            command.Parameters.Add("@Field1", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@Field2", SqlDbType.NVarChar, 50);

            try
            {
                Console.WriteLine("=> Add row with Id = 1");
                command.Parameters["@Id"].Value = 1;
                command.Parameters["@Field1"].Value = "field 1.1";
                command.Parameters["@Field2"].Value = "field 2.1";
                command.ExecuteNonQuery( );
            }
            catch (Exception ex)
            {
                // Exception occurred. Roll back the transaction.
                tran.Rollback( );
                Console.WriteLine("\nTRANSACTION ROLLBACK.\n{0}", ex.Message);
                connection.Close( );

                Console.WriteLine("\nPress any key to continue.");
                Console.ReadKey( );
                return;
            }

            // Create a SavePoint
            tran.Save(savePointName);
            Console.WriteLine("\nSavePoint [{0}] created.\n", savePointName);

            try
            {
                Console.WriteLine("=> Add row with Id = 2");
                command.Parameters["@Id"].Value = 2;
                command.Parameters["@Field1"].Value = "field 1.2";
                command.Parameters["@Field2"].Value = "field 2.2";
                command.ExecuteNonQuery( );

                Console.WriteLine("=> Add row with Id = null");
                command.Parameters["@Id"].Value = DBNull.Value;
                command.Parameters["@Field1"].Value = "field 1.4";
                command.Parameters["@Field2"].Value = "field 2.4";
                command.ExecuteNonQuery( );

                // If OK to here, commit the transaction.
                tran.Commit( );
                Console.WriteLine("\nTRANSACTION COMMIT.\n");
            }
            catch (Exception ex)
            {
                // Rollback to the SavePoint
                tran.Rollback(savePointName);
                Console.WriteLine("\nTRANSACTION ROLLBACK to {0}.\n{1}\n",
                    savePointName, ex.Message);
                // Commit the transaction up to the SavePoint
                tran.Commit( );
            }

            connection.Close( );

            // Retrieve and output the contents of the table
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            Console.WriteLine("---TABLE NestManualTransaction---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }
    }
}

The output is shown in Figure 7.3, "Output for NestManualTransaction solution".

Figure 7.3. Output for NestManualTransaction solution

Output for NestManualTransaction solution

Discussion

The OLE DB .NET data provider's transaction class OleDbTransaction has a Begin() method that is used to initiate a nested transaction. A nested transaction allows part of a transaction to be rolled back without rolling back the entire transaction. An InvalidOperationException is raised if the OLE DB data source does not support nested transactions.

The SQL Server .NET data provider's transaction class SqlTransaction does not have a Begin() method to initiate a nested transaction. Instead, it has a Save() method that creates a savepoint in the transaction that can later be used to roll back a portion of the transaction-to the savepoint rather than rolling back to the start of the transaction. The savepoint is named using the only argument of the Save() method. An overload of the Rollback() method of the SqlTransaction class accepts an argument that you can use to specify the name of the savepoint to roll back to.

Problem

You need to use a DBMS transaction within a SQL Server stored procedure from an ADO.NET transaction with the SQL Server .NET data provider.

Solution

Use error-checking within a try-catch block as shown in Example 7.5, "File: Program.cs for NestManualTransaction solution".

The solution uses a table named SimultaneousTransaction in the database AdoDotNet35Cookbook. Execute the following T-SQL to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE SimultaneousTransaction(
	    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution uses a single stored procedure named InsertSimultaneousTransaction that inserts a single record into the SimultaneousTransaction table within a DBMS transaction. If the record insert fails, the transaction is rolled back; otherwise, the transaction is committed. The solution takes an argument named @RollbackFlag that causes the DBMS transaction to be rolled back simulating a failure. Create the stored procedure by executing the T-SQL code in Example 7.6, "Stored procedure: InsertSimultaneousTransaction".

Example 7.6. Stored procedure: InsertSimultaneousTransaction

USE AdoDotNet35Cookbook
GO

CREATE PROCEDURE InsertSimultaneousTransaction
    @Id int output,
    @Field1 nvarchar(50),
    @Field2 nvarchar(50),
    @RollbackFlag bit = 0
AS
    SET NOCOUNT ON

    BEGIN TRAN

    INSERT SimultaneousTransaction(
        Field1,
        Field2)
    VALUES (
        @Field1,
        @Field2)

    IF @@ERROR <> 0 OR @@ROWCOUNT = 0 OR @RollbackFlag = 1
    BEGIN
        ROLLBACK TRAN
        SET @Id = -1
        RETURN 1
    END

    COMMIT TRAN

    SET @ID = SCOPE_IDENTITY( )

    SELECT @Id Id

    RETURN 0

The solution fills a DataTable with the SimultaneousTransaction table from the AdoDotNet35Cookbook database. The method InsertRecord() is called to insert two records-the second record causes a DBMS rollback. The InsertRecord() method creates a command that calls the stored procedure InsertSimultaneousTransaction in a try-catch block, which shows how to handle the SqlException that indicates a DBMS rollback.

The C# code in Program.cs in the project SimultaneousTransaction is shown in Example 7.7, "File: Program.cs for SimultaneousTransaction solution".

Example 7.7. File: Program.cs for SimultaneousTransaction solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace SimultaneousTransaction
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlText = "SELECT Id, Field1, Field2 " +
                "FROM SimultaneousTransaction";

            // Fill the table
            SqlDataAdapter da = new SqlDataAdapter(sqlText, sqlConnectString);
            DataTable dt = new DataTable( );
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);

            InsertRecord(da, dt, sqlConnectString, "Field1.a", "Field2.a", false);
            InsertRecord(da, dt, sqlConnectString, "Field1.b", "Field2.b", true);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }

        static void InsertRecord(SqlDataAdapter da, DataTable dt,
            string sqlConnectString, string field1, string field2, bool rollbackFlag)
        {
            Console.WriteLine(
                "\nINSERT: Field1 = {0}, Field2 = {1}, RollbackFlag = {2}",
                field1, field2, rollbackFlag);

            // Create the connection.
            SqlConnection connection = new SqlConnection(sqlConnectString);
            // Create the transaction.
            connection.Open( );
            SqlTransaction tran = connection.BeginTransaction( );
            Console.WriteLine("\n=> SqlConnection.BeginTransaction( )");

            // Create command in the transaction with parameters.
            SqlCommand command =
                new SqlCommand("InsertSimultaneousTransaction", connection, tran);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@Id", SqlDbType.Int).Direction =
                ParameterDirection.Output;
            command.Parameters.Add("@Field1", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@Field2", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@RollbackFlag", SqlDbType.Bit);

            try
            {
                // Set the parameters to the user-entered values.
                // Set Field1 and Field2 to DBNull if empty.
                if (field1.Trim( ).Length == 0)
                    command.Parameters["@Field1"].Value = DBNull.Value;
                else
                    command.Parameters["@Field1"].Value = field1;
                if (field2.Trim( ).Length == 0)
                    command.Parameters["@Field2"].Value = DBNull.Value;
                else
                    command.Parameters["@Field2"].Value = field1;
                command.Parameters["@RollbackFlag"].Value = rollbackFlag;

                // Attempt to insert the record.
                command.ExecuteNonQuery( );

                // Success. Commit the transaction.
                tran.Commit( );

                Console.WriteLine("=> SqlTransaction.Commit( ).");
            }
            catch (SqlException ex)
            {
                bool spRollback = false;
                foreach (SqlError err in ex.Errors)
                {
                    // Check if transaction rolled back in the
                    // stored procedure.
                    if (err.Number == 266)
                    {
                        Console.WriteLine(
                            "\n DBMS transaction rolled back in " +
                            "stored procedure.\nEXCEPTION: {0}", ex.Message);
                        spRollback = true;
                        break;
                    }
                }

                if (!spRollback)
                {
                    // transaction was not rolled back by the DBMS
                    // SqlException error. Roll back the transaction.
                    tran.Rollback( );
                    Console.WriteLine("\n=> SqlTransaction.Rollback( )");
                    Console.WriteLine("\nEXCEPTION: {0}", ex.Message);
                }
            }
            catch (Exception ex)
            {
                // Other Exception. Roll back the transaction.
                tran.Rollback( );
                Console.WriteLine("\n=> SqlTransaction.Rollback( )");
                Console.WriteLine("\nEXCEPTION: {0}", ex.Message);
            }
            finally
            {
                connection.Close( );
            }

            // Refresh the data.
            da.Fill(dt);
        }
    }
}

The output is shown in Figure 7.4, "Output for SimultaneousTransaction solution".

Figure 7.4. Output for SimultaneousTransaction solution

Output for SimultaneousTransaction solution

Discussion

SQL Server returns error 266 if a stored procedure exits with a transaction count that is not the same as when the stored procedure was entered. The count is returned by the function @@TRANCOUNT. The error simply sends a message to the client and does not affect execution of the stored procedure. It doesn't mean that the DBMS transaction in the stored procedure could not be started, completed, or terminated properly.

When calling a stored procedure from a .NET manual transaction, the transaction count entering the stored procedure is 1. Using the SQL BEGIN TRAN command in the stored procedure creates a nested transaction, increasing the transaction count to 2. If the stored procedure transaction is subsequently committed with the COMMIT TRAN command, the transaction count is decremented back to 1. Keep in mind commits of inner transactions don't free resources or make modifications permanent, and don't affect outer transactions. If ROLLBACK is called, all inner transactions to the outermost transaction are rolled back and the transaction count is decremented to 0. Error 266 is returned by the rolled-back stored procedure since the transaction count entering the stored procedure is 1 while the count when exiting is 0. Attempting to commit or roll back the transaction from .NET after it has been rolled back in the stored procedure will cause an InvalidOperationException because the transaction has already been rolled back.

The solution catches exceptions raised while executing a stored procedure and checks if they correspond to SQL Server error 266, which is the mismatch between the starting and exiting stored procedure transaction count values as a result of the stored procedure rolling back the transaction. If the stored procedure has rolled back the transaction, it is not rolled back by the .NET code. All other errors raised while executing the stored procedure are rolled back by the .NET code.

Problem

You need to use a transaction when updating a data source using a DataAdapter.

Solution

Associate a Transaction with the Command object for the DataAdapter.

The solution uses a table named DataAdapterTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL in Example 7.8, "Create table DataAdapterTransaction" to create the table.

Example 7.8. Create table DataAdapterTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE DataAdapterTransaction (
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL )

The solution creates a DataAdapter that loads a DataTable with the DataAdapterTransaction table in the AdoDotNet35Cookbook database. The method InsertRecords() adds records to a DataTable and updates the DataAdapterTransaction table using a transaction to rollback the update if all records cannot be added. A CommandBuilder is used to generate the updating logic. A Transaction object on the Connection of the SelectCommand of the DataAdapter is created. The Transaction is associated with the Connection objects for the update commands generated for the DataAdapter by the CommandBuilder. The Update() method of the DataAdapter is called to update DataTable changes to the Orders table. If no errors are encountered, the transaction is committed; otherwise, all changes made are rolled back. The InsertRecords() method is called twice. The first call successfully adds two records. The second call fails to add two records because the Id of the second record is null. The contents of the DataAdapterTransaction table is output to the console after both insert attempts.

The C# code in Program.cs in the project DataAdapterTransaction is shown in Example 7.9, "File: Program.cs for DataAdapterTransaction solution".

Example 7.9. File: Program.cs for DataAdapterTransaction solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace DataAdapterTransaction
{
    class Program
    {
        private static string sqlConnectString = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        private static string sqlSelect = "SELECT * FROM DataAdapterTransaction";

        static void Main(string[] args)
        {
            object[,] o1 = {{ "1", "field 1.1", "field 2.1" },
                          { "2", "field 1.2", "field 2.2" }};
            InsertRecords(o1);

            object[,] o2 = {{ "3", "field 1.3", "field 2.3" },
                           { null, "field 1.4", "field 2.4" }};
            InsertRecords(o2);

            // Retrieve and output the contents of the table
            SqlDataAdapter daRead = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dtRead = new DataTable( );
            daRead.Fill(dtRead);
            Console.WriteLine("---TABLE DataAdapterTransaction---");
            foreach (DataRow row in dtRead.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }

        static void InsertRecords(object[,] o)
        {
            DataTable dt = new DataTable( );
            SqlTransaction tran;

            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create a DataAdapter
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connection);
            // Stop updating when an error is encountered for roll back.
            da.ContinueUpdateOnError = false;
            // Create CommandBuilder and generate updating logic.
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            // Create and fill a DataTable with schema and data
            da.Fill(dt);
            // Open the connection
            connection.Open( );
            // Begin a new transaction and assign it to the DataAdapter
            tran = connection.BeginTransaction( );
            da.SelectCommand.Transaction = tran;

            // Add two rows that will succeed update
            for (int i = 0; i <= o.GetUpperBound(0); i++)
            {
                dt.Rows.Add(new object[] { o[i, 0], o[i, 1], o[i, 2] });
                Console.WriteLine(
                    "=> Row with [Id = {0}] added to DataTable.", o[i, 0]);
            }

            Console.WriteLine("=> Updating data source using DataAdapter.");
            try
            {
                da.Update(dt);
                tran.Commit( );

                Console.WriteLine("\nTRANSACTION COMMIT.\n");
            }
            catch (Exception ex)
            {
                tran.Rollback( );
                Console.WriteLine("\nTRANSACTION ROLLBACK.\n{0}\n", ex.Message);
            }
            finally
            {
                connection.Close( );
            }
        }
    }
}

The output is shown in Figure 7.5, "Output for DataAdapterTransaction solution".

Figure 7.5. Output for DataAdapterTransaction solution

Output for DataAdapterTransaction solution

Discussion

You can use a transaction with a DataAdapter to roll back updates made by the DataAdapter before an error.

If, as in the solution, a CommandBuilder is used to generate the update logic for the DataAdapter, associate the Transaction with the SelectCommand of the DataAdapter as shown in the solution code:

	da.SelectCommand.Transaction = tran;

If custom update logic is used for the DataAdapter, the Transaction must be associated with the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter, but not the SelectCommand, as shown in the following code:

	da.DeleteCommand.Transaction = tran;
	da.InsertCommand.Transaction = tran;
	da.UpdateCommand.Transaction = tran;

Problem

You sometimes get referential integrity errors when you update a DataSet that contains related parent, child, and grandchild records back to the underlying data source, but want to perform the update without errors.

Solution

Use one DataAdapter for each DataTable to update the deleted, updated, and inserted rows as shown in the following example.

The solution uses three tables named Parent, Child, and Grandchild. Execute the following T-SQL batch to create the tables:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE Parent (
	    ParentId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    Field1 nvarchar(50) NOT NULL,
	    Field2 nvarchar(50) NOT NULL
	);

	CREATE TABLE Child (
	    ChildId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    ParentId int NOT NULL,
	    Field3 nvarchar(50) NOT NULL,
	    Field4 nvarchar(50) NOT NULL
	);
	CREATE TABLE Grandchild (
	    GrandchildId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    ChildId int NOT NULL,
	    Field5 nvarchar(50) NOT NULL,
	    Field6 nvarchar(50) NOT NULL
	);

	ALTER TABLE Child WITH CHECK
	ADD CONSTRAINT FK_Child_Parent FOREIGN KEY(ParentId)
	REFERENCES Parent (ParentId)
	GO

	ALTER TABLE Grandchild WITH CHECK
	ADD CONSTRAINT FK_Grandchild_Child FOREIGN KEY(ChildId)
	REFERENCES Child (ChildId)
	GO

The schema of table Parent used in this solution is shown in Table 7.1, "Parent table schema".

Table 7.1. Parent table schema

Column name

Data type

Length

Allow nulls?

ParentId

int

4

No

Field1

nvarchar

50

Yes

Field2

nvarchar

50

Yes


The schema of table Child used in this solution is shown in Table 7.2, "Child table schema".

Table 7.2. Child table schema

Column name

Data type

Length

Allow nulls?

ChildId

int

4

No

ParentId

int

4

No

Field3

nvarchar

50

Yes

Field4

nvarchar

50

Yes


The schema of table Grandchild used in this solution is shown in Table 7.3, "Grandchild table schema".

Table 7.3. Grandchild table schema

Column name

Data type

Length

Allow nulls?

GrandchildId

int

4

No

ChildId

int

4

No

Field5

nvarchar

50

Yes

Field6

nvarchar

50

Yes


The solution uses 12 stored procedures:

GetParent

Used to retrieve a single record from the Parent table if the optional @ParentId parameter is specified or all Parent records if it is not.

DeleteParent

Used to delete the record specified by the @ParentId parameter from the Parent table.

InsertParent

Used to insert a record into the Parent table and return the ParentId identity value for the new record.

UpdateParent

Used to update all field values for the record in the Parent table specified by the @ParentId input parameter.

GetChild

Used to retrieve a single record from the Child table if the optional @ChildId parameter is specified or all Child records if it is not.

DeleteChild

Used to delete the record specified by the @ChildId parameter from the Child table.

InsertChild

Used to insert a record into the Child table and return the ChildId identity value for the new record.

UpdateChild

Used to update all field values for the record in the Child table specified by the @ChildId input parameter.

GetGrandchild

Used to retrieve a single record from the Grandchild table if the optional @GrandchildId parameter is specified or all Grandchild records if it is not.

DeleteGrandchild

Used to delete the record specified by the @GrandchildId parameter from the Grandchild table.

InsertGrandchild

Used to insert a record into the Grandchild table and return the GrandchildId identity value for the new record.

UpdateGrandchild

Used to update all field values for the record in the Grandchild table specified by the @GrandchildId input parameter.

The 12 stored procedures are shown in Examples Example 7.10, "Stored procedure: GetParent" through Example 7.21, "Stored procedure: UpdateGrandchild".

Example 7.10. Stored procedure: GetParent

CREATE PROCEDURE GetParent
    @ParentId int = NULL
AS
    SET NOCOUNT ON

    IF @ParentId IS NOT NULL
    BEGIN
        SELECT ParentId, Field1, Field2
        FROM Parent
        WHERE ParentId = @ParentId

        RETURN 0
    END

    SELECT ParentId, Field1, Field2
    FROM Parent

    RETURN 0

Example 7.11. Stored procedure: DeleteParent

CREATE PROCEDURE DeleteParent
    @ParentId int
AS
    SET NOCOUNT ON

    DELETE FROM  Parent
    WHERE ParentId = @ParentId

    RETURN 0

Example 7.12. Stored procedure: InsertParent

CREATE PROCEDURE InsertParent
    @ParentId int OUTPUT,
    @Field1 nvarchar(50) = NULL,
    @Field2 nvarchar(50) = NULL
AS
    SET NOCOUNT ON

    INSERT Parent(Field1, Field2)
    VALUES (@Field1, @Field2)

    IF @@ROWCOUNT=0
        RETURN 1

    SET @ParentId = SCOPE_IDENTITY( )

    SELECT @ParentId ParentId

    RETURN 0

Example 7.13. Stored procedure: UpdateParent

CREATE PROCEDURE UpdateParent
    @ParentId int,
    @Field1 nvarchar(50) = NULL,
    @Field2 nvarchar(50) = NULL
AS
    SET NOCOUNT ON

    UPDATE Parent
    SET
        Field1 = @Field1,
        Field2 = @Field2
    WHERE
        ParentId = @ParentId

    IF @@ROWCOUNT=0
        RETURN 1

    RETURN 0

Example 7.14. Stored procedure: GetChild

CREATE PROCEDURE GetChild
    @ChildId int = NULL
AS
    SET NOCOUNT ON

    IF @ChildId IS NOT NULL
    BEGIN
        SELECT ChildId, ParentId, Field3, Field4
        FROM Child
        WHERE ChildId = @ChildId

        RETURN 0
    END

    SELECT ChildId, ParentId, Field3, Field4
    FROM Child

    RETURN 0

Example 7.15. Stored procedure: DeleteChild

CREATE PROCEDURE DeleteChild
    @ChildId int
AS
    SET NOCOUNT ON

    DELETE FROM Child
    WHERE ChildId = @ChildId

    RETURN 0

Example 7.16. Stored procedure: InsertChild

CREATE PROCEDURE InsertChild
    @ChildId int OUTPUT,
    @ParentId int,
    @Field3 nvarchar(50) = NULL,
    @Field4 nvarchar(50) = NULL
AS
    SET NOCOUNT ON

    INSERT Child(ParentId, Field3, Field4)
    VALUES (@ParentId, @Field3, @Field4)

    IF @@ROWCOUNT = 0
        RETURN 1

    SET @ChildId = SCOPE_IDENTITY( )

    SELECT @ChildId ChildId

    RETURN 0

Example 7.17. Stored procedure: UpdateChild

CREATE PROCEDURE UpdateChild
    @ChildId int,
    @ParentId int,
    @Field3 nvarchar(50) = NULL,
    @Field4 nvarchar(50) = NULL
AS
    SET NOCOUNT ON

    UPDATE
        Child
    SET
        ParentId = @ParentId,
        Field3 = @Field3,
        Field4 = @Field4
    WHERE
        ChildId = @ChildId

    if @@ROWCOUNT=0
        RETURN 1

    RETURN 0

Example 7.18. Stored procedure: GetGrandchild

CREATE PROCEDURE GetGrandchild
    @GrandchildId int = null
AS
    SET NOCOUNT ON

    IF @GrandchildId IS NOT NULL
    BEGIN
        SELECT GrandchildId, ChildId, Field5, Field6
        FROM Grandchild
        WHERE GrandchildId = @GrandchildId

        RETURN 0
    END

    SELECT GrandchildId, ChildId, Field5, Field6
    FROM Grandchild

    RETURN 0

Example 7.19. Stored procedure: DeleteGrandchild

CREATE PROCEDURE DeleteGrandchild
    @GrandchildId int
AS
    SET NOCOUNT ON

    DELETE FROM Grandchild
    WHERE GrandchildId = @GrandchildId

    return 0

Example 7.20. Stored procedure: InsertGrandchild

CREATE PROCEDURE InsertGrandchild
    @GrandchildId int OUTPUT,
    @ChildId int,
    @Field5 nvarchar(50) = null,
    @Field6 nvarchar(50) = null
AS
    SET NOCOUNT ON

    INSERT Grandchild(ChildId, Field5, Field6)
    VALUES (@ChildId, @Field5, @Field6)

    IF @@ROWCOUNT=0
        RETURN 1

    SET @GrandchildId = SCOPE_IDENTITY( )

    SELECT @GrandchildId GrandchildId

    RETURN 0

Example 7.21. Stored procedure: UpdateGrandchild

CREATE PROCEDURE UpdateGrandchild
    @GrandchildId int,
    @ChildId int,
    @Field5 nvarchar(50) = NULL,
    @Field6 nvarchar(50) = NULL
AS
    SET NOCOUNT ON

    UPDATE
        Grandchild
    SET
        ChildId = @ChildId,
        Field5 = @Field5,
        Field6 = @Field6
    WHERE
        GrandchildId=@GrandchildId

    IF @@ROWCOUNT=0
        RETURN 1

    RETURN 0

The solution creates a DataSet containing the Parent, Child, and Grandchild DataTable objects. DataRelation objects are created relating the tables. DataAdapter objects are created for each DataTable and the select, delete, insert, and update Command objects are specified for each using the custom logic in the 12 stored procedures used by this solution. The DataAdapter objects are used to fill the tables in the DataSet. The solution creates random data, randomly modifies the data including the relationships, and deletes all data-each time the data source is updated by calling the UpdateData( ) method, which calls the Update ( ) method of the DataAdapter for each table in the correct order so that referential integrity do not occur when the tables in the database are updated. The contents of the data source are output after each output.

The C# code in Program.cs in the project AvoidReferentialIntegrityProblems is shown in Example 7.22, "File: Program.cs for AvoidReferentialIntegrityProblem solution".

Example 7.22. File: Program.cs for AvoidReferentialIntegrityProblem solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace AvoidReferentialIntegrityProblems
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
               "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            DataColumnCollection cols;
            DataColumn col;

            // Build the parent table.
            DataTable dtParent = new DataTable("Parent");
            cols = dtParent.Columns;
            col = cols.Add("ParentId", typeof(int));
            col.AutoIncrement = true;
            col.AutoIncrementSeed = -1;
            col.AutoIncrementStep = -1;
            cols.Add("Field1", typeof(string)).MaxLength = 50;
            cols.Add("Field2", typeof(string)).MaxLength = 50;

            // Build the child table.
            DataTable dtChild = new DataTable("Child");
            cols = dtChild.Columns;
            col = cols.Add("ChildId", typeof(int));
            col.AutoIncrement = true;
            col.AutoIncrementSeed = -1;
            col.AutoIncrementStep = -1;
            cols.Add("ParentId", typeof(int)).AllowDBNull = false;
            cols.Add("Field3", typeof(string)).MaxLength = 50;
            cols.Add("Field4", typeof(string)).MaxLength = 50;

            // Build the grandchild table.
            DataTable dtGrandchild = new DataTable("Grandchild");
            cols = dtGrandchild.Columns;
            col = cols.Add("GrandchildId", typeof(int));
            col.AutoIncrement = true;
            col.AutoIncrementSeed = -1;
            col.AutoIncrementStep = -1;
            cols.Add("ChildId", typeof(int)).AllowDBNull = false;
            cols.Add("Field5", typeof(string)).MaxLength = 50;
            cols.Add("Field6", typeof(string)).MaxLength = 50;

            // Create the DataSet and add tables to it
            DataSet ds = new DataSet( );
            ds.Tables.Add(dtParent);
            ds.Tables.Add(dtChild);
            ds.Tables.Add(dtGrandchild);

            // Create the data relations in the DataSet
            ds.Relations.Add("FK_Child_Parent",
                dtParent.Columns["ParentId"], dtChild.Columns["ParentId"]);
            ds.Relations.Add("FK_Grandchild_Child",
                dtChild.Columns["ChildId"], dtGrandchild.Columns["ChildId"]);

            // Create a connection
            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create the DataAdapter objects for the tables.
            SqlDataAdapter daParent = new SqlDataAdapter( );
            SqlDataAdapter daChild = new SqlDataAdapter( );
            SqlDataAdapter daGrandchild = new SqlDataAdapter( );

            // Build the parent select command.
            SqlCommand selectCommand = new SqlCommand("GetParent", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
            daParent.SelectCommand = selectCommand;

            // Build the parent delete command.
            SqlCommand deleteCommand = new SqlCommand("DeleteParent",
                daParent.SelectCommand.Connection);
            deleteCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0,
                "ParentId");
            daParent.DeleteCommand = deleteCommand;

            // Build the parent insert command.
            SqlCommand insertCommand = new SqlCommand("InsertParent",
                daParent.SelectCommand.Connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
            insertCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0,
                "ParentId");
            insertCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50,
                "Field1");
            insertCommand.Parameters.Add("@Field2", SqlDbType.NVarChar, 50,
                "Field2");
            daParent.InsertCommand = insertCommand;

            // Build the parent update command.
            SqlCommand updateCommand = new SqlCommand("UpdateParent",
                daParent.SelectCommand.Connection);
            updateCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0,
                "ParentId");
            updateCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50,
                "Field1");
            updateCommand.Parameters.Add("@Field2", SqlDbType.NVarChar, 50,
                "Field2");
            daParent.UpdateCommand = updateCommand;

            // Build the child select command.
            selectCommand = new SqlCommand("GetChild", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
            daChild.SelectCommand = selectCommand;

            // Build the child delete command.
            deleteCommand = new SqlCommand("DeleteChild",
                daChild.SelectCommand.Connection);
            deleteCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.Parameters.Add("@ChildId", SqlDbType.Int, 0,
                "ChildId");
            daChild.DeleteCommand = deleteCommand;

            // Build the child insert command.
            insertCommand = new SqlCommand("InsertChild",
                daChild.SelectCommand.Connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
            insertCommand.Parameters.Add("@ChildId", SqlDbType.Int, 0,
                "ChildId");
            insertCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0,
                "ParentId");
            insertCommand.Parameters.Add("@Field3", SqlDbType.NVarChar, 50,
                "Field3");
            insertCommand.Parameters.Add("@Field4", SqlDbType.NVarChar, 50,
                "Field4");
            daChild.InsertCommand = insertCommand;

            // Build the child update command.
            updateCommand = new SqlCommand("UpdateChild",
                daChild.SelectCommand.Connection);
            updateCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.Parameters.Add("@ChildId", SqlDbType.Int, 0,
                "ChildId");
            updateCommand.Parameters.Add("@ParentId", SqlDbType.Int, 0,
                "ParentId");
            updateCommand.Parameters.Add("@Field3", SqlDbType.NVarChar, 50,
                "Field3");
            updateCommand.Parameters.Add("@Field4", SqlDbType.NVarChar, 50,
                "Field4");
            daChild.UpdateCommand = updateCommand;

            // Build the grandchild select command.
            selectCommand = new SqlCommand("GetGrandchild", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
            daGrandchild.SelectCommand = selectCommand;

            // Build the grandchild delete command.
            deleteCommand = new SqlCommand("DeleteGrandchild",
                daGrandchild.SelectCommand.Connection);
            deleteCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.Parameters.Add("@GrandchildId", SqlDbType.Int, 0,
                "GrandchildId");
            daGrandchild.DeleteCommand = deleteCommand;

            // Build the grandchild insert command.
            insertCommand = new SqlCommand("InsertGrandchild",
                daGrandchild.SelectCommand.Connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
            insertCommand.Parameters.Add("@GrandchildId", SqlDbType.Int, 0,
                "GrandchildId");
            insertCommand.Parameters.Add("@ChildId", SqlDbType.Int, 0,
                "ChildId");
            insertCommand.Parameters.Add("@Field5", SqlDbType.NVarChar, 50,
                "Field5");
            insertCommand.Parameters.Add("@Field6", SqlDbType.NVarChar, 50,
                "Field6");
            daGrandchild.InsertCommand = insertCommand;

            // Build the grandchild update command.
            updateCommand = new SqlCommand("UpdateGrandchild",
                daGrandchild.SelectCommand.Connection);
            updateCommand.CommandType = CommandType.StoredProcedure;
            updateCommand.Parameters.Add("@GrandchildId", SqlDbType.Int, 0,
                "GrandchildId");
            updateCommand.Parameters.Add("@ChildId", SqlDbType.Int, 0,
                "ChildId");
            updateCommand.Parameters.Add("@Field5", SqlDbType.NVarChar, 50,
                "Field5");
            updateCommand.Parameters.Add("@Field6", SqlDbType.NVarChar, 50,
                "Field6");
            daGrandchild.UpdateCommand = updateCommand;

            // Fill the DataSet.
            daParent.Fill(ds, "Parent");
            daChild.Fill(ds, "Child");
            daGrandchild.Fill(ds, "Grandchild");

            // Create some data, update the source, and output to console
            CreateData(ds, 3, 3, 3);
            UpdateData(ds, daParent, daChild, daGrandchild);
            Console.WriteLine("=> Data created.");
            OutputData(ds);

            // Modufy some data, update the source, and output to console
            ModifyData(ds);
            UpdateData(ds, daParent, daChild, daGrandchild);
            Console.WriteLine("\n=> Data modified.");
            OutputData(ds);

            // Delete all data, update the source, and output to console
            DeleteData(ds);
            UpdateData(ds, daParent, daChild, daGrandchild);
            Console.WriteLine("\n=> Data deleted.");
            OutputData(ds);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }

        static void CreateData(DataSet ds, int parentRows, int childRows,
            int grandchildRows)
        {
            // Generate some data into each of the related tables.
            for (int iParent = 0; iParent < parentRows; iParent++)
            {
                // Generate parentRows of data in the parent table.
                DataRow parentRow = ds.Tables["Parent"].NewRow( );
                parentRow["Field1"] = Guid.NewGuid( ).ToString( );
                parentRow["Field2"] = Guid.NewGuid( ).ToString( );
                ds.Tables["Parent"].Rows.Add(parentRow);

                for (int iChild = 0; iChild < childRows; iChild++)
                {
                    // Generate childRows of data in the child table.
                    DataRow childRow = ds.Tables["Child"].NewRow( );
                    childRow["ParentId"] =
                        (int)parentRow["ParentId"];
                    childRow["Field3"] = Guid.NewGuid( ).ToString( );
                    childRow["Field4"] = Guid.NewGuid( ).ToString( );
                    ds.Tables["Child"].Rows.Add(childRow);

                    for (int iGrandchild = 0; iGrandchild < grandchildRows;
                        iGrandchild++)
                    {
                        // Generate grandchildRows of data in the
                        // grandchild table.
                        DataRow grandchildRow =
                            ds.Tables["Grandchild"].NewRow( );
                        grandchildRow["ChildId"] =
                            (int)childRow["ChildId"];
                        grandchildRow["Field5"] =
                            Guid.NewGuid( ).ToString( );
                        grandchildRow["Field6"] =
                            Guid.NewGuid( ).ToString( );
                        ds.Tables["Grandchild"].Rows.Add(
                            grandchildRow);
                    }
                }
            }
        }

        static void ModifyData(DataSet ds)
        {
            // Randomly delete or modify rows from the grandchild, child, and
            // parent rows.
            Random r = new Random((int)DateTime.Now.Ticks);

            // Modify grandchild rows.
            for (int i = ds.Tables["Grandchild"].Rows.Count; i > 0; i--)
            {
                DataRow grandchildRow =
                    ds.Tables["Grandchild"].Rows[i - 1];

                if (r.Next(2) == 0)
                {
                    grandchildRow["Field5"] = Guid.NewGuid( ).ToString( );
                    grandchildRow["Field6"] = Guid.NewGuid( ).ToString( );
                }
                else
                    grandchildRow.Delete( );
            }

            // Modify or delete child rows.
            for (int i = ds.Tables["Child"].Rows.Count; i > 0; i--)
            {
                DataRow childRow = ds.Tables["Child"].Rows[i - 1];

                if (r.Next(2) == 0)
                {
                    childRow["Field3"] = Guid.NewGuid( ).ToString( );
                    childRow["Field4"] = Guid.NewGuid( ).ToString( );
                }
                else
                    childRow.Delete( );
            }

            // Modify or delete parent rows.
            for (int i = ds.Tables["Parent"].Rows.Count; i > 0; i--)
            {
                DataRow parentRow = ds.Tables["Parent"].Rows[i - 1];

                if (r.Next(2) == 0)
                {
                    parentRow["Field1"] = Guid.NewGuid( ).ToString( );
                    parentRow["Field2"] = Guid.NewGuid( ).ToString( );
                }
                else
                    parentRow.Delete( );

            }

            // Insert two rows into parent, child, and grandchild.
            CreateData(ds, 2, 2, 2);
        }

        static void DeleteData(DataSet ds)
        {
            foreach (DataRow row in ds.Tables["Grandchild"].Rows)
                row.Delete( );

            foreach (DataRow row in ds.Tables["Child"].Rows)
                row.Delete( );

            foreach (DataRow row in ds.Tables["Parent"].Rows)
                row.Delete( );
        }

        static void UpdateData(DataSet ds, SqlDataAdapter daParent,
            Sql DataAdapter daChild, SqlDataAdapter daGrandchild)
        {
            daGrandchild.Update(ds.Tables["Grandchild"].Select(
                null, null, DataViewRowState.Deleted));
            daChild.Update(ds.Tables["Child"].Select(
                null, null, DataViewRowState.Deleted));
            daParent.Update(ds.Tables["Parent"].Select(
                null, null, DataViewRowState.Deleted));
            daParent.Update(ds.Tables["Parent"].Select(
                null, null, DataViewRowState.ModifiedCurrent));
            daParent.Update(ds.Tables["Parent"].Select(
                null, null, DataViewRowState.Added));
            daChild.Update(ds.Tables["Child"].Select(
                null, null, DataViewRowState.ModifiedCurrent));
            daChild.Update(ds.Tables["Child"].Select(
                null, null, DataViewRowState.Added));
            daGrandchild.Update(ds.Tables["Grandchild"].Select(
                null, null, DataViewRowState.ModifiedCurrent));
            daGrandchild.Update(ds.Tables["Grandchild"].Select(
                null, null, DataViewRowState.Added));
        }

        static void OutputData(DataSet ds)
        {
            // Output data for the parent, junction, and child tables
            Console.WriteLine("\n---DATASET---");

            if (ds.Tables["Parent"].Rows.Count == 0)
                Console.WriteLine("[Empty]");
            else
            {
                foreach (DataRow rowParent in ds.Tables["Parent"].Rows)
                {
                    Console.WriteLine("ParentId = {0}\tField1 = {1}\tField2 = {2}",
                        rowParent["ParentId"],
                        rowParent["Field1"].ToString( ).Substring(0, 13),
                        rowParent["Field2"].ToString( ).Substring(0, 13));

                    foreach (DataRow rowChild in
                        rowParent.GetChildRows("FK_Child_Parent"))
                    {
                        Console.WriteLine("++ChildId = {0}\tField3 = {1}\tField4 = {2}",
                            rowChild["ChildId"],
                            rowChild["Field3"].ToString( ).Substring(0, 13),
                            rowChild["Field4"].ToString( ).Substring(0, 13));

                        foreach (DataRow rowGrandchild in
                            rowChild.GetChildRows("FK_Grandchild_Child"))
                        {
                            Console.WriteLine(
                                "++++GrandchildId = {0}\tField5 = {1}\tField6 = {2}",
                                rowGrandchild["GrandchildId"],
                                rowGrandchild["Field5"].ToString( ).Substring(0, 13),
                                rowGrandchild["Field6"].ToString( ).Substring(0, 13));
                        }
                    }
                }
            }
        }
    }
}

The output is shown in Figure 7.6, "Output for AvoidReferentialIntegrityProblems solution".

Figure 7.6. Output for AvoidReferentialIntegrityProblems solution

Output for AvoidReferentialIntegrityProblems solution

Discussion

To avoid referential integrity problems when updating the data source from a DataSet containing related tables, use one DataAdapter for each DataTable to update the deleted, updated, and inserted rows in the following order:

  1. Deleted grandchild rows

  2. Deleted child rows

  3. Deleted parent rows

  4. Updated parent rows

  5. Inserted parent rows

  6. Updated child rows

  7. Inserted child rows

  8. Updated grandchild rows

  9. Inserted grandchild rows

In the solution, this is done using the following code:

	daGrandchild.Update(ds.Tables["Grandchild"].Select(
	    null, null, DataViewRowState.Deleted));
	daChild.Update(ds.Tables["Child"].Select(
	    null, null, DataViewRowState.Deleted));
	daParent.Update(ds.Tables["Parent"].Select(
	    null, null, DataViewRowState.Deleted));
	daParent.Update(ds.Tables["Parent"].Select(
	    null, null, DataViewRowState.ModifiedCurrent));
	daParent.Update(ds.Tables["Parent"].Select(
	    null, null, DataViewRowState.Added));
	daChild.Update(ds.Tables["Child"].Select(
	    null, null, DataViewRowState.ModifiedCurrent));
	daChild.Update(ds.Tables["Child"].Select(
	    null, null, DataViewRowState.Added));
	daGrandchild.Update(ds.Tables["Grandchild"].Select(
	    null, null, DataViewRowState.ModifiedCurrent));
	daGrandchild.Update(ds.Tables["Grandchild"].Select(
	    null, null, DataViewRowState.Added));

There are three related tables-parent, child, and grandparent-and one DataAdapter for each table. An overload of the Select( ) method of the DataTable is used to retrieve the subset of rows identified by the state argument containing a value from the DataViewRowState enumeration:

  • Added to get the subset of inserted rows

  • Deleted to get the subset of deleted rows

  • ModifiedCurrent to get the subset of modified rows

There are a few other considerations involving the primary key:

  • If the primary key cannot be modified once added, the updated and inserted rows can be processed together in the same statement.

  • If the primary key can be modified after it has been added, the database must cascade the updated primary key values to the child records or else a referential integrity violation will occur. The UpdateCommand property of child tables must accept either the Original or the Current value of the foreign key if it is used in the concurrency check.

  • If the primary key for the DataTable is an autoincrement value and the primary key value is generated by the data source, the InsertCommand must return the primary key value from the data source and use it to update the value in the DataSet. The DataSet can automatically cascade this new value to the foreign keys in the related child records.

Problem

You need to enforce a business rule based on multiple columns in a table.

Solution

Use expression-based columns to enforce business rules at the user interface tier. The business rule for this solution is that the sum of Field1 and Field2 for a row in the table must be 10.

The solution uses a table named EnforceBusinessRulesColumnExpressions in the AdoDotNet35Cookbook database. Execute the T-SQL statement in Example 7.23, "Create table EnforceBusinessRulesColumnExpressions" to create the table.

Example 7.23. Create table EnforceBusinessRulesColumnExpressions

USE AdoDotNet35Cookbook
GO
CREATE TABLE EnforceBusinessRulesColumnExpressions (
    Id int NOT NULL PRIMARY KEY,
    Field1 int NOT NULL,
    Field2 int NOT NULL
)

The solution creates a DataTable and creates a schema for it matching the EnforceBusinessRulesColumnExpressions table in the database. An expression column is added to the table that returns a Boolean value indicating whether the sum of Field1 and Field2 is equal to 10. A DataAdapter is created and an event handler is attached to handle its RowUpdating event. A CommandBuilder is used to supply the updating logic. Four rows are added to the DataTable-two are valid and two are invalid based on the expression. The Update() method of the DataAdapter is called to update the table in the database. For each row being updated or inserted, the handler for the RowUpdating event of the DataAdapter checks whether the value of the expression column is false indicating that the data is invalid according to the business rule defined by the expression column. If the business rule has not been met, an error is set on the row and the update for the row is skipped. The contents of the DataTable and the table in the database are output to the console.

The C# code in Program.cs in the project EnforceBusinessRulesColumnExpressions is shown in Example 7.24, "File: Program.cs for EnforceBusinessRulesColumnExpressions solution".

Example 7.24. File: Program.cs for EnforceBusinessRulesColumnExpressions solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace EnforceBusinessRulesColumnExpressions
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";
            string sqlSelect = "SELECT * FROM EnforceBusinessRulesColumnExpressions";

            // Build the table.
            DataTable dt = new DataTable(  );
            DataColumnCollection cols = dt.Columns;
            cols.Add("Id", typeof(int));
            cols.Add("Field1", typeof(int));
            cols.Add("Field2", typeof(int));
            // add the primary key
            dt.PrimaryKey = new DataColumn[] { cols["Id"] };
            // Expression whether the sum of Field1 and Field2 equals 10
            cols.Add("CK_Expression", typeof(Boolean), "Field1 + Field2 = 10");

            // Create the DataAdapter, handling the RowUpdating event.
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            // Add some rows to the DataTable 
            dt.Rows.Add(new object[] { 1, 7, 3 }); //Valid
            dt.Rows.Add(new object[] { 2, 5, 2 }); //Invalid
            dt.Rows.Add(new object[] { 3, 5, 5 }); //Valid
            dt.Rows.Add(new object[] { 4, 1, 1 }); //Inalid

            // Output the DataTable
            Console.WriteLine("---DATATABLE BEFORE DATAADAPTER.UPDATE(  )---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine(
                    "Id = {0}\tField1 = {1}\tField2 = {2}\tRowError = {3}",
                    row["Id"], row["Field1"], row["Field2"], row.RowError);

            // Update the data source using the DataAdapter
            da.Update(dt);
            Console.WriteLine("\n=> DataAdapter.Update(  )");

            Console.WriteLine("\n---DATATABLE AFTER DATAADAPTER.UPDATE(  )---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine(
                    "Id = {0}\tField1 = {1}\tField2 = {2}\tRowError = {3}",
                    row["Id"], row["Field1"], row["Field2"], row.RowError);

            //Load the Datatable from the data source and output
            dt.Clear(  );
            da.Fill(dt);
            Console.WriteLine("\n---DATA SOURCE AFTER DATAADAPTER.UPDATE(  )---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }

        static void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
            // For insert or update statements, check that the
            // calculated constraint column is true.
            if ((e.StatementType == StatementType.Insert ||
                e.StatementType == StatementType.Update) &
                !(bool)e.Row["CK_Expression"])
            {
                // Constraint has not been met.
                // Set an error on the row and skip it.
                e.Row.RowError = "Constraint error.";
                e.Status = UpdateStatus.SkipCurrentRow;
            }
        }
    }
}

The output is shown in Figure 7.7, "Output for EnforceBusinessRulesColumnExpressions solution".

Figure 7.7. Output for EnforceBusinessRulesColumnExpressions solution

Output for EnforceBusinessRulesColumnExpressions solution

Discussion

The RowUpdating event of the DataAdapter occurs during the Update() method before the command to update a row is executed against the data source. The event fires with each row update attempt.

The RowUpdating event handler receives an argument of type RowUpdatingEventArgs that provides information specifically related to the event as described in Table 7.4, "RowUpdatingEventArgs properties".

Table 7.4. RowUpdatingEventArgs properties

Property

Description

Command

Gets the Command to execute during the Update() method.

Errors

Gets errors generated by the .NET data provider when the Command was executed.

Row

Gets the DataRow to send through the Update() method.

StatementType

Gets the type of SQL statement to execute. This is one of the following values from the StatementType enumeration: Select, Insert, Update, or Delete.

Status

Gets or sets the action to take with the current and remaining rows during the Update() method. This is a value from the UpdateStatus enumeration (described in Table 7.5, "UpdateStatus enumeration").

TableMapping

Gets the DataTableMapping to send through the Update() method.


Table 7.5, "UpdateStatus enumeration" describes the values in the UpdateStatus enumeration used by the Status property of the RowUpdatingEventArgs object.

Table 7.5. UpdateStatus enumeration

Value

Description

Continue

Continue processing the rows. This is the default value.

ErrorsOccurred

The event handler reports that the update should be treated as an error.

SkipAllRemainingRows

Do not update the current row and skip updating the remaining rows.

SkipCurrentRow

Do not update the current row and continue updating with the subsequent row.


The Update() method of the DataAdapter raises two events for every row in the data source that is updated. The order of the events is:

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

  2. The OnRowUpdating event is raised.

  3. The update command executes against the row in the data source.

  4. If the UpdatedRowSource property of the Command is set to FirstReturnedRecord or Both, the first returned result is placed in the DataRow.

  5. If the UpdateRowSource property of the Command is set to OutputParameters or Both, the output parameters are placed in the DataRow.

  6. The OnDataRowUpdated event is raised.

  7. AcceptChanges() is called.

If zero rows are affected, the DBConcurrencyException is raised during the update operation on a row. This usually indicates a concurrency violation.

The solution uses the RowUpdating event of the DataAdapter to check whether the expression column in the DataTable is true, indicating that the business rule has been satisfied, before a database record is updated. If the expression if false, an error is set on the row and the Status is set to SkipCurrentRow, preventing the record in the database from being updated and continuing the processing with the next row.

Problem

You need to programmatically retrieve the constraint information that is defined in a SQL Server database.

Solution

Use the SQL Server Management Objects (SMO), catalog views, or INFORMATION_SCHEMA views to get information about primary key, foreign key, and check constraints. The solution shows how to use all three techniques to retrieve constraint information and output it to the console.

This solution needs a reference to the Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.SqlEnum assemblies.

The C# code in Program.cs in the project RetrieveConstraintsSqlServer is shown in Example 7.25, "File: Program.cs for RetrieveConstraintsSqlServer solution".

Example 7.25. File: Program.cs for RetrieveConstraintsSqlServer solution

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;

namespace RetrieveConstraintsSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            // ---Use SMO---
            Console.WriteLine("---SMO---");
            Server server = new Server("(local)");
            Database db = server.Databases["AdventureWorks"];
            Console.WriteLine("---Primary key constraints---");
            Console.WriteLine("Row 1:");
            bool pkFlag = false;
            int pkCount = 0;
            // Iterate over table collection
            foreach (Table table in db.Tables)
            {
                // Iterate over index collection
                foreach (Index index in table.Indexes)
                {
                    // Count the primary keys
                    if (index.IndexKeyType == IndexKeyType.DriPrimaryKey)
                    {
                        pkCount++;
                        // Output details about the first primary index
                        if (!pkFlag)
                        {
                            Console.WriteLine("\tConstraintName = {0}", index.Name);
                            Console.WriteLine("\tTableName = {0}", table.Name);
                            Console.WriteLine("\tColumnCount = {0}",
                                index.IndexedColumns.Count);
                            pkFlag = true;
                        }
                    }
                }
            }
            Console.WriteLine("Total = {0}", pkCount);

            Console.WriteLine("\n---Foreign key constraints---"); 
            Console.WriteLine("Row 1:");
            bool fkFlag = false;
            int fkCount = 0;
            // Iterate over table collection
            foreach (Table table in db.Tables)
            {
                // Iterate over the foreign key collection
                foreach (ForeignKey fk in table.ForeignKeys)
                {
                    // Count the foreign keys
                    fkCount++;
                    // Output details about the first foreign key
                    if (!fkFlag)
                    {
                        Console.WriteLine("\tConstraintName = {0}", fk.Name);
                        Console.WriteLine("\tUpdateRule = {0}", fk.UpdateAction);
                        Console.WriteLine("\tDeleteRule = {0}", fk.DeleteAction);
                        Console.WriteLine("\tParentTable = {0}", table.Name);
                        Console.WriteLine("\tChildTable = {0}", fk.ReferencedTable);
                        Console.WriteLine("\tColumnCount = {0}", fk.Columns.Count);
                        fkFlag = true;
                    }
                }
            }
            Console.WriteLine("Total = {0}", fkCount);

            Console.WriteLine("\n---Check constraints---");
            Console.WriteLine("Row 1:");
            bool ccFlag = false;
            int ccCount = 0;
            // Iterate over table collection
            foreach (Table table in db.Tables)
            {
                // Iterate over index collection
                foreach (Check cc in table.Checks)
                {
                    // Count the check constraints
                    ccCount++;
                    // Output details about the first check constraint
                    if (!ccFlag)
                    {
                        Console.WriteLine("\tTableName = {0}", table.Name);
                        Console.WriteLine("\tConstraintName = {0}", cc.Name);
                        Console.WriteLine("\tCheckClause = {0}", cc.Text);
                        ccFlag = true;
                    }
                }
            }
            Console.WriteLine("Total = {0}", ccCount);

            // ---Use catalog views--- 
            Console.WriteLine("\n---CATALOG VIEWS---");
            // Get primary key constraints
            string cvGetPrimaryKeyConstraints =
                "SELECT i.name PKName, t.name TableName, " +
                "COUNT(c.column_id) ColumnCount "+
                "FROM sys.indexes i " +
                "JOIN sys.tables t ON i.object_id = t.object_id " +
                "JOIN sys.index_columns c ON i.index_id = c.index_id AND " +
                "    t.object_id = c.object_id " +
                "WHERE is_primary_key = 1 " +
                "GROUP BY i.name, t.name " +
                "ORDER BY t.name, i.name";
            SqlDataAdapter daCvPKC =
                new SqlDataAdapter(cvGetPrimaryKeyConstraints, sqlConnectString);
            DataTable dtCvPKC = new DataTable(  );
            daCvPKC.Fill(dtCvPKC);

            Console.WriteLine("---Primary key constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtCvPKC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtCvPKC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtCvPKC.Rows.Count);

            // Get foreign key constraints
            string cvGetForeignKeyConstraints =
                "SELECT fk.name ConstraintName, " +
                "MIN(fk.update_referential_action_desc) UpdateRule, " +
                "MIN(fk.delete_referential_action_desc) DeleteRule, " +
                "MIN(pt.name) ParentTable, MIN(ct.name) ChildTable, " +
                "COUNT(fkc.constraint_column_id) " +
                "FROM sys.foreign_keys fk " +
                "JOIN sys.tables pt ON fk.referenced_object_id = pt.object_id " +
                "JOIN sys.tables ct ON fk.parent_object_id = ct.object_id " +
                "JOIN sys.foreign_key_columns fkc ON " +
                "    fk.object_id = fkc.constraint_object_id " +
                "GROUP BY fk.name " +
                "ORDER BY fk.name ";
            SqlDataAdapter daCvFKC =
                new SqlDataAdapter(cvGetForeignKeyConstraints, sqlConnectString);
            DataTable dtCvFKC = new DataTable(  );
            daCvFKC.Fill(dtCvFKC);

            Console.WriteLine("\n---Foreign key constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtCvFKC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtCvFKC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtCvFKC.Rows.Count);

            // Get check constraints 
            string cvGetCheckConstraints =
                "SELECT t.name TableName, cc.name ConstraintName, " +
                "cc.definition CheckClause " +
                "FROM sys.check_constraints cc " +
                "JOIN sys.tables t on cc.parent_object_id = t.object_id " +
                "ORDER BY TableName, ConstraintName";
            SqlDataAdapter daCvCC =
                new SqlDataAdapter(cvGetCheckConstraints, sqlConnectString);
            DataTable dtCvCC = new DataTable(  );
            daCvCC.Fill(dtCvCC);

            Console.WriteLine("\n---Check constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtCvCC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtCvCC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtCvCC.Rows.Count);

            // ---Use information schema views---
            Console.WriteLine("\n---INFORMATION SCHEMA VIEWS---");
            // Get primary key constraints
            string isvGetPrimaryKeyConstraints =
                "SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, " +
                "COUNT(kcu.COLUMN_NAME) ColumnCount " +
                "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
                "JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON " +
                "tc.CONSTRAINT_NAME=kcu.CONSTRAINT_NAME " +
                "WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY' " +
                "GROUP BY tc.CONSTRAINT_NAME, tc.TABLE_NAME " +
                "ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_NAME;";
            SqlDataAdapter daIsvPKC =
                new SqlDataAdapter(isvGetPrimaryKeyConstraints, sqlConnectString);
            DataTable dtIsvPKC = new DataTable(  );
            daIsvPKC.Fill(dtIsvPKC);

            Console.WriteLine("---Primary key constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtIsvPKC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtIsvPKC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtIsvPKC.Rows.Count);

            // Get foreign key constraints
            string isvGetForeignKeyConstraints =
                "SELECT rc.CONSTRAINT_NAME, MIN(rc.UPDATE_RULE) UpdateRule, " +
                "MIN(rc.DELETE_RULE) DeleteRule, " +
                "MIN(kcuP.TABLE_NAME) ParentTable, " +
                "MIN(kcuC.TABLE_NAME) ChildTable, " +
                "COUNT(kcuP.COLUMN_NAME) ColumnCount " +
                "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc " +
                "LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON " +
                "rc.UNIQUE_CONSTRAINT_NAME=kcuP.CONSTRAINT_NAME " +  
                "LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON " +
                "rc.CONSTRAINT_NAME=kcuC.CONSTRAINT_NAME AND " +
                "kcuP.ORDINAL_POSITION=kcuC.ORDINAL_POSITION " +
                "GROUP BY rc.CONSTRAINT_NAME " +
                "ORDER BY rc.CONSTRAINT_NAME";
            SqlDataAdapter daIsvFKC =
                new SqlDataAdapter(isvGetForeignKeyConstraints, sqlConnectString);
            DataTable dtIsvFKC = new DataTable(  );
            daIsvFKC.Fill(dtIsvFKC);

            Console.WriteLine("\n---Foreign key constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtIsvFKC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtIsvFKC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtIsvFKC.Rows.Count);

            // Get check constraints
            string isvGetCheckConstraints =
                "SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, cc.CHECK_CLAUSE " +
                "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " +
                "JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON " +
                "tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME " +
                "WHERE CONSTRAINT_TYPE='CHECK' " +
                "ORDER BY tc.TABLE_NAME, cc.CONSTRAINT_NAME";
            SqlDataAdapter daIsvCC =
                new SqlDataAdapter(isvGetCheckConstraints, sqlConnectString);
            DataTable dtIsvCC = new DataTable(  );
            daIsvCC.Fill(dtIsvCC);

            Console.WriteLine("\n---Check constraints---");
            Console.WriteLine("Row 1:");
            foreach (DataColumn col in dtIsvCC.Columns)
                Console.WriteLine("\t{0} = {1}",
                    col.ColumnName, dtIsvCC.Rows[0][col.Ordinal]);
            Console.WriteLine("Total = {0}", dtIsvCC.Rows.Count);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in Figure 7.8, "Output for RetrieveConstraintsSqlServer solution".

Discussion

The following subsections discuss the three approaches demonstrated in the solution-SQL Management Objects (SMO), catalog views, and information schema views.

Figure 7.8. Output for RetrieveConstraintsSqlServer solution

Output for RetrieveConstraintsSqlServer solution

SQL Server Management Objects

SMO is supported in .NET Framework 2.0 and later and is compatible with SQL Server 7.0 and later. SMO is a collection of objects designed for programming SQL Server management tasks. SMO is used to create databases, perform backups, create jobs, configure SQL Server, configure security, and many other administrative tasks.

The SMO Server class represents an instance of SQL Server and determines the connection to the physical SQL Server installation. The Server object is the topmost object in the SMO instance object hierarchy. A connection to a SQL Server instance is created when a Server object is instantiated.

The SMO Database class represents a SQL Server database, either system- or user-defined, on the SQL Server instance specified by the Server parameter in the Database class constructor and by the Parent property. In the solution, the AdventureWorks database is specified in the constructor.

A Database object has a TableCollection object that represents all tables defined in the database as a collection of Table objects. The Tables property accesses the collection. The Table class gives you access to the different constraints.

A Table object has an IndexCollection object that represents all indexes defined on the table as a collection of Index objects. The Indexes property accesses the collection. The Index object has an IndexKeyType property, which takes a value from the IndexKeyType enumeration-one of DriPrimaryKey, DriUniqueKey,or None-that specifies the type of key on which the index is created. The solution accesses properties for the first primary key and outputs that information to the console. Information about unique key constraints is accessed similarly. The number of primary key constraints on all of the tables in the database is output to the console.

A Table object has a ForeignKeyCollection object that represents all foreign keys defined on the table as a collection of ForeignKey objects. The ForeignKeys property accesses the collection. The solution accesses properties for the first foreign key and outputs that information to the console. The number of foreign key constraints on all of the tables in the database is output to the console.

A Table object has a CheckCollection object that represents all check constraints defined on a table as a collection of Check objects. The Checks property accesses the collection. The solution accesses properties for the first check constraint and out-puts that information to the console. The number of check constraints on all of the tables in the database is output to the console.

Catalog views

Catalog views were introduced in SQL Server 2005 and return metadata used by the SQL Server Database Engine. Microsoft recommends using catalog views because they provide the most general interface to catalog metadata and the most efficient way to get and present this information. All user-available catalog metadata is exposed through catalog views. Catalog views don't expose information about replication, backup, Database Maintenance Plan, or SQL Server Agent.

Catalog views are defined within each database in a schema named sys. The meta-data returned is limited to that which the user has permission to view. To access a catalog view, simply specify the fully qualified view name. For more information about catalog views, see the section called "Retrieving Database Schema Information from SQL Server" and SQL Server Books Online.

The solution uses six catalog views to retrieve information about constraints in the database.

sys.check_constraints

A row for each check constraint in the current database.

sys.foreign_keys

A row for each foreign key in the current database.

sys.foreign_key_columns

A row for each column or set of columns that make up a foreign key.

sys.index_columns

A row for each column that is part of an index.

sys.indexes

A row for each index in the current database.

sys.tables

A row for each table in the current database.

Primary key information is obtained by querying the sys.indexes, sys.index_columns, and sys.tables catalog views. The result set returns the primary key name, table name, and the number of columns in the primary key for each primary key in the database, sorted by primary key name and table name.

	SELECT
	    i.name PKName,
	    t.name TableName,
	    COUNT(c.column_id) ColumnCount
	FROM sys.indexes i
	    JOIN sys.tables t ON i.object_id = t.object_id
	    JOIN sys.index_columns c ON i.index_id = c.index_id AND t.object_id = c.object_id
	WHERE is_primary_key = 1
	GROUP BY i.name, t.name
	ORDER BY t.name, i.name

Information about foreign keys is obtained by querying the sys.foreign_keys, sys.foreign_key_columns, and sys.tables catalog views. The result set returns the foreign key name, update rule, delete rule, parent table, child table, and the number of columns in the constraint for each foreign key constraint in the database sorted by the foreign key name.

	SELECT
	    fk.name ConstraintName,
	    MIN(fk.update_referential_action_desc) UpdateRule,
	    MIN(fk.delete_referential_action_desc) DeleteRule,
	    MIN(pt.name) ParentTable,
	    MIN(ct.name) ChildTable,
	    COUNT(fkc.constraint_column_id)
	FROM sys.foreign_keys fk
	    JOIN sys.tables pt ON fk.referenced_object_id = pt.object_id
	    JOIN sys.tables ct ON fk.parent_object_id = ct.object_id
	    JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
	GROUP BY fk.name
	ORDER BY fk.name

Check constraint information is obtained by querying the sys.check_constraints and sys.tables catalog views. The results set returns the table name, constraint name, and check clause for each check constraint in the database.

	SELECT
	    t.name TableName,
	    cc.name ConstraintName,
	    cc.definition CheckClause
	FROM sys.check_constraints cc
	    JOIN sys.tables t on cc.parent_object_id = t.object_id
	ORDER BY TableName, ConstraintName

Information schema views

Information schema views were first available in SQL Server 7.0 and later. They provide system-table independent access to SQL Server metadata. The views are based on system tables and provide a layer of abstraction that allows applications to continue to work properly if the system tables change in future releases of SQL Server. Information schema views provide an alternative to using system stored procedures that were previously and are still available. The INFORMATION_SCHEMA views conform to the SQL-92 standard.

Information schema views are defined within each database in a schema named INFORMATION_SCHEMA. To access the views, specify the fully qualified view name. In the solution, for example, the view containing metadata about the tables in the database is accessed using the following syntax:

	INFORMATION_SCHEMA.TABLES

The metadata returned is limited to that which the user has permission to view. Like any other views, information schema views can also be joined in queries or participate in complex queries to extract specific information. For more information about information schema views, see the section called "Retrieving Database Schema Information from SQL Server" and SQL Server Books Online.

The solution uses four information schema views to retrieve information about constraints in the database:

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

A row for each CHECK constraint in the current database.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

A row for each column that is constrained as a key in the current database.

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

A row for each foreign key constraint in the current database.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

A row for each table constraint in the current database.

Information about primary keys is obtained by querying the TABLE_CONSTRAINTS and KEY_COLUMN_USAGE information schema views. The views are joined on the CONSTRAINT_NAME field and restricted to constraints with a CONSTRAINT_TYPE of Primary Key. The result set is sorted on the TABLE_NAME, COLUMN_NAME, and ORDINAL_POSITION fields:

	SELECT
	    tc.CONSTRAINT_NAME,
	    tc.TABLE_NAME,
	    kcu.COLUMN_NAME,
	    kcu.ORDINAL_POSITION
	FROM
	    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN
	    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON
	    tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
	WHERE
	    tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
	ORDER BY
	    tc.TABLE_NAME,
	    kcu.COLUMN_NAME,
	    kcu.ORDINAL_POSITION

Foreign key information is obtained by querying the REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE information schema views. The REFERENTIAL_CONSTRAINTS view is joined to the KEY_COLUMN_USAGE view on the UNIQUE_CONSTRAINT_NAME column to return information about the parent table and its columns. The REFERENTIAL_CONSTRAINTS view is joined again to the KEY_COLUMN_USAGE view on the CONSTRAINT_NAME, matching the ORDINAL_POSITION of the parent column to return information about the child table and its columns. The result set is sorted in ascending order on the parent TABLE_NAME, child TABLE_NAME, and parent constraint column ORDINAL_POSITION:

	SELECT
	    rc.CONSTRAINT_NAME,
	    rc.UPDATE_RULE,
	    rc.DELETE_RULE,
	    kcuP.TABLE_NAME ParentTable,
	    kcuC.TABLE_NAME ChildTable,
	    kcuP.COLUMN_NAME ParentColumn,
	    kcuC.COLUMN_NAME ChildColumn
	FROM
	    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc LEFT JOIN
	    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuP ON
	    rc.UNIQUE_CONSTRAINT_NAME = kcuP.CONSTRAINT_NAME LEFT JOIN
	    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuC ON
	    rc.CONSTRAINT_NAME = kcuC.CONSTRAINT_NAME AND
	    kcuP.ORDINAL_POSITION = kcuC.ORDINAL_POSITION
	ORDER BY
	    kcuP.TABLE_NAME,
	    kcuC.TABLE_NAME,
	    kcuP.ORDINAL_POSITION;

Check constraint information is obtained by querying the TABLE_CONSTRAINTS and CHECK_CONSTRAINTS information schema views. The views are joined on the CONSTRAINT_NAME field and restricted to constraints with a CONSTRAINT_TYPE of CHECK. The result set is sorted on the TABLE_NAME and CONSTRAINT_NAME fields:

	SELECT
	    tc.TABLE_NAME,
	    tc.CONSTRAINT_NAME,
	    cc.CHECK_CLAUSE
	FROM
	    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN
	    INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON
	    tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
	WHERE
	    CONSTRAINT_TYPE = 'CHECK'
	ORDER BY
	    tc.TABLE_NAME,
	    cc.CONSTRAINT_NAME

Problem

You need to check for concurrency violations while using optimistic concurrency.

Solution

Use a timestamp data type column to manage data concurrency violations.

The solution uses a single table named ConcurrencyViolation in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE ConcurrencyViolation(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Version timestamp NOT NULL)

Execute the following T-SQL batch to create sample data needed by the solution:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO ConcurrencyViolation VALUES (1, 'Field1.1', null);
	INSERT INTO ConcurrencyViolation VALUES (2, 'Field1.2', null);
	INSERT INTO ConcurrencyViolation VALUES (3, 'Field1.3', null);

The solution creates a DataTable named TableA and fills it with the schema and data from the ConcurrencyViolation table in the AdoDotNet35Cookbook database.

The timestamp column is made read-only. An event handler named da_RowUpdated is created for the RowUpdated event of the DataAdapter. The event handler da_RowUpdated checks that an error did not occur (Status=Continue) and that a row was either inserted or updated. For those rows, the current value of the timestamp column is retrieved from the ConcurrencyViolaton in the database and used to update the row in the DataTable TableA. The parameterized update command is created for the DataAdapter-the WHERE clause of the update command matches both the Id (primary key used to locate the record) field and the Version field (timestamp column used to determine whether the data has been changed since last read and control concurrency violations).

The solution creates a DataTable named TableB and fills and configures it in exactly the same way as TableA in the preceding paragraph. Together DataTable objects TableA and TableB simulate two users simultaneously interacting with the same data.

The contents of DataTable objects TableA and TableB are output to the console to show the initial state.

The record with Id = 2 in DataTable TableA is modified, updated to table ConcurrencyViolation, and output to the console to show the effect of the change. The RowUpdated event handler da_RowUpdated retrieves the updated value for the timestamp field and updates the value in the updated DataTable row.

The record with Id=2 in DataTable TableB is modified. An attempt is made to update the modification to the table ConcurrencyViolation. The update command of the DataAdapter cannot find a matching record for both the Id and timestamp field because of the previous update by DataTable TableA. The RowUpdated handler does not process the timestamp update logic for the row because the Status is ErrorsOccurred rather than Continue. It might make sense in some situations to update the contents of DataTable TableB at this point. The catch block for the Update() method of the DataAdapter outputs the error to the console. The contents of DataTable objects TableA and TableB are output to the console.

The C# code in Program.cs in the project CheckConcurrencyViolation is shown in Example 7.26, "File: Program.cs for CheckConcurrencyViolation solution".

Example 7.26. File: Program.cs for CheckConcurrencyViolation solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace CheckConcurrencyViolation
{
    class Program
    {
        private static string sqlConnectString = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        private static DataTable dtA; 
        private static DataTable dtB;
        private static SqlDataAdapter daA;
        private static SqlDataAdapter daB;

        static void Main(string[] args)
        {
            // Build SELECT and UPDATE statement; DELETE and INSERT not
            // required for the solution, but would need to be supplied
            string selectText = "SELECT Id, Field1, Version FROM " +
                "ConcurrencyViolation";
            string updateText = "UPDATE ConcurrencyViolation " +
                "SET Field1 = @Field1 " +
                "WHERE Id = @Id AND Version = @Version";

            // Create table A and fill it with the schema.
            dtA = new DataTable("TableA");
            daA = new SqlDataAdapter(selectText, sqlConnectString);
            daA.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);
            daA.FillSchema(dtA, SchemaType.Source);
            dtA.Columns["Version"].ReadOnly = false;
            daA.Fill(dtA);

            // Create the update command and define the parameters.
            daA.UpdateCommand = new SqlCommand(updateText,
                daA.SelectCommand.Connection);
            daA.UpdateCommand.CommandType = CommandType.Text;
            daA.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
            daA.UpdateCommand.Parameters["@Id"].SourceVersion =
                DataRowVersion.Original;
            daA.UpdateCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50,
                "Field1");
            daA.UpdateCommand.Parameters["@Field1"].SourceVersion =
                DataRowVersion.Current;
            daA.UpdateCommand.Parameters.Add("@Version", SqlDbType.Timestamp, 0,
                "Version");
            daA.UpdateCommand.Parameters["@Version"].SourceVersion =
                DataRowVersion.Original;

            // Create table B and fill it with the schema.
            dtB = new DataTable("TableB");
            daB = new SqlDataAdapter(selectText, sqlConnectString);
            daB.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);
            daB.FillSchema(dtB, SchemaType.Source);
            dtB.Columns["Version"].ReadOnly = false;
            daB.Fill(dtB);

            // Create the update command and define the parameters.
            daB.UpdateCommand = new SqlCommand(updateText,
                daB.SelectCommand.Connection);
            daB.UpdateCommand.CommandType = CommandType.Text;
            daB.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
            daB.UpdateCommand.Parameters["@Id"].SourceVersion = 
                DataRowVersion.Original;
            daB.UpdateCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50,
                "Field1");
            daB.UpdateCommand.Parameters["@Field1"].SourceVersion =
                DataRowVersion.Current;
            daB.UpdateCommand.Parameters.Add("@Version", SqlDbType.Timestamp, 0,
                "Version");
            daB.UpdateCommand.Parameters["@Version"].SourceVersion =
                DataRowVersion.Original;

            // Output the tables
            Console.WriteLine("---INITIAL---");
            OutputTable(dtA);
            OutputTable(dtB);   

            // Update table A
            dtA.Rows.Find(2)["Field1"] += " (new.A)";
            UpdateTable(daA, dtA);

            // Output the tables
            Console.WriteLine("\n---AFTER TABLE A UPDATE---");
            OutputTable(dtA);
            OutputTable(dtB);

            // Update table B
            dtB.Rows.Find(2)["Field1"] += " (new.B)";
            UpdateTable(daB, dtB);

            // Output the tables
            Console.WriteLine("\n---AFTER TABLE B UPDATE---");
            OutputTable(dtA);
            OutputTable(dtB);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }

        static void OutputTable(DataTable dt)
        {
            Console.WriteLine("\n---{0}---", dt.TableName);
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("{0}\t{1}\t{2}",
                    row["Id"], row["Field1"],
                    Convert.ToBase64String(row.Field<byte[]>("Version")));
            }
        }

        static void UpdateTable(SqlDataAdapter da, DataTable dt)
        {
            Console.WriteLine("\n=> Update({0})", dt.TableName);
            try 
            {
                da.Update(dt);
                Console.WriteLine("=> Update succeeded.");
            }
            catch (DBConcurrencyException ex)
            {
                // Error if timestamp does not match
                Console.WriteLine("=> EXCEPTION: {0}", ex.Message);
                dt.RejectChanges(  );
            }
        }

        static void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            Console.WriteLine("=> DataAdapter.RowUpdated event. Status = {0}.",
                e.Status);

            // Check if an insert or update operation is being performed.
            if (e.Status == UpdateStatus.Continue &
                (e.StatementType == StatementType.Insert ||
                e.StatementType == StatementType.Update))
            {
                // Build a command object to retrieve the updated timestamp.
                String sqlGetRowVersion = "SELECT Version FROM " +
                    "ConcurrencyViolation WHERE Id = " + e.Row["Id"];
                SqlConnection conn = new SqlConnection(sqlConnectString);
                SqlCommand cmd = new SqlCommand(sqlGetRowVersion, conn);

                // Set the timestamp to the new value in the data source and
                // call accept changes.
                conn.Open(  );
                e.Row["Version"] = (Byte[])cmd.ExecuteScalar(  );
                conn.Close(  );
                e.Row.AcceptChanges(  );
            }
        }
    }
}

The output is shown in Figure 7.9, "Output for CheckConcurrencyViolation solution".

Discussion

The timestamp data type automatically generates an eight-byte binary value guaranteed to be unique within the database. It tracks row versions within a database and has no relation to clock time. To record the time of record inserts and updates, define a datetime column in the table and create update and insert triggers to set its value.

Figure 7.9. Output for CheckConcurrencyViolation solution

Output for CheckConcurrencyViolation solution

The T-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The timestamp data type defined by the SQL-92 standard is equivalent to the T-SQL datetime data type.

A table can have only one timestamp column. Its value is updated with the current database row version each time a row with a timestamp column is inserted or updated.

The rowversion data type is a synonym for the timestamp data type.

Problem

You need to effectively resolve data conflicts and prevent overwriting of existing data when attempting to update changes in a DataSet to a database where the underlying data has changed.

Solution

Handle the DBConcurrencyException within the RowUpdated event of the DataAdapter.

The solution uses a table named ResolveDataConflict in the database AdoDotNet35Cookbook. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE ResolveDataConflict(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The schema of table ResolveDataConflict is shown in Table 7.6, "ResolveDataConflict table schema".

Table 7.6. ResolveDataConflict table schema

Column name

Data type

Length

Length

Id

int

4

No

Field1

nvarchar

50

Yes

Field2

nvarchar

50

Yes


Execute the following T-SQL statement to create some sample data for the solution:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO ResolveDataConflict VALUES (1, 'Field1.1', 'Field2.1');
	INSERT INTO ResolveDataConflict VALUES (2, 'Field1.2', 'Field2.2');
	INSERT INTO ResolveDataConflict VALUES (3, 'Field1.3', 'Field2.3');

The solution creates a DataTable named User 1 and fills it with the schema and data in the table ResolveDataConflict in the AdoDotNet35Cookbook database. The ContinueUpdateOnError property of the DataAdapter is set to true. A handler named daUser1_RowUpdated is assigned to the RowUpdated event of the DataAdapter. A CommandBuilder object is created to generate the updating logic for the DataAdapter.

The daUser1_RowUpdated() event handler checks to see if a concurrency error occurred when updating the row in the DataTable named User 1 to table ResolveDataConflict in the database. If an error occurred during the deletion of a row, the RejectChanges() method is used to cancel the delete. For all rows with a concurrency error, the Id for the row is retrieved from the row and used with the DataAdapter for the conflict table to try to get the original data for the row from the table ResolveDataConflict. An error is set on the row in the conflict table indicating whether it was changed (the row in error was retrieved from the database) or deleted (a row in error could not be retrieved from the database).

A DataTable named User 1 Conflict is created to store the original row data for a row when a concurrency error is encountered while updating a row from the DataTable named User 1 back to the ResolveDataConflict table in the database. A DataAdapter is created that uses a parameterized SQL SELECT statement to retrieve the original row. The schema for the conflict table is loaded from the ResolveDataConflict table using the DataAdapter.

A DataTable named User 2 and a DataTable named User 2 Conflict and their associated DataAdapter objects are created in the same way as for the DataTable named User 1 and the DataTable named User 1 Conflict and described in the preceding three paragraphs.

The DataTable objects User 1 and User 2 are filled using their DataAdapter objects and their contents output to the console.

In the DataTable named User 1, the row with Id=2 is modified and the row with Id=3 is deleted. The UpdateTable() method is called, which clears the DataTable named User 1 Conflict, updates the changes in the DataTable named User 1 back to the ResolveDataConflict table in the database using the Update() method of the DataAdapter, and outputs the number of rows affected by the update and whether the update had errors. In this case, both rows successfully update back to the database and no rows have errors updating. The contents of the DataTable objects User 1 and User 1 Conflict are output to the console.

In the DataTable named User 2, the row with Id=1 is modified, the row with Id=2 is modified, and the row with Id=3 is modified. The UpdateTable() method is called, which clears the DataTable named User 2 Conflict, updates the changes in the DataTable named User 2 back to the ResolveDataConflict table in the database using the Update() method of the DataAdapter, and outputs the number of rows affected by the update and whether the update had errors. In this case, one row is successful updated. The row with Id=1 is updated successfully because it had not been changed previously. The attempted update of the row with Id = 2 raises a concurrency violation because it has been modified by the earlier update of the User 1 DataTable. The attempted update of the row with Id=3 raises a concurrency violation because it has been deleted by the earlier update of the User1DataTable. The contents of the DataTable objects User1 and User 1 Conflict are output to the console. The User 1 conflict table shows additional details about the rows that could not be updated back to the database.

The C# code in Program.cs in the project ResolveDataConflict is shown in Example 7.27, "File: Program.cs for ResolveDataConflict solution".

Example 7.27. File: Program.cs for ResolveDataConflict solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace ResolveDataConflict
{
    class Program
    {
        private static SqlDataAdapter daUser1Conflict;
        private static SqlDataAdapter daUser2Conflict;
        private static DataTable dtUser1;
        private static DataTable dtUser2;
        private static DataTable dtUser1Conflict;
        private static DataTable dtUser2Conflict;

        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlText = "SELECT * FROM ResolveDataConflict";

            // Create the DataAdapter for user 1 table.
            SqlDataAdapter daUser1 = new SqlDataAdapter(sqlText, sqlConnectString);
            daUser1.ContinueUpdateOnError = true;
            // Handle the RowUpdated event.
            daUser1.RowUpdated += new SqlRowUpdatedEventHandler(daUser1_RowUpdated);
            // Get the schema and data for user 1 table.
            dtUser1 = new DataTable("User 1");
            daUser1.FillSchema(dtUser1, SchemaType.Source);
            daUser1.Fill(dtUser1);
            // Create the command builder.
            SqlCommandBuilder cbUser1 = new SqlCommandBuilder(daUser1);

            // Create a DataAdapter to retrieve original rows
            // for conflicts when updating user 1 table.
            daUser1Conflict =
                new SqlDataAdapter(sqlText + " WHERE Id = @Id", sqlConnectString);
            daUser1Conflict.SelectCommand.Parameters.Add("@Id", SqlDbType.Int);
            // Create a DataSet with the conflict table schema.
            dtUser1Conflict = new DataTable("User 1 Conflicts");
            daUser1.FillSchema(dtUser1Conflict, SchemaType.Source);

            // Create the DataAdapter for user 2 table.
            SqlDataAdapter daUser2 = new SqlDataAdapter(sqlText, sqlConnectString);
            daUser2.ContinueUpdateOnError = true;
            // Handle the RowUpdated event.
            daUser2.RowUpdated += new SqlRowUpdatedEventHandler(daUser2_RowUpdated);
            // Get the schema and data for user 2 table.
            dtUser2 = new DataTable("User 2"); 
            daUser2.FillSchema(dtUser2, SchemaType.Source);
            daUser2.Fill(dtUser2);
            // Create the command builder.
            SqlCommandBuilder cbB = new SqlCommandBuilder(daUser2);

            // Create a DataAdapter to retrieve original rows
            // for conflicts when updating user 2 table.
            daUser2Conflict =
                new SqlDataAdapter(sqlText + " WHERE Id = @Id", sqlConnectString);
            daUser2Conflict.SelectCommand.Parameters.Add("@Id", SqlDbType.Int);
            // Create a DataSet with the conflict table schema.
            dtUser2Conflict = new DataTable("User 2 Conflicts");
            daUser2.FillSchema(dtUser2Conflict, SchemaType.Source);

            // Fill and output tables for both user 1 and user 2
            Console.WriteLine("---INITIAL---");
            daUser1.Fill(dtUser1);
            OutputTable(dtUser1);
            daUser2.Fill(dtUser2);
            OutputTable(dtUser2);

            // Modify row with Id = 2 and delete row with Id = 3
            // in User 1 table, and update
            dtUser1.Rows.Find(2)["Field2"] += " (new1)";
            dtUser1.Rows.Find(3).Delete(  );

            Console.WriteLine("\n---AFTER USER 1 TABLE UPDATES---");
            UpdateTable(daUser1, dtUser1, dtUser1Conflict);
            OutputTable(dtUser1);
            OutputTable(dtUser1Conflict);

            // Update rows 1, 2, and 3 in User 2 table, and update
            dtUser2.Rows.Find(1)["Field1"] += " (new2)";
            dtUser2.Rows.Find(2)["Field1"] += " (new2)";
            dtUser2.Rows.Find(3)["Field2"] += " (new2)";
 
           Console.WriteLine("\n---AFTER USER 2 TABLE UPDATES---");
            UpdateTable(daUser2, dtUser2, dtUser2Conflict);
            OutputTable(dtUser2);
            OutputTable(dtUser2Conflict);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }

        static void OutputTable(DataTable dt)
        {
            Console.WriteLine("\nTABLE: {0}", dt.TableName); 
            Console.WriteLine("ID\tField1\t\tField2\t\tRowError");
            Console.WriteLine("--\t------\t\t------\t\t--------");
            if (dt.Rows.Count == 0)
                Console.WriteLine("[EMPTY]");
            else
                foreach (DataRow row in dt.Rows)
                {
                    if (row.RowState != DataRowState.Deleted)
                    {
                        Console.WriteLine("{0}\t{1}\t{2}\tRowError = {3}",
                            row["Id"], row["Field1"], row["Field2"],
                            row.RowError);
                    }
                }
        }

        static void UpdateTable(SqlDataAdapter da, DataTable dt, DataTable dtConflict)
        {
            // clear the conflict table
            dtConflict.Clear(  );

            // update the table
            int rows = da.Update(dt);

            // output update stats
            Console.WriteLine(
                "\n=> Table '{0}' udpated. RowsAffected = {1}, HasErrors = {2}",
                dt.TableName, rows, dt.HasErrors);
        }

        static void daUser1_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            // Check if a concurrency exception occurred.
            if (e.Status == UpdateStatus.ErrorsOccurred &
                e.Errors.GetType(  ) == typeof(DBConcurrencyException))
            {
                // If the row was deleted, reject the delete.
                if (e.Row.RowState == DataRowState.Deleted)
                    e.Row.RejectChanges(  );

                // Get the row ID.
                int id = e.Row.Field<int>("Id");
                daUser1Conflict.SelectCommand.Parameters["@Id"].Value = id;
                // Get the row from the data source for the conflicts table.
                if (daUser1Conflict.Fill(dtUser1Conflict) == 1)
                    dtUser1Conflict.Rows.Find(e.Row["Id"]).RowError =
                        "Row changed in database.";
                else
                {
                    // Add a row for the deleted row 
                    dtUser1Conflict.ImportRow(e.Row);
                    dtUser1Conflict.Rows.Find(id).RowError =
                        "Row deleted from database.";
                }
            }
        }

        static void daUser2_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            // Check if a concurrency exception occurred.
            if (e.Status == UpdateStatus.ErrorsOccurred &
                e.Errors.GetType(  ) == typeof(DBConcurrencyException))
            {
                // If the row was deleted, reject the delete.
                if (e.Row.RowState == DataRowState.Deleted)
                    e.Row.RejectChanges(  );

                // Get the row ID.
                int id = e.Row.Field<int>("Id");
                daUser2Conflict.SelectCommand.Parameters["@Id"].Value = id;
                // Get the row from the data source for the conflicts table.
                if (daUser2Conflict.Fill(dtUser2Conflict) == 1)
                    dtUser2Conflict.Rows.Find(e.Row["Id"]).RowError =
                        "Row changed in database.";
                else
                {
                    // Add a row for the deleted row
                    dtUser2Conflict.ImportRow(e.Row);
                    dtUser2Conflict.Rows.Find(id).RowError =
                        "Row deleted from database.";
                }
            }
        }
    }
}

The output is shown in Figure 7.10, "Output for ResolveDataConflict solution".

Discussion

The RowUpdated event of the DataAdapter occurs during the Update() method after the command to update a row is executed against the data source. The event fires with each row update attempt.

The RowUpdated event handler receives an argument of type RowUpdatedEventArgs that provides information specifically related to the event as described in Table 7.7, "RowUpdatedEventArgs properties".

Figure 7.10. Output for ResolveDataConflict solution

Output for ResolveDataConflict solution

Table 7.7. RowUpdatedEventArgs properties

Property

Description

Command

Gets or sets the Command executed when the Update() method is called.

Errors

Gets errors generated by the .NET data provider when the Command was executed.

RecordsAffected

Gets the number of rows changed, inserted, or deleted by the execution of the Command.

Row

Gets the DataRow sent through the Update() method.

StatementType

Gets the type of SQL statement executed. This is one of the following values from the StatementType enumeration: Select, Insert, Update, or Delete.

Status

Gets or sets the action to take with the current and remaining rows during the Update() method. This is a value from the UpdateStatus enumeration described in Table 7.5, "UpdateStatus enumeration".

TableMapping

Gets the DataTableMapping sent through the Update() method.


Table 7.8, "UpdateStatus enumeration" describes the values in the UpdateStatus enumeration used by the Status property of the RowUpdatedEventArgs object.

Table 7.8. UpdateStatus enumeration

Value

Description

Continue

Continue processing the rows. This is the default value.

ErrorsOccurred

The event handler reports that the update should be treated as an error.

SkipAllRemainingRows

Do not update the current row and skip updating the remaining rows.

SkipCurrentRow

Do not update the current row and continue updating with the subsequent row.


The Update() method of the DataAdapter raises two events for every row in the data source that is updated. The order of the events is:

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

  2. The OnRowUpdating event is raised.

  3. The update command executes against the row in the data source.

  4. If the UpdatedRowSource property of the Command is set to FirstReturnedRecord or Both, the first returned result is placed in the DataRow.

  5. If the UpdateRowSource property of the Command is set to OutputParameters or Both, the output parameters are placed in the DataRow.

  6. The OnDataRowUpdated event is raised.

  7. AcceptChanges() is called.

The DBConcurrencyException is raised during the update operation on a row if zero rows are affected. This usually indicates a concurrency violation.

Problem

You want to effectively use transaction isolation levels to ensure data consistency for a range of data rows.

Solution

Use the Begin() method of the Transaction object to control isolation level.

The solution uses a table named TransactionIsolationLevel in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook 
	GO
	CREATE TABLE Transaction IsolationLevel(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Create the test data the solution needs by executing the following T-SQL batch:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO TransactionIsolationLevel VALUES (1, 'Field1.1', 'Field2.1');
	INSERT INTO TransactionIsolationLevel VALUES (2, 'Field1.2', 'Field2.2');
	INSERT INTO TransactionIsolationLevel VALUES (3, 'Field1.3', 'Field2.3');

The solution uses snapshot isolation. Enable snapshot isolation in the AdoDotNet35Cookbook database by executing the following T-SQL statement:

	ALTER DATABASE AdoDotNet35Cookbook SET ALLOW_SNAPSHOT_ISOLATION ON

The solution outputs the contents of the TransactionIsolationLevel table to the console. Next, a Connection is opened and a transaction started with the isolation level SnapShot. An attempt is made to insert two records into the TransactionIsolationLevel table within the transaction. The TransactionIsolationLevel table is read into a DataTable and output to the console between the two insert commands showing the data as it was before the first insert. The transaction is committed after the second insert and the contents of TransactionIsolationLevel is output to the console showing the new records.

The C# code in Program.cs in the project TransactionIsolationLevel is shown in Example 7.28, "File: Program.cs for TransactionIsolationLevel solution".

Example 7.28. File: Program.cs for TransactionIsolationLevel solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace TransactionIsolationLevel
{
    class Program
    {
        private static string sqlConnectString = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        static void Main(string[] args)
        {
            string sqlInsert1 = "INSERT INTO TransactionIsolationLevel " +
                "VALUES (4, 'Field1.4', 'Field2.4')";
            string sqlInsert2 = "INSERT INTO TransactionIsolationLevel " +
                "VALUES (5, 'Field1.5', 'Field2.5')";

            OutputData("Initial");

            // Open a connection. 
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                connection.Open(  );

                // Start a transaction.
                SqlTransaction tran =
                    connection.BeginTransaction(IsolationLevel.Snapshot);
                Console.WriteLine("\nTransaction started: IsolationLevel = {0}",
                    tran.IsolationLevel);

                try
                {
                    // Insert two records by executing sqlInsert1 and sqlInsert2
                    Console.WriteLine("\n=> Executing SQL:\n   {0}", sqlInsert1);
                    SqlCommand command1 = new SqlCommand(sqlInsert1, connection, tran);
                    command1.ExecuteNonQuery(  );

                    // Output the data from the database
                    OutputData("During transaction");

                    Console.WriteLine("\n=> Executing SQL:\n   {0}", sqlInsert2);
                    SqlCommand command2 = new SqlCommand(sqlInsert2, connection, tran);
                    command2.ExecuteNonQuery(  );

                    Console.WriteLine("\nTransaction committed");
                    tran.Commit(  );
                }
                catch(Exception ex)
                {
                    Console.WriteLine("\nException: {0}", ex.Message);

                    tran.Rollback(  );
                    Console.WriteLine("\nTransaction rolled back.");
                }
            }

            // Output the data from the database
            OutputData("After transaction");

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }

        static void OutputData(string s)
        {
            string sqlSelect = "SELECT * FROM TransactionIsolationLevel";

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable(  );
            da.Fill(dt);

            Console.WriteLine("\n---TransactionIsolationLevel table: {0}", s); 
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);
            }
        }
    }
}

The output is shown in Figure 7.11, "Output for TransactionIsolationLevel solution".

Figure 7.11. Output for TransactionIsolationLevel solution

Output for TransactionIsolationLevel solution

Discussion

The isolation level specifies the transaction locking behavior for a connection. It determines what changes made to data within a transaction are visible outside of the transaction while the transaction is uncommitted.

Concurrency violations occur when multiple users or processes attempt to modify the same data in a database at the same time without locking. Table 7.9, "Concurrency problems" describes concurrency problems.

Table 7.9. Concurrency problems

Condition

Description

Lost update

Two or more transactions select the same row and subsequently update that row. Data is lost because the transactions are unaware of each other and overwrite each other's updates.

Uncommitted dependency (dirty read)

A second transaction selects a row that has been updated, but not committed, by another transaction. The first transaction makes more changes to the data or rolls back the changes already made resulting in the second transaction having invalid data.

Inconsistent analysis (nonrepeatable read)

A second transaction reads different data each time that the same row is read. Another transaction has changed and committed the data between the reads.

Phantom read

An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected by the transaction are missing the inserted rows and still contain the deleted rows that no longer exist.


Isolation level defines the degree to which one transaction must be isolated from other transactions. A higher isolation level increases data correctness but decreases concurrent access to data. Table 7.10, "IsolationLevel enumeration" describes the different isolations levels supported by ADO.NET. The first four levels are listed in order of increasing isolation.

Table 7.10. IsolationLevel enumeration

Name

Description

Unspecified

A different isolation level than the one specified is being used, but that level cannot be determined.

Chaos

Pending changes from more highly isolated transactions cannot be overwritten. This isolation level is not supported by SQL Server.

ReadUncommitted

No shared locks are issued. Exclusive locks are not honored. A dirty read is possible.

ReadCommitted

Shared locks are held while data is being read by the transaction. Dirty reads are not possible. Nonrepeatable reads or phantom rows can still occur because data can be changed prior to being committed.

RepeatableRead

Shared locks are placed on all data used by the query. Other users are prevented from updating the data. Nonrepeatable reads are prevented, but phantom reads are still possible.

Serializable

A range lock-covering individual records and the ranges between them-is placed on the data preventing other users from updating or inserting rows until the transaction is complete. Phantom reads are prevented.

SnapShot

Stores a version of the data that can be read by other applications while an application is modifying the same data-this reduces blocking. The changes made during the transaction cannot be seen by other applications until committed.


In ADO.NET, the isolation level can be set by creating the transaction using an overload of the BeginTransaction() method of the Command or by setting the IsolationLevel property of an existing Transaction object. The default isolation level is ReadCommitted.

Parallel transactions are not supported, so the isolation level applies to the entire transaction. It can be changed programmatically at any time. If the isolation level is changed within a transaction, the new level applies to all statements remaining in the transaction.

Problem

You need to explicitly control locking behavior a T-SQL transaction.

Solution

Use SQL Server locking hints from ADO.NET.

The solution uses a table named LockingHint in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE LockingHint(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Execute the following T-SQL batch to create the sample data this solution uses:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO LockingHint VALUES (1, 'Field1.1', 'Field2.1');
	INSERT INTO LockingHint VALUES (2, 'Field1.2', 'Field2.2');
	INSERT INTO LockingHint VALUES (3, 'Field1.3', 'Field2.3');
	INSERT INTO LockingHint VALUES (4, 'Field1.4', 'Field2.4');

The solution creates a DataTable and fills it with records with Id=2 and Id = 3 from the table LockingHint in the AdoDotNet35Cookbook database. The contents of the DataTable are output to the console. The select statement that retrieves the records locks uses an update lock (UPDLOCK). The UpdateRow() method is called twice to modify and update rows with Id=1 and Id=3. The row with Id=1 updates successfully, but the row with Id=3 times out because of the update lock on rows with Id= 2 and Id=3. The contents of the table LockingHint are output to the console at the end of the solution to confirm the results of the update on the database.

The C# code in Program.cs in the project SpecifyLockingHints is shown in Example 7.29, "File: Program.cs for SpecifyLockingHints solution".

Example 7.29. File: Program.cs for SpecifyLockingHints solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace SpecifyLockingHints
{
    class Program
    {
        private static string sqlConnectString = "Data Source=(local);" +
            "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

        static void Main(string[] args)
        {
            // SQL query with pessimistic locking.
            string sqlSelect = "SELECT * FROM LockingHint " +
                "WITH (UPDLOCK) WHERE Id BETWEEN 2 AND 3";
            
            // Start the transaction and create the command.
            SqlConnection connection = new SqlConnection(sqlConnectString);
            connection.Open(  );
            SqlTransaction tran = connection.BeginTransaction(  );
            SqlCommand command = new SqlCommand(sqlSelect, connection, tran);
            
            // Create the DataAdapter and CommandBuilder.
            SqlDataAdapter da = new SqlDataAdapter(command);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            
            // Fill table using the DataAdapter.
            DataTable dt = new DataTable(  );
            da.Fill(dt);

            // Output the result set
            Console.WriteLine("---RESULT SET FROM LOCKINGHINT TABLE (UPDLOCK)---");
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);
            }

            // Update 2 rows
            UpdateRow(1);
            UpdateRow(3);

            // Commit the transaction and close the connection.
            tran.Commit(  );
            connection.Close(  );

            // Output the table after the updates
            dt.Clear(  );
            da = new SqlDataAdapter("SELECT * FROM LockingHint", sqlConnectString);
            da.Fill(dt);

            Console.WriteLine("\n---LOCKINGHINT TABLE (AFTER UPDATE)---"); 
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("Id = {0}\tField1 = {1}\tField2 = {2}",
                    row["Id"], row["Field1"], row["Field2"]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }

        static void UpdateRow(int id)
        {
            Console.WriteLine("\n=> Updating row with Id = {0}.", id);

            string sqlUpdate = "UPDATE LockingHint SET " +
                "Field1 = 'Field1." + id + " (new)' WHERE Id = " + id;

            SqlConnection connection = new SqlConnection(sqlConnectString);
            connection.Open(  );
            SqlCommand command2 = new SqlCommand(sqlUpdate, connection);

            // try the update
            try
            {
                command2.ExecuteNonQuery(  );
                Console.WriteLine("=> Update successful.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("=> Update failed.");
                Console.WriteLine("=> EXCEPTION: {0}", ex.Message);
            }
            finally
            {
                connection.Close(  );
            }
        }
    }
}

The output is shown in Figure 7.12, "Output for SpecifyLockingHints solution".

Discussion

A lock is an object indicating that a user has a dependency on a resource. Locks ensure transactional integrity and database consistency by preventing other users from changing data being read by a user and preventing users from reading data being changed by another user. A lock indicates that a user has a dependency on a resource.

Figure 7.12. Output for SpecifyLockingHints solution

Output for SpecifyLockingHints solution

It prevents other users from performing operations that would adversely affect the locked resources. Locks are acquired and released by user actions; they are managed internally by database software.

A locking hint can be specified with SELECT, INSERT, DELETE, and UPDATE statements to instruct SQL Server as to the type of lock to use. You can use locking hints when you need control over locks acquired on objects. The SQL Server Optimizer automatically determines correct locking; hints should be used only when necessary. Locking hints override the current transaction isolation level for the session.

A locking hint is specified following the FROM clause using a WITH clause. The hint is specified within parentheses and multiple hints are separated by commas. For example, the query in the solution specifies update locks with the clause WITH (UPDLOCK).

Table 7.11, "SQL Server locking hints for isolation level" describes the different locking hints that you can use.

Table 7.11. SQL Server locking hints for isolation level

Locking hint

Description

HOLDLOCK

Hold a shared lock until a transaction is completed instead of releasing it as soon as the required table or data is no longer needed. HOLDLOCK is equivalent to SERIALIZABLE.

NOLOCK

Allow dirty reads-do not issue shared locks and do not recognize exclusive locks. Applies only to the SELECT statement. Equivalent to READUNCOMMITTED.

PAGLOCK

Use page locks where an individual lock on a row or key or a single table lock would normally be taken.

READCOMMITTED

Read operations comply with rules for a transaction with an isolation level of READ COMMITTED by using either locking or row versioning.

READCOMMITTEDLOCK

Read operations comply with rules for a transaction with an isolation level of READ COMMITTED by using locking.

READPAST

Skip locked rows that would ordinarily appear in the result set rather than blocking the transaction by waiting for other transactions to release locks on those rows. Applies only to transactions with an isolation level of READ COMMITTED or REPEATABLE READ. Applies only to the SELECT statement.

READUNCOMMITTED

Allow dirty reads-do not issue shared locks and do not recognize exclusive locks. Applies only to the SELECT statement. Equivalent to NOLOCK.

REPEATABLEREAD

Use the same locking semantics as a transaction with an isolation level of REPEATABLEREAD.

ROWLOCK

Use row-level locks instead of page-level and table-level locks.

SERIALIZABLE

Hold a shared lock until a transaction is completed instead of releasing it as soon as the required table or data is no longer needed. SERIALIZABLE is equivalent to HOLDLOCK.

TABLOCK

Use a shared lock on the table instead of using row-level and page-level locking. By default, the lock is held until the end of the statement.

TABLOCKX

Use an exclusive lock on the table preventing other users from reading or updating the table. By default, the lock is held until the end of the statement.

UPDLOCK

Use update locks instead of shared locks when reading a table. This allows you to read data and later update it with a guarantee that it has not changed since you last read it while other users are not blocked from reading the data.

XLOCK

Use an exclusive lock that is held until the end of the transaction on all data processed by the statement. Can be specified with ROWLOCK, PAGLOCK, or TABLOCK granularity. Cannot be used with either NOLOCK or UPDLOCK.


There are a number ways to get information about database locks:

  • The dynamic management view sys.dm_tran_locks returns a result set containing currently active lock manager resources.

  • The system stored procedure sp_lock returns a result set containing all active locks in the sessions currently active in the database engine instance. Two stored procedure arguments let you narrow the scope of the result set. This feature is provided for backward compatibility-Microsoft recommends you use the sys.dm_tran_locks dynamic management view.

  • The syslockinfo table in the master database contains information about all granted, converting, and waiting lock requests. It is a denormalized view of the data structures used internally by the lock manager. This table is included for backward compatibility as of SQL Server 2000.

  • The SQL Server Profiler can be used to monitor and record locking information.

  • The Windows Performance Monitor has a SQL Server Locks Object counter that can be used to monitor lock activity.

For more information about database locks, using locking hints, or monitoring database locks, see Microsoft SQL Server Books Online.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.