SQL Helper Class
public class SQLHelper
{
private SqlConnection _objConnection;
private SqlCommand _objCommand;
private SqlTransaction _sqlTrans;
private string _connectionString;
private String[] _tables = { "Table", "Table1", "Table2", "Table3", "Table4", "Table5", "Table6", "Table7", "Table8" };
public SQLHelper()
{
_connectionString = WebConfigurationManager.ConnectionStrings["Connect"].ToString();
_objConnection = new SqlConnection(_connectionString);
_objCommand = new SqlCommand();
_objCommand.CommandTimeout = 40000;
}
private void Open()
{
try
{
if (_objConnection.State != ConnectionState.Open)
_objConnection.Open();
if (_objCommand == null)
_objCommand = new SqlCommand();
_objCommand.Connection = _objConnection;
}
catch (Exception ex)
{
Close();
throw new ApplicationException("Error", ex);
}
}
private void Close()
{
try
{
_objCommand.Dispose();
if (_sqlTrans == null)
{
if (_objConnection.State != ConnectionState.Closed)
_objConnection.Close();
}
}
catch(Exception ex)
{
throw new ApplicationException("Error", ex);
}
}
public string StoreProcedureName
{
get
{
if (_objCommand != null)
return _objCommand.CommandText;
else
return null;
}
set
{
if (_objCommand == null)
{
_objCommand = new SqlCommand();
}
_objCommand.Parameters.Clear();
_objCommand.CommandText = value;
_objCommand.CommandType = CommandType.StoredProcedure;
}
}
public DataSet ExecDataSet()
{
DataSet dataSet = new DataSet();
try
{
Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(_objCommand);
dataAdapter.Fill(dataSet);
return dataSet;
}
catch (Exception ex)
{
throw new ApplicationException("Error:ExecDataSet", ex);
}
finally
{
Close();
}
}
public void ExecDataSet(DataSet dataSet, string[] mapping)
{
try
{
Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(_objCommand);
for (int i = 0; i < mapping.Length; i++)
{
dataAdapter.TableMappings.Add(_tables[i], mapping[i]);
}
}
catch (Exception ex)
{
throw new ApplicationException("Error:ExecDataSet(DataSet dataSet, string[] mapping)",ex)
}
finally
{
Close();
}
}
public void ExecDataSet(T dataSet) where T : DataSet
{
try
{
Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(_objCommand);
dataAdapter.Fill(dataSet);
}
catch (Exception ex)
{
throw new ApplicationException("Error ExecDataSet(T dataSet) ", ex);
}
finally
{
if (_sqlTrans != null)
Close();
}
}
public DataTable ExecDataTable()
{
DataTable dataTable = new DataTable();
try
{
Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(_objCommand);
dataAdapter.Fill(dataTable);
return dataTable;
}
catch (Exception ex)
{
throw new ApplicationException("Error ExecDataTable()", ex);
}
finally
{
Close();
}
}
public SqlParameterCollection Parameters
{
get
{
return _objCommand.Parameters;
}
}
public int ExecDML()
{
int intCount;
try
{
Open();
intCount = _objCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
intCount = 0;
throw new ApplicationException("Error ExecDML", ex);
}
finally
{
Close();
}
return intCount;
}
public SqlDataReader ExecReader()
{
SqlDataReader dataReader = null;
try
{
Open();
dataReader = _objCommand.ExecuteReader();
return dataReader;
}
catch (Exception ex)
{
throw new ApplicationException("Error ExecReader",ex)
}
finally
{
Close();
}
}
public object ExecScalar()
{
try
{
Open();
return _objCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw new ApplicationException("Error ExecScalar",ex)
}
finally
{
Close();
}
}
public void BeginTransaction()
{
try
{
Open();
_sqlTrans = this._objConnection.BeginTransaction(IsolationLevel.ReadCommitted);
_objCommand.Transaction = _sqlTrans;
}
catch (Exception ex)
{
throw new ApplicationException("Error BeginTransaction", ex);
}
}
public void RollBack()
{
try
{
if (_sqlTrans != null)
{
_sqlTrans.Rollback();
_sqlTrans = null;
}
Close();
}
catch (Exception ex)
{
throw new ApplicationException("Error RollBack()", ex);
}
}
public void Commit()
{
try
{
if (_sqlTrans != null)
{
_sqlTrans.Commit();
_sqlTrans = null;
}
Close();
}
catch (Exception ex)
{
throw new ApplicationException("Error Commit()", ex);
}
}
}