Dbhelper: Write method of the advanced and effective background database operation class of the three-tier architecture or user help class

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. text;
Using system. collections;
Using system. Data;
Using system. Data. sqlclient;

Namespace istext
{
/// <Summary>
/// Database operation help class
/// </Summary>
Public static class dbhelper
{
Public static readonly string connectionstring = system. configuration. configurationmanager. deleetting ["webdal"];
// Create a backflush parameter to store SQL statement Parameters
// Return the synchronous packaging of hashtable
Private Static hashtable parmcache = hashtable. synchronized (New hashtable ());

/// <Summary>
/// Execute add, delete, and modify data processing
/// </Summary>
/// <Returns> </returns>
Public static int executenonquery (string connectionstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Using (sqlconnection conn = new sqlconnection (connectionstring ))
{
// On the surface, the SQL statement is executed. It is actually an additional SQL statement parameter, and a transaction, stored procedure, and other parameters.
Preparecommand (CMD, connectionstring, null, struct type, plain text, commandparameters );
// Real execution starts at this time
Int Var = cmd. executenonquery ();
Return var;
}
}
/// <Summary>
/// Reload the execution of the transaction
/// </Summary>
/// <Returns> </returns>
Public static int executenonquer (sqltransaction Tran, commandtype primitive type, string plain text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Preparecommand (CMD, Tran. Connection, Tran, primitive type, plain text, commandparameters );
Int Var = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return var;
}
/// <Summary>
/// Process the queried data
/// Previous versions only have two parameters
/// The current version has four parameters.
/// </Summary>
/// <Returns> the data reader reads objects </returns>
Public static sqldatareader executereader (string connectionstring, commandtype cmdtype, string cmdtext, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection conn = new sqlconnection (connectionstring );
Preparecommand (CMD, Conn, null, struct type, plain text, commandparameters );
Sqldatareader SDR = cmd. executereader (commandbehavior. closeconnection)
Cmd. Parameters. Clear ();
Return SDR;
// The traditional method is
// Return cmd. executereader (commandbehavior. closeconnection );
}
Public static object executescalar (string connstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Using (sqlconnection conn = new sqlconnection (connstring ))
{
Preparecommand (CMD, Conn, null, struct type, plain text, commandparameters );
Object Var = cmd. executescalar ();
Return var;
}
}
// Put the SQL statement parameters in the reversed array: Do not put them in.
// Use the SQL statement as the key value to traverse Parameters
Public static void cacheparameters (string cachekey, Params sqlparameter [] commandparameters)
{
// Declared array parmcache
Parmcache [cachekey] = commandparameters;
}
/// <Summary>
/// Start parameter Removal
/// Use SQL statements
/// Apply the clone method to the de-parameter: Do not
/// </Summary>
/// <Returns> </returns>
Public static sqlparameter [] getcacheparameters (string cachekey)
{
// Convert to the parameter array type
Sqlparameter [] cacheparameter = (sqlparameter []) parmcache [cachekey];
If (cacheparameter = NULL)
{
Return NULL;
}
Sqlparameter [] cloneparams = new sqlparameter [cacheparameter. Length];
For (INT I = 0, j = cacheparameter. length; I <j; I ++)
{
// First convert the parameter to a clone interface type: (icloneable) cacheparameter [I]
// (Icloneable) cacheparameter [I]
// In clone
(Icloneable) cacheparameter [I]). Clone ();
// Finally convert the data into a parameter array type
Cloneparams [I] = (sqlparameter []) (icloneable) cacheparameter [I]). Clone ();
Return cloneparams;
}
}
//////// Summary of the cloning procedure: First, you can understand that cloning stores the parameter in the backflush and then retrieves the clone or copy equivalent to this parameter.
///////// 1. Declare a cloned parameter array and initialize the length of the array.
////////// 2. traverse the array and clone it continuously. Three steps are included.
// First, a cloned parameter array is converted into an interface type of the clone type.
// Second: start cloning and call the clone () method.
// Third: convert to the final parameter array type
/////////// 3. The cloned array is returned, that is, the parameters stored in the backflush are returned.
/// <Summary>
/// Execute the SQL statement
/// Traditional SQL statements are directly passed and then attached with Parameters
/// Currently, SQL statements are not passed in, and a method for executing SQL statements is still written separately. Parameters and many parameter conditions are appended.
/// </Summary>
/// <Param name = "cmd"> </param>
/// <Param name = "conn"> </param>
/// <Param name = "Tran"> </param>
/// <Param name = "partition type"> </param>
/// <Param name = "plain text"> </param>
/// <Param name = "Parameters"> </param>
Public static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction Tran, commandtype primitive type, string plain text, sqlparameter [] parameter parameters)
{
If (conn. State! = Connectionstate. open)
{
Conn. open ();
}
Cmd. Connection = conn;
Cmd. commandtext = plain text;
If (Tran! = NULL)
{
Cmd. Transaction = Tran;
}
Cmd. commandtype = primitive type;
If (parameter parameters! = NULL)
{
Foreach (sqlparameter parm in parameter parameters)
{
Cmd. Parameters. Add (parm );
}
}
}
}
}