C# "painless" way to call stored procedures with parameters?

Question

I'm looking for the absolute easiest way to call stored procedures from C# without explicit parameter objects, like so:

using (DataTable dt=conn.ExecuteQuery("MySP", param1, "param2", p3, p4)) {

On first invocation the library queries the DB schema for the SP signature then caches it for subsequent calls.

A) Is there any way to do it THIS SIMPLY with the Enterprise Library Data Access Block?

B) I don't find ORMs attractive because of synchronization issues between schema and code metadata.

I DID find this generator-less wrapper but am hoping there is a major library or best practice I somehow just haven't discovered yet.

**Best** is an opinion, please consider rephrasing. – SQLMason Nov 20 '13 at 20:23 — SQLMason, Nov 20 '13 at 20:23

score 1 · Answer 1 · answered Nov 20 '13 at 20:57

Dapper?

var rows = conn.Query("procname",
    new { name = "abc", id = 123 }, // <=== args, fully named and typed
    commandType: CommandType.StoredProcedure
).ToList();

The above is the dynamic API which allows automatic binding to column names:

foreach(var row in rows) {
    int x = row.X; // look ma, no column mappings
    string y = row.Y;
    //...
}

But you can also use Query<SomeType> and it will populate an object model for you. When binding to an object model it includes all the meta-programming / caching you might expect from people obsessive-compulsive about performance. Hint: I usually use the generic API - it is very very fast.

score 1 · Accepted Answer · answered Nov 20 '13 at 21:00

I do have an example of an SqlDataReader where the Function call is

ExecuteNonQuery("dbo.[Sp_Skp_UpdateFuncties]", parameters);

This is in a class DataBaseManager which hold the databaseconnectionstring

public classDataBaseManager
{
...
public int ExecuteStoredProcedure(string storedprocedureNaam, IEnumerable<KeyValuePair<string, object>> parameters)
{
    var sqlCommand = new SqlCommand
    {
        Connection = DatabaseConnectie.SqlConnection,
        CommandType = CommandType.StoredProcedure,
        CommandText = storedprocedureNaam,
    };

    foreach (KeyValuePair<string, object> keyValuePair in parameters)
    {
        sqlCommand.Parameters.Add(
            new SqlParameter { ParameterName = "@" + keyValuePair.Key, Value = keyValuePair.Value ?? DBNull.Value }
        );
    }

    if (sqlCommand == null)
        throw new KoppelingException("Stored procedure ({0}) aanroepen lukt niet", storedprocedureNaam);
    return sqlCommand.ExecuteNonQuery();
}
....
}

C# "painless" way to call stored procedures with parameters?

2 Answers2