Pass multiple parameters with SqlParameter

user_9951940 0
Viewed 5713

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);
asp.net-core sql c#
3 Answers

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[].

user_30945330

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).

user_84475620

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.

user_12885890
Related