Optimistic Concurrency with C# using the IOC and DI Design Patterns

• Download source - 290.11 KB

Introduction

My last article showed Optimistic Concurrency using the PostgreSQL database. I wanted to write a C# program with the Design pattern IOC and DI so we can support multiple databases. This version also uses parameterized queries and transactions.

Background

The IOC design pattern written by Shivprasad Koirala can be read in detail at Design Pattern IOC and DI.

Using the Code

Just as described in Shivprasad Koirala's article, I have an abstract class iDatabase that the actual database classes derive from. I have implemented Postgres only, the other could be SQLServer or any other database.

In the abstract class, I have set the methods that need to be implemented in the derived classes. There is some code in this class since it is an abstract class and not an interface.

public abstract class iDatabase
{
    string strdbName;        // the database name
    string strServer;        // the database name
    string struserName;      // the user name
    string strpassword;      // the password
    string strSAName;        // the SA user name
    string strSApassword;    // the SA password

    public iDatabase()
    {
        strdbName = "";             // the database name
        strServer = "";             // the database name
        struserName = "";           // the user name
        strpassword = "";           // the password
        strSAName = "";             // the SA user name
        strSApassword = "";         // the SA password
    }

    public abstract void CheckDBConnectionInfo();

    public abstract string GetUserPassword(string userName);

    public abstract int UpdateUser(UserData ud);

    public abstract int AddUser(string userName, string Password, string ModUser);

    public abstract UserData GetUser(string userName);
}

The update user code in the postgres class is a normal piece of code as you would expect. It is this code that runs and updates the user table if PostreSQL is selected. The main difference is this code does handle Optimistic Concurrency and sends back the entire data class for the database record instead of each data item.

    public override int UpdateUser(UserData ud)
    {
        string sqlstring;
        int iret = -1;
        bool errflg = false;
        Object result;
        //
        // get the connection string
        //
        sqlstring = GetConnectionString();
        NpgsqlConnection conn = new NpgsqlConnection(sqlstring);
        conn.Open();
        // create a transaction object
        NpgsqlTransaction trano = conn.BeginTransaction();
        // set the sql command
        //                          uuid,  int,   str,   str,   str
        sqlstring = "Update_MinUser(:Arg1, :Arg2, :Arg3, :Arg4, :Arg5)";
        NpgsqlCommand command = new NpgsqlCommand(sqlstring, conn, trano);
        command.CommandType = CommandType.StoredProcedure;

        try
        {
        // Now add the parameter to the parameter collection of the command 
        // specifying its type.
        command.Parameters.Add(new NpgsqlParameter("Arg1", DbType.Guid));

        // Now, add a value to it and later execute the command as usual.
        command.Parameters[0].Value = ud.UserId;

        // Now add the parameter to the parameter collection of the command 
        // specifying its type.
        command.Parameters.Add(new NpgsqlParameter("Arg2", DbType.Int16));

        // Now, add a value to it and later execute the command as usual.
        command.Parameters[1].Value = ud.UserConcur;

        // Now add the parameter to the parameter collection of the command 
        // specifying its type.
        command.Parameters.Add(new NpgsqlParameter("Arg3", DbType.String));

        // Now, add a value to it and later execute the command as usual.
        command.Parameters[2].Value = ud.UserName;

        // Now add the parameter to the parameter collection of the command 
        // specifying its type.
        command.Parameters.Add(new NpgsqlParameter("Arg4", DbType.String));

        // Now, add a value to it and later execute the command as usual.
        command.Parameters[3].Value = ud.UserPassword;

        // Now add the parameter to the parameter collection of the command 
        // specifying its type.
        command.Parameters.Add(new NpgsqlParameter("Arg5", DbType.String));

        // Now, add a value to it and later execute the command as usual.
        command.Parameters[4].Value = ud.ModUser;
        }
        catch (NpgsqlException nex)
        {
            trano.Rollback();
            throw new Exception(nex.Message);
        }

        try
        {
            result = command.ExecuteScalar();
        }
        catch (NpgsqlException nex)
        {
            trano.Rollback();
            throw new Exception(nex.Message);
        }
        finally
        {
            if (!errflg)
            {
                trano.Commit();
            }
            conn.Close();
        }
        iret = (int)result;
        return (iret);
    } 

Where I choose the type of database is in the clsUser class, as shown below. If I wanted to change the type of database at runtime I would have checked a setting somewhere and done an if statement to create the required class.

    public ClsUser()
    {
        idb = new Postgres();
        idb.DBName = "CONNC";
        idb.Server = "localhost";
        idb.SAUserName = "postgres";
        idb.SAPassword = "Password1";
    }

Using the code is extremely easy and clean. In this program I added a user in case it is not already added. I then get the user data record. Then I update the password and call the update procedure. Since I still have the original data, I call the update procedure again to see what will happen. As you would expect, I get an error since the record has already been updated.

UserData    ud1;
int ires;

ClsUser ccus;
ccus = new ClsUser();

// do a try catch since we may have run this once before
try
{
    ires = ccus.AddUser("User1", "User1p", "Don");
}
catch (Exception ex)
{
    MessageBox.Show("Error from {0}" + ex.Message, "test");
}

// get the user data
ud1 = ccus.GetUser("User1");
// change the password.
ud1.UserPassword = "NewPassword";
// update the data.
ires = ccus.UpdateUser(ud1);
if (ires != 0)
{
    MessageBox.Show("Error updating the user record.");
}

// now with the original data try and update it again. It should give us an error.
// this will happen since the Optimistic Concurrency has already been updated.

// change the password.
ud1.UserPassword = "NewPassword1";
// update the data.
ires = ccus.UpdateUser(ud1);
if (ires != 0)
{
    MessageBox.Show("Error updating the user record the second time.");
}

Points of Interest

One item to take from this design pattern is it is a lot of work and code to support the possibility of using two or more databases. If you know you are not going to support other databases, there are other patterns that might be better. The file Db1.sql in the zip file is the SQL file necessary to create the tables for this project. As part of responding to user input, I modified the code to support parameterized queries and transactions. In doing this, I found that Npgsql version 1 did not support UUIDs all that well. I had to download the newer version 2.

History

• 9^th February, 2009 - Initial release
• 23^rd February, 2009 - Updated parameterized queries, transactions