execute stored procedures
I decided to used stored procedures in my last project (HR/Benefits/Payroll related web application). I wanted one procedure to execute all my stored procedures, no matter what the list of parameters:
using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;
public DataSet returnAnyStoredProcOleDB(string spName, ArrayList myParams)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
string questionMarks = null;
StringBuilder list = new StringBuilder();
string testString = null;
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmdSel = new SqlCommand(spName, cn);
da.SelectCommand = cmdSel;
cmdSel.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
if (myParams != null)
{
foreach(dbcParams q in myParams)
{
list.Append("?,");
cmdSel.Parameters.Add("@" + q.ParamName, q.ParamValue);
testString = testString + " " + "ParamName=" + q.ParamName + " ParamVal=" + q.ParamValue.ToString();
}
list.Length -= 1; //remove trailing comma
questionMarks = list.ToString();
}
da.Fill(ds);
cn.Close();
bool returnLog = false;
returnLog = ErrorLog.ErrorRoutine(false, " params = > " + testString);
cn.Dispose();
}
catch (Exception e)//yeah, I know, lazy...this is just an example
{
bool returnLog = false; //logging code I'll post about in the future
returnLog = ErrorLog.ErrorRoutine(false, e, e.ToString() + spName + " " + "=spName, params = > " + testString);
errorMessage = e.ToString();
}
return ds;
}
