Optimize usage of SqlCommand

Question

I execute a stored procedure from C# like this:

medianOfProjects = db.ExeSQLParamByDateTime("usp_TaskStatistics_Median_Calculation", parameters, "@TaskTypeTableType", 1, startDate, endDate
                     unitNumberFrom, unitNumberTo, unitNumberBldgsSegsFrom, unitNumberBldgsSegsTo, unitNumberSqrFrom, unitNumberSqrTo, unitNumberWoodStoriesFrom,
                unitNumberWoodStoriesTo, currentRegionKey);

ExeSQLParamByDateTime method is too large:

public DataTable ExeSQLParamByDateTime(string sprocName, DataTable paramArray, string tableTypeName, int LegacyKey, DateTime startingDate, DateTime endingDate,
            int unitNumberFrom, int unitNumberTo, int BldgSegsFrom, int BldgSegsFromTo, int SquareFootageFrom, int SquareFootageTo, int WoodStoriesFrom,
            int WoodStoriesTo, int StatusKey)
{
   SqlCommand cmd = new SqlCommand(sprocName, this.dbconn);

   var startDate = startingDate.Date;
   var endDate = endingDate.Date;

   cmd.CommandType = CommandType.StoredProcedure;

   cmd.Parameters.Add(new SqlParameter(tableTypeName, SqlDbType.Structured));
   cmd.Parameters[tableTypeName].Value = paramArray;

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(new SqlParameter("@LegacyKey", SqlDbType.Int));
   cmd.Parameters["@LegacyKey"].Value = LegacyKey;

   cmd.Parameters.Add(new SqlParameter("@StartingDate", SqlDbType.DateTime));
   cmd.Parameters["@StartingDate"].Value = startDate;

   cmd.Parameters.Add(new SqlParameter("@EndingDate", SqlDbType.DateTime));
   cmd.Parameters["@EndingDate"].Value = endDate;

   cmd.Parameters.Add(new SqlParameter("@UnitNumberFrom", SqlDbType.Int));
   cmd.Parameters["@UnitNumberFrom"].Value = unitNumberFrom;

   cmd.Parameters.Add(new SqlParameter("@UnitNumberTo", SqlDbType.Int));
   cmd.Parameters["@UnitNumberTo"].Value = unitNumberTo;

   //etc
   //etc
}

There is no way to optimize this? I just checking for good practices, as you can see I send param DataTable to execute sql TableType and in addition send parameters, but I don't found anything similar like this. Help is appreciated. regards

I think he just means reduce the amount of code. Two lines per parameter (3 including the white space) + a lot of parameters can get tedious. — Joel Coehoorn
– Joel Coehoorn, Commented Aug 23, 2018 at 3:19
If you use a micro ORM like Dapper or PetaPoco it will reduce a fair amount of this kind of boilerplate. — mjwills
– mjwills, Commented Aug 23, 2018 at 6:49

Joel Coehoorn · Accepted Answer · 2018-08-23 19:10:34Z

3

public DataTable ExeSQLParamByDateTime(string sprocName, DataTable paramArray, string tableTypeName, int LegacyKey, DateTime startingDate, DateTime endingDate,
        int unitNumberFrom, int unitNumberTo, int BldgSegsFrom, int BldgSegsFromTo, int SquareFootageFrom, int SquareFootageTo, int WoodStoriesFrom,
        int WoodStoriesTo, int StatusKey
        )
{
    var result = new DataTable();

    //Not good to re-use the same connection object.
    // ADO.Net is designed to use connection pooling, which means you want a new connection each time.
    // Instead, just re-use the connection string
    using (var cn = new SqlConnection(this.dbconn.ConnectionString))
    using (var cmd = new SqlCommand(sprocName, cn))
    {
        cmd.CommandType = CommandType.StoredProcedure; //only need to do this once

        //Most parameters can get down to a single line
        cmd.Parameters.Add(tableTypeName, SqlDbType.Structured).Value = paramArray;
        cmd.Parameters.Add("@LegacyKey", SqlDbType.Int).Value = LegacyKey;
        cmd.Parameters.Add("@StartingDate", SqlDbType.DateTime).Value = startingDate.Date;
        cmd.Parameters.Add("@EndingDate", SqlDbType.DateTime).Value = endingDate.Date;
        cmd.Parameters.Add("@UnitNumberFrom", SqlDbType.Int).Value = unitNumberFrom;
        cmd.Parameters.Add("@UnitNumberTo", SqlDbType.Int).Value = unitNumberTo;
        //etc
        //etc

        //you can also handle parameters with size scopes this way:
        cmd.Parameters.Add("@FakeParam", SqlDbType.Decimal, 5, 2).Value = 123.45;
        cmd.Parameters.Add("@AlsoFake", SqlDbType.NVarChar, 30).Value = "Hello World";

        cn.Open(); // wait as long as possible to open the connection
        using (var rdr = cmd.ExecuteReader())
        {
            result.Load(rdr);
            rdr.Close();
        }
    } //using block handles closing the connection, even if an exception is thrown
    return result;
}

edited Aug 23, 2018 at 19:10

answered Aug 23, 2018 at 3:14

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

3 Comments

JohnyL Over a year ago

using eliminates the need for closing the reader. Dispose will close reader eventually.

mjwills Over a year ago

The documentation for SqlDataReader doesn't make the same claim for the reader. msdn.microsoft.com/en-us/library/… states The base implementation of this method calls the Close method if disposing is set to true.

Joel Coehoorn Over a year ago

Well that's a fairly recent change :) Welcome to programming, where everything you think you know is just a snapshot of how things were at some point in the past.

Collectives™ on Stack Overflow

Optimize usage of SqlCommand

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related