Asynchronous SQL Database Wrapper

• Download source - 23.8 KB

Introduction

I have always been searching to find a simple yet useful library to simplify database related programming while it provides asynchronous methods to prevent any deadlocks.

Most of the libraries I found were too complicated or were not flexible enough so I started to develop my own.

Using this library, you will be able to easily connect to any SQL-Server database, execute any stored procedure or T-SQL query and receive the results asynchronously. The library is written in C# and uses no external dependency.

Background

You may need some background knowledge about event driven programming although it is not required at all and you can use the code easily.

Using the Code

The library consists of two classes:

• BLL (Business Logic Layer) which provides methods and properties to access MS-SQL database to execute commands and queries and finally returns the result to the caller. You cannot drive an object from this class directly and it must be inherited by another class.
• DAL (Data Access Layer) which you will write down your own functions to execute SQL store procedure and queries and you may have different DAL classes for different tables in your database.

First of all, you need to create your own DAL class like this:

namespace SQLWrapper
{
  public class DAL : BLL
  {
    public DAL(string server, string db, string user, string pass)
    {
      base.Start(server, db, user, pass);
    }

    ~DAL()
    {
      base.Stop(eStopType.ForceStopAll);
    }

    ///////////////////////////////////////////////////////////
    // TODO: Here you can add your code here...
  }
}

Because BLL class maintains threads to process requests async, you need to start it and provide required data to make connection string. Please do not forget to call Stop function otherwise, destructor will do it forcefully.

NOTE: If you need to be able to connect to a database rather than MS-SQL, you can modify CreateConnectionString function in BLL class to generate a proper connection string.

In order to call a stored procedure, your function in DAL is like this:

public int MyStoreProcedure(int param1, string param2)
{
    // Create user data according to return type of store procedure in SQL
    StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult(eRequestType.Scalar);
    
    // If your store procedure accepts some parameters, define them here, 
    // or you can omit it incase there is no parameter definition
    userData.Parameters = new System.Data.SqlClient.SqlParameter[] { 
        new System.Data.SqlClient.SqlParameter("@param1", param1),
        new System.Data.SqlClient.SqlParameter("@param2", param2),
    };
    
    // Execute procedure...
    if (!ExecuteStoredProcedure("usp_MyStoreProcedure", userData))
        throw new Exception("Execution failed");
        
    // Wait until it finishes...
    // Note, it will wait <userdata.tswaitforresult> 
    // for the command to be completed otherwise returns <timeout>
    if (WaitSqlCompletes(userData) != eWaitForSQLResult.Success)
        throw new Exception("Execution failed");
        
    // Get the result...
    return userData.ScalarValue;
}

As you can see, the stored procedure return value can be Scalar, Reader and NonQuery. For the scalar, the ScalarValue parameter of userData has meaning, for the nonQuery the AffectedRows parameter of userData means number of affected rows and for the Reader type, the ReturnValue means exactly your function's return value and in addition, you can access to the recordset by resultDataReader parameter of userData.

See the sample below:

