Asynchronous SQL database Encapsulation detailed _mssql

Introduction

I've been looking for a simple and efficient 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 less flexible, so I decided to write one myself.

With this library, you can easily connect to any Sql-server database, execute any stored procedures or T-SQL queries, and receive the query results asynchronously. This library is developed in C # with no other 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:

1. BLL (Business Logic Layer) 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 for other classes to inherit.
2, 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 p Ass) 
  { 
   base. Start (server, DB, user, pass); 
 
  ~dal () 
  { 
   base. Stop (Estoptype.forcestopall); 
  } 
  //Todo:here can add your code here ... 
 } 
} 

Because the BLL class maintains threads that handle asynchronous queries, you need to provide the necessary data to splice the connection string. Never forget to call the ' Stop ' function, or the destructor will force it to call it.

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.

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

 public int mystoreprocedure (int param1, string param2) {//To create user data based on the return type of the stored procedure S 
   
  Toredprocedurecallbackresult userData = new Storedprocedurecallbackresult (erequesttype.scalar); This defines the parameters of the incoming stored procedure, and if there are no parameters, omit <span style= "LINE-HEIGHT:1.5;FONT-SIZE:9PT;" >userdata.parameters = new system.data.sqlclient.sqlparameter[] {</span> new System.Data.SqlClient.SqlPara 
   
  Meter ("@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 finish ...//wait long for <userdata.tswaitforresult>//perform incomplete 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 may be ' Scalar ', ' Reader ' and ' nonquery '. For ' Scalar ', the ' scalarvalue ' parameter of ' userData ' is meaningful (that is, the return result); for ' nonquery ', ' userData ' the ' 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 ' UserData ' ' resultdatareader ' parameter.

And take a look at this example:

public bool Mysqlquery (int param1, string param2) {//Create user data according to return type of store procedure I 
   
  N SQL (This comment is not updated to show that "the Annotation is Devil" has a point) 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'll wait (userdata.tswaitforresult)//For the command to be completed oth 
     
  Erwise returns <timeout> if (Waitsqlcompletes (userData)!= ewaitforsqlresult.success) return false; Get the result ... if (userData.resultDataReader.HasRows && userData.resultDataReader.Read ()) {//D 
    o 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 ' 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 categories of userData that need to be passed in Executesqlstatement:

1, Readerquerycallbackresult UserData: For statements that return a recordset, access to the returned recordset can be obtained through Userdata.resultdatareader.
2. Nonquerycallbackresult UserData: For statements that do not return content such as update, you can use Userdata.affectedrows to check the results of execution.
3. Scalarquerycallbackresult UserData: A case where a query statement returns only a scalar value, such as ' SELECT code from TBL when id=10 ', through Userdata.scalarvalue Gets the result of the return.
For stored procedures, there is only one type of data that needs to be passed in Executestoredprocedure. But when declaring a variable, you need to indicate the return value type of the stored procedure:

Storedprocedurecallbackresult UserData (Erequesttype): In addition to declaring different, 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) 
///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 can omit it incase there is no Paramet ER 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 are milliseconds 
to ... Switch (mydal.waitsqlcompletes (userData)) {case 
ewaitforsqlresult.waiting: 
 goto Wait_more; 
Case ewaitforsqlresult.success: 
 goto Get_the_result; 
Default: 
 goto execution_failed; 
} 

Database state

Only one event in BLL that provides the state of the database 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 hung up again to notify you of the new database state.

Interesting place.

As I developed the code, I understood that the connection time limit (connection timeout) in the connection string and the execution time of the SQL command object (execution timeout) are equally important.

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

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

The above is the asynchronous SQL database encapsulation all the process, I hope to help you learn.