时间戳

首页 > 解决方案 > 优化SqlCommand的使用

c# - 优化SqlCommand的使用

问题描述

我从 C# 执行一个存储过程，如下所示：

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

ExeSQLParamByDateTime方法太大：

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
}

有没有办法优化这个？我只是检查好的做法，如您所见，我发送参数 DataTable 来执行 sqlTableType并发送参数，但我没有发现类似的东西。帮助表示赞赏。问候

标签： c#sql-serverstored-proceduressqlcommand

解决方案

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;
}

推荐阅读