How to use stored procedures?

Question

I have many stored procedures. To use them, I create a static class with static methods like this:

public class DBHandler
{
    private static Database db = DatabaseFactory.CreateDatabase("db");

    public static DataTable GetItems(long filterId)
    {
        DbCommand command = db.GetStoredProcCommand("dbo.P_GetItems");
        db.AddInParameter(command, "@filter_id", DbType.Int64, filterId);
        IDataReader reader = db.ExecuteReader(command);
        DataTable dt= new DataTable();
        dt.Load(reader, LoadOption.OverwriteChanges);
        reader.Close();
        return dt;
    }
    .
    .
    .
    .
}

As I already wrote, I have a lot of stored procedure, and repeating this code is annoying. Is there a better way to call a stored procedure?

Answer 1

You can use the command line utility sqlmetal.exeto automatically generate data context code that can be used in simple LINQ queries.

Answer 2

DAL. , IDisposable requriements , . . - , .

Answer 3

I like things like this:

    public static IEnumerable<IDataRecord> SqlRetrieve(string ConnectionString, string StoredProcName, Action<SqlCommand> addParameters)
    {
        using (SqlConnection cn = new SqlConnection(ConnectionString))
        using (SqlCommand cmd = new SqlCommand(StoredProcName, cn))
        {
            cn.Open();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            if (addParameters != null)
            {
                addParameters(cmd);
            }

            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                    yield return rdr;
            }
        }
    }

What you get here is a reusable counter (you can change it to return a table), which the delegate accepts to provide parameters.

In real use, it looks something like this:

foreach (var dr in 
        SqlRetrieve(cn, sp,
                    delegate (SqlCommand cmd) {
                        cmd.Parameters.Add("@paramname", System.Data.SqlDbType.Int).Value = someint;
                    }
        )
) {
}

Thus, you do not repeat all the same code, but only parts of the parameters. In your case, if you always return DataTables instead of getting a lot of code in a DataReader loop, the code can become even simpler.