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); }