Sqlhelper class Implementation Details

Sqlhelper class Implementation Details
The sqlhelper class is used to encapsulate data access through a set of static methods. This class cannot be inherited or instantiated, so it is declared as a non-inherited class that contains a dedicated constructor.
Each method implemented in the sqlhelper class provides a set of consistent overloading. This provides a good way to use the sqlhelper class to execute commands. It also provides necessary flexibility for developers to select a way to access data. Each method overload supports different method parameters, so developers can determine how to pass connection, transaction, and parameter information. The methods implemented in the sqlhelper class include:
Executenonquery. This method is used to execute commands that do not return any rows or values. These commands are usually used to execute database updates, but can also be used to return output parameters of stored procedures.
Executereader. This method is used to return the sqldatareader object, which contains the result set returned by a command.
Executedataset. This method returns the DataSet object, which contains the result set returned by a command.
Executescalar. This method returns a value. This value is always the first column in the first line returned by the command.
Executexmlreader. This method returns the XML fragment of the for XML query.
In addition to these common methods, the sqlhelper class also contains some special functions for managing parameters and preparing commands to be executed. Regardless of the method implementation called by the client, all commands are executed through the sqlcommand object. Before the sqlcommand object can be executed, all parameters must be added to the parameters set, and the connection, commandtype, commandtext, and transaction attributes must be correctly set. The special functions in the sqlhelper class are mainly used to provide a consistent method for issuing commands to the SQL Server database without considering client applications. Program Call the implementation of the overload method. The special utility functions in the sqlhelper class include:
Attachparameters: This function is used to connect all necessary sqlparameter objects to the running sqlcommand.
Assignparametervalues: This function is used to assign values to sqlparameter objects.
Preparecommand: This function is used to initialize command attributes (such as connection and transaction environment.
Executereader: This dedicated executereader implementation is used to open the sqldatareader object through the appropriate commandbehavior, so as to most effectively manage the validity period of the connection associated with the reader.
Sqlhelper experience:
To download the blog from ceocio (MVP/csdn_web development Moderator:
The sqlhelper class in the Data Access Application Block provided by Microsoft encapsulates the most common data operations.Code There are also great differences.
I used to write a method for returning dataset as follows:
Public dataset getdepartmentmemberlist (INT extends mentid)
...{
Try
...{
String SQL = "oa_department_getdepartment_members ";
String conn = configurationsettings. receivettings ["strconnection"];
Sqlparameter [] P =
...{
Sqlhelper. makeinparam ("@ departmentid", sqldbtype. Int, 4, departmentid)
};
Dataset DS = sqlhelper. executedataset (Conn, commandtype. storedprocedure, SQL, P );
Return Ds;
}
Catch (system. Data. sqlclient. sqlexception ER)
...{
Throw new exception (ER. Message );
}
}
Now I wrote it like this:
// Connection string
Private string _ connectionstring = configurationsettings. deleettings ["strconnection"];
Public String connectionstring
...{
Get... {return this. _ connectionstring ;}
Set... {This. _ connectionstring = value ;}
}
Public dataset getnewstoindexpage (INT newstypeid)
...{
Return getdataset ("yzb_getnewstoindexpage", getnewstoindexpage_parameters (newstypeid ));
}
// Set stored procedure parameters
Private sqlparameter [] getnewstoindexpage_parameters (INT newstypeid)
...{
Sqlparameter [] P =
...{
Sqlhelper. makeinparam ("@ newstypeid", sqldbtype. Int, 4, newstypeid)
};
Return P;
}
// Sqlhelper is actually called here
Private dataset getdataset (string SQL, Params sqlparameter [] P)
...{
Return sqlhelper. executedataset (connectionstring, commandtype. storedprocedure, SQL, P );
}
Code is more flexible and safer: P
As a lazy person, I often write as follows:
System. Data. datatable dt = sqlhelper. executedataset (sqlhelper. conn_string_non_dtc, commandtype. Text, SQL). Tables [0];
Directly return the datatable. Basically, only one table is returned for binding.
String conn = configurationsettings. receivettings ["strconnection"];
I wrote it in sqlhelper. conn_string_non_dtc.
Makeinparam code is cumbersome. If the query input conditions cannot be combined with SQL,
I directly exec pronamr parw.gs
If the query input conditions have security issues or return parameters
Use sqlparameter [] queryparam = new sqlparameter []... {
New sqlparameter ("@ userid", sqldbtype. INT)
};
Queryparam [0]. value = This. userid;
The @ and string. Format techniques are used to combine SQL statements. For example:
SQL = @ "Update test_user_statistic
Set
Test_user_statistic. [isqualified] = {0 },
Test_user_statistic. [reason] = '{1 }'
Where userid = {2 }";
SQL = string. Format (SQL, isqualified, reason, userid );
Sqlhelper. executenonquery (sqlhelper. conn_string_non_dtc, commandtype. Text, SQL );
SQL statements can be generated using the SQL query analyzer.
To ensure security, if there is an input vulnerability, we recommend that you use sqlparameter [] to cache parameters, because it will process aggressive code, such as a semicolon with single quotation marks, as common characters.
Superdullwolf (super stupid wolf, improving every day) at 4:52:13
Sqlhelper can roll back the transaction, but I think the transaction is better solved by the stored procedure itself:
Dbtransaction = cs. begintransaction ();
Sqlhelper. executenonquery (dbtransaction, commandtype. storedprocedure, "pro_t_accredit_create", arparams );
P_intoutput = convert. toint32 (arparams [3]. value. tostring ());
If (p_intoutput <0)
...{
Dbtransaction. rollback (); // -- roll back the transaction
}
In addition, the stored procedure rollback is used.
Create procedure...
@ Idint
....
As
Declare...
Begin transaction
Insert...
Delete...
If @ error <> 0 rollback transaction
Commit transaction
Go
Compare ASP rollback:
Title using transaction control julyclyde in ASP (original)
Keyword ASP, transaction, COM +
Written by Microsoft in September Community Star Microsoft China Community star
Transactions are often used in programming. A transaction is a series of operations that must be successful. If one operation fails, all other steps must be undone. For example, if you use ASP to develop a network hard drive system, the user registration must be:
Record user information to the database
Open a folder for users to store
Initialize user operation logs
Transactions must be used in these three steps. Otherwise, if the disk operation fails and the database operation is not revoked, the "dead user" phenomenon can only be logged in but cannot be operated.
Due to the special development history of the database system, from access to DB2, there is no transaction support. Therefore, the preceding steps can be described as follows:
On Error resume next
Step 1:
Log User information to the database in the transaction environment
If err then
Close connection
Exit
Else
Step 2: Create a folder
If err then
Roll back the first database operation and exit
Else
Step 3: Operate the log database in the transaction environment
If err then
Roll back the folder created in step 1
Exit
End if
End if
End if
Commit the first database operation transaction
Commit the transactions for database operations in step 2
End
Each step must be judged. If it fails, you also need to manually roll back the previous steps to make the program complex and difficult to understand. If you have updated the program and added other steps in the future, you also need to nest more layers of IF... else... end if to make the program process more complex.
The correct solution is to use ASP's transaction control function. By contacting the MTS service, IIS can control multiple transaction-supporting systems. When a program sends a "failure" signal, all systems that support transactions will automatically roll back, even if the operation has been completed, manual rollback is also convenient for operations that do not support transactions. The preceding example is rewritten using the ASP transaction control function as follows:
<% @ Transaction = required %>
On Error resume next
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open ....
Conn. Execute "insert ...."
Conn. Close
Set conn = nothing
Set conn2 = server. Createobject ("ADODB. Connection ")
Conn2.open ....
Conn2.execute "insert ...."
Conn2.close
Set conn2 = nothing
Set FSO = server. Createobject ("scripting. FileSystemObject ")
FSO. createfolder "...."
If err then
Objectcontext. setabort notifies all components that support transactions to roll back, and runs the manual rollback code.
Else
Objectcontext. setcomplete
End if
Set FSO = nothing
Sub ontransactionabort
Response. Write "error"
FSO. deletefile server. mappath ("a.txt") 'fso manual rollback -- delete folder
End sub
Sub ontransactioncommit
Response. Write "successfully completed the task"
End sub
%>
<% @ Transaction = required %> In the first line indicates that the ASP file on this page requires MTS transaction support. All the operations in the middle are written in the normal order, so you do not need to consider rollback. At the end of the program, determine whether there is an error. If the setabort method of objectcontext is called, IIS will notify all transaction-supporting components (mainly databases) to roll back through the MTS service ), run sub ontransactionabort to manually roll back operations that do not support transactions. If no error occurs, call the setcomplete method of objectcontext to run sub ontransactioncommit to display successful messages.
The entire ASP program does not need to write redundant code for judgment errors and rollback operations. It only needs to make judgment at the end, even if multiple steps are added in the future, you only need to control sub ontransactionabort, which is very convenient. programmers can focus on process writing rather than writing error correction code.
In fact, ASP also provides many more useful functions, waiting for us to use, do not think ASP uses the script language, the function will be weak.
Compare ADO rollback:
<%
'Use transactions in ASP
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "course_dsn", "course_user", "course_password"
Conn. begintrans 'Start the transaction
SQL = "delete from user_info"
Set rs = server. Createobject ("ADODB. recordset ")
Rs. Open SQL, Conn, 3, 3
If conn. errors. Count> 0 then 'has an error
Conn. rollbacktrans roll back
Set rs = nothing
Conn. Close
Set conn = nothing
Response. Write "transaction failed, rolled back to the status before modification! "
Response. End
Else
Conn. committrans 'commit a transaction
Set rs = nothing
Conn. Close
Set conn = nothing
Response. Write "transaction successful! "
Response. End
End if
%>
At first, sqlhelper appeared to be at the core of Microsoft petshop as the Dal layer.
In addition, for sqldatareader, I think this is similar to the recordset In ADO, which requires close.
If it is dangerous to PASS Parameters and return values, close () is required for outgoing transfer, and close () is easy to be ignored in the past, therefore, I think it is better to use a strong dataset as a parameter to pass the record set with the return value.
It is said that the following statement will automatically Recycle resources without close, and it is not true or false:
Using (sqldatareader reader = sqlhelper. executereader (sqlhelper. conn_string_non_dtc, commandtype. Text, SQL ))
...{
If (reader. Read ())
...{
Arr [0] = reader. getstring (1 );
....}
}