public bool MySQLQuery(int param1, string param2)
{
    // Create user data according to return type of store procedure in SQL
    ReaderQueryCallbackResult userData = new ReaderQueryCallbackResult();
    
    string sqlCommand = string.Format("SELECT TOP(1) * FROM tbl1 
    	WHERE code = {0} AND name LIKE '%{1}%'", param1, param2);
    
    // Execute procedure...
    if (!ExecuteSQLStatement(sqlCommand, userData))
        return false;
        
    // Wait until it finishes...
    // Note, it will wait (userData.tsWaitForResult) 
    // for the command to be completed otherwise returns <timeout>
    if (WaitSqlCompletes(userData) != eWaitForSQLResult.Success)
        return false;
        
    // Get the result...
    if(userData.resultDataReader.HasRows && userData.resultDataReader.Read())
    {
        // Do whatever you want....
        int field1 = GetIntValueOfDBField(userData.resultDataReader["Field1"], -1);
        string field2 = GetStringValueOfDBField(userData.resultDataReader["Field2"], null);
        Nullable<datetime> field3 = GetDateValueOfDBField(userData.resultDataReader["Field3"], null);
        float field4 = GetFloatValueOfDBField(userData.resultDataReader["Field4"], 0);
        long field5 = GetLongValueOfDBField(userData.resultDataReader["Field5"], -1);
    }
    userData.resultDataReader.Dispose();
    
    return true;
}

In this case, we are trying to execute an SQL command directly into database, as you see, the idea is the same but we are calling ExecuteSQLStatement instead of ExecuteStoredProcedure.

To process returned recordset, we will iterate into resultDataReader using .Read() method of it. In addition, there are some helper methods to avoid any exception while iterating the returned recordset because of the existance of any NULL field, GetIntValueOfDBField and so on...

If you want to execute SQL command rather than stored procedure, there are three types of userData which must be filled and passed to ExecuteSQLStatement and the rest is the same:

• ReaderQueryCallbackResult userData;
  In case your statement returns a recordset, you can use userData.resultDataReader to obtain access to returned recordset.
• NonQueryCallbackResult userData
  In case your statement returns nothing, like UPDATE command, the userData.AffectedRows is accessible to check the execution result.
• ScalarQueryCallbackResult userData
  In case your statement returns only one scalar value like SELECT code FROM tbl WHEN ID=10, the userData.ScalarValue is accessible to get the result.

For the stored procedures, there is only one data type to pass to ExecuteStoredProcedure. You have to specify the type of stored procedure return value when you are declaring a variable by that type:

• StoredProcedureCallbackResult userData(eRequestType)
  The behavior is the same as before, just the declaration is different.

Using the Code Asynchronously

What if you don't want the calling thread to be blocked to finalize the execution? In that case, you need to call WaitSqlCompletes periodically to see when the result is ready or know the execution failed or is still in progress.

/// <summary>
/// You have to call WaitSqlCompletes(userData, 10) 
/// periodically to see whether result is available or not!
/// </summary>
public StoredProcedureCallbackResult MyStoreProcedureASYNC(int param1, string param2)
{
    // Create user data according to return type of store procedure in SQL
    StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult(eRequestType.Reader);
    
    // If your store procedure accepts some parameters, define them here, 
    // or you can omit it incase there is no parameter definition
    userData.Parameters = new System.Data.SqlClient.SqlParameter[] { 
        new System.Data.SqlClient.SqlParameter("@param1", param1),
        new System.Data.SqlClient.SqlParameter("@param2", param2),
    };
    
    // Execute procedure...
    if (!ExecuteStoredProcedure("usp_MyStoreProcedure", userData))
        throw new Exception("Execution failed");
        
    return userData;
}

and in the calling thread, you have to do something like this:

...
DAL.StoredProcedureCallbackResult userData = myDal.MyStoreProcedureASYNC(10,"hello");
...
// each time we wait 10 milliseconds to see the result...
switch(myDal.WaitSqlCompletes(userData, 10))
{
case eWaitForSQLResult.Waiting:
	goto WAIT_MORE;
case eWaitForSQLResult.Success:
	goto GET_THE_RESULT;
default:
	goto EXECUTION_FAILED;
}
...

Database Status

There is only one event in the BLL class library to provide status of the database asynchronously. If the connection to database drops (mostly because of network failures), OnDatabaseStatusChanged event will be raised to show you the reason.

Additionally, if the connection recovers, again this event is risen to inform you about the new database status.

Points of Interest

While I was developing the code, I understood the connection timeout in the connection string is as important as the execution timeout in SQL command object.

At first, you must be aware of your maximum available timeout which is defined in ConnectionString and is impossible to give any execution command more time than connection string's timeout.

Secondly, each command has its own execution timeout which is 30 seconds by default (in this code) and you can easily modify this for all type of commands like:

userData.tsWaitForResult = TimeSpan.FromSeconds(15);

History

• 6^th April, 2015 - First stable release