Pass multiple parameters with SqlParameter

Question

I know how to pass one parameter to an sql query but i want to create a function to pass multiple params that will have differents type and here im stuck.

public List<T> RawSql<T>(string query,  params object[] parameters)
{
    var command = context.Database.GetDbConnection().CreateCommand();

    command.CommandText = query;
    command.CommandType = CommandType.Text;

    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "@bookId";
    parameter.SqlDbType = SqlDbType.Int;

    parameter.Value = parameters[0];

    command.Parameters.Add(parameter);

    var result = command.ExecuteReader())

    return result;
}

Usage :

var rows = helper.RawSql("myStoreProc @bookId", x=> new Book { Id = (bool)x[0] }, bookId);

But how i can change the RawSql function to pass multiple parameters like this :

var rows = helper.RawSql("myStoreProc @bookId, @authorName", x=> new Book { Id = (bool)x[0] }, bookId, authorName);

Your problem here is going to be how to name the parameters. Frankly, you're making your life hard here. A tool to do this already exists, by name of "dapper"; then you can just use: var data = connection.Query<Book>("myStoreProc", new { bookId, authorName }, commandType: CommandType.StoredProcedure).AsList(); - which will do everything you would expect, including column-name based data binding of Book (optimized, etc) (note: methods exist for all the usual scenarios) — Marc Gravell
– Marc Gravell, Commented Nov 6, 2018 at 22:35
Why not create more SqlParamters and add them to command.Parameters? — devlin carnate
– devlin carnate, Commented Nov 6, 2018 at 22:37
There is an overload for params that can be done in one line - hardly a need for a "helper" in the first place: cmd.Parameters.Add(string, dbtype).Value = data The collection creates the parameter, names it, tells it the type and sets the value in one easy line of code. ORMs like Dapper allow that syntax you have at the end if that is critical — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Nov 6, 2018 at 22:50

Zohar Peled · Accepted Answer · 2018-11-06 22:49:39Z

1

I would also suggest using Dapper instead of reinventing the wheel - but if you can't for some reason, I would change the method signature to accept params SqlParameter[] parameters instead of params object[] parameters - and then all you need to do in the method is command.Parameters.AddRange(parameters);.

As Marc Gravel wrote in his comment - naming the parameters is going to be the biggest problem if you are simply using object[].

answered Nov 6, 2018 at 22:49

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

1 Comment

lory Over a year ago

Im using .net Core 2.0 hence i cannot use the .Query function. I didnt know about Dapper. I will check this out thanks

Jaskier · Accepted Answer · 2018-11-06 22:53:47Z

Here is a method I wrote to compare values from two different days:

public DataTable sqlToDTCompare(string conStr, string stpName, DateTime startDate, DateTime endDate, int percent)
    {
        //receives connection string and stored procedure name
        //then returns populated data table
        DataTable dt = new DataTable();

        using (var con = new SqlConnection(conStr))
        using (var cmd = new SqlCommand(stpName, con))
        using (var da = new SqlDataAdapter(cmd))
        {
            cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = startDate;
            cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = endDate;
            cmd.Parameters.Add("@Percent", SqlDbType.Int).Value = percent;
            cmd.CommandType = CommandType.StoredProcedure;
            da.Fill(dt);
        }

        return dt;
    }

This method then returns that data to a DataTable (was what I needed at time of writing). You would be able to use this , with modifying to be of better fit for your needs.

What you're looking to use is something along:

SqlCommand.Parameters.Add("@Param1", SqlDbType.Type).Value = param1;
SqlCommand.Parameters.Add("@Param2", SqlDbType.Type).Value = param2;
SqlCommand.Parameters.Add("@Param3", SqlDbType.Type).Value = param3;
.....

Where .Type in SqlDbType.Type can be changed to matche whatever SQL datatype you're needing (ex. SqlDbType.Date).

Greg · Accepted Answer · 2018-11-06 23:18:43Z

I have previously done implementations along these lines.

public IEnumerable<SampleModel> RetrieveSampleByFilter(string query, params SqlParameter[] parameters)
{
     using(var connection = new SqlConnection(dbConnection))
          using(var command = new SqlCommand(query, connection))
          {
              connection.Open();
              if(parameters.Length > 0)
                  foreach(var parameter in parameters)
                       command.Parameters.Add(parameter);

                       // Could also do, instead of loop:
                       // command.Parameters.AddRange(parameters);

              using(var reader = command.ExecuteReader())
                   while(reader != null)
                        yield return new Sample()
                        {
                             Id = reader["Id"],
                             ...
                        }                   
          }
}

I actually wrote an extension method to read the values returned back into my object, but this allows you to pass a query and a series of parameters to simply return your object.

I would look into Dapper, saves a lot of time. But I find the problem with trying to reuse with the above type of solution creates a bit of tightly coupling often.

By doing this approach you push specific information about your query elsewhere, which separates logic directly out of the repository and tightly couples to another dependency and knowledge.

Collectives™ on Stack Overflow

Pass multiple parameters with SqlParameter

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related