public static void ExecuteNonQuery(string procedureName, Common.ParameterList SQLParam, int timeOutDuration) { SqlConnection conn = new SqlConnection(); try { using (conn = new SqlConnection(ConnectionString)) { conn.Open(); using (SqlCommand cmd = BuildSqlCommand(conn, null, CommandType.StoredProcedure, procedureName, SQLParam)) { cmd.CommandTimeout = timeOutDuration; cmd.ExecuteNonQuery(); StoreParameterOutputValues(cmd, SQLParam); } conn.Close(); // Gurukul.Database.Common.SQLParameter.ConType = ConnectionType.WebConn; } } catch (Exception) { throw; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } //Gurukul.Database.Common.SQLParameter.ConType = ConnectionType.WebConn; } }
private static void StoreParameterOutputValues(SqlCommand cmd, Common.ParameterList SQLParam) { foreach (SqlParameter param in cmd.Parameters) { if (param.Direction != ParameterDirection.Input) { SQLParam.GetParameterByName(param.ParameterName).ParameterValue = param.Value; } } }
public static DataTable ExecuteDataTable(string SQL, Common.ParameterList SQLParam) { SqlDataAdapter ad; DataTable dt = new DataTable(); try { ad = ExecuteDataAdapter(SQL, SQLParam); ad.Fill(dt); return(dt); } catch (Exception) { throw; } }
public static DataSet ExecuteDataSet(string procedureName, Common.ParameterList SQLParam) { SqlDataAdapter ad; DataSet ds = new DataSet(); try { ad = ExecuteDataAdapter(procedureName, SQLParam); ad.Fill(ds); return(ds); } catch (Exception) { throw; } }
public static DataTableReader ExecuteReader(string procedureName, Common.ParameterList SQLParam, int timeOut) { DataTableReader reader; DataSet ds; try { ds = ExecuteDataSet(procedureName, SQLParam, timeOut); reader = ds.CreateDataReader(); return(reader); } catch (Exception) { throw; } }
public static SqlDataAdapter ExecuteDataAdapter(string procedureName, Common.ParameterList SQLParam) { SqlConnection conn = null; SqlDataAdapter ad; try { conn = new SqlConnection(ConnectionString); conn.Open(); ad = new SqlDataAdapter(); ad.SelectCommand = BuildSqlCommand(conn, null, CommandType.StoredProcedure, procedureName, SQLParam); conn.Close(); return(ad); } catch (Exception) { throw; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } }
private static SqlCommand BuildSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.ParameterList SQLParam, int timeOutDuration) { SqlCommand cmd = BuildBaseSqlCommand(conn, transaction, cmdType, procedureName, SQLParam); cmd.CommandTimeout = timeOutDuration; return(cmd); }
//private static SqlCommand BuildSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.SQLParameterList SQLParam) //{ // SqlCommand cmd = new SqlCommand(); // cmd.CommandType = cmdType; // cmd.Connection = conn; // cmd.Transaction = transaction; // cmd.CommandText = procedureName; // foreach (SqlParameter param in SQLParam) // { // cmd.Parameters.Add(param); // } // return cmd; //} private static SqlCommand BuildBaseSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.ParameterList SQLParam) { SqlCommand cmd = new SqlCommand(); cmd.CommandType = cmdType; cmd.Connection = conn; cmd.Transaction = transaction; cmd.CommandText = procedureName; foreach (Common.SQLParameter param in SQLParam) { cmd.Parameters.Add(new SqlParameter(param.ParameterName, param.ParameterValue)); if (param.DBType != null) { cmd.Parameters[cmd.Parameters.Count - 1].SqlDbType = (SqlDbType)param.DBType; } if (param.Size != -1) { cmd.Parameters[cmd.Parameters.Count - 1].Size = param.Size; } cmd.Parameters[cmd.Parameters.Count - 1].Direction = param.ParameterDirection; } return(cmd); }