ADO. NET database operations

Using System;
Using System. Configuration;
Using System. Data;
Using System. Data. SqlClient;

Namespace DBHelper. utility
{
Public class SQLHelper
{

Public SQLHelper ()
{

}

Public static SqlConnection GetConn ()
{
SqlConnection conn = new SqlConnection (ConfigurationSettings. receivettings ["constr"]);
Conn. Open ();
Return conn;
}

Public static void CloseConn (SqlConnection conn)
{
If (conn! = Null)
{
Conn. Close ();
Conn. Dispose ();
}

}

Public static int ExecuteNonQuery (CommandType parameter type, string parameter text, params SqlParameter [] parameter params)
{
SqlConnection conn = SQLHelper. GetConn ();
SqlCommand cmd = new SqlCommand ();
PrepareCommand (conn, null, cmd, partition type, plain text, callback Params );
Int val;
Try
{
Val = cmd. ExecuteNonQuery ();
}
Catch
{
Throw;
}
Finally
{
Cmd. Parameters. Clear ();
CloseConn (conn );
}
Return val;
}

Public static int ExecuteNonQuery (SqlTransaction trans, CommandType primitive type, string plain text, params SqlParameter [] parallel parms)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (trans. Connection, trans, cmd, plain type, plain text, plain parms );
Int val = cmd. ExecuteNonQuery ();
Cmd. Parameters. Clear ();
Return val;
}

Public static object ExecuteScalar (CommandType parameter type, string parameter text, params SqlParameter [] parameter params)
{
SqlConnection conn = SQLHelper. GetConn ();
SqlCommand cmd = new SqlCommand ();
PrepareCommand (conn, null, cmd, partition type, plain text, callback Params );
Object val;
Try
{
Val = cmd. ExecuteScalar ();
}
Catch
{
Throw;
}
Finally
{
Cmd. Parameters. Clear ();
CloseConn (conn );
}
Return val;
}

Public static object ExecuteScalar (SqlTransaction trans, CommandType parameter type, string parameter text, params SqlParameter [] parameter params)
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (trans. Connection, trans, cmd, argument type, plain text, callback Params );
Object val = cmd. ExecuteScalar ();
Cmd. Parameters. Clear ();
Return val;
}

Public static SqlDataReader ExecuteReader (CommandType primitive type, string plain text, params SqlParameter [] writable params)
{
SqlConnection conn = SQLHelper. GetConn ();
SqlCommand cmd = new SqlCommand ();
Try
{
PrepareCommand (conn, null, cmd, partition type, plain text, callback Params );
SqlDataReader rdr = cmd. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Return rdr;
}
Catch (Exception ex)
{
CloseConn (conn );
Throw new Exception (ex. Message );
}
}

Public static DataView ExecuteDataView (CommandType parameter type, string parameter text, params SqlParameter [] parameter params)
{
SqlConnection conn = SQLHelper. GetConn ();
SqlCommand cmd = new SqlCommand ();
Try
{
PrepareCommand (conn, null, cmd, partition type, plain text, callback Params );
SqlDataAdapter dap = new SqlDataAdapter ();
Dap. SelectCommand = cmd;
DataSet ds = new DataSet ();
Dap. Fill (ds, "DefaultTable ");
DataView dv = new DataView ();
Dv = ds. Tables ["DefaultTable"]. DefaultView;
Return dv;
}
Catch
{
Throw;
}
Finally
{
CloseConn (conn );
}
}


Public static DataSet ExecuteDataSet (string strTableName, CommandType primitive type, string plain text, params SqlParameter [] partition params)
{
SqlConnection conn = SQLHelper. GetConn ();
SqlCommand cmd = new SqlCommand ();
Try
{
PrepareCommand (conn, null, cmd, partition type, plain text, callback Params );
SqlDataAdapter dap = new SqlDataAdapter ();
Dap. SelectCommand = cmd;
DataSet ds = new DataSet ();
Dap. Fill (ds, strTableName );
Cmd. Parameters. Clear ();
Dap. Dispose ();
Cmd. Dispose ();
Return ds;
}
Catch
{
Throw;
}
Finally
{
CloseConn (conn );
}
}

Public static void PrepareCommand (SqlConnection conn, SqlTransaction trans, SqlCommand cmd, CommandType primitive type, string plain text, SqlParameter [] partition paras)
{
Cmd. Connection = conn;
Cmd. CommandText = plain text;
Cmd. CommandType = primitive type;
If (trans! = Null)
{
Cmd. Transaction = trans;
}
If (partition paras! = Null)
{
Foreach (SqlParameter parm in parallel paras)
{
If (parm! = Null)
{
Cmd. Parameters. Add (parm );
}
}
}
}

Public static DataSet Query (string SQLString)
{
Using (SqlConnection conn = SQLHelper. GetConn ())
{
DataSet ds = new DataSet ();
Try
{
SqlDataAdapter command = new SqlDataAdapter (SQLString, conn );
Command. Fill (ds, "ds ");
}
Catch (System. Data. SqlClient. SqlException ex)
{
Throw new Exception (ex. Message );
}
Finally
{
CloseConn (conn );
}
Return ds;
}
}
}
}