SQLHelper. cs
Copy codeThe Code is as follows: using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Collections;
Using System. Data;
Using System. Data. SqlClient;
Using System. Configuration;
Namespace HelloWinForm. DBUtility
{
Class SQLHelper
{
# Region General Method
// Data connection pool
Private SqlConnection con;
/// <Summary>
/// Return the database connection string
/// </Summary>
/// <Returns> </returns>
Public static String GetSqlConnection ()
{
String conn = ConfigurationManager. etettings ["connectionString"]. ToString ();
Return conn;
}
# Endregion
# Region execute an SQL string
/// <Summary>
/// Execute an SQL statement without Parameters
/// </Summary>
/// <Param name = "Sqlstr"> </param>
/// <Returns> </returns>
Public static int ExecuteSql (String Sqlstr)
{
String ConnStr = GetSqlConnection ();
Using (SqlConnection conn = new SqlConnection (ConnStr ))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. CommandText = Sqlstr;
Conn. Open ();
Cmd. ExecuteNonQuery ();
Conn. Close ();
Return 1;
}
}
/// <Summary>
/// Execute an SQL statement with Parameters
/// </Summary>
/// <Param name = "Sqlstr"> SQL statement </param>
/// <Param name = "param"> parameter object array </param>
/// <Returns> </returns>
Public static int ExecuteSql (String Sqlstr, SqlParameter [] param)
{
String ConnStr = GetSqlConnection ();
Using (SqlConnection conn = new SqlConnection (ConnStr ))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. CommandText = Sqlstr;
Cmd. Parameters. AddRange (param );
Conn. Open ();
Cmd. ExecuteNonQuery ();
Conn. Close ();
Return 1;
}
}
/// <Summary>
/// Return DataReader
/// </Summary>
/// <Param name = "Sqlstr"> </param>
/// <Returns> </returns>
Public static SqlDataReader ExecuteReader (String Sqlstr)
{
String ConnStr = GetSqlConnection ();
SqlConnection conn = new SqlConnection (ConnStr); // using () is not allowed when DataReader is returned
Try
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd. CommandText = Sqlstr;
Conn. Open ();
Return cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection); // close the associated Connection
}
Catch // (Exception ex)
{
Return null;
}
}
/// <Summary>
/// Execute the SQL statement and return the data table
/// </Summary>
/// <Param name = "Sqlstr"> SQL statement </param>
/// <Returns> </returns>
Public static DataTable ExecuteDt (String Sqlstr)
{
String ConnStr = GetSqlConnection ();
Using (SqlConnection conn = new SqlConnection (ConnStr ))
{
SqlDataAdapter da = new SqlDataAdapter (Sqlstr, conn );
DataTable dt = new DataTable ();
Conn. Open ();
Da. Fill (dt );
Conn. Close ();
Return dt;
}
}
/// <Summary>
/// Execute the SQL statement and return the DataSet
/// </Summary>
/// <Param name = "Sqlstr"> SQL statement </param>
/// <Returns> </returns>
Public static DataSet ExecuteDs (String Sqlstr)
{
String ConnStr = GetSqlConnection ();
Using (SqlConnection conn = new SqlConnection (ConnStr ))
{
SqlDataAdapter da = new SqlDataAdapter (Sqlstr, conn );
DataSet ds = new DataSet ();
Conn. Open ();
Da. Fill (ds );
Conn. Close ();
Return ds;
}
}
# Endregion
# Region Operation Stored Procedure
/// <Summary>
/// Run the Stored Procedure (reloaded)
/// </Summary>
/// <Param name = "procName"> name of the stored procedure </param>
/// <Returns> return value of the stored procedure </returns>
Public int RunProc (string procName)
{
SqlCommand cmd = CreateCommand (procName, null );
Cmd. ExecuteNonQuery ();
This. Close ();
Return (int) cmd. Parameters ["ReturnValue"]. Value;
}
/// <Summary>
/// Run the Stored Procedure (reloaded)
/// </Summary>
/// <Param name = "procName"> name of the stored procedure </param>
/// <Param name = "prams"> List of input parameters of the stored procedure </param>
/// <Returns> return value of the stored procedure </returns>
Public int RunProc (string procName, SqlParameter [] prams)
{
SqlCommand cmd = CreateCommand (procName, prams );
Cmd. ExecuteNonQuery ();
This. Close ();
Return (int) cmd. Parameters [0]. Value;
}
/// <Summary>
/// Run the Stored Procedure (reloaded)
/// </Summary>
/// <Param name = "procName"> name of the stored procedure </param>
/// <Param name = "dataReader"> result set </param>
Public void RunProc (string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand (procName, null );
DataReader = cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
}
/// <Summary>
/// Run the Stored Procedure (reloaded)
/// </Summary>
/// <Param name = "procName"> name of the stored procedure </param>
/// <Param name = "prams"> List of input parameters of the stored procedure </param>
/// <Param name = "dataReader"> result set </param>
Public void RunProc (string procName, SqlParameter [] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand (procName, prams );
DataReader = cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
}
/// <Summary>
/// Create a Command object for accessing the Stored Procedure
/// </Summary>
/// <Param name = "procName"> name of the stored procedure </param>
/// <Param name = "prams"> List of input parameters of the stored procedure </param>
/// <Returns> Command object </returns>
Private SqlCommand CreateCommand (string procName, SqlParameter [] prams)
{
// Confirm that the connection is enabled
Open ();
// Command = new SqlCommand (sprocName, new SqlConnection (ConfigManager. DALConnectionString ));
SqlCommand cmd = new SqlCommand (procName, con );
Cmd. CommandType = CommandType. StoredProcedure;
// List of input parameters for adding a stored procedure
If (prams! = Null)
{
Foreach (SqlParameter parameter in prams)
Cmd. Parameters. Add (parameter );
}
// Return the Command object
Return cmd;
}
/// <Summary>
/// Create input parameters
/// </Summary>
/// <Param name = "ParamName"> parameter name </param>
/// <Param name = "DbType"> parameter type </param>
/// <Param name = "Size"> parameter Size </param>
/// <Param name = "Value"> parameter Value </param>
/// <Returns> New Parameter object </returns>
Public SqlParameter MakeInParam (string ParamName, SqlDbType DbType, int Size, object Value)
{
Return MakeParam (ParamName, DbType, Size, ParameterDirection. Input, Value );
}
/// <Summary>
/// Create output parameters
/// </Summary>
/// <Param name = "ParamName"> parameter name </param>
/// <Param name = "DbType"> parameter type </param>
/// <Param name = "Size"> parameter Size </param>
/// <Returns> New Parameter object </returns>
Public SqlParameter MakeOutParam (string ParamName, SqlDbType DbType, int Size)
{
Return MakeParam (ParamName, DbType, Size, ParameterDirection. Output, null );
}
/// <Summary>
/// Create stored procedure parameters
/// </Summary>
/// <Param name = "ParamName"> parameter name </param>
/// <Param name = "DbType"> parameter type </param>
/// <Param name = "Size"> parameter Size </param>
/// <Param name = "Direction"> parameter Direction (input/output) </param>
/// <Param name = "Value"> parameter Value </param>
/// <Returns> New Parameter object </returns>
Public SqlParameter MakeParam (string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
If (Size> 0)
{
Param = new SqlParameter (ParamName, DbType, Size );
}
Else
{
Param = new SqlParameter (ParamName, DbType );
}
Param. Direction = Direction;
If (! (Direction = ParameterDirection. Output & Value = null ))
{
Param. Value = Value;
}
Return param;
}
# Endregion
# Region database connection and Shutdown
/// <Summary>
/// Open the connection pool
/// </Summary>
Private void Open ()
{
// Open the connection pool
If (con = null)
{
// Not only using System. Configuration is required, but also the reference directory.
Con = new SqlConnection (GetSqlConnection ());
Con. Open ();
}
}
/// <Summary>
/// Close the connection pool
/// </Summary>
Public void Close ()
{
If (con! = Null)
Con. Close ();
}
/// <Summary>
/// Release the connection pool
/// </Summary>
Public void Dispose ()
{
// Confirm that the connection is closed
If (con! = Null)
{
Con. Dispose ();
Con = null;
}
}
# Endregion
}
}
Easy to use:Copy codeThe Code is as follows: using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Data;
Using System. Data. SqlClient;
Using System. Collections;
Using HelloWinForm. DBUtility;
Namespace HelloWinForm. DAL
{
Class Student
{
Public string test ()
{
String str = "";
SqlDataReader dr = SQLHelper. ExecuteReader ("select * from Student ");
While (dr. Read ())
{
Str + = dr ["StudentNO"]. ToString ();
}
Dr. Close ();
Return str;
}
}
}
