Custom public class (tested through) that you often use _ Practical tips

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Configuration;
Using System.IO;
/*
* Author:zhanglei
* CREATE date:2007.1.5
* Function: BLL layer, realize the encapsulation of database operation
* and provides enough parameter combinations to execute stored procedures
* DESCRIPTION: Method overloads are used in this class
* The ExecuteDataset method implements four overloads in this class
* */
Namespace Job_17
{
<summary>
Summary description of the JOB17.
</summary>
public class Job17
{
Private readonly string p_con=configurationsettings.appsettings["P_con"]. ToString ();
Public Job17 ()
{
//
TODO: Add constructor logic here
//
}
#region "Execute stored procedures with any combination of parameters"
<summary>
Command ready to operate, providing a sufficient number of parameter combinations
One of the more important methods in this class
</summary>
public void PrepareCommand (SqlConnection myconn,sqlcommand mycomm,sqltransaction trans,commandtype cmdType,string cmdtext,sqlparameter[] param)//Note the parameters inside
{
if (myconn. State!=connectionstate.open)
{
MyConn. Open ();
}
Mycomm. Connection=myconn;
Mycomm.commandtext=cmdtext;
if (trans!=null)
{
Mycomm. Transaction=trans;
}
Mycomm.commandtype=cmdtype;
if (param!=null)
{
foreach (SqlParameter parameter in param)
{
Mycomm. Parameters.Add (parameter);
}
}
}
<summary>
The first ExecuteDataset method that returns the dataset type
</summary>
Public DataSet ExecuteDataset (SqlConnection myconn,commandtype cmdtype,string cmdtext,sqlparameter[] Commandpara)
{
SqlCommand mycomm=new SqlCommand ();
PrepareCommand (Myconn,mycomm, (sqltransaction) Null,cmdtype,cmdtext,commandpara);
SqlDataAdapter Adpt=new SqlDataAdapter (Mycomm); Call the PrepareCommand method above
DataSet ds=new DataSet ();
Adpt. Fill (DS);
Mycomm. Parameters.clear ();
return DS;
}
<summary>
The second ExecuteDataset method that returns the dataset type
is implemented on the basis of the first method, and realizes the overload of the ExecuteDataset method.
</summary>
The public DataSet executedataset (string Connstr,commandtype cmdtype,string cmdtext,sqlparameter[] Cmdpara)/method overload, Overload on the first basis to provide a sufficient number of parameter combinations
{
Using (SqlConnection myconn=new SqlConnection (ConnStr))
{
Return ExecuteDataset (Myconn,cmdtype,cmdtext,cmdpara);
}
}
<summary>
The third ExecuteDataset method that returns the dataset type
Provides a combination with no parameters when using stored procedures
</summary>
Public DataSet ExecuteDataset (SqlConnection myconn,commandtype cmdtype,string cmdtext)
{
Return ExecuteDataset (Myconn,cmdtype,cmdtext, (sqlparameter[]) null);
}
<summary>
Fourth ExecuteDataset method for returning a dataset type
Provides a combination with no parameters when using stored procedures
</summary>
Public DataSet ExecuteDataset (string connstr,commandtype cmdtype,string cmdtext)
{
Return ExecuteDataset (Connstr,cmdtype,cmdtext, (sqlparameter[]) null);
}
#endregion
#region "Execute SQL statement that returns results"
<summary>
Returns the type of the result as a DataTable
</summary>
Public DataTable Executedatatablesql (String sql)
{
SqlConnection myconn=new SqlConnection (P_con);
SqlDataAdapter Adpt=new SqlDataAdapter (Sql,myconn);
DataSet ds=new DataSet ();
Adpt. Fill (DS);
Return DS. Tables[0];
}
<summary>
Returns the type of the result is SqlDataReader
</summary>
Public SqlDataReader Executedatareadersql (String sql)
{
SqlConnection myconn=new SqlConnection (P_con);
SqlDataReader Dr=null;
SqlCommand mycomm=new SqlCommand (sql,myconn);
Try
{
MyConn. Open ();
Dr=mycomm. ExecuteReader ();
}
Catch
{
StreamWriter Sw=new StreamWriter (@ "C:\err.txt", True,system.text.encoding.getencoding ("GB2312"));
Sw. WriteLine ("============================ error message ==========================");
Sw. WriteLine ("Error Time:" +datetime.now.tostring () + "");
Sw. WriteLine (ex. ToString ());
Sw. Close ();
Throw
}
Return Dr;
}
<summary>
Returns the type of the result as a dataset
</summary>
Public DataSet Executesqlds (String sql)
{
SqlConnection myconn=new SqlConnection (P_con);
SqlDataAdapter Adpt=new SqlDataAdapter (Sql,myconn);
DataSet ds=new DataSet ();
Adpt. Fill (DS);
return DS;
}
#endregion
#region "Execute an SQL statement that does not return results"
<summary>
Execute an SQL statement that does not return results
</summary>
public void Executenonsql (String sql)
{
SqlConnection myconn=new SqlConnection (P_con);
SqlCommand mycomm=new SqlCommand (sql,myconn);
Try
{
MyConn. Open ();
Mycomm. ExecuteNonQuery ();
MyConn. Close ();
}
catch (Exception e)
{
StreamWriter Sw=new StreamWriter (@ "C:\err.txt", True,system.text.encoding.getencoding ("GB2312"));
Sw. WriteLine ("============================ error message ==========================");
Sw. WriteLine ("Error Time:" +datetime.now.tostring () + "");
Sw. WriteLine (E.tostring ());
Sw. Close ();
throw new Exception (e.message,e);
}
}
#endregion
#region "Enable SQL statements with transactions such as (Insert,update)"
<summary>
Working with transactions
</summary>
public void Executetransql (String sql)
{
SqlConnection myconn=new SqlConnection (P_con);
SqlCommand mycomm=new SqlCommand (sql,myconn);
SqlTransaction Trans=null;
Try
{
MyConn. Open ();
Trans=myconn. BeginTransaction ();
Mycomm. Transaction=trans;
Mycomm. ExecuteNonQuery ();
Trans.commit ();
}
catch (Exception ex)
{
Trans. Rollback ();
throw new Exception (ex. MESSAGE,EX);
}
}
#endregion
}
}