public static int ExecuteSqlQuery(string ConnectionString, string strSql, List <object> ocRows, object oRow, PopulateDTO delPopulateDTO)
{
int ctrRows = 0;
try
{
// 1) Get Conn SqlConnection oSqlConnection
// 2) Open DB oSqlConnection.Open();
// 3) Set Command SqlCommand oSqlCommand = new SqlCommand(sql, oSqlConnection);
// 4) Exec Sql / Read Rows SqlDataReader rdr = oSqlCommand.ExecuteReader();
using (SqlConnection oSqlConnection = new SqlConnection(ConnectionString)) // 1) Get Conn
{
oSqlConnection.Open(); // 2) Get Conn
SqlCommand oSqlCommand = new SqlCommand(strSql, oSqlConnection); // 3) Set Command
using (SqlDataReader rdr = oSqlCommand.ExecuteReader()) // 4) Exec Sql / Read Rows
{
while (rdr.Read())
{
ctrRows++;
delPopulateDTO(ocRows, oRow, rdr);
}
}
} // using conn
}
catch (Exception ex)
{
var msg = ex.Message + "\n" + StackTraceParse(ex.StackTrace);
throw new Exception($"Method: {MethodBase.GetCurrentMethod().Name}\nError Message: {msg}\nSql: {strSql}\nConnectionString: {ConnectionString}");
}
return(ctrRows);
}
} // ExecuteStoredProcedureQuery
public static string ExecuteStoredProcedureQuery(string ConnectionString, string StoredProcedureName
, List <string> SqlParmNames, List <object> SqlParmValues, List <object> ocRows, object oRow, PopulateDTO delegatePopulateDTO)
{
string s = "";
try
{
using (SqlConnection oSqlConnection = new SqlConnection(ConnectionString))
{
oSqlConnection.Open();
// 1. create a command object identifying the stored procedure
SqlCommand oSqlCommand = new SqlCommand(StoredProcedureName, oSqlConnection);
// 2. set the command object so it knows to execute a stored procedure
oSqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
// SqlParmNames, SqlParmValues
for (int i = 0; i < SqlParmNames.Count; i++)
{
oSqlCommand.Parameters.Add(new SqlParameter(SqlParmNames[i], SqlParmValues[i]));
}
// execute the command
using (SqlDataReader rdr = oSqlCommand.ExecuteReader())
{
var sch = rdr.GetSchemaTable();
// rdr.MetaData.metaDataArray[0].column
// iterate through results, printing each to console
while (rdr.Read())
{
delegatePopulateDTO(ocRows, oRow, rdr);
}
}
} // using conn
}
catch (Exception ex)
{
var msg = ex.Message.IndexOf("CallStack=") > -1 ? ex.Message : ex.Message + $" - CallStack= {ex.StackTrace}";
throw new Exception($"Method: {MethodBase.GetCurrentMethod().Name}\nStored Procedure: {StoredProcedureName}\nConnectionString: {ConnectionString}\nError Message: {msg}");
}
return(s);
} // ExecuteStoredProcedureQuery
