Database connection Generic DBHelper class

Using System;
Using System.Collections;
Using System.Collections.Generic;
Using System.Data;
Using System.Data.SqlClient;
Using System.Linq;
Using System.Text;
Using System.Threading.Tasks;
Using Log4net;
Using System.Configuration;

Namespace Ch_common
{
public class DBHelper
{
#region member variables
String connectionString;
SqlConnection connection;
SqlTransaction transaction;
Hashtable listparameters;
Private ILog log = Logmanager.getlogger (System.Reflection.MethodBase.GetCurrentMethod (). DeclaringType);
#endregion

#region Construction Method
<summary>
Default constructor method
</summary>
Public DBHelper ()
{
this.connectionstring = configurationmanager.connectionstrings["connectionString"]. ConnectionString;
Connection = new SqlConnection (connectionString);
}

<summary>
Method of construction with parameters
</summary>
<param name= "connectionString" > Connection string </param>
Public DBHelper (String connectionString)
{
this.connectionstring = connectionString;
Connection = new SqlConnection (connectionString);
}
#endregion

#region Open, close, release of database connections
<summary>
Open a database connection
</summary>
public void Open ()
{
if (connection. state = = connectionstate.closed)
{
Connection. Open ();
}
}
<summary>
To close a database connection
</summary>
public void Close ()
{
if (connection. state.tostring () = = "Open")
{
Connection. Close ();
Initmember ();
}
}
<summary>
Parameter initialization
</summary>
void Initmember ()
{
Listparameters = null;
cmd = null;
}

<summary>
Release connection
</summary>
public void Dispose ()
{
if (connection! = NULL)
{
Connection. Close ();
Connection. Dispose ();
}
Gc. Collect ();
}
#endregion

#region Setting the connection string
<summary>
Setting the connection string
</summary>
<param name= "strConnectionString" > Connection string </param>
public void SetConnection (string strconnectionstring)
{
connectionString = strConnectionString;
Connection = new SqlConnection (connectionString);
}
#endregion

#region Command Generation and Setup
<summary>
Set command
</summary>
<param name= "SQL" >sql </param>
<param name= "Cmdtype" >CommandType</param>
Private SqlCommand Setcmd (string SQL, CommandType cmdtype)
{
Open ();
SqlCommand cmd = new SqlCommand (SQL, connection);
if (null! = Transaction)
{
Cmd. Transaction = Transaction;
}
Cmd.commandtype = Cmdtype;
Cmd.commandtext = SQL;
Cmd. Parameters.clear ();
String strsqllog = "";
if (listparameters! = null)
{
foreach (dictionaryentry parameter in listparameters)
{
Cmd. Parameters.Add (new SqlParameter (parameter. Key.tostring (), parameter. Value));
Strsqllog = Strsqllog + "[" + parameter. Key.tostring () + "]=[" + parameter. Value.tostring () + "]";
}
Log. Info (Strsqllog);
Clearparameter ();
}
return cmd;
}
<summary>
Generate Commond
</summary>
<param name= "SQL" ></param>
<returns></returns>
Private SqlCommand Createcmd (string SQL)
{
Return Setcmd (SQL, CommandType.Text);
}

Private SqlCommand Createproc (string procname)
{
Return Setcmd (procname, CommandType.StoredProcedure);
}

#endregion

#region SqlDataReader return
<summary>
SqlDataReader return
</summary>
<param name= "SQL" >sql </param>
<returns>SqlDataReader</returns>
Private SqlDataReader Returndatareader (string SQL)
{
Return Createcmd (SQL). ExecuteReader ();
}
#endregion

#region SqlParameter parameter Append
<summary>
SqlParameter parameter Append
</summary>
<param name= "paramname" > Parameter name </param>
<param name= "value" > Parameter value </param>
public void Addparameter (string paramname, Object Value)
{
Try
{
if (listparameters = = null)
{
Listparameters = new Hashtable ();
}
Listparameters.add (ParamName, Value);
}
catch (Exception ex)
{
Log. Error ("Add SqlParameter Error:" + ex.) Message);
Log. Error (ex);
Throw ex;
}
}
#endregion

#region SqlParameter Empty
<summary>
SqlParameter Empty
</summary>
public void Clearparameter () {listparameters = null;}
#endregion

#region SqlDataAdapter Adapter returns
<summary>
SqlDataAdapter Adapter Return
</summary>
<param name= "SQL" >sql statements </param>
<returns>sqldataadapter Object </returns>
Private SqlDataAdapter Getdataadapter (string SQL)
{
SqlDataAdapter Da = new SqlDataAdapter ();

Da.selectcommand = Setcmd (SQL, CommandType.Text);
SqlCommandBuilder CUSTCB = new SqlCommandBuilder (Da);
return Da;
}
#endregion

#region SQL execution

<summary>
SQL execution
</summary>
<param name= "SQL" >sql statements </param>
<returns>-1: Failure Other: Success </returns>
public int Execmd (string SQL)
{
int ret =-1;

String Strsqllog = String. Empty;
String sqllog = "";
if (listparameters! = null)
{
Strsqllog = SQL;
foreach (dictionaryentry parameter in listparameters)
{
Strsqllog = Strsqllog.replace ("@" + parameter. Key.tostring (), "'" + parameter. Value.tostring () + "'");
Sqllog = Sqllog + "[" + parameter. Key.tostring () + "]=[" + parameter. Value.tostring () + "]";
}
}

Try
{
Log. Info ("SQL Execute Start:" + SQL + "" + Sqllog);
ret = Createcmd (SQL). ExecuteNonQuery ();
Log. Info ("SQL Execute End");
}
catch (Exception ex)
{
Log. Error ("SQL Execute Error occured:" + SQL + "" + Sqllog);
Log. Error ("SQL Execute errormessage:" + ex.) Message);
Log. Error (ex);
RollbackTransaction ();
Throw ex;
}
Finally
{
if (null = = Transaction)
{
Close ();
}

Clearparameter ();
}
return ret;
}
#endregion

