calling sprocs from a db layer in c#

Question

suppose you have some simple sprocs eg

AddXYZ(param1, param 2...etc)

getAllXYZ()

getXYZ(id)

what is the "best practice" way of calling these sprocs from the "db layer"

i don't want to use linq. just simple c# static methods on how to do this.

im using sqlserver.

Answer 1

Is this what you are looking for?

SqlCommand cmd  = new SqlCommand("AddXYZ", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@param1", someValue));

Answer 2

In general, then, you'll want to look at the SQLConnection and SQLCommand classes (presuming you're connecting to a SQL db, of course). You'll set the SQLCommand.CommandText property to something like "EXEC AddXYZ @X, @Y, @Z" and then use SQLCommand.Parameters.AddWithValue() for each of @X, @Y, and @Z.

You'll then call your SQLCommand's appropriate execute method (NonQuery, Scalar, or Reader).

Answer 3

I don't think you really meant static methods, as that would be a pretty non-OO way of going about something like this, and no such facility is built into C#. There are standard ADO.NET classes, however, that will let you do this without any ORM wrapping or using DataSet's and the like.-

If you truly want to manually invoke the stored proc and get back a set of results without ANY ORM or standardized storage mechanism doing it for you, this would be your best bet:

using(System.Data.IDbConnection conn = /*create your connection here*/)
{
    using(System.Data.IDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "AddXYZ";

        // add your parameters here using cmd.CreateParameter() and cmd.Parameters.Add()

        using(System.Data.IDbDataReader reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                // read your results row-by-row
            }
        }
    }
}

You didn't specify which database engine you were connecting through, so I used the common interfaces that will abstract you away from that. If you wish (though I generally frown on code that does this) you can use platform-specific classes that make things SLIGHTLY easier, at least in terms of adding parameters (the code isn't as verbose as the interface-based approach)

Answer 4

First, you create a single place in the data layer for getting connection information. This might be a private member if your data layer is confined to a single class, or internal item if the layer encompasses an entire assembly. It could return a connection string or an actual connection object itself, but the main thing is that it's not exposed outside the data layer at all:

private static ConnectionString { get { // read from config file once.... return ""; } }

private SqlConnection getConnection()
{
    SqlConnection result = new SqlConnection(ConnectionString);
    result.Open();  // I like to open it in advance, but that's less common
    return result;  // you'll want some error handling code in here as well
}

You then provide public methods in the data layer that match the interface you want to provide to the business layer. In a well-designed app this will generally match the stored procedures, and that sounds like what you're going for, but sometimes it doesn't quite work work out so well. For example, you may need to call multiple procedures from one method.

Whatever you do, the method should accept strongly-typed parameters values for use when calling the procedures. There's some debate about whether the method should return a business object or a datarecord. Personally, I tend to favor returning a datarecord, but providing an additional "layer" where the datarecords are translated to strongly-typed business objects:

public IDataRecord GetXYZ(int id)
{
    DataTable dt = new DataTable();
    using (var cn = getConnection())
    using (var cmd = new SqlCommand("getXYZ"))
    {
        cmd.CommandType = CommandTypes.StoredProcedure;
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

        using (var rdr = cmd.ExecuteReader())
        {
           dt.Load(rdr);
        }
    }

    //obviously put a little more work into your error handling
    if (dt.Rows.Count <= 0)
       throw new Exception("oops");  

    return dt.Rows[0];
}

public class XYZFactory
{
    public static XZY Create(IDataRecord row)
    {
        XYZ result = new XYZ();
        result.id = row["ID"];
        result.otherfield = row["otherfield"];
        return result;
    }
}

Answer 5

Thomas' example is the standard way. I suggest you look at the following for further help:

http://msdn.microsoft.com/en-us/library/aa902662.aspx

Personally, I've written a static class (DataHelper) which exposes a bunch of methods that you're likely to use such as those that return IDataReader objects for when your stored proc returns stuff, and void methods for when they do not, as well as some which return the Connection object in an out param so you can keep it open.

The class just wraps the ADO.NET code in try catch blocks for common problem checking and logging/tracing (DNS down, server down, svc down), and uses the using statement to ensure that resources aren't wasted. I also wrap a method which uses a ConnectionStringBuilder to return a connection string so I don't have to keep writing that code.

I did find that for a big project, coding all my entity 'providers' was repetitive and I should have designed a more reusable configuration driven model.

These days if I had greenfield I'd look at using the EDM.

http://msdn.microsoft.com/en-us/library/aa697428(VS.80).aspx