Database Concurrency Patterns - SIP and SUP

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace DatabaseConcurrency
{
    public static class Dal
    {
        private static string dbconnectstring = ConfigurationManager.ConnectionStrings["DbConnectString"].ConnectionString;
        /// <summary>
        /// Make a call to get the latest unique identifier
        /// </summary>
        /// <param name="NumberClaimed"></param>
        /// <param name="ThreadId"></param>
        /// <returns></returns>
        public static DataTable ThreadCall(int NumberClaimed, int ThreadId)
        {
            OleDbConnection sc = null;
            OleDbDataAdapter sda = null;
            try
            {
                sc = new OleDbConnection(dbconnectstring);
                sc.Open();
                sda = new OleDbDataAdapter("SingleUpdateTableCall", sc);
                sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                OleDbCommandBuilder.DeriveParameters(sda.SelectCommand);
                sda.SelectCommand.Parameters["ThreadId"].Value = ThreadId;
                sda.SelectCommand.Parameters["NumberClaimed"].Value = NumberClaimed;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
            finally
            {
                if (sda != null) sda.Dispose();
                sda = null;
                if (sc != null)
                {
                    if (sc.State == ConnectionState.Open)
                        sc.Close();
                    sc.Dispose();
                }
                sc = null;
            }
        }
        /// <summary>
        /// Get a history of the Single Update Table so we can see what activity
        /// is going on when generating unique identifiers
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetDbResults()
        {
            OleDbConnection sc = null;
            OleDbDataAdapter sda = null;
            try
            {
                sc = new OleDbConnection(dbconnectstring);
                sc.Open();
                sda = new OleDbDataAdapter("SingleUpdateTableHistory", sc);
                sda.SelectCommand.CommandType = CommandType.TableDirect; ;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
            finally
            {
                if (sda != null) sda.Dispose();
                sda = null;
                if (sc != null)
                {
                    if (sc.State == ConnectionState.Open)
                        sc.Close();
                    sc.Dispose();
                }
                sc = null;
            }
        }
        /// <summary>
        /// Get a history of all the identifiers we have generated
        /// </summary>
        /// <returns></returns>
        public static DataTable GetActionResults()
        {
            OleDbConnection sc = null;
            OleDbDataAdapter sda = null;
            try
            {
                sc = new OleDbConnection(dbconnectstring);
                sc.Open();
                sda = new OleDbDataAdapter("SingleUpdateAction", sc);
                sda.SelectCommand.CommandType = CommandType.TableDirect; ;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
            finally
            {
                if (sda != null) sda.Dispose();
                sda = null;
                if (sc != null)
                {
                    if (sc.State == ConnectionState.Open)
                        sc.Close();
                    sc.Dispose();
                }
                sc = null;
            }
        }
        /// <summary>
        /// Clean up the tables and create an initial value
        /// </summary>
        public static void Setup()
        {
            OleDbConnection sc = null;
            OleDbCommand sda = null;
            try
            {
                sc = new OleDbConnection(dbconnectstring);
                sc.Open();
                sda = new OleDbCommand("SingleUpdateTableSetup", sc);
                sda.CommandType = CommandType.StoredProcedure;
                sda.ExecuteNonQuery();
            }
            finally
            {
                if (sda != null) sda.Dispose();
                sda = null;
                if (sc != null)
                {
                    if (sc.State == ConnectionState.Open)
                        sc.Close();
                    sc.Dispose();
                }
                sc = null;
            }
        }

    }
}