What's the most elegant way to use stored procedures?

Question

I'm re-writing the inline SQL in my repository class to use stored procedures instead (security requirement). After using Fluent NHibernate and Linq2Sql in the past I'm finding it to be extremely unwieldy and inelegant.

EDIT: To clarify, I'm not looking for an ORM solution that works with stored procs. I just want some advice on a nice way to write the code below.

Are there any strategies for making this sort of code as elegant as possible?

        string commandText = "dbo.Save";

        using (SqlConnection sql = new SqlConnection(_connString.ConnectionString))
        using (SqlCommand cmd = sql.CreateCommand())
        {
            cmd.CommandText = commandText;
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter idParam = new SqlParameter("identity", item.Identity);
            idParam.Direction = ParameterDirection.Input;

            SqlParameter nameParam = new SqlParameter("name", item.Name);
            nameParam.Direction = ParameterDirection.Input;

            SqlParameter descParam = new SqlParameter("desc", item.Description);
            descParam.Direction = ParameterDirection.Input;

            SqlParameter titleParam = new SqlParameter("title", item.)
            descParam.Direction = ParameterDirection.Input;

            //SNIP More parameters

            cmd.Parameters.Add(idParam);
            cmd.Parameters.Add(descParam);
            cmd.Parameters.Add(titleParam);
            //SNIP etc

            sql.Open();

            cmd.ExecuteNonQuery();

            //Get out parameters
        }

        return item;

Answer 1

You could use SubSonic as an ORM layer between your class and the stored procedures. Here's a basic example. Phil Haack has a good article on it as well.

There's some good info in this other question.

EDIT: Since you've updated your question to indicate you don't want to use an ORM, SubSonic is not for you. However, I'll leave the answer here for other folks who use stored procs. :) You should also take a look at if there's even a possibility you can use it.

Answer 2

You could halve the linecount by deriving your own InputSqlParameter from SqlParameter and setting the direction to Input in the constructor.

That would let you write

    cmd.Parameters.Add(new InputSqlParameter("title", item.title));
    cmd.Parameters.Add(new InputSqlParameter("property", item.property));

This shows a pattern, and lets you setup a list of parameter names and item fields and do the parameter adding in a for loop.

Answer 3

Keep each parameter for a given stored procedure in a "data class." Use annotations to specify things like "in" or "out" sproc parameters.

Then you can load up the class from client code, then use reflection to build all the parameters for the sproc, execute the sproc, and load the output parameters back into the data class.

Slightly more clean: Have one class for inputs and another for outputs (even if some are in/out). That way it's clear (to client code) which parameters need to be filled on the way in and which get returned. Also this obviates the need for those annotations.

Answer 4

Hi Rob

Within our internal applications we generally use the SqlHelper class which can be found at the following link (download and description): http://www.microsoft.com/downloads/details.aspx?familyid=f63d1f0a-9877-4a7b-88ec-0426b48df275&displaylang=en

Essentially the SqlHelper class takes away some of the need to declare connection objects, commands etc and allows you to call methods to return objects such as DataSet

You might then use SqlHelper as such:

public static int UpdateItem(int parameter1, int parameter2, string parameter3)
    {
        SqlParameter[] arParam = new SqlParameter[3];
        arParam[0] = new SqlParameter("@Parameter1", lotId);
        arParam[1] = new SqlParameter("@Parameter2", saleId);
        arParam[2] = new SqlParameter("@Parameter3", lotNumber);


        return int.Parse(SqlHelper.ExecuteScalar(connString, CommandType.StoredProcedure, "spName", arParam).ToString(), CultureInfo.InvariantCulture);
    }

Hope this helps :)

Answer 5

using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "[dbo].[Save]";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter(
        "Identity", SqlDbType.Int) { Value = item.Identity });

    cmd.Parameters.Add(new SqlParameter(
        "Name", SqlDbType.NVarChar, 50) { Value = item.Name });

    cmd.Parameters.Add(new SqlParameter(
        "Title", SqlDbType.NVarChar, 100) { Value = item.Title });

    conn.Open();
    cmd.ExecuteNonQuery();
}

Here is how it could look like with Ent Lib:

// Note, that you don't need to specify connection string here,
// it will be automatically taken from a configuration file
var db = DatabaseFactory.CreateDatabase();

using (var cmd = db.GetStoredProcCommand("[dbo].[Save]"))
{
    db.AddInParameter(cmd, "Identity", DbType.Int32, item.Identity);
    db.AddInParameter(cmd, "Name", DbType.String, item.Name);
    db.AddInParameter(cmd, "Title", DbType.String, item.Title);
    db.ExecuteNonQuery(cmd);
}

You can also use SqlHelper method from Enterprise Library to simplify this syntax.