#region DataSet returns

//<summary>
//SQL Execute
//</summary>
//<param name= "SQL" >sql text </param>
// /<returns>DataSet</returns>
Public DataSet Returndataset (string SQL)
{
DataSet Ds = new DataSet ( );
Try
{
Log. Info ("SQL Execute Start:" + sql);
SqlDataAdapter Da = Getdataadapter (SQL);
Da.fill (Ds);
Log. Info ("SQL Execute End");
}
catch (Exception ex)
{
Log. Error ("SQL Execute Error occured:" + sql);
Log. Error ("SQL Execute errormessage:" + ex.) Message);
Log. Error (ex);
Throw ex;
}
Finally
{
if (null = = Transaction)
{
Close ();
}
}
return Ds;
}
#endregion

#region DataTable returns
//<summary>
//DataTable return
//</summary>
//<param name= "SQL" >sql text </param>
//<returns>datatable</returns>
Public DataTable returndatatable (string SQL)
{
DataTable dt = new DataTable ("TEMPDT");//cross-process serialization, you need to name the DataTable
Try
{
Log. Info ("SQL Execute Start:" + sql);
SqlDataAdapter Da = Getdataadapter (SQL);
Da.fill (DT);
Log. Info ("SQL Execute End");
}
catch (Exception ex)
{
Log. Error ("SQL Execute Error occured:" + sql);
Log. Error ("SQL Execute errormessage:" + ex.) Message);
Log. Error (ex);
Throw ex;
}
Finally
{
if (null = = Transaction)
{
Close ();
}
}
return dt;
}
#endregion

#region Transactions
<summary>
Start a transaction
</summary>
public void BeginTransaction ()
{
Open ();
cmd = connection. CreateCommand ();
Transaction = connection. BeginTransaction ();

Cmd. Transaction = Transaction;
}
<summary>
Commit a transaction
</summary>
public void CommitTransaction ()
{
Try
{
if (null! = Transaction)
{
Transaction.commit ();
}
}
catch (System.Exception ex)
{
Log. Error ("Transaction error:" + ex.) Message);
Log. Error (ex);
Transaction. Rollback ();
Throw ex;
}
Finally
{
transaction = NULL;
Close ();
}
}
<summary>
Rolling back a transaction
</summary>
public void RollbackTransaction ()
{
if (null! = Transaction)
{
Transaction. Rollback ();
transaction = NULL;
}
}
#endregion

#region Stored Procedures
<summary>
Executing stored procedures
</summary>
<param name= "Proc_name" ></param>
<returns></returns>
public int execprocedure (string proc_name)
{
int ret =-1;

String Strsqllog = String. Empty;
String sqllog = "";
if (listparameters! = null)
{

foreach (dictionaryentry parameter in listparameters)
{
Strsqllog = Strsqllog.replace ("@" + parameter. Key.tostring (), "'" + parameter. Value.tostring () + "'");
Sqllog = Sqllog + "[" + parameter. Key.tostring () + "]=[" + parameter. Value.tostring () + "]";
}
}

Try
{
Log. Info ("Procedure Execute Start:" + proc_name + "" + Sqllog);
ret = Createproc (proc_name). ExecuteNonQuery ();
Log. Info ("Procedure Execute End");
}
catch (Exception ex)
{
Log. Error ("Procedure Execute error occured:" + proc_name + "" + Sqllog);
Log. Error ("Procedure Execute errormessage:" + ex.) Message);
Log. Error (ex);
RollbackTransaction ();
Throw ex;
}
Finally
{
if (null = = Transaction)
{
Close ();
}

Clearparameter ();
}
return ret;
}
#endregion
}
}

Database connection Generic DBHelper class