Asynchronous SQL Database Encapsulation

Introduction

I've been looking for a simple and efficient library that can provide an asynchronous way to prevent deadlocks while simplifying database-related programming.

Most of the libraries I found were either too cumbersome or less flexible, so I decided to write them myself.

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. This library is developed in C # with no external dependencies.

Background

You may need some background knowledge of event-driven programming, but this is not required.

Use

This library consists of two classes:

• The BLL (Business Logic Layer) provides methods and properties that 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 for other classes to inherit.
• The dal (Data Access Layer) you need to write your own functions for executing 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 ...}}

Since the BLL class maintains a thread that handles asynchronous queries, you need to provide the necessary data to stitch the connection strings. Don't forget to call the ' Stop ' function, otherwise the destructor will force it to be called.

Note: If you need to connect to other non-ms-sql databases, you can generate the appropriate connection string by modifying the ' createconnectionstring ' function in the BLL class.

In order to invoke a stored procedure, you should write this function in the DAL:

Public int mystoreprocedure (int param1, string param2) {    //   Create user data based on the return type of the stored procedure     storedprocedurecallbackresult userdata = new  storedprocedurecallbackresult (erequesttype.scalar);    //  here defines the parameters of the incoming stored procedure, If no parameters can be omitted 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 for execution to complete ...     //&nThe length of the wait is  <userdata.tswaitforresult>     //  does not complete return  <timeout >    if  (Waitsqlcompletes (userData)  != ewaitforsqlresult.success)          throw new exception ("execution failed");     // get the result...    return userdata.scalarvalue;}

As you can see, the return value type of the stored procedure could be ' Scalar ', ' Reader ' and ' nonquery '. For ' Scalar ', the ' scalarvalue ' parameter of ' userData ' has meaning (i.e. return result); for ' nonquery ', ' UserData ' 's ' affectedrows ' parameter is the number of rows affected; for ' Reader ' type, ' ReturnValue ' is the return value of the function, and you can access the recordset through the ' Resultdatareader ' parameter of ' UserData '.

Look again at this example:

Public bool mysqlquery (INT&NBSP;PARAM1,&NBSP;STRING&NBSP;PARAM2) {    //  Create user data according to return type of store procedure  in sql (This note has not been updated, stating "The comment is the devil" a little Bit)     ReaderQueryCallbackResult userData  = new readerquerycallbackresult ();     string sqlcommand = string. Format ("Select top (1)  * from tbl1       where code  = {0} AND name LIKE &apos;%{1}%&apos; ",  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 example, we call ' Executesqlstatement ' to execute a SQL query directly, but the idea is the same as ' executestoredprocedure '.

We use the ' Resultdatareader '. The Read () ' method iterates over the returned result set. Some helper methods are also provided to avoid exceptions caused by null fields, Getintvalueofdbfield, etc. in the iteration.

If you want to execute SQL commands instead of stored procedures, there are three classes of userData that need to pass in Executesqlstatement:

• Readerquerycallbackresult UserData;
  For statements that have a return recordset, access to the returned recordset can be obtained through Userdata.resultdatareader.
• Nonquerycallbackresult UserData
  For statements like update that do not return content, you can use Userdata.affectedrows to check the results of the execution.
• Scalarquerycallbackresult UserData
  A case in which a query statement returns only a scalar value, for example ' SELECT code from TBL when id=10 ', to obtain the returned result by Userdata.scalarvalue.

For stored procedures, there is only one type of data that needs to be passed in to Executestoredprocedure. But when declaring a variable you need to indicate the type of return value of the stored procedure:

• Storedprocedurecallbackresult UserData (Erequesttype)
  In addition to declaring different, the other operations are the same as above.

To use code asynchronously

If you do not want the calling thread to be blocked by the query, you need to periodically call ' waitsqlcompletes ' to check whether the query is complete and whether the execution failed.

 <summary>///  you need to periodically call Waitsqlcompletes (USERDATA,&NBSP;10)  ///  to see if the results are available!///  </summary>public storedprocedurecallbackresult mystoreprocedureasync (INT&NBSP;PARAM1, &NBSP;STRING&NBSP;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;}

You need to do this in the calling thread:

... DAL. Storedprocedurecallbackresult UserData = Mydal.mystoreprocedureasync (ten, "Hello") ...//each time we wait 10 Milliseconds to see the Result...switch (Mydal.waitsqlcompletes (UserData, ten)) {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 that provides the state of the database asynchronously. If the database connection is disconnected (usually due to network problems), the Ondatabasestatuschanged event is suspended.

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

Interesting place.

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

First, you must realize that the maximum allowable time limit is defined in the connection string, and that you can give some execution instructions longer than the time-out in the connection string.

Second, each command has its own execution time limit, which in the code here defaults to 30 seconds. You can easily modify it so that it applies to all types of commands, just like this:

Userdata.tswaitforresult = Timespan.fromseconds (15);

Asynchronous SQL Database Encapsulation