SqlHelper, a data access component provided by Microsoft

The data access component is a set of common database access code that can be used in all projects and generally does not need to be modified. This section uses the data access assistant provided by Microsoft, which is tightly encapsulated and simple to use.

First, add a class and name it SqlHelper. The system will prompt whether to put the class in the App_Code folder. In this case, you must select "yes", because the system will automatically compile the folder, and the programmer can directly use it without additional compilation.

SqlHelper aims to obtain information from the database or save the information to the database. The main functions of this instance are as follows.

(1) execute the T-SQL command that does not return data. For example, modifying membership card information and adding member information.

(2) return the T-SQL command of a field. For example, you can obtain the membership card type credit rules.

(3) return a group of data. For example, obtaining member information and obtaining all membership card types.

(4) List of cache parameters. When executing a statement, there may be multiple parameters. To increase the speed, cache the parameters.

(5) read cache parameters.

The following describes the functions of the SqlHelper class in charts, as shown in 7-16.

Add database processing in the SqlHelper class. The Code is as follows:

// Id 1

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Data.SqlClient;

// ID 2
/** // <Summary>
/// Common Database Access Code
/// This class is an abstract class and cannot be instantiated. It can be called directly during the application.
/// </Summary>
Public abstract class SqlHelper
...{
// Obtain the database connection string, which is a static variable and read-only. All documents in the project can be used directly, but cannot be modified.
// 13
Public static readonly string ConnectionStringLocalTransaction =
ConfigurationManager. ConnectionStrings ["connstring"]. ConnectionString;
// A hash table is used to store cached parameter information. A hash table can store any type of parameter information.
// 8
Private static Hashtable parmCache = Hashtable. Synchronized (new Hashtable (); // Number 9

/** // <Summary>
/// Execute a SqlCommand that does not require a return value by specifying a dedicated connection string.
/// Provide the parameter list in the form of parameter Arrays
/// </Summary>
/// <Remarks>
/// Example:
/// Int result = ExecuteNonQuery (connString, CommandType. StoredProcedure,
/// "PublishOrders", new SqlParameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionString"> A valid database connection string </param>
/// <Param name = "commandType"> SqlCommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandText"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandParameters"> provides an array of parameters used in the SqlCommand.
/// </Param>
/// <Returns> return a value indicating the number of rows affected by the execution of the SqlCommand </returns>
// 3
Public static int ExecuteNonQuery (string connectionString, CommandType cmdType,
String plain text, params SqlParameter [] commandParameters)
...{
SqlCommand cmd = new SqlCommand ();
// ID 4
Using (SqlConnection conn = new SqlConnection (connectionString ))
...{
// Add parameters to the SqlCommand parameter set one by one using the PrePareCommand Method
PrepareCommand (cmd, conn, null, struct type, plain text, commandParameters );
Int val = cmd. ExecuteNonQuery ();
// Clear the parameter list in SqlCommand
Cmd. Parameters. Clear ();
// 14
Return val;
}
}

　

/** // <Summary>
/// Execute a SqlCommand that does not return results and connect to an existing database
/// Provide parameters using the parameter Array
/// </Summary>
/// <Remarks>
/// Example:
/// Int result = ExecuteNonQuery (conn, CommandType. StoredProcedure,
/// "PublishOrders", new SqlParameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "conn"> an existing database connection </param>
/// <Param name = "commandType"> SqlCommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandText"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandParameters"> provides an array of parameters used in the SqlCommand.
/// </Param>
/// <Returns> return a value indicating the number of rows affected by the execution of the SqlCommand </returns>

// ID 5
Public static int ExecuteNonQuery (SqlConnection connection, CommandType parameter type, string parameter text, params SqlParameter [] commandParameters)
...{

　　　　　　　 SqlCommand cmd = new SqlCommand();
　　　　　　　 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
　　　　　　　 int val = cmd.ExecuteNonQuery();
　　　　　　　 cmd.Parameters.Clear();
　　　　　　　 return val;
　　　 }

/** // <Summary>
/// Execute a SqlCommand that does not return the result and process it through an existing database transaction
/// Provide parameters using the parameter Array
/// </Summary>
/// <Remarks>
/// Example:
/// Int result = executenonquery (trans, commandtype. storedprocedure,
/// "Publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "trans"> an existing SQL transaction processing </param>
/// <Param name = "commandtype"> sqlcommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandtext"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandparameters"> provides an array of parameters used in the sqlcommand.
/// </Param>
/// <Returns> return a value indicating the number of rows affected by the execution of the sqlcommand </returns>

// ID 12
Public static int executenonquery (sqltransaction trans, commandtype primitive type, string plain text, Params sqlparameter [] commandparameters)
...{

　　　　　　　 SqlCommand cmd = new SqlCommand();
　　　　　　　 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
　　　　　　　 int val = cmd.ExecuteNonQuery();
　　　　　　　 cmd.Parameters.Clear();
　　　　　　　 return val;
　　　 }

/** // <Summary>
/// Execute a SqlCommand to return the result set and use a dedicated connection string.
/// Provide parameters using the parameter Array
/// </Summary>
/// <Remarks>
/// Example:
/// SqlDataReader r = ExecuteReader (connString, CommandType. StoredProcedure,
/// "PublishOrders", new SqlParameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionString"> A valid database connection string </param>
/// <Param name = "commandType"> SqlCommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandText"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandParameters"> provides an array of parameters used in the SqlCommand.
/// </Param>
/// <Returns> returns a SqlDataReader containing the result. </returns>

　　　 public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
　　　 ...{

SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (connectionString );
// Here try/catch is used for processing because SqlDataReader does not exist if a method exception occurs,
// The CommandBehavior. CloseConnection statement is not executed, and the exception triggered is caught by catch.
// Close the database connection and throw the exception to be caught again.
Try
...{
PrepareCommand (cmd, conn, null, struct type, plain text, commandParameters );
SqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Return rdr;
}
Catch
...{
Conn. Close ();
Throw; // number 7
}
}

/** // <Summary>
/// Execute a SqlCommand that returns the first record column and uses a dedicated connection string.
/// Provide parameters using the parameter Array
/// </Summary>
/// <Remarks>
/// Example:
/// Object obj = ExecuteScalar (connString, CommandType. StoredProcedure,
/// "PublishOrders", new SqlParameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionString"> A valid database connection string </param>
/// <Param name = "commandType"> SqlCommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandText"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandParameters"> provides an array of parameters used in the SqlCommand.
/// </Param>
/// <Returns> returns the data of the object Type. You can use the Convert. To {Type} method To Convert the data Type. </returns>
Public static object executescalar (string connectionstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
...{

　　　　　　　 SqlCommand cmd = new SqlCommand();
　　　　　　　 using (SqlConnection connection = new SqlConnection(connectionString))
　　　　　　　 ...{
　　　　　　　　　　　 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
　　　　　　　　　　　 object val = cmd.ExecuteScalar();
　　　　　　　　　　　 cmd.Parameters.Clear();
　　　　　　　　　　　 return val;
　　　　　　　 }

　　　 }

/** // <Summary>
/// Execute a sqlcommand that returns the first column of record and uses an existing database connection.
/// Provide parameters using the parameter Array
/// </Summary>
/// <Remarks>
/// Example:
/// Object OBJ = executescalar (connstring, commandtype. storedprocedure,
/// "Publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "conn"> an existing database connection </param>
/// <Param name = "commandtype"> sqlcommand command type (stored procedures, T-SQL statements, and so on .)
/// </Param>
/// <Param name = "commandtext"> stored procedure name or T-SQL statement </param>
/// <Param name = "commandParameters"> provides an array of parameters used in the SqlCommand.
/// </Param>
/// <Returns> returns the data of the object Type. You can Convert the data Type using the Convert. To {Type} method.
/// </Returns>
Public static object ExecuteScalar (SqlConnection connection, CommandType parameter type, string parameter text, params SqlParameter [] commandParameters)
...{

　　　　　　　 SqlCommand cmd = new SqlCommand();
　　　　　　　 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
　　　　　　　 object val = cmd.ExecuteScalar();
　　　　　　　 cmd.Parameters.Clear();
　　　　　　　 return val;
　　　 }

/** // <Summary>
/// Cache parameter Array
/// </Summary>
/// <Param name = "cacheKey"> parameter cache key value </param>
/// <Param name = "paramparms"> List of cached parameters </param>

　　　 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
　　　 ...{

// 10
ParmCache [cacheKey] = commandParameters;

　　　 }

/** // <Summary>
/// Obtain the cached Parameters
/// </Summary>
/// <Param name = "cacheKey"> KEY value used to search for a parameter </param>
/// <Returns> return the cached parameter array </returns>

　　　 public static SqlParameter[] GetCachedParameters(string cacheKey)
　　　 ...{

SqlParameter [] cachedParms = (SqlParameter []) parmCache [cacheKey];
If (cachedParms = null)
Return null;
// Create a parameter clone list
SqlParameter [] clonedParms = new SqlParameter [cachedParms. Length];
// Assign values to the clone parameter list through Loops
For (int I = 0, j = cachedParms. Length; I <j; I ++)
// Use the clone method to copy parameters in the parameter list
// ID 11
ClonedParms [I] = (SqlParameter) (ICloneable) cachedParms [I]). Clone ();
Return clonedParms;
}

/** // <Summary>
/// Prepare parameters for executing the command
/// </Summary>
/// <Param name = "cmd"> SqlCommand command </param>
/// <Param name = "conn"> existing database connection </param>
/// <Param name = "trans"> Database Transaction Processing </param>
/// <Param name = "primitive type"> SqlCommand command type (stored procedure, T-SQL statement, and so on .) </Param>
/// <Param name = "plain text"> Command text, T-SQL statement such as Select * from
/// Products </param>
/// <Param name = "paiparms"> return the command with parameters </param>

// 6
Private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType primitive type, string plain text, SqlParameter []
CmdParms)
...{

// Determine the database connection status
If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd. commandtext = plain text;
// Determine whether transaction processing is required
If (trans! = NULL)
Cmd. Transaction = trans;
Cmd. commandtype = primitive type;
If (partition parms! = NULL)
...{
Foreach (sqlparameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );

　　　　　　　 }
　　　 }

}

 

Code is a complete data access component. The following describes the specific implementation of the Code.

(1) Using Keyword: see code number 1. Using processes references to namespaces. Generally, if the system prompts that a class cannot be found, you must carefully check whether the namespace of the class is referenced. In C #2.0, Using can also implement the namespace alias, for example, Using SC = System. Collections. The alias is used to simplify the namespace. the alias statement is SC: ArrayList list = new SC: Arraylist ().

(2) Note: See code number 2. If you enter "//" in the line above the method, the system automatically builds the annotation architecture of this method, including Summary and param name. If the method returns a value, it also includes returns. If special instructions are required, you can use the remarks flag. Based on this structure, you can easily describe the composition and usage of the entire method. Use "//" in comments. A prompt will be displayed in future code calls. The prompt content is the added comments.

(3) Use CommandType: see Code 3. Two ways to process SQL Server data: stored procedures and T-SQL statements. Each execution statement has a CommandType parameter, which is an enumeration type and has three options: StoredProcedure, TableDirect, and Text. They represent stored procedures, tables, and T-SQL statements, respectively. If the CommandType parameter is StoredProcedure, the stored Text parameter is the name of the stored procedure. If the CommandType is Text, the stored Text parameter is an SQL statement. If the CommandType is TableDirect, the stored Text parameter is the table name.

(4) Using statement: see code number 4. In the Using statement, "{}" is used to define a range and release the resources used in the statement when the statement is completed. The Using statement is usually used to obtain data. The scope of the statement is from the start of the database connection, to the end of all resources Using the connection after it is run. The Using statement can be used with multiple objects. The object Using the Using statement must inherit the IDispose interface. This interface implements the Dispose method, which is the executor of the released object resource.

(5) parameter array: see code number 5. Optional parameters are not allowed in C #. Therefore, parameters are usually implemented by arrays of unspecified sizes. Parameters in the array are added by the PrepareCommand method.

(6) SQL transaction processing: see Code 6. A transaction is a group of associated operations. During transaction processing, the related tables are usually locked and unlocked only after the transaction is processed. This ensures data integrity. A transaction generally includes three methods: Start the transaction, execute the transaction, and roll back the transaction ). If a statement in the transaction has a problem, the transaction will be rolled back and the execution of other statements will be canceled.

(7) throw: see Code 7. The caught exception is thrown again to add the exception handling information to the text. If an exception is to be thrown, throw and catch must be used together. If catch has parameters, throw must also contain parameters, and vice versa.

(8) hash table: see code 8. Indicates a combination of key/value pairs. Access the hash code through key-value ing .. The hash table in. NET is a container provided by the System. Collections namespace and its English name is HashTable. The key is used for quick search, and the key is case sensitive. The value stores the value corresponding to the key, usually represented as the object type. When the value is set, the corresponding type conversion is required.

(9) Synchronous packaging of hash tables: see Code 9. The private static Hashtable parmCache = Hashtable. Synchronized (new Hashtable () Statement implements synchronous packaging of hash tables, which is thread-safe. The hash table here is a static variable of the static type. Since it is static, It is a definition and is used globally. If all parameters use this hash table, how can we ensure that other users do not affect their reading during modification? Previously, you can use the lock method to lock the table first, and do not allow others to modify the table. After reading the table, unlock the table. Currently,. NET provides the HashTable Synchronized method to implement the same functions without manual locking. It is directly completed by the system framework.

(10) cache parameter list: see code number 10. The cache parameter list stores the parameter information in the defined static HashTable. HashTable is static. Once defined, the memory address is allocated and can be used at any time in the project, which plays a role in caching data.

(11) clone: clone. See Code 11. In. NET, almost all Collections that inherit the Collections class have clone methods. The following code is used to obtain cache parameters: clonedParms [I] = (SqlParameter) (ICloneable) cachedParms [I]). Clone (). Because all parameters are saved in a HashTable table, only the parameter names are saved in the table, and the parameter content is given different values by different calls. To correctly reflect the value of the caller, a parameter list must be cloned and assigned to the caller Based on the function.

(12) method overloading: see Code 12. Defines two or more methods with the same name but different parameters. In SqlHelper, The ExcuteNonQuery is overloaded four times. The comment in the Code clearly shows the differences in the parameter list. In actual application, call the corresponding method according to the functional environment.

(13) ConfigurationManager: see code 13. Is a newly added class in ASP. NET 2.0, mainly managing the Web. config file. You can easily obtain the configurations defined in the Web. config file, which is usually used to obtain database connection strings and personalized configuration information. In this example, the following database connection string configuration is added to Web. config.

　<connectionStrings　 >
<add name="connstring" connectionString ="server=.;database=membercard; uid=sa; pwd="/>
</connectionStrings>

(14) Return Value of the ExcuteNonQuery method: see Code 14. In the SqlCommand method, ExcuteNonQuery is used to perform insert, update, or delete operations. The program does not require any return values, but when defining this method in SqlHelper, a numeric data is returned. This data indicates the number of affected rows in the database after the current statement is executed. Although the return value is defined here, the method can be directly executed without the return value in the program call. For example, SqlHelper. ExcuteNonQuery () is correct. You do not have to write it as int val = SqlHelper. ExcuteNonQuery (). If the int value is not required in the program, it can be converted.