C # sqlhelper)

Note: This article from http://www.tzwhx.com/newOperate/html/3/31/312/13080.html
Visual C # tutorial on Dynamically Operating SQL Server database instances: sqlhelper)

The generic database category class introduced in this article is a base class actually used by my personal website. It is a set of common database access code sets, most of my website's accesses to the database use this class. Its main functions include:

1. Check whether the database exists?

2. Check whether the database table exists?

3. Check whether the database stored procedure exists?

4. check whether a view exists?

5. automatically create a database

6. automatically create database tables and stored procedures

7. Execution method of SQL statement executenonquery without Parameters

8. Execute a sqlcommand that does not return results. Use a dedicated connection string to execute a sqlcommand that does not require a return value.

9. Execute a sqlcommand that does not return results. Handle an existing database event

10. Execute a sqlcommand that does not return results and process it through an existing database transaction.

11. Execute a sqlcommand to return the result set and use a dedicated connection string.

12. Execute a sqlcommand that returns the first record column and uses a dedicated connection string.

13. Execute a sqlcommand that returns the first record column and uses an existing database connection.

The code list is as follows:

Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. collections;
Using system. Data. sqlclient;

///

/// Common Database Access Code
/// This class is an abstract class and cannot be instantiated. It can be called directly during the application.
///

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.
Public static readonly string connectionstringlocaltransaction = configurationmanager. connectionstrings ["mysqldataconnectionstring"]. connectionstring;

// A hash table is used to store cached parameter information. A hash table can store any type of parameter.
Private Static hashtable parmcache = hashtable. synchronized (New hashtable ());

///

/// Determine whether the database exists
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
///

///
/// Example:
/// Bool databaseexist = sqlhelper. checkexistsdatebase (databasenamestr );
///
//////
Public static bool checkexistsdatebase (string databasename)
{
String connstring = sqlhelper. connectionstringlocaltransaction;
String databasenamestr = "select count (1) from Master. DBO. sysdatabases where name = '" + databasename + "'";
Using (sqlconnection con = new sqlconnection (connstring ))
{

Con. open ();
Sqlcommand cmd = new sqlcommand (databasenamestr, con );
Int result = convert. toint32 (CMD. executescalar ());
If (result = 0)
{
Return false;
}
Else
{
Return true;
}
}
}
///

Return page header
/// Determine whether a database table exists
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
///

///
/// Example:
/// Bool databaseexist = sqlhelper. checkexistsdatebase (databasenamestr );
///
//////
Public static bool checkexiststable (string databasenamestr, string tablename)
{
String connstring = "Server =" server name "; database = '" + databasenamestr + "'" + "; trusted_connection = sspi ";
String tablenamestr = "select count (1) From sysobjects where name = '" + tablename + "'";
Using (sqlconnection con = new sqlconnection (connstring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (tablenamestr, con );
Int result = convert. toint32 (CMD. executescalar ());
If (result = 0)
{
Return false;
}
Else
{
Return true;
}
}
}
///

Return page header
/// Determine whether the database stored procedure exists
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
///

///
/// Example:
/// Bool databaseexist = sqlhelper. checkexistsdatebase (databasenamestr );
///
/////////
Public static bool checkexistsproc (string databasenamestr, string procname)
{
String connstring = "Server =" server name "; database = '" + databasenamestr + "'" + "; trusted_connection = sspi ";
// String procnamestr = "select count (1) From sysobjects where name = '" + procname + "'";
String procnamestr = "select count (1) From sysobjects where name = '" + procname + "'" + "and type = 'P '";
Using (sqlconnection con = new sqlconnection (connstring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (procnamestr, con );
Int result = convert. toint32 (CMD. executescalar ());
If (result = 0)
{
Return false;
// Does not exist
}
Else
{
Con. Close ();
Return true;
// Exists
}
}
}
///

Return page header
/// Determine whether a view exists
///

///
/// Example:
/// Bool databaseexist = sqlhelper. checkexistsdatebase (databasenamestr );
///
/////////
Public static bool checkexistsview (string databasenamestr, string tablename, string viewname)
{
String connstring = "Server =" server name "; database = '" + databasenamestr + "'" + "; trusted_connection = sspi ";
String viewnamestr = "select count (1) From sysobjects where name = '" + viewname + "'" + "and type = 'V '";
Using (sqlconnection con = new sqlconnection (connstring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (viewnamestr, con );
Int result = convert. toint32 (CMD. executescalar ());
If (result = 0)
{
Return false;
// Does not exist
}
Else
{
Con. Close ();
Return true;
// Exists
}
}
}
///

Return page header
/// Call executenonquery () to create a database
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
///

///
/// Example:
/// Sqlhelper. createsqldatabase (connstring, "Master", createstr );
///
////////////
Public static bool createsqldatabase (string connstring, string databasename, string createstr)
{
//
Using (sqlconnection conn = new system. Data. sqlclient. sqlconnection (connstring ))
{
Sqlcommand command = new system. Data. sqlclient. sqlcommand (createstr, Conn );
Conn. open ();
Command. Connection. changedatabase (databasename );
Try
{
Command. executenonquery ();
}
Catch (system. Exception ex)
{
Command. Connection. Close ();
Throw ex;
}
Finally
{
Command. Connection. Close ();
}
}
Return true;
}
///

/// Executenonquery ()
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
///

///
/// Example:
/// Sqlhelper. createsqltable (connstring, tablestr );
///
/////////
Public static bool createsqltable (string connstring, string createstr)
{
//
Using (sqlconnection conn = new sqlconnection (connstring ))
{
Sqlcommand command = new sqlcommand (createstr, Conn );
Conn. open ();
Try
{
Command. executenonquery ();
Return true;
}
Catch (system. Exception ex)
{
Return false;
}
Finally
{
Command. Connection. Close ();
}
}
}

///

/// Execution method of the SQL statement executenonquery without Parameters
/// Specify a dedicated connection string to execute a sqlcommand that does not require a return value.
/// Provide the parameter list in the form of parameter Arrays
///

///
/// Example:
/// Int result = executenonquery (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns a value indicating the number of rows affected by the execution of the sqlcommand.
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 ))
{
// 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 ();
Return val;
}
}

///

/// Execute a sqlcommand that does not return results and connect to an existing database
/// Provide parameters using the parameter Array
///

///
/// Example:
/// Int result = executenonquery (Conn, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns a value indicating the number of rows affected by the execution of the sqlcommand.
Public static int executenonquery (sqlconnection connection, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{

Sqlcommand cmd = new sqlcommand ();

Preparecommand (CMD, connection, null, argument type, plain text, commandparameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}

///

/// Execute a sqlcommand that does not return the result and process it through an existing database transaction
/// Provide parameters using the parameter Array
///

///
/// Example:
// Int result = executenonquery (trans, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns a value indicating the number of rows affected by the execution of the sqlcommand.
Public static int executenonquery (sqltransaction trans, commandtype primitive type, string plain text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Preparecommand (CMD, trans. Connection, trans, argument type, plain text, commandparameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}

///

/// Execute a sqlcommand to return the result set and use a dedicated connection string.
/// Provide parameters using the parameter Array
///

///
/// Example:
/// Sqldatareader r = executereader (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns a sqldatareader containing the result.
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;
}
}

///

/// Execute a sqlcommand that returns the first record column and uses a dedicated connection string.
/// Provide parameters using the parameter Array
///

///
/// Example:
/// Object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns data of the object type, which can be converted using the convert. To {type} method.
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, argument type, plain text, commandparameters );
Object val = cmd. executescalar ();
Cmd. Parameters. Clear ();
Return val;
}
}

///

/// Execute a sqlcommand that returns the first column of record and uses an existing database connection.
/// Provide parameters using the parameter Array
///

///
/// Example:
/// Object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
///
/////////////// Returns data of the object type, which can be converted using the convert. To {type} method.
Public static object executescalar (sqlconnection connection, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{

Sqlcommand cmd = new sqlcommand ();

Preparecommand (CMD, connection, null, argument type, plain text, commandparameters );
Object val = cmd. executescalar ();
Cmd. Parameters. Clear ();
Return val;
}

///

/// Cache parameter Array
///

//////Public static void cacheparameters (string cachekey, Params sqlparameter [] commandparameters)
{
Parmcache [cachekey] = commandparameters;
}

///

/// Obtain the cached Parameters
///

////// Returned cached parameter Array
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
Clonedparms [I] = (sqlparameter) (icloneable) cachedparms [I]). Clone ();

Return clonedparms;
}

///

/// Prepare command parameters for executing commands
///

//////////////////Private Static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction trans, commandtype primitive type, string plain text, sqlparameter [] partial parms)
{

// 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 );
}
}
} Return page header

In the above Code, most of them are annotated. In addition to processing references to namespaces, using defines a range, when the statement is completed, the resources used in the statement are automatically released. The preparecommand method is used to add parameter data.