以输入变量为条件的SQL选择

Question

我必须在C#中的一个方法中执行SQL Server查询。场景是：

该方法接受的变量为string category。如果值为category = "Heterogeneous"，则我必须选择为：

SqlCommand myCommand = con.CreateCommand();
if (simCategory == "Heterogeneous")
{
   myCommand.CommandText = @"SELECT ProbabilityHeterogeneous FROM Graph  
   WHERE SourceID = @sourceID AND DestinationID = @destinationID";
}
else if (simCategory == "Low")
{
   myCommand.CommandText = @"SELECT ProbabilityLow FROM Graph  
   WHERE SourceID = @sourceID AND DestinationID = @destinationID";
}
else if (simCategory == "Medium")
{
   myCommand.CommandText = @"SELECT ProbabilityMedium FROM Graph  
   WHERE SourceID = @sourceID AND DestinationID = @destinationID";
}
else if (simCategory == "High")
{
   myCommand.CommandText = @"SELECT ProbabilityHigh FROM Graph  
   WHERE SourceID = @sourceID AND DestinationID = @destinationID";
}

myCommand.Parameters.AddWithValue("@sourceID", sID);
myCommand.Parameters.AddWithValue("@destinationID", dID);

using (SqlDataReader myReader = myCommand.ExecuteReader())
{
   while (myReader.Read())
   {
      inNeighborActivationProbability = Convert.ToDouble(myReader["Probability"]);
   }
   myReader.Close();
}

现在，这是真的吗？

Answer 1

你的代码(真的)没有任何问题。你可能认为有原因，是因为你似乎重复了很多代码，这让人感觉不是“正确”或“高效”。如果您希望保留在c#代码中使用哪个字段的决定权，那么您可以像这样更高效地执行操作：

        string fieldName;
        switch (simCategory)
        {
            case "Heterogeneous":
                fieldName = "ProbabilityHeterogeneous";
                break;

            case "Low":
                fieldName = "ProbabilityLow";
                break;

            case "Medium":
                fieldName = "ProbabilityMedium";
                break;

            case "High":
                fieldName = "ProbabilityHigh";
                break;

            default:
                throw new ArgumentOutOfRangeException(nameof(simCategory), "Unsupported Simulation Category requested.");
        }

        SqlCommand myCommand = con.CreateCommand();
        myCommand.CommandText = string.Format("SELECT {0} as Probability FROM Graph WHERE SourceID = @sourceID AND DestinationID = @destinationID", fieldName);
        myCommand.Parameters.AddWithValue("@sourceID", sID);
        myCommand.Parameters.AddWithValue("@destinationID", dID);

        using (SqlDataReader myReader = myCommand.ExecuteReader())
        {
             etc...

这应该更容易阅读和维护，以防您需要添加字段。

如果出于某种原因，您希望在SQL中进行决策(例如，当您将逻辑放在存储过程中时，您可以扩展数据库模型和功能，而不必升级每台客户端计算机)，您可以构建如下SQL代码：

SELECT (CASE @parameter WHEN 'Heterogeneous' THEN ProbabilityHeterogeneous
                        WHEN 'Low'           THEN ProbabilityLow
                        WHEN 'Medium'        THEN ProbabilityMedium
                        WHEN 'High'          THEN ProbabilityHigh
                                             ELSE NULL END) as Probability 
    FROM Graph 
    WHERE SourceID = @sourceID 
    AND DestinationID = @destinationID

请记住，在这种情况下，“错误的”参数值不会导致错误，而只是返回NULL作为值。

Answer 2

使用Switch使其更加清晰。此外，您还需要在Probabilty上使用别名。所以无论哪个用户选择，你都可以在最后调用它：myReader["Probability"]

最后，尝试包含一个默认选项，以防万一。

SqlCommand myCommand = con.CreateCommand();

switch (simCategory)
{
    case "Heterogeneous":
       myCommand.CommandText = @"SELECT ProbabilityHeterogeneous as Probability FROM Graph  
                 WHERE SourceID = @sourceID AND DestinationID = @destinationID";
       break;

    case  "Low":
       myCommand.CommandText = @"SELECT ProbabilityLow  as Probability FROM Graph  
                 WHERE SourceID = @sourceID AND DestinationID = @destinationID";
       break;

    case  "Medium":
       myCommand.CommandText = @"SELECT ProbabilityMedium  as Probability FROM Graph  
                 WHERE SourceID = @sourceID AND DestinationID = @destinationID";
       break;

    case "High": 
       myCommand.CommandText = @"SELECT ProbabilityHigh as Probability FROM Graph  
                 WHERE SourceID = @sourceID AND DestinationID = @destinationID";
       break;

    default:
          Console.WriteLine("Default case Rise ERROR?");
          break;
}

myCommand.Parameters.AddWithValue("@sourceID", sID);
myCommand.Parameters.AddWithValue("@destinationID", dID);

using (SqlDataReader myReader = myCommand.ExecuteReader())
{
   while (myReader.Read())
   {
      inNeighborActivationProbability = Convert.ToDouble(myReader["Probability"]);
   }
   myReader.Close();
}

Answer 3

我看到您的列名类似于'Probability*‘，并以simCategory结尾，因此我建议下面的代码更清晰

注意:您还需要处理simCategory是否为null或空。

string sql = "";
switch (simCategory)
{
 case "Heterogeneous":
 case  "Low":
 case  "Medium":
 case "High":        
     sql=simCategory;
     break;
 default:
       // handle error
     break;
}

SqlCommand myCommand = con.CreateCommand();
myCommand.CommandText = string.Format(@"SELECT Probability{0}  as Probability FROM Graph WHERE SourceID =
                    @sourceID AND DestinationID = @destinationID", sql); 
myCommand.Parameters.AddWithValue("@sourceID", sID);
myCommand.Parameters.AddWithValue("@destinationID", dID);

using (SqlDataReader myReader = myCommand.ExecuteReader())
{
 while (myReader.Read())
  {
   inNeighborActivationProbability = Convert.ToDouble(myReader["Probability"]);
  }
  myReader.Close();
}