public static SqlDataReader CreateReader(DbSqlParameterCollection sqlParams, string text)
{
using (SqlCommand myCmd = new SqlCommand(text, (IsTransaction) ? GetConnection : GetNewConnection))
{
myCmd.CommandType = CommandType.Text;
SqlDataReader dr = null;
if (myCmd.Connection.State != ConnectionState.Open)
{
myCmd.Connection.Open();
}
if (currentTransaction != null)
{
myCmd.Transaction = currentTransaction;
}
foreach (DbSqlParameter par in sqlParams)
{
myCmd.Parameters.Add(par.Parameter);
}
dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
return(dr);
}
}
public static DataSet GetDataSet(DbSqlParameterCollection sqlParams, string text)
{
bool isTransaction = IsTransaction;
using (SqlCommand myCommand = new SqlCommand(text, (isTransaction) ? GetConnection : GetNewConnection))
{
myCommand.CommandType = CommandType.Text;
foreach (DbSqlParameter p in sqlParams)
{
myCommand.Parameters.Add(p.Parameter);
}
if (isTransaction)
{
myCommand.Transaction = currentTransaction;
}
using (SqlDataAdapter adapter = new SqlDataAdapter(myCommand))
{
using (DataSet ds = new DataSet())
{
try
{
adapter.Fill(ds);
return(ds);
}
catch (Exception ex)
{
LogFileWrite("Page Url: " + HttpContext.Current.Request.Url + Environment.NewLine + "In Dal layer (GetDataSet Two Para Text) " + Environment.NewLine + "Error : " + ex.Message);
throw;
}
}
}
}
}
public static DataSet SecureCreateDataset(string sqlstring, DbSqlParameterCollection sqlParams)
{
//DataSet Secure = new DataSet();
using (SqlCommand cmd = (IsTransaction) ? GetConnection.CreateCommand() : GetNewConnection.CreateCommand())
{
cmd.CommandText = sqlstring;
using (SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(cmd))
{
if (IsTransaction)
{
SqlDataAdapter.SelectCommand.Transaction = currentTransaction;
}
foreach (DbSqlParameter ss in sqlParams)
{
cmd.Parameters.Add(ss.Parameter);
}
try
{
if (currentTransaction != null)
{
cmd.Transaction = currentTransaction;
}
using (DataSet Secure = new DataSet())
{
try
{
SqlDataAdapter.Fill(Secure);
return(Secure);
}
catch (Exception ex)
{
LogFileWrite("Page Url: " + HttpContext.Current.Request.Url + Environment.NewLine + "In Dal layer (SecureCreateDataset) " + Environment.NewLine + "Error : " + ex.Message);
throw;
}
}
}
catch (Exception ex)
{
LogFileWrite("Page Url: " + HttpContext.Current.Request.Url + Environment.NewLine + "Error : " + ex.Message);
return(null);
}
finally
{
cmd.Connection.Close();
closeconnection();
}
;
}
}
}
public static object SqlScalartoObj(string sqlStored, DbSqlParameterCollection sqlParams)
{
object DoStored;
using (SqlCommand myCommand = new SqlCommand(sqlStored, GetNewConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
foreach (DbSqlParameter p in sqlParams)
{
myCommand.Parameters.Add(p.Parameter);
}
if (myCommand.Connection.State != ConnectionState.Open)
{
myCommand.Connection.Open();
}
DoStored = myCommand.ExecuteScalar();
myCommand.Connection.Close();
}
return(DoStored);
}
public static object ExecuteProc(string sql, DbSqlParameterCollection sqlParams)
{
bool isTransaction = IsTransaction;
using (SqlCommand cmd = new SqlCommand(sql, (isTransaction) ? GetConnection : GetNewConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
if (currentTransaction != null)
{
cmd.Transaction = currentTransaction;
}
foreach (DbSqlParameter p in sqlParams)
{
cmd.Parameters.Add(p.Parameter);
}
//cmd.ExecuteNonQuery();
object ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(ret);
}
catch (Exception ex)
{
LogFileWrite("Page Url: " + HttpContext.Current.Request.Url + Environment.NewLine + "In Dal layer (ExecuteProc) " + Environment.NewLine + "Error : " + ex.Message);
RollBackTransaction();
throw;
}
finally
{
cmd.Connection.Close();
// closeconnection();
}
};
}
public static int ExecuteCommandWithParamInTransaction(DbSqlParameterCollection sqlParam, string Text)
{
int i = 0;
bool isTransaction = true;
using (SqlCommand cmd = new SqlCommand(Text, (isTransaction) ? GetConnection : GetNewConnection))
{
cmd.CommandType = CommandType.Text;
foreach (DbSqlParameter p in sqlParam)
{
cmd.Parameters.Add(p.Parameter);
}
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
if (currentTransaction != null)
{
cmd.Transaction = currentTransaction;
}
i = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
LogFileWrite("Page Url: " + HttpContext.Current.Request.Url + Environment.NewLine + "In Dal layer (ExecuteCommandWithParam) " + Environment.NewLine + "Error : " + ex.Message);
RollBackTransaction();
throw;
}
finally
{
cmd.Connection.Close();
}
};
return(i);
}
