Asynchronous SQL database Encapsulation

Asynchronous SQL database Encapsulation

Introduction

I have been searching for a simple and effective library that provides an Asynchronous Method to prevent deadlocks while simplifying database-related programming.

Most of the databases I found were either too cumbersome or inflexible, so I decided to write one by myself.

With this library, you can easily connect to any SQL-Server database, execute any stored procedure or T-SQL queries, and asynchronously receive query results. This library is developed using C # and has no other external dependencies.

Background

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

Use

This library consists of two classes:

1. BLL (Business Logic Layer) provides methods and properties for accessing the MS-SQL database, executing commands and querying, and returning results to callers. You cannot directly call the object of this class. It is only inherited by other classes.
2. You need to write the SQL stored procedure and query functions by yourself, and you may need different DAL classes for different tables.
First, you need to create a 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 the BLL class maintains the thread for processing asynchronous queries, you need to provide necessary data to splice the connection string. Do not forget to call the 'stop' function. Otherwise, the Destructor will force it to be called.

NOTE: To connect to other non-MS-SQL databases, you can generate the appropriate connection string by modifying the 'createononstring' function in The BLL class.

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

Public int MyStoreProcedure (int param1, string param2) {// create user data StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult (eRequestType. scalar); // defines the parameters for passing in the stored procedure here. If there is no parameter, <span style = "line-height: 1.5; font-size: 9pt;"> userData. parameters = new System. data. sqlClient. sqlParameter [] {</span> new System. data. sqlClient. sqlParameter ("@ param1", param1), new System. data. sqlCl Ient. SqlParameter ("@ param2", param2),}; // Execute procedure... if (! ExecuteStoredProcedure ("usp_MyStoreProcedure", userData) throw new Exception ("Execution failed"); // wait until Execution is completed... // The waiting duration is <userdata. tswaitforresult> // <timeout> if (WaitSqlCompletes (userData) returned when the execution is incomplete )! = EWaitForSQLResult. Success) throw new Exception ("Execution failed"); // Get the result... return userData. ScalarValue ;}

As you can see, the return values of stored procedures can be 'scalar ', 'reader', or 'nonquery '. The 'scalarvalue' parameter of 'scalar 'and 'userdata' makes sense (that is, the returned result). For 'nonquery', The 'affectedrows' parameter of 'userdata' is the number of affected rows; for the 'reader' type, 'returnvalue' is the return value of the function. In addition, you can access the recordset through the 'resultdatareader 'parameter of 'userdata.

Let's 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 ;}

In this example, we call 'executesqlstatement 'to directly execute an SQL query, but the idea is the same as that of 'executestoredprocedure.

We use the '. Read ()' method of 'result' to iterate over the returned result set. In addition, some helper methods are provided to avoid exceptions caused by NULL fields and GetIntValueOfDBField in the stack generation.

If you want to execute SQL commands instead of stored procedures, there are three types of userData that need to be passed into ExecuteSQLStatement:

1. ReaderQueryCallbackResult userData: Applicable to statements that return recordset. You can use userData. resultDataReader to obtain access to the returned recordset.
2. NonQueryCallbackResult userData: Applicable to statements without returned content such as UPDATE. You can use userData. AffectedRows to check the execution result.
3. ScalarQueryCallbackResult userData: used WHEN a query statement returns only one scalar value, for example, 'select code FROM tbl when id = 10', the returned result is obtained through userData. ScalarValue.
For stored procedures, there is only one data type that needs to be imported into ExecuteStoredProcedure. However, when declaring a variable, You need to specify the type of the return value of the stored procedure:

StoredProcedureCallbackResult userData (eRequestType): Except declaration, other operations are the same as above.
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 fails.

/// <Summary> /// you need to periodically call WaitSqlCompletes (userData, 10) /// to check whether the result is available! /// </Summary> public StoredProcedureCallbackResult partition (int param1, string param2) {// Create user data according to return type of store procedure in SQL StoredProcedureCallbackResult userData = new partition (eRequestType. reader); // If your store procedure accepts some parameters, define them here, // or you can omit it incase there is no parameter definiti On 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 ;}

In the call 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; } ... 

Database status

In BLL, there is only one event that asynchronously provides the database status. If the database connection is disconnected (usually due to network problems), The OnDatabaseStatusChanged event will be suspended.

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

Interesting

When I develop code, I understand that connection timeout in the connection string is equally important as execution timeout.

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

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

userData.tsWaitForResult = TimeSpan.FromSeconds(15); 

The above is the whole process of asynchronous SQL database encapsulation, and we hope to help you learn it.