Archive

Archive for the ‘C# Code and stuff’ Category

C# params keywork in datalayer

February 19, 2010 Leave a comment

I have been having to access a lot of stored procedures recently and decided it was time to optimize my datalayer class.  I wanted to be able to call just about any stored procedure, that returns a data set, regardless of how many parameters the stored procedure requires.

I wrote the following method using the params keyword.  This worked so well that I think I will use this format for update, insert (ExecuteNonQuery methods) and ExecuteScalar methods.

public bool GetLookUpData(string storedProc, string spParms, ref SqlDataReader rdr, params string[] strParms)
{
string[] Parms = spParms.Split(‘,’);
StringBuilder sqlErr = new StringBuilder();
try
{
if (Parms.Length != strParms.Length)
{
throw new System.ArgumentOutOfRangeException(“Arguments do not match”);
}

if (sqlConn.State == ConnectionState.Closed)
{
return false;
}

SqlCommand sqlQuery = new SqlCommand(storedProc, sqlConn);
sqlQuery.CommandType = CommandType.StoredProcedure;
sqlQuery.CommandTimeout = ConstSettings.SQLTimeOut;

for (int i = 0; i < strParms.Length; i++)
{
sqlQuery.Parameters.AddWithValue(Parms[i], strParms[i]);
}

sqlErr.Append(sqlQuery.CommandText + ” ‘”);

for (int i = 0; i < strParms.Length – 1; i++)
{
sqlErr.Append(strParms[i] + “‘,’”);
}

sqlErr.Append(strParms[strParms.Length-1] + “‘”);

rdr = sqlQuery.ExecuteReader();

if (rdr == null)
{
logEntry(“Null – “ + this.sqlErr);
return false;
}

sqlErr.Remove(0,strParms.Length);

return true;
}
catch (Exception ex) // catches without assigning to a variable
{

logEntry(ex.Message + ” – “ + this.sqlErr);
return false;
}
}

Categories: C# Code and stuff
Follow

Get every new post delivered to your Inbox.

Join 56 other followers