/// <summary>
/// Execute Query with specific timeout and nRowsAffected as output
/// </summary>
/// <param name="sqlQuery"></param>
/// <param name="nRowsAffected"></param>
/// <param name="timeOut"></param>
/// <returns></returns>
public bool ExecuteDml(string sqlQuery, ref int nRowsAffected, int timeOut)
{
DbCommand cmd = null;
bool bReturn = false;
try
{
if (_adoConnection == null)
{
_strLastError = ErrorMessage.DbConnectionNotOpened;
return false;
}
//Create Command Instance with respective SQLQuery or SPName
CreateCmdInstance(sqlQuery, ref cmd);
if (timeOut > 0)
{
cmd.CommandTimeout = timeOut;
}
nRowsAffected = cmd.ExecuteNonQuery();
bReturn = true;
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - 000",
Convert.ToString(DateTime.Now), ex.Message,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
_objCommon = null;
}
return bReturn;
}
/// <summary>
/// Open Connection to Database
/// </summary>
/// <returns>bool</returns>
private bool OpenConnection()
{
bool bReturn = false;
try
{
if (GetConnection())
{
bReturn = true;
}
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - 000",
Convert.ToString(DateTime.Now), ErrorMessage.ErrorWhileEstablishingConnection,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
_objCommon = null;
}
return bReturn;
}
/// <summary>
/// Close database Connection
/// </summary>
private void CloseConnection()
{
try
{
if (_adoConnection != null)
{
_adoConnection.Close();
}
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - 000",
Convert.ToString(DateTime.Now), ErrorMessage.ErrorWhileClosingConnection,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
if (_adoConnection != null)
{
_adoConnection.Dispose();
_adoConnection = null;
}
_objCommon = null;
}
}
/// <summary>
/// Execury Stored Procedure by passing parameters and getting return values as output
/// </summary>
/// <param name="spName"></param>
/// <param name="inParams"></param>
/// <param name="retValues"></param>
/// <param name="isNonQuery"></param>
/// <returns>bool</returns>
public bool SpQueryReturnValue(string spName, SqlParameter[] inParams, ref SqlParameter[] retValues, bool isNonQuery)
{
try
{
if (OpenConnection())
{
bool bReturn = false;
SqlCommand cmd = null;
SqlParameter objParam = null;
try
{
objParam = default(SqlParameter);
if (_adoConnection == null)
{
_strLastError = ErrorMessage.DbConnectionNotOpened;
return false;
}
//Create Command Instance with respective SQLQuery or SPName
CreateCmdInstanceSql(spName, ref cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = intTimeOut; // increased the timeout for SQL Queries
if (inParams != null)
{
foreach (DbParameter dbPar in inParams)
{
objParam = ((SqlCommand)cmd).Parameters.Add((SqlParameter)dbPar);
objParam.Direction = ParameterDirection.Input;
}
}
if (retValues != null)
{
foreach (DbParameter dbPar in retValues)
{
objParam = ((SqlCommand)cmd).Parameters.Add((SqlParameter)dbPar);
objParam.Direction = ParameterDirection.ReturnValue;
}
}
if (isNonQuery)
{
cmd.ExecuteNonQuery();
}
bReturn = true;
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - {3} - 000",
Convert.ToString(DateTime.Now), ex.Message, spName,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
cmd = null;
_objCommon = null;
}
return bReturn;
}
else
{
return false;
}
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
/// <summary>
/// Execute Stored Procedure by getting resultSet as output
/// </summary>
/// <param name="spName"></param>
/// <param name="resultSet"></param>
/// <returns>bool</returns>
public bool SpQueryDataset(string spName, ref DataSet resultSet)
{
try
{
if (OpenConnection())
{
bool bReturn = false;
SqlCommand cmd = null;
SqlDataAdapter objDA = null;
try
{
objDA = default(SqlDataAdapter);
if (_adoConnection == null)
{
_strLastError = ErrorMessage.DbConnectionNotOpened;
return false;
}
//Create Command Instance with respective SQLQuery or SPName
CreateCmdInstanceSql(spName, ref cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = intTimeOut; // increased the timeout for SQL Queries
resultSet = new DataSet();
objDA = new SqlDataAdapter((SqlCommand)cmd);
objDA.Fill(resultSet);
bReturn = true;
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - {3} - 000",
Convert.ToString(DateTime.Now), ex.Message, spName,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
objDA = null;
cmd = null;
_objCommon = null;
}
return bReturn;
}
else
{
return false;
}
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
/// <summary>
/// Execute Query with specific timeout and resultset as output
/// </summary>
/// <param name="sqlQuery"></param>
/// <param name="resultSet"></param>
/// <param name="timeOut"></param>
/// <returns>bool</returns>
public bool RsQuery(string sqlQuery, ref DataSet resultSet, int timeOut)
{
DbCommand cmd = null;
DbDataAdapter da = null;
bool bReturn = false;
try
{
da = default(DbDataAdapter);
if (_adoConnection == null)
{
_strLastError = ErrorMessage.DbConnectionNotOpened;
return false;
}
//Create Command Instance with respective SQLQuery or SPName
CreateCmdInstance(sqlQuery, ref cmd);
if (timeOut > 0) cmd.CommandTimeout = timeOut;
if (!_bOracle)
{
da = new SqlDataAdapter((SqlCommand)cmd);
}
else
{
da = new OracleDataAdapter((OracleCommand)cmd);
}
resultSet = new DataSet();
da.Fill(resultSet);
bReturn = true;
}
catch (Exception ex)
{
_objCommon = new Common();
_objCommon.WriteToFile(_objCommon.GetLogPath(),
string.Format("{0} DBPool-Catch - {1} - {2} - 000",
Convert.ToString(DateTime.Now), ex.Message,
System.Net.Dns.GetHostName()), true);
_strLastError = ex.Message;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
da = null;
_objCommon = null;
}
return bReturn;
}