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