public class Sqloperation
{
#region Properties
<summary>
The connection string saved in the web.config
</summary>
protected static string connectionstring = system.configuration.configurationmanager.connectionstrings["Hao". ConnectionString;
<summary>
SqlConnection objects
</summary>
protected static SqlConnection conn = new SqlConnection ();
<summary>
SqlCommand objects
</summary>
protected static SqlCommand comm = new SqlCommand ();
#endregion
#region Internal functions
<summary>
Open a database connection
</summary>
private static void ConnectionOpen ()
{
IF (Conn. State!= ConnectionState.Open)
{
Conn. Close ();
Conn. ConnectionString = ConnectionString;
Comm. Connection = conn;
Try
{
Conn. Open ();
}
catch (Exception ex)
{
throw new Exception (ex. message);
}
}
}
<summary>
To close a database connection
</summary>
private static void Connectionclose ()
{
Conn. Close ();
Conn. Dispose ();
Comm. Dispose ();
}
#endregion
<summary>
Execute SQL statement
</summary>
<param name= "SqlString" > SQL statement to execute </param>
public static void ExecuteSQL (String sqlstring)
{
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.Text;
Comm.commandtext = SqlString;
Comm. ExecuteNonQuery ();
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
}
<summary>
Executing stored procedures
</summary>
<param name= "procedurename" > Stored procedure name </param>
<param name= "Coll" > Stored procedures required parameters set </param>
public static void Executeprocedure (String procedurename, params sqlparameter[] coll)
{
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = procedurename;
Comm. Parameters.clear ();
for (int i = 0; i < Coll. Length; i++)
{
Comm. Parameters.Add (Coll[i]);
}
Comm. ExecuteNonQuery ();
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
}
<summary>
Executes the SQL query and returns the first record of the first row, returning object, which needs to be disassembled when used-> unbox
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns> returns the first line of type Object first record </returns>
public static Object ExecuteScalar (String sqlstring)
{
Object obj = new Object ();
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.Text;
Comm.commandtext = SqlString;
obj = Comm. ExecuteScalar ();
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return obj;
}
<summary>
Execute the SQL statement while transaction processing
</summary>
<param name= "SQLSTR" > SQL statement to execute </param>
public static void Executetransactionsql (String sqlstring)
{
SqlTransaction Trans;
trans = conn. BeginTransaction ();
Comm. Transaction = trans;
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.Text;
Comm.commandtext = SqlString;
Comm. ExecuteNonQuery ();
Trans.commit ();
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
}
<summary>
Executes the specified SQL query, returning the dataset
</summary>
<param name= "SQLSTR" > SQL statement to execute </param>
<returns>DataSet</returns>
public static DataSet Getdatasetbysql (string sqlstring)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.Text;
Comm.commandtext = SqlString;
Da. SelectCommand = comm;
Da. Fill (DS);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DS;
}
<summary>
Returning a dataset through a stored procedure
</summary>
<param name= "procedurename" > Stored procedure name </param>
<param name= "Coll" >sqlparameter collection </param>
<returns>DataSet</returns>
public static DataSet Getdatasetbyprocedure (string procedurename, params sqlparameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.StoredProcedure;
Comm. Parameters.clear ();
for (int i = 0; i < Coll. Length; i++)
{
Comm. Parameters.Add (Coll[i]);
}
Comm.commandtext = procedurename;
Da. SelectCommand = comm;
Da. Fill (DS);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DS;
}
<summary>
Returning a dataset through a stored procedure
</summary>
<param name= "procedurename" > Stored procedure name </param>
<returns>DataSet</returns>
public static DataSet Getdatasetbyprocedure (String procedurename)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = procedurename;
Comm. Parameters.clear ();
Da. SelectCommand = comm;
Da. Fill (DS);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DS;
}
<summary>
Returns a datatable of the specified SQL statement
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns>DataTable</returns>
public static DataTable Getdatatablebysql (string sqlstring)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm.commandtype = CommandType.Text;
Comm.commandtext = SqlString;
Da. SelectCommand = comm;
Da. Fill (DT);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DT;
}
<summary>
Returning a DataTable based on a stored procedure
</summary>
<param name= "procedurename" > Stored procedure name </param>
<param name= "Coll" >sqlparameter collection </param>
<returns>DataTable</returns>
public static DataTable Getdatatablebyprocedure (string procedurename, params sqlparameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm. Parameters.clear ();
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = procedurename;
for (int i = 0; i < Coll. Length; i++)
{
Comm. Parameters.Add (Coll[i]);
}
Da. SelectCommand = comm;
Da. Fill (DT);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DT;
}
<summary>
Returning a DataTable based on a stored procedure
</summary>
<param name= "procedurename" > Stored procedure name </param>
<returns>DataTable</returns>
public static DataTable Getdatatablebyprocedure (String procedurename)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm. Parameters.clear ();
Comm.commandtype = CommandType.StoredProcedure;
Comm.commandtext = procedurename;
Da. SelectCommand = comm;
Da. Fill (DT);
}
catch (Exception ex)
{
Try
{
Connectionclose ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
throw new Exception (ex. message);
}
Finally
{
Connectionclose ();
}
return DT;
}
}
