A good way to access stored procedure on SQL Server 2008 from C#

Question

I am trying to access stored procedures from my ASP.NET / C# web-application.

Is there a particularly good way to do so (also a good way to pass parameters to it)?

I followed following steps and absolutely don't like the approach, as you enter the procedure as a string (un-debugable): http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx

Answer 1

The best way is to use LINQ to SQL or ADO.NET Entities. Visual Studio will generate corresponding strongly-typed methods for your stored procedures.

Answer 2

If you were to use LinqToSQL, ScottGU has some excellent blog posts on the subject.

Answer 3

I like SubSonic. SubSonic will generate a DAL for you based on your database schema. Most folks use the generated SubSonic objects with the activerecord or simplerepository pattern to work with the database, but Subsonic also generates strongly-typed methods to wrap your stored procedures and views.

It is a rather basic example, but you can make a stored procedure call similar to the following:

SubSonic.StoredProcedure sp = Dal.StoredProcedures.UpdatePassword(id, pw);
DataSet ds = sp.GetDataSet();

In the code above, the StoredProcedures class includes a strongly-typed method for the UpdatePassword stored procedure. It works great for me.

The SubSonic Docs are here.

Answer 4

Using LINQ to SQL or Entity Framework does indeed give you nice strongly typed access to your parameters and results, but there are a lot of considerations to be done before you use either of them. LINQ to SQL is end-of-life and Entity Framework will get a major overhaul in .NET Framework 4.0.

In order to make an informed consideration, I thought I'd share the low level ADO.NET way of doing things:

void CallMyStoredProcedure(string connectionString, string paramValue)
{
    string spName = "MyStoredProcedure";
    string columnValue;

    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(spName, conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // Set parameter values
        cmd.Parameters.AddWithValue("@inputParam",
            (object)paramValue ?? DBNull.Value);

        // Parameter to store return value of your SP
        SqlParameter returnParam = cmd.Parameters.Add(
            new SqlParameter("RETURN_VALUE", SqlDbType.Int));
        returnParam.Direction = ParameterDirection.ReturnValue;

        // Execute SP and read results
        conn.Open();
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            // Do something with return value
            int? returnValue = cmd.Parameters["RETURN_VALUE"].Value as int?;

            while (dr.Read())
            {
                // Suggested way to read results if you use
                // "SELECT * FROM" in your SP. If you return columns
                // in a fixed order, it's fairly safe to be reading
                // in that order without looping
                for (int field = 0; field < dr.FieldCount; field++)
                {
                    switch (dr.GetName(field))
                    {
                        case "StringColumn":
                            columnValue = dr.GetValue(field) as string;
                            break;
                        default:
                            // Column not recognized by your code.
                            // You might choose to complain about it here.
                            break;
                    }
                }
            }
        }
    }
}

Yup, that's quite a bit of code, so you may want to write some helper methods if you want to go vanilla ADO.NET.