Shulou(Shulou.com)05/31 Report--

This article focuses on "the encapsulation process of asynchronous SQL database". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the encapsulation process of asynchronous SQL database.

Introduction

I have been looking for a simple and effective 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 inflexible, so I decided to write one 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 # and has no other external dependencies.

Background

You may need some background in 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 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... }}

Because the BLL class maintains threads that handle asynchronous queries, you need to provide the necessary data to concatenate connection strings. Don't forget to call the `Stop` function, or 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.

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

Public int MyStoreProcedure (int param1, string param2) {/ / create user data StoredProcedureCallbackResult userData = new StoredProcedureCallbackResult (eRequestType.Scalar) based on the return type of the stored procedure / / define the parameters passed into the stored procedure here. If there are no parameters, you can omit 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. / / wait for the length of time to be / / return if (WaitSqlCompletes (userData)! = eWaitForSQLResult.Success) throw new Exception ("Execution failed"); / / Get the result... Return userData.ScalarValue;}

As you can see, the return types of stored procedures can be `Scalar`, `Reader` and `NonQuery`. For `Scalar`, the parameter `ScalarValue` of `userData` is meaningful (that is, the result is returned); for `NonQuery`, the parameter `AffectedRows` of `userData` is the number of rows affected; for the type of `Reader`, `ReturnValue` is the return value of the function. In addition, you can access recordset through the parameter `resultDataReader` of `userData`.

Take another 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 & 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 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 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 `.Read () `method of `resultDataReader` to iterate through the returned result set. In addition, some helper methods are provided to avoid exceptions caused by NULL fields, GetIntValueOfDBField, and so on.

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. Access to the returned recordset can be obtained through userData.resultDataReader. 2. NonQueryCallbackResult userData: suitable for statements with no return, such as UPDATE, you can use userData.AffectedRows to check the results of execution. 3. ScalarQueryCallbackResult userData: used in cases where the query statement returns only one scalar value, such as `SELECT code FROM tbl WHEN ID= 10`, and the returned result is obtained through userData.ScalarValue. For stored procedures, there is only one data type that needs to be passed in ExecuteStoredProcedure. However, when you declare a variable, you need to specify the type of return value of the stored procedure:

StoredProcedureCallbackResult userData (eRequestType): the operation is the same as above except for a different declaration. Use code asynchronously

If you do not want the calling thread to be blocked by the query, you need to call `WaitSqlCompletes` periodically to check whether the query has completed and failed.

/ you need to call WaitSqlCompletes (userData, 10) / / periodically to see if the result is available! / 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;}

You need to do this in the calling thread:

... 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 BLL that provides database state asynchronously. 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 hung up again to inform you of the new database status.

An interesting place.

When I was developing 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 of all, you must be aware that the maximum allowable time limit is defined in the connection string, and you can give some instructions that are longer than the timeout in the connection string.

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

UserData.tsWaitForResult = TimeSpan.FromSeconds (15); at this point, I believe you have a better understanding of "the encapsulation process of asynchronous SQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.