Asp.net (C #) layer-based Class

Using System;
Using System. Collections;
Using System. Collections. Specialized;
Using System. Data;
Using System. Data. SqlClient;
Using System. Configuration;

Namespace LiTianPing. SQLServerDAL // you can change it to The namespace name of the actual project.
{
/// <Summary>
/// Copyright (C) 2004-2008 LiTianPing
/// Data Access Base Class (based on SQLServer)
/// You can modify the settings to meet your project requirements.
/// </Summary>
Public abstract class DbHelperSQL
{
// Database connection string (configured in web. config)
// <Add key = "ConnectionString" value = "server = 127.0.0.1; database = DATABASE; uid = sa; pwd ="/>
Protected static string connectionString = ConfigurationSettings. etettings ["ConnectionString"];
Public DbHelperSQL ()
{
}

# Region execute simple SQL statements

/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteSql (string SQLString)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
Using (SqlCommand cmd = new SqlCommand (SQLString, connection ))
{
Try
{
Connection. Open ();
Int rows = cmd. ExecuteNonQuery ();
Return rows;
}
Catch (System. Data. SqlClient. SqlException E)
{
Connection. Close ();
Throw new Exception (E. Message );
}
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
Public static void ExecuteSqlTran (ArrayList SQLStringList)
{
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
SqlTransaction tx = conn. BeginTransaction ();
Cmd. Transaction = tx;
Try
{
For (int n = 0; n <SQLStringList. Count; n ++)
{
String strsql = SQLStringList [n]. ToString ();
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
Cmd. ExecuteNonQuery ();
}
}
Tx. Commit ();
}
Catch (System. Data. SqlClient. SqlException E)
{
Tx. Rollback ();
Throw new Exception (E. Message );
}
}
}
/// <Summary>
/// Execute the SQL statement with a stored procedure parameter.
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Param name = "content"> parameter content. For example, if a field is in a complex format and has a special symbol, you can add it in this way. </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteSql (string SQLString, string content)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
SqlCommand cmd = new SqlCommand (SQLString, connection );
System. Data. SqlClient. SqlParameter myParameter = new System. Data. SqlClient. SqlParameter ("@ content", SqlDbType. NText );
MyParameter. Value = content;
Cmd. Parameters. Add (myParameter );
Try
{
Connection. Open ();
Int rows = cmd. ExecuteNonQuery ();
Return rows;
}
Catch (System. Data. SqlClient. SqlException E)
{
Throw new Exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Insert an image format field into the database (another example similar to the above)
/// </Summary>
/// <Param name = "strSQL"> SQL statement </param>
/// <Param name = "fs"> image byte, where the database field type is image </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteSqlInsertImg (string strSQL, byte [] fs)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
SqlCommand cmd = new SqlCommand (strSQL, connection );
System. Data. SqlClient. SqlParameter myParameter = new System. Data. SqlClient. SqlParameter ("@ fs", SqlDbType. Image );
MyParameter. Value = fs;
Cmd. Parameters. Add (myParameter );
Try
{
Connection. Open ();
Int rows = cmd. ExecuteNonQuery ();
Return rows;
}
Catch (System. Data. SqlClient. SqlException E)
{
Throw new Exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}

/// <Summary>
/// Execute a query result statement and return the query result (object ).
/// </Summary>
/// <Param name = "SQLString"> calculate the Query Result Statement </param>
/// <Returns> query result (object) </returns>
Public static object GetSingle (string SQLString)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
Using (SqlCommand cmd = new SqlCommand (SQLString, connection ))
{
Try
{
Connection. Open ();
Object obj = cmd. ExecuteScalar ();
If (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
{
Return null;
}
Else
{
Return obj;
}
}
Catch (System. Data. SqlClient. SqlException e)
{
Connection. Close ();
Throw new Exception (e. Message );
}
}
}
}
/// <Summary>
/// Execute the query statement and return SqlDataReader
/// </Summary>
/// <Param name = "strSQL"> query statement </param>
/// <Returns> SqlDataReader </returns>
Public static SqlDataReader ExecuteReader (string strSQL)
{
SqlConnection connection = new SqlConnection (connectionString );
SqlCommand cmd = new SqlCommand (strSQL, connection );
Try
{
Connection. Open ();
SqlDataReader myReader = cmd. ExecuteReader ();
Return myReader;
}
Catch (System. Data. SqlClient. SqlException e)
{
Throw new Exception (e. Message );
}

}
/// <Summary>
/// Execute the query statement and return DataSet
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Returns> DataSet </returns>
Public static DataSet Query (string SQLString)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
DataSet ds = new DataSet ();
Try
{
Connection. Open ();
SqlDataAdapter command = new SqlDataAdapter (SQLString, connection );
Command. Fill (ds, "ds ");
}
Catch (System. Data. SqlClient. SqlException ex)
{
Throw new Exception (ex. Message );
}
Return ds;
}
}

# Endregion

# Region Execute SQL statements with Parameters

/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteSql (string SQLString, params SqlParameter [] extends parms)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
Using (SqlCommand cmd = new SqlCommand ())
{
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Int rows = cmd. ExecuteNonQuery ();
Cmd. Parameters. Clear ();
Return rows;
}
Catch (System. Data. SqlClient. SqlException E)
{
Throw new Exception (E. Message );
}
}
}
}


/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "SQLStringList"> hash table of an SQL statement (key is an SQL statement, and value is the SqlParameter [] of this statement) </param>
Public static void ExecuteSqlTran (Hashtable SQLStringList)
{
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
Conn. Open ();
Using (SqlTransaction trans = conn. BeginTransaction ())
{
SqlCommand cmd = new SqlCommand ();
Try
{
// Loop
Foreach (DictionaryEntry myDE in SQLStringList)
{
String plain text = myDE. Key. ToString ();
SqlParameter [] parameter parms = (SqlParameter []) myDE. Value;
PrepareCommand (cmd, conn, trans, plain text, plain parms );
Int val = cmd. ExecuteNonQuery ();
Cmd. Parameters. Clear ();

Trans. Commit ();
}
}
Catch
{
Trans. Rollback ();
Throw;
}
}
}
}
 

/// <Summary>
/// Execute a query result statement and return the query result (object ).
/// </Summary>
/// <Param name = "SQLString"> calculate the Query Result Statement </param>
/// <Returns> query result (object) </returns>
Public static object GetSingle (string SQLString, params SqlParameter [] partition parms)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
Using (SqlCommand cmd = new SqlCommand ())
{
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Object obj = cmd. ExecuteScalar ();
Cmd. Parameters. Clear ();
If (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
{
Return null;
}
Else
{
Return obj;
}
}
Catch (System. Data. SqlClient. SqlException e)
{
Throw new Exception (e. Message );
}
}
}
}

/// <Summary>
/// Execute the query statement and return SqlDataReader
/// </Summary>
/// <Param name = "strSQL"> query statement </param>
/// <Returns> SqlDataReader </returns>
Public static SqlDataReader ExecuteReader (string SQLString, params SqlParameter [] extends parms)
{
SqlConnection connection = new SqlConnection (connectionString );
SqlCommand cmd = new SqlCommand ();
Try
{
PrepareCommand (cmd, connection, null, SQLString, callback parms );
SqlDataReader myReader = cmd. ExecuteReader ();
Cmd. Parameters. Clear ();
Return myReader;
}
Catch (System. Data. SqlClient. SqlException e)
{
Throw new Exception (e. Message );
}

}

/// <Summary>
/// Execute the query statement and return DataSet
/// </Summary>
/// <Param name = "SQLString"> query statement </param>
/// <Returns> DataSet </returns>
Public static DataSet Query (string SQLString, params SqlParameter [] partition parms)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (cmd, connection, null, SQLString, callback parms );
Using (SqlDataAdapter da = new SqlDataAdapter (cmd ))
{
DataSet ds = new DataSet ();
Try
{
Da. Fill (ds, "ds ");
Cmd. Parameters. Clear ();
}
Catch (System. Data. SqlClient. SqlException ex)
{
Throw new Exception (ex. Message );
}
Return ds;
}
}
}

Private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string plain text, SqlParameter [] partial parms)
{
If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd. CommandText = plain text;
If (trans! = Null)
Cmd. Transaction = trans;
Cmd. CommandType = CommandType. Text; // specify type;
If (partition parms! = Null)
{
Foreach (SqlParameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );
}
}

# Endregion

# Region Stored Procedure operations

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Returns> SqlDataReader </returns>
Public static SqlDataReader RunProcedure (string storedProcName, IDataParameter [] parameters)
{
SqlConnection connection = new SqlConnection (connectionString );
SqlDataReader returnReader;
Connection. Open ();
SqlCommand command = BuildQueryCommand (connection, storedProcName, parameters );
Command. CommandType = CommandType. StoredProcedure;
ReturnReader = command. ExecuteReader ();
Return returnReader;
}


/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Param name = "tableName"> name of the table in the DataSet result </param>
/// <Returns> DataSet </returns>
Public static DataSet RunProcedure (string storedProcName, IDataParameter [] parameters, string tableName)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
DataSet dataSet = new DataSet ();
Connection. Open ();
SqlDataAdapter sqlDA = new SqlDataAdapter ();
SqlDA. SelectCommand = BuildQueryCommand (connection, storedProcName, parameters );
SqlDA. Fill (dataSet, tableName );
Connection. Close ();
Return dataSet;
}
}

/// <Summary>
/// Construct a SqlCommand object (used to return a result set instead of an integer)
/// </Summary>
/// <Param name = "connection"> database connection </param>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Returns> SqlCommand </returns>
Private static SqlCommand BuildQueryCommand (SqlConnection connection, string storedProcName, IDataParameter [] parameters)
{
SqlCommand command = new SqlCommand (storedProcName, connection );
Command. CommandType = CommandType. StoredProcedure;
Foreach (SqlParameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}
Return command;
}

/// <Summary>
/// Execute the stored procedure and return the number of affected rows
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Param name = "rowsAffected"> Number of affected rows </param>
/// <Returns> </returns>
Public static int RunProcedure (string storedProcName, IDataParameter [] parameters, out int rowsAffected)
{
Using (SqlConnection connection = new SqlConnection (connectionString ))
{
Int result;
Connection. Open ();
SqlCommand command = BuildIntCommand (connection, storedProcName, parameters );
RowsAffected = command. ExecuteNonQuery ();
Result = (int) command. Parameters ["ReturnValue"]. Value;
// Connection. Close ();
Return result;
}
}

/// <Summary>
/// Create a SqlCommand object instance (used to return an integer)
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Returns> SqlCommand object instance </returns>
Private static SqlCommand BuildIntCommand (SqlConnection connection, string storedProcName, IDataParameter [] parameters)
{
SqlCommand command = BuildQueryCommand (connection, storedProcName, parameters );
Command. Parameters. Add (new SqlParameter ("ReturnValue ",
SqlDbType. Int, 4, ParameterDirection. ReturnValue,
False, 0, 0, string. Empty, DataRowVersion. Default, null ));
Return command;
}
# Endregion

}
}