Introduction
If you've dealt with SQL from C#, you know how contrived it is. A whole lot of repetition that begs for a better way. Several on this site have already documented the pain and submitted a "better" way here, here, and here. These solutions involve an intermediate code generation step that requires you to paste in a bunch of code for each sproc (stored procedure), which allows you to then call the sproc like a normal procedure from C#. This extra code generation step, while kinda cool, is not without pain. The solutions are somewhat unwieldy, and some involve external dependencies.
A better way
There's a way to call an sproc from C# almost like a regular procedure, without any intermediate generating, pasting, or dependencies, and with reasonable performance (sproc info is cached). Here's how you might use the code:
MySqlConn _sqlConn = new MySqlConn(
"provide a valid connection string here");
...
_sqlConn.ExecSProc("AddStaff", staffName, isDoctor);
_sqlConn.ExecSProc("AddRoom", roomName);
Honest, that's it! You just called two sprocs; one named "AddStaff
" with two input parameters, then "AddRoom
" passing in one parameter. About the only drawback (compared to the code generation method) is that since parameters can be of any type, we won't know until runtime if there was a type mismatch, or if we passed the wrong number of parameters. Wah. Also note that neither this method nor automatic code generation will keep you from going stale - if you change the number or type or order of parameters in your sproc, you'll have to remember to update your C# code.
Does your sproc return a value and/or have output parameters? Well, if your sproc returns an error code and sets an out Param
@roomId
, then it might look like this:
int err = _sqlConn.ExecSProc("AddRoom", roomName);
if (err == 0)
roomId = (int)_sqlConn.Param("@roomId");
How much does it cost, you ask? About 130 lines of code that you can copy, paste, and forget about. Here it is:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
namespace CodeProjectRocks
{
public class MySqlConn
{
SqlConnection _dbConn;
SProcList _sprocs;
SqlParameterCollection _lastParams;
public MySqlConn(string connStr)
{
_dbConn = new SqlConnection(connStr);
_sprocs = new SProcList(this);
}
void Open()
{ if (_dbConn.State != ConnectionState.Open) _dbConn.Open(); }
void Close()
{ if (_dbConn.State == ConnectionState.Open) _dbConn.Close(); }
SqlCommand NewSProc(string procName)
{
SqlCommand cmd = new SqlCommand(procName, _dbConn);
cmd.CommandType = CommandType.StoredProcedure;
#if EmulateDeriveParameters //see below for our
MySqlCmdBuilder.DeriveParameters(cmd);
#else
Open();
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter prm in cmd.Parameters)
if (prm.Direction == ParameterDirection.InputOutput)
prm.Direction = ParameterDirection.Output;
#endif
return cmd;
}
SqlCommand FillParams(string procName,
params object[] vals)
{
SqlCommand cmd = _sprocs[procName];
int i = 0;
foreach (SqlParameter prm in cmd.Parameters)
{
if (prm.Direction == ParameterDirection.Input
|| prm.Direction == ParameterDirection.InputOutput)
prm.Value = vals[i++];
}
Debug.Assert(i == (vals == null ? 0 : vals.Length));
_lastParams = cmd.Parameters;
return cmd;
}
public DataRowCollection QueryRows(string strQry)
{
DataTable dt = new DataTable();
new SqlDataAdapter(strQry, _dbConn).Fill(dt);
return dt.Rows;
}
public int ExecSProc(string procName,
params object[] vals)
{
int retVal = -1;
try
{
Open();
FillParams(procName, vals).ExecuteNonQuery();
retVal = (int)_lastParams[0].Value;
}
catch (System.Exception e)
{
}
finally
{
Close();
}
return retVal;
}
public DataSet ExecSProcDS(string procName,
params object[] vals)
{
DataSet ds = new DataSet();
try
{
Open();
new SqlDataAdapter(
FillParams(procName, vals)).Fill(ds);
}
finally
{
Close();
}
return ds;
}
public object Param(string param)
{
return _lastParams[param].Value;
}
class SProcList : DictionaryBase
{
MySqlConn _db;
public SProcList(MySqlConn db)
{ _db = db; }
public SqlCommand this[string name]
{ get {
if (!Dictionary.Contains(name))
Dictionary.Add(name, _db.NewSProc(name));
return (SqlCommand)Dictionary[name];
} }
}
}
}
As an FYI only to show how to query SQL for param info (you do not need this code, unless, as pointed out by a reader, you are inside a SQL transaction, which the framework apparently does not handle):
#if EmulateDeriveParameters
class MySqlCmdBuilder {
static SqlTypeMap _sqlTypeMap = null;
class SqlTypeMap : DictionaryBase {
public SqlDbType this[string key]
{ get { return (SqlDbType)Dictionary[key]; }}
public void Add(string key, SqlDbType value)
{ Dictionary.Add(key, value); }
}
private MySqlCmdBuilder() {}
public static void DeriveParameters(SqlCommand cmd)
{
EnsureTypeMap();
SqlParameter prmRet =
new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
prmRet.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(prmRet);
string qrySProc =
"SELECT parameter_name as name"
+ ", data_type as xtype"
+ ", cast(isnull(character_maximum_length, " +
"numeric_scale) as int) as prec"
+ ", case when parameter_mode like '%out%' " +
"then 1 else 0 end as isoutparam"
+ " FROM INFORMATION_SCHEMA.PARAMETERS"
+ " WHERE specific_name = '" + cmd.CommandText + "'"
+ " ORDER BY ordinal_position";
DataTable dt = new DataTable();
new SqlDataAdapter(qrySProc, cmd.Connection).Fill(dt);
foreach (DataRow dr in dt.Rows)
{
SqlParameter prm = new SqlParameter(
(string)dr[0],
_sqlTypeMap[(string)dr[1]],
(int)dr[2]);
if ((int)dr[3] == 1)
prm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm);
}
}
static void EnsureTypeMap()
{
if (_sqlTypeMap == null) {
_sqlTypeMap = new SqlTypeMap();
_sqlTypeMap.Add("bit", SqlDbType.Bit);
_sqlTypeMap.Add("int", SqlDbType.Int);
_sqlTypeMap.Add("smallint", SqlDbType.SmallInt);
_sqlTypeMap.Add("tinyint", SqlDbType.TinyInt);
_sqlTypeMap.Add("datetime", SqlDbType.DateTime);
_sqlTypeMap.Add("smalldatetime",SqlDbType.SmallDateTime);
_sqlTypeMap.Add("char", SqlDbType.Char);
_sqlTypeMap.Add("varchar", SqlDbType.VarChar);
_sqlTypeMap.Add("nchar", SqlDbType.NChar);
_sqlTypeMap.Add("nvarchar", SqlDbType.NVarChar);
}
}
}
#endif
The magic here lies in the fact that SQL knows all about its sprocs and lets us derive the parameter info. Also nifty here is the use of lookup tables - or in C#, DictionaryBase
-derived collections - that lets us cache that info. So, when we ExecSProc
, we get _sprocs[procName]
which looks up the sproc name in our SProcList
. If it's the first time calling this sproc, we NewSProc
and add it to the lookup table. From there we just fill and go.
Thanks go to Ian Barker for pointing out the improved syntax via the params
keyword. Barry Solomon provided ExecSProcDS
, which is handy for returning datasets where your SELECT
statement is in an sproc. This routine could be easily modified to return a DataRowCollection
like QueryRows
, which is a favorite of mine. My queries don't rely on user input (thus not vulnerable to sql injection attack - thanks Curt Drake for the heads up), so I have my SELECT
statements in my C# code, and use QueryRows
as follows:
string qryStr = "SELECT id, name FROM ...";
foreach (DataRow dr in _sqlConn.QueryRows(qryStr)) { ... }
string qryStr = "SELECT count(*) FROM ...";
if ((int)_sqlConn.QueryRows(qryStr)[0][0] == ...
A couple of readers have pointed out that all of this looks a whole lot like MS Application Data Block. I haven't tried it, and don't know exactly what is involved in getting it installed and working. Perhaps one of you will provide some info. *_*
Hope this helps!