如何使用C#对Select Query进行个性化设置

Question

我想创建一个函数，允许动态获取SELECT查询。所以我考虑过使用stringjoin和string.format，但我做不到。下面是我的代码：

private void ExecuteQuery()
{
string databaseName = "Cars";
string tableName= "Berline";
List<string> colNames = new List<string>{"NAME","TYPE","REV"};
List<string> colValues = new List<string>{"Nitro","N1-55","1.00"};
Console.WriteLine( GetSelectQuery( databaseName , tableName, colNames , colValues );
}
private string GetSelectQuery( string databaseName, string tableName, List<string> colNames, List<string> colValues)
{
var selectColValuesQuery = string.Format( "{0} = {1}", colNames.Select(n => string.Format("`{0}`", n)), colValues.Select(n => string.Format("'{0}'", n)));
return string.Format("SELECT * FROM {0}.{1} WHERE {2};", databaseName, tableName, selectColValuesQuery);
}

预期输出

SELECT * FROM `Cars`.`Berline` WHERE `NAME`='Nitro' AND `TYPE`='N1-55' AND `REV`='1.00';

Answer 1

我会使用Parameter类来存储键/值对。

public class Parameter
{
public string Key { get; set; }
public string Value { get; set; }
}

然后使用string.join到lambda来连接值。

private static void ExecuteQuery()
{
string databaseName = "Cars";
string tableName = "Berline";
List<Parameter> para=new List<Parameter>()
{
new Parameter()
{
Key = "NAME",
Value = "Nitro"
},
new Parameter()
{
Key = "TYPE",
Value = "N1-55"
},
new Parameter()
{
Key = "REV",
Value = "1.00"
}
};
Console.WriteLine(GetSelectQuery(databaseName, tableName, para));
}
private static string GetSelectQuery(string databaseName, string tableName, List<Parameter> para)
{

var selectColValuesQuery = string.Join(" AND ", para.Select(x => $"{x.Key}={x.Value}").ToArray());
return $"SELECT * FROM {databaseName}.{tableName} WHERE {selectColValuesQuery};";
}

注意：

我建议使用Parameterize而不是concat SQL字符串来避免SQL注入。

Answer 2

如果您想使用参数化命令来避免注入，可以使用以下命令

private void ExecuteQuery(SqlConnection mySqlConnection, SqlTransaction mySqlTransaction)
{
string databaseName = "Cars";
string tableName = "Berline";
List<string> colNames = new List<string> { "NAME", "TYPE", "REV" };
List<string> colValues = new List<string> { "Nitro", "N1-55", "1.00" };

using (SqlCommand cmd = new SqlCommand(GetSelectQuery(databaseName, tableName, colNames), mySqlConnection, mySqlTransaction))
{
for (int i = 0; i < colNames.Count; i++)
cmd.Parameters.AddWithValue("@val"+i, colValues[i]);
//Execute your query and read values
}

}

private string GetSelectQuery(string databaseName, string tableName, List<string> colNames)
{
string[] whereConditions = new string[colNames.Count];
for(int i=0; i< colNames.Count; i++)
whereConditions[i] = colNames[i] + "=@val" + i;
string selectColValuesQuery = string.Join(" AND ", whereConditions);
return string.Format("SELECT * FROM [{0}.{1}] WHERE {2};", databaseName, tableName, selectColValuesQuery);
}

不幸的是，我认为没有办法打印最终的命令字符串(至少，我不能(。如果有人知道如何使用参数化命令，那就太好了。