Sql Helper Class
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
namespace AI_Portal_Cloud.DAL
{
public class clsSQLHelper : IDisposable
{
#region "Private Members"
private SqlConnection _connection;
private SqlTransaction _transaction;
private bool _disposed;
//private static HttpServerUtility _httpServer;
private static string _applicationName;
private static bool _applicationDebug;
private static string _logFolder;
private static string _tempFolder;
private static string _reportFolder;
private static string _logFolderAbsolute;
private static string _tempFolderAbsolute;
private static string _reportFolderAbsolute;
private static string _dbServerName;
private static string _dbName;
private static string _dbUser;
private static string _dbPassword;
#endregion
#region " Constructors "
private clsSQLHelper()
{
_disposed = false;
}
public clsSQLHelper(string ConnectionString)
: this()
{
_connection = new SqlConnection(ConnectionString);
}
#endregion
#region " Private Methods "
private SqlCommand GetNewCommand(string SQLCommand, CommandType SQLType)
{
SqlCommand cmd = new SqlCommand();
OpenConnection();
cmd.CommandText = SQLCommand;
cmd.CommandType = SQLType;
cmd.Connection = _connection;
cmd.CommandTimeout = 60;
// Apply transaction if required
if (this.IsTransaction)
{
cmd.Transaction = _transaction;
}
return cmd;
}
private SqlCommand GetNewCommand(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
SqlCommand cmd;
cmd = GetNewCommand(SQLProcedureName, CommandType.StoredProcedure);
if ((SQLParameters != null) && SQLParameters.Length > 0)
{
foreach (SqlParameter param in SQLParameters)
{
cmd.Parameters.Add(param);
}
}
return cmd;
}
private void OpenConnection()
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
}
private void CloseConnection()
{
if (!this.IsTransaction)
{
_connection.Close();
}
}
private int ExecuteNonQuery(ref SqlCommand cmd)
{
try
{
return cmd.ExecuteNonQuery();
}
finally
{
CloseConnection();
cmd.Dispose();
cmd = null;
}
}
private SqlDataReader ExecuteReader(ref SqlCommand cmd)
{
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
finally
{
cmd.Dispose();
cmd = null;
}
}
private object ExecuteScalar(ref SqlCommand cmd)
{
try
{
return cmd.ExecuteScalar();
}
finally
{
CloseConnection();
cmd.Dispose();
cmd = null;
}
}
private XmlReader ExecuteXmlReader(ref SqlCommand cmd)
{
try
{
return cmd.ExecuteXmlReader();
}
finally
{
CloseConnection();
cmd.Dispose();
cmd = null;
}
}
private DataTable ExecuteDataTable(ref SqlDataAdapter adptr)
{
DataTable tbl = new DataTable();
try
{
adptr.Fill(tbl);
return tbl;
}
finally
{
CloseConnection();
adptr.Dispose();
adptr = null;
tbl.Dispose();
tbl = null;
}
}
private DataSet ExecuteDataSet(ref SqlDataAdapter adptr)
{
DataSet ds = new DataSet();
try
{
adptr.Fill(ds);
return ds;
}
finally
{
CloseConnection();
adptr.Dispose();
adptr = null;
ds.Dispose();
ds = null;
}
}
#endregion
#region " Public Properties "
public bool IsTransaction
{
// Is a transaction active not
get
{
if (_transaction == null)
{
return false;
}
else
{
return true;
}
}
}
//public static HttpServerUtility HttpServer
//{
// get { return _httpServer; }
// set
// {
// if (_httpServer == null)
// {
// _httpServer = value;
// }
// }
//}
public static string ApplicationName
{
get { return _applicationName; }
set
{
if (_applicationName == null)
{
_applicationName = value;
}
}
}
public static bool ApplicationDebug
{
get { return _applicationDebug; }
set { _applicationDebug = value; }
}
public static string LogFolder
{
get { return _logFolder; }
set
{
if (_logFolder == null)
{
_logFolder = value;
_logFolder = _logFolder + (_logFolder.EndsWith("/") ? "" : "/");
// _logFolderAbsolute = HttpServer.MapPath(_logFolder);
}
}
}
public static string LogFolderAbsolute
{
get { return _logFolderAbsolute; }
}
public static string TempFolder
{
get { return _tempFolder; }
set
{
if (_tempFolder == null)
{
_tempFolder = value;
_tempFolder = _tempFolder + (_tempFolder.EndsWith("/") ? "" : "/");
//_tempFolderAbsolute = HttpServer.MapPath(_tempFolder);
}
}
}
public static string TempFolderAbsolute
{
get { return _tempFolderAbsolute; }
}
public static string DBServerName
{
get { return _dbServerName; }
set
{
if (_dbServerName == null)
{
_dbServerName = value;
}
}
}
public static string DBName
{
get { return _dbName; }
set
{
if (_dbName == null)
{
_dbName = value;
}
}
}
public static string DBUser
{
get { return _dbUser; }
set
{
if (_dbUser == null)
{
_dbUser = value;
}
}
}
public static string DBPassword
{
get { return _dbPassword; }
set
{
if (_dbPassword == null)
{
_dbPassword = value;
}
}
}
public static string ConnectionString
{
//Return "Server=" & _dbServerName & ";Initial Catalog=" & _dbName & ";UID=" & _dbUser & ";Pwd= " & _dbPassword & ";Connection Timeout= 60;Integrated Security=True"
get { return "Server=" + _dbServerName + ";Initial Catalog=" + _dbName + ";UID=" + _dbUser + ";Pwd= " + _dbPassword + ";Integrated Security=FALSE"; }
}
#endregion
#region " IDisposable Implementation "
public void Dispose()
{
if (_disposed)
{
return;
}
try
{
// Rollback current transaction
if (this.IsTransaction)
{
_transaction.Rollback();
}
_connection.Close();
// Error while rolling back
}
catch (Exception)
{
_connection.Close();
}
finally
{
if ((_transaction != null))
{
_transaction.Dispose();
_transaction = null;
}
if ((_connection != null))
{
_connection.Dispose();
_connection = null;
}
_disposed = true;
GC.SuppressFinalize(this);
}
}
~clsSQLHelper()
{
this.Dispose();
}
#endregion
#region " Public Methods "
public int ExecuteNonQuery(string SQLCommand, CommandType SQLType)
{
SqlCommand cmdObj = GetNewCommand(SQLCommand, SQLType);
return ExecuteNonQuery(ref cmdObj);
}
public int ExecuteNonQuery(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteNonQuery(ref GetNewCommand(SQLProcedureName, SQLParameters));
SqlCommand cmdObj = GetNewCommand(SQLProcedureName, ref SQLParameters);
return ExecuteNonQuery(ref cmdObj);
}
public SqlDataReader ExecuteReader(string SQLCommand, CommandType SQLType)
{
//return ExecuteReader(GetNewCommand(SQLCommand, SQLType));
SqlCommand cmdObj = GetNewCommand(SQLCommand, SQLType);
return ExecuteReader(ref cmdObj);
}
public SqlDataReader ExecuteReader(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteReader(GetNewCommand(SQLProcedureName, SQLParameters));
SqlCommand cmdObj = GetNewCommand(SQLProcedureName, ref SQLParameters);
return ExecuteReader(ref cmdObj);
}
public object ExecuteScalar(string SQLCommand, CommandType SQLType)
{
//return ExecuteScalar(GetNewCommand(SQLCommand, SQLType));
SqlCommand cmdObj = GetNewCommand(SQLCommand, SQLType);
return ExecuteScalar(ref cmdObj);
}
public object ExecuteScalar(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteScalar(GetNewCommand(SQLProcedureName, SQLParameters));
SqlCommand cmdObj = GetNewCommand(SQLProcedureName, ref SQLParameters);
return ExecuteScalar(ref cmdObj);
}
public XmlReader ExecuteXmlReader(string SQLCommand, CommandType SQLType)
{
//return ExecuteXmlReader(GetNewCommand(SQLCommand, SQLType));
SqlCommand cmdObj = GetNewCommand(SQLCommand, SQLType);
return ExecuteXmlReader(ref cmdObj);
}
public XmlReader ExecuteXmlReader(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteXmlReader(GetNewCommand(SQLProcedureName, SQLParameters));
SqlCommand cmdObj = GetNewCommand(SQLProcedureName, ref SQLParameters);
return ExecuteXmlReader(ref cmdObj);
}
public DataTable ExecuteDataTable(string SQLCommand, CommandType SQLType)
{
//return ExecuteDataTable(new SqlDataAdapter(GetNewCommand(SQLCommand, SQLType)));
SqlDataAdapter cmdObj = new SqlDataAdapter(GetNewCommand(SQLCommand, SQLType));
return ExecuteDataTable(ref cmdObj);
}
public DataTable ExecuteDataTable(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteDataTable(new SqlDataAdapter(GetNewCommand(SQLProcedureName, SQLParameters)));
SqlDataAdapter cmdObj = new SqlDataAdapter(GetNewCommand(SQLProcedureName, ref SQLParameters));
return ExecuteDataTable(ref cmdObj);
}
public DataSet ExecuteDataSet(string SQLCommand, CommandType SQLType)
{
//return ExecuteDataSet(new SqlDataAdapter(GetNewCommand(SQLCommand, SQLType)));
SqlDataAdapter cmdObj = new SqlDataAdapter(GetNewCommand(SQLCommand, SQLType));
return ExecuteDataSet(ref cmdObj);
}
public DataSet ExecuteDataSet(string SQLProcedureName, ref SqlParameter[] SQLParameters)
{
//return ExecuteDataSet(new SqlDataAdapter(GetNewCommand(SQLProcedureName, SQLParameters)));
SqlDataAdapter cmdObj = new SqlDataAdapter(GetNewCommand(SQLProcedureName, ref SQLParameters));
return ExecuteDataSet(ref cmdObj);
}
// Refreshes all the resources and prepares SQLHelper for another execution
public void Refresh(string ConnectionString)
{
_disposed = false;
try
{
// Rollback current transaction
if (this.IsTransaction)
{
_transaction.Rollback();
}
_connection.Close();
// Error while rolling back
}
catch (Exception)
{
_connection.Close();
}
finally
{
if ((_transaction != null))
{
_transaction.Dispose();
_transaction = null;
}
if ((_connection != null))
{
_connection.Dispose();
_connection = null;
}
_connection = new SqlConnection(ConnectionString);
// Get a new connection
}
}
public bool BeginTransaction()
{
// If transaction already exists, nothing happens else a transaction is created
if (!this.IsTransaction)
{
OpenConnection();
_transaction = _connection.BeginTransaction();
return true;
}
else
{
return false;
}
}
public bool CommitTransaction()
{
// Commit the transaction if it exists else nothing happens
try
{
if (this.IsTransaction)
{
_transaction.Commit();
return true;
}
else
{
return false;
}
}
finally
{
if ((_transaction != null))
{
_transaction.Dispose();
_transaction = null;
}
CloseConnection();
}
}
public bool RollBackTransaction()
{
// Rollback transaction is it exists else nothing happens
try
{
if (this.IsTransaction)
{
_transaction.Rollback();
return true;
}
else
{
return false;
}
}
finally
{
if ((_transaction != null))
{
_transaction.Dispose();
_transaction = null;
}
CloseConnection();
}
}
#endregion
}
}
Comments
Post a Comment