SqlHelper.ExecuteNonQuery(connectinString,
     CommandType.StoredProcedure, "[dbo].[Save]", new SqlParameter[]
         {
             new SqlParameter("Identity", item.Identity),
             new SqlParameter("Name", item.Name),
             new SqlParameter("Title", item.Title)
         });

Answer 6

Thats funny I acutally asked this same question. Still looking for a good solution.

http://stackoverflow.com/questions/687762/which-orm-is-the-best-when-using-stored-procedures

Answer 7

Grab a copy of Enterprise Library. It is a nice wrapper around ADO. For example:

using System.Data.Common;
using System.Globalization;
using Microsoft.Practices.EnterpriseLibrary.Data;

Database db = DatabaseFactory.CreateDatabase(DatabaseType.MyDatabase.ToString());

using (DbCommand dbCommand = db.GetStoredProcCommand("dbo.MyStoredProc")) {
    db.AddInParameter(dbCommand, "identity", DbType.Int32, item.Identity);
    db.AddInParameter(dbCommand, "name", DbType.String, item.Name);
    db.AddInParameter(dbCommand, "desc", DbType.String, item.Description);
    db.AddInParameter(dbCommand, "title", DbType.String, item.Title);

    db.ExecuteNonQuery(dbCommand);
} // using dbCommand

Answer 8

I would recommend using the Microsoft Application Blocks SqlHelper object.

For a statement like you have listed above, I can do the following.

SqlHelper.ExecuteNonQuery(_connectionString, "MyProcName", 1, "NameValue", "Description", "Title");

Basically SQL Helper takes a few parameters.

1. The Connection String to connect to the db
2. The name of the stored procedure
3. An array of parameter values, in the order in which they appear in the Stored Procedure.

There is a VERY slight performance drawback with this method over explicitly creating each parameter, but the time savings usually outbalances it since it is so small.

Answer 9

I usually use some variation of the following example, depending on the environment of course:

My basic helper methods that I call throughout my code

public static SqlCommand CreateStoredProcCmd(string name, SqlConnection con)
{
    var cmd = new SqlCommand(name, con);
    cmd.CommandType = CommandType.StoredProcedure;
    return cmd;
}

public static void AddParams(this SqlCommand cmdObject, Params SqlParameter[] parameters)
{
  foreach(SqlParameter param in parameters)
  {
    cmdObject.Parameters.add(param);
  }
}

/* Any overloaded methods to create params receiving my param definitions 
in any manner that the usual new SqlParameter() constructor doesn't handle */
public static SqlParameter CreateSqlParam(string ParamName,
                                          SqlDbType ParamType,
                                          object value)
{
    return CreateSqlParam(ParamName, ParamType, ParameterDirection.Input, value);
}

public static SqlParameter CreateSqlParam(string ParamName, 
                                          SqlDbType ParamType, 
                                          ParameterDirection ParamDir)
{
    return CreateSqlParam(ParamName, ParamType, ParamDir, null;
}                          

public static SqlParameter CreateSqlParam(string ParamName, 
                                          SqlDbType ParamType, 
                                          ParameterDirection ParamDir,
                                          object value)
{
    var parm = new SqlParameter(ParamName, ParamType);
    parm.Direction = ParamDir;
    parm.Value = value;
    return parm;
}

Now here's how I set up my stored procs and add all my parameters elegantly

public static string DoStuff()
{
    using (var oCon = new SqlConnection("MyConnectionString"))
    {
        oCon.Open();
        var oCmd = CreateStoredProcCmd("sp_Name", oCon).AddParams(
            CreateSqlParam("Param1", SqlDBType.Int, 3),
            CreateSqlParam("Param2", SqlDBType.VarChar, "Hello World"),
            CreateSqlParam("Param3", SqlDBType.VarChar, ParameterDirection.Output)
        );
        oCmd.Prepare();
        oCmd.ExecuteNonQuery();
        object outVal = oCmd.Parameters["Param3"];
        return null != outVal ? outVal.ToString() : String.Empty;
    }
}

Answer 10

To make the code a little less verbose i've always added parameters using

cmd.Parameters.AddWithValue("name", item.Name);
cmd.Parameters.AddWithValue("title", item.Title);
// and so on

Answer 11

Input is the default direction and you can shorten the parameter adding and probably want to declare the SqlDBTypes as well...

cmd.Parameters.Add("identity", SqlDBType.???).Value = item.Identity;
cmd.Parameters.Add("desc", SqlDbType.???, ?size?).Value = item.Description;
cmd.Parameters.Add("title", SqlDBType.???, ?size?).Value = item.Title;

//Output params generally don't need a value so...
cmd.Parameters.Add("someOut", SqlDBType.???).Direction = ParameterDirection.Output;