/// <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>
/// 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 SPQueryDatasetNoParams( string strConn , string strSP , ref DataSet objDS )
{
try
{
// if (OpenConnection("Password=ghost5;Persist Security Info=True;User ID=GClass_admin;Initial Catalog=CCT;Data Source=bdc-sqld023.na.pg.com\\devnt2322"))
if ( OpenConnection () )
{
bool bReturn = false;
SqlCommand cmd = null;
//bool bReturn = false;
//SqlCommand cmd = null;
SqlDataAdapter objDA = default ( SqlDataAdapter );
try
{
if ( ( _adoConnection == null ) )
{
_strLastError = ErrorMessage . DbConnectionNotOpened;
return false;
}
//Create Command Instance with respective SQLQuery or SPName
CreateCmdInstanceSql ( strSP , ref cmd );
cmd . CommandType = CommandType . StoredProcedure;
cmd . CommandTimeout = intTimeOut; // increased the timeout for SQL Queries
objDS = new DataSet ( );
objDA = new SqlDataAdapter ( ( SqlCommand ) cmd );
objDA . Fill ( objDS );
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 , strSP ,
System . Net . Dns . GetHostName ( ) ) , true );
_strLastError = ex . Message;
}
finally
{
cmd = null;
_objCommon = null;
}
return bReturn;
}
else
{ return false; }
}
catch ( Exception )
{
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;
}