Using Dapper with SQL Server

Question

I am currently developing an application using C# (Visual Studio 2019)and SQL Server 2017 using Dapper. Below is a routine that works fine right now to execute a stored procedure in SQL Server.

The C# code uses the MVVM framework, and I currently have 60 model classes to map the tables in the SQL Server database. There are about 120 stored procedures that produce results that are mapped to the model classes using Dapper.

Consider the following code snippet as pseudocode (but it actually works to execute the stored procedure and return the correct results).

public List<SomeDefinedModel> ExecuteSQLSPROC(string SPROCName, SqlParameter[] parameters)
{
    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(DBHelper.CNNVal("MYLocalServer")))
    {
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        command.Connection = (SqlConnection)connection;
        command.CommandText = SPROCName;

        DynamicParameters parms = new DynamicParameters();

        for (int i = 0; i < parameters.Length; i++)
        {
            var parmname = parameters[i].ParameterName;
            var parmvalue = parameters[i].Value;
            parms.Add(parmname, parmvalue);
        }

        var output = connection.Query<SomeDefinedModel>(SPROCName, parms, commandType: CommandType.StoredProcedure).ToList();

        return output;
    }
}

I want to change this routine so the routine so that it method signature is as follows:

public List<TheNameOfTheModelToMap> ExecuteSQLSPROC(string SPROCName, SqlParameter[] parameters, TheNameOfTheModelToMap)

That is, the designation of the model to which Dapper is to map is a variable - it can be any valid model. I have define the variable TheNameOfTheModelToMap as type object, and the return value as var, as List<object>, and so forth. They produce errors because of this syntax:

var output = connection.Query<TheNameOfTheModelToMap>(SPROCName, parms, commandType: CommandType.StoredProcedure).ToList();

I want to comply with the principles of DRY and code just one method to accept ANY model designation, ANY stored procedure designation and have Dapper map the query results. Any possibilities?

What about implementing a generic method like public List<T> ExecuteSQLSPROC<T>(string SPROCName, SqlParameter[] parameters)? — Alberto Chiesa
– Alberto Chiesa, Commented Mar 4, 2021 at 21:33
What is the point of the SqlCommand here, you don't seem to use it, and surely Dapper sorts that out for you anyway — Charlieface
– Charlieface, Commented Mar 5, 2021 at 0:07
Also you should probably use the SqlParameter object directly instead of adding them o the DynamicParameters() collection. You're loosing the parameter directions, which would prevent you from using Out or ReturnValue parameters. — David Browne - Microsoft
– David Browne - Microsoft, Commented Mar 5, 2021 at 0:52
You want public List<TheNameOfTheModelToMap> ExecuteSQLSPROC(string SPROCName, SqlParameter[] parameters, object TheNameOfTheModelToMap)? It's not clear from your signature what the type of the TheNameOfTheModelToMap parameter is. — Connor Low
– Connor Low, Commented Mar 5, 2021 at 1:29

Alberto Chiesa · Accepted Answer · 2021-03-04 21:41:12Z

1

I think you just need one generic method like so:


public List<T> ExecuteSQLSPROC<T>(string SPROCName, SqlParameter[] parameters)
{
    using (var connection = new System.Data.SqlClient.SqlConnection(DBHelper.CNNVal("MYLocalServer")))
    {
        var parms = new DynamicParameters();
        foreach (var parameter in parameters)
            parms.Add(parameter.ParameterName, parameter.Value);

        var output = connection.Query<T>(
                SPROCName,
                parms,
                commandType: CommandType.StoredProcedure
            )
            .ToList();

        return output;
    }
}

And every other method becomes a specialized invoke of it:

public List<SomeDefinedModel> ExecuteOneProc(SqlParameter[] parameters)
  => ExecuteSQLSPROC<SomeDefinedModel>("OneProcName", parameters);

Is this ok for your use case? If you need one procedure for single values (which would not use Dapper's Query method) you would just create specialized variations of the ExecuteSQLSPROC like ExecuteStoredProcSingleRow or something along those lines.

answered Mar 4, 2021 at 21:41

Alberto Chiesa

7,4702 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JustDavid Over a year ago

I used the following syntax to invoke the new version of ExecuteSQLSPROC. The method ExecuteSQLSPROC compiles correctly as suggested but the following code to invoke the new method: public List ExecuteSQLSPROC(SqlParameter[] parameters) => ExecuteSQLSPROC("ReturnCarDetails", parameters); will not compile and it produces the following errors: CS0106 The modifier 'public'‌ is not valid for this item CS0308: The non-generic method "ExecuteSQL(SqlParameter[])' cannot be used with type arguments (note: the above error refers to everything following the lambda expression)

Alberto Chiesa Over a year ago

Well, as in my example, you have to specify the type returned by the stored procedure. Something like:

List<CarDetail> ExecuteSQLSPROC(SqlParameter[] parameters) => ExecuteSQLSPROC<CarDetail>("ReturnCarDetails", parameters);

That is, returning a List<T> and not a List, and calling the generic method specifying the generic type argument.

Collectives™ on Stack Overflow

Using Dapper with SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related