The introduction

I’ve been looking for a simple and effective library that provides an asynchronous way to prevent deadlocks while simplifying database-related programming.

Most of the libraries I found were either too cumbersome or inflexible, so I decided to write my own.

Using this library, you can easily connect to any SQL-Server database, execute any stored procedure or T-SQL query, and receive query results asynchronously. The library is developed in C# with no external dependencies.

background

You may need some background in event-driven programming, but it’s not required.

use

The library consists of two classes:

  • The Business Logic Layer (BLL) provides methods and properties to access the MS-SQL database, execute commands and queries, and return the results to the caller. You cannot call the object of this class directly, it is only inherited by other classes.
  • DAL (Data Access Layer) You will need to write your own functions that execute SQL stored procedures and queries, and you may need different DAL classes for different tables.

First, you need to create the 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... }}Copy the code

Since the BLL class maintains the thread that handles the asynchronous query, you need to provide the necessary data to concatenate the join string. Don’t forget to call ‘Stop’, otherwise the destructor will force it to be called.

NOTE: If you need to connect to another non-MS-SQL database, you can modify the ‘CreateConnectionString’ function in the BLL class to generate the appropriate connection string.

To call the stored procedure, you should write this function in DAL:

public int MyStoreProcedure(int param1, String param2) {/ / depending on the type of the stored procedure returns to create user data StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult(eRequestType.Scalar); // Define the parameters passed to the stored procedure here, If there is no parameter can be omitted the userData. The Parameters = new System. The 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 for execution to complete... Tswaitforresult > // Execution not completed <timeout> if (WaitSqlCompletes(userdata)! = eWaitForSQLResult.Success) throw new Exception("Execution failed"); // Get the result... return userData.ScalarValue; }Copy the code

As you can see, the return value types of stored procedures can be ‘Scalar’, ‘Reader’, or ‘NonQuery’. For ‘Scalar’, the ‘ScalarValue’ parameter of ‘userData’ makes sense (i.e. returns results); For ‘NonQuery’, the ‘AffectedRows’ parameter of’ userData ‘is the number of rows affected; For Reader types, ‘ReturnValue’ is the ReturnValue of the function, and you can access the recordset from userData’s ‘resultDataReader’ parameter.

Take a look at this example:

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; }Copy the code

In this example, we call ‘ExecuteSQLStatement’ to execute an SQL query directly, but the idea is the same as’ ExecuteStoredProcedure ‘.

We use the ‘.read () ‘method of’ resultDataReader ‘to iterate on the returned result set. Helper methods are also provided to avoid exceptions caused by NULL fields, GetIntValueOfDBField, etc.

There are three types of userData that you pass into an ExecuteSQLStatement if you want to execute SQL commands rather than stored procedures:

  • ReaderQueryCallbackResult userData; Apply to have returned you the statement, can pass the userData. ResultDataReader gain access to return you.
  • NonQueryCallbackResult userData is suitable for statements like UPDATE that return nothing, and you can check the result of the execution using userData.affectedrows.
  • ScalarQueryCallbackResult userData is used to query only returns a scalar value, such as ` SELECT code FROM TBL WHEN ID = 10 `, through the userData. ScalarValue returned results.

For stored procedures, there is only one data type that needs to be passed to ExecuteStoredProcedure. But when declaring variables you need to specify the return type of the stored procedure:

  • StoredProcedureCallbackResult userData (eRequestType) in addition to declare different, other operating the same as above.

Use code asynchronously

In case you don’t want the calling thread to be blocked by a query, you’ll need to periodically call ‘WaitSqlCompletes’ to check whether the query has completed and the execution has failed.

/// you'll need to periodically call WaitSqlCompletes(userData, 10) /// to see if the results are available! /// </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; }Copy the code

In the calling thread you need to do 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; }...Copy the code

Database status

There is only one event in the BLL that provides database state asynchronously. If the database connection is disconnected (usually due to a network problem), the OnDatabaseStatusChanged event is suspended.

In addition, if the connection is restored, this event will be suspended again to notify you of the new database status.

Interesting places

As I developed the code, I learned that the connection timeout in the connection string is just as important as the execution timeout of the SQL command object.

First, you must realize that the maximum allowable time is defined in the connection string, and that some execution instructions can be given longer timeouts than those in the connection string.

Second, each command has its own execution time, which defaults to 30 seconds in this code. You can easily modify it to work with all types of commands, like this:

userData.tsWaitForResult = TimeSpan.FromSeconds(15);Copy the code