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. For more information, see my website.