C# Linq ExecuteCommand 不理解空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/859985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq ExecuteCommand doesn't understand nulls
提问by Samuel Carrijo
I'm having a problem when passing nulls to a ExecuteCommand() method using linq. My code is similar to the one that follows:
使用 linq 将空值传递给 ExecuteCommand() 方法时遇到问题。我的代码类似于以下代码：
public void InsertCostumer(string name, int age, string address)
{
List<object> myList = new List<object>();
myList.Add(name);
myList.Add(age);
myList.Add(address);
StringBuilder queryInsert = new StringBuilder();
queryInsert.Append("insert into Customers(name, address) values ({0}, {1}, {2})");
this.myDataContext.ExecuteCommand(queryInsert.ToString(), myList.ToArray());
}
But, when a parameter is null (address, for instance), I get the following error: "A query parameter cannot be of type 'System.Object'."
但是，当参数为空（例如地址）时，我会收到以下错误：“查询参数的类型不能为‘System.Object’。”
The error doesn't occur if no parameters are null. I know the design in my example is a little poor, I just created a simplified example to focus on the problem. Any suggestions?
如果没有参数为空，则不会发生该错误。我知道我的例子中的设计有点糟糕，我只是创建了一个简化的例子来关注这个问题。有什么建议？
采纳答案by Kevin Berridge
This is a known bug and Microsoft does not intend to fix it...
这是一个已知错误，Microsoft 不打算修复它...
The work around is to either:
解决方法是：
- Drop into ADO.NET and execute the SQL Command directly
- Format the string you're executing yourself and call ExecuteCommand with an empty object array (new object[0])
- 放入 ADO.NET 直接执行 SQL 命令
- 格式化您自己正在执行的字符串并使用空对象数组（新对象 [0]）调用 ExecuteCommand
The second isn't a good idea as it opens you up to SQL inject attacks, but its a quick hack.
第二个不是一个好主意，因为它使您容易受到 SQL 注入攻击，但它是一个快速黑客。
回答by northpole
have you tried assigning a value to those that are null? Meaning (pseudo):
你有没有试过给那些空值赋值？含义（伪）：
If address is null then address = "" or If age is < 0 then age = 0
如果地址为空，则地址 = "" 或如果年龄 < 0，则年龄 = 0
then add it to myList
然后将其添加到 myList
or you could always use a Ternary operator:
或者你总是可以使用三元运算符：
name = name.Length < 1 ? "" : name;
age = age < 1 ? Int32.MinValue : age;
then add it to myList
然后将其添加到 myList
回答by tim
why not use nullable values?
为什么不使用可空值？
public void InsertCostumer(string? name, int? age, string? address)
{
List<object> myList = new List<object>();
myList.Add(name.GetValueOrDefault());
myList.Add(age.GetValueOrDefault());
myList.Add(address.GetValueOrDefault());
StringBuilder queryInsert = new StringBuilder();
queryInsert.Append("insert into Customers(name, address) values ({0}, {1}, {2})");
this.myDataContext.ExecuteCommand(queryInsert.ToString(), myList.ToArray());
}
回答by andy
In .NET, a null/nothing string does not evaluate to an empty string, i.e. "". If you want "", then that has to be the value of the string, or if you want to represent null/nothing in SQL, you have to manually write out "NULL" if your .NET string is in fact null/nothing.
在 .NET 中，null/nothing 字符串不会计算为空字符串，即“”。如果您想要“”，那么它必须是字符串的值，或者如果您想在 SQL 中表示空/无，如果您的 .NET 字符串实际上是空/无，则必须手动写出“NULL”。
All the execute command does, is execute a SQL query, provide by you, as a String. it doesn't do anything else special in terms of that SQL string.
执行命令所做的就是执行您提供的 SQL 查询，作为字符串。就该 SQL 字符串而言，它没有做任何其他特殊的事情。
So, for the Execute Command to work, you have to pass in a valid SQL string, you have to manually construct the string correctly.
因此，要使执行命令起作用，您必须传入一个有效的 SQL 字符串，您必须手动正确构造该字符串。
回答by BillJam
Same issue for me. So stupid of MS not to fix that. Here's my solution although I did not support all parameter types but ya get the idea. I stuck this in the DataContext class so it looks like it's built in to Linq :) .
对我来说同样的问题。愚蠢的 MS 不去解决这个问题。这是我的解决方案，虽然我不支持所有参数类型，但你明白了。我把它放在 DataContext 类中，所以它看起来像是内置于 Linq :) 。
public int ExecuteCommandEx(string sCommand, params object[] parameters)
{
object[] newParams = new object[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
if (parameters[i] == null)
newParams[i] = "NULL";
else if (parameters[i] is System.Guid || parameters[i] is System.String || parameters[i] is System.DateTime)
newParams[i] = string.Format("'{0}'", parameters[i]);
else if (parameters[i] is System.Int32 || parameters[i] is System.Int16)
newParams[i] = string.Format("{0}", parameters[i]);
else
{
string sNotSupportedMsg = string.Format("Type of param {0} not currently supported.", parameters[i].GetType());
System.Diagnostics.Debug.Assert(false, sNotSupportedMsg);
}
}
return ExecuteCommand(string.Format(sCommand, newParams));
}
回答by Mark
I use something like this (note I'm using the SO "IDE" so I can't, guarantee this will compile or work correctly but you'll get the idea)
我使用这样的东西（注意我使用的是 SO“IDE”，所以我不能，保证这会编译或正常工作，但你会明白的）
public void InsertCostumer(string name, int age, string address)
{
List<object> myList = new List<object>();
myList.Add(name);
myList.Add(age);
myList.Add(address);
StringBuilder queryInsert = new StringBuilder();
queryInsert.Append("insert into Customers(name, age, address) values (");
int i = 0;
foreach (var param in myList.ToArray())
{
if (param == null)
{
queryInsert.Append("null, ");
myList.RemoveAt(i);
}
else
{
queryInsert.Append("{" + i + "}, ");
i++;
}
}
queryInsert.Remove(queryInsert.Length - 2, 2);
queryInsert.Append(")");
this.myDataContext.ExecuteCommand(queryInsert.ToString(), myList.ToArray());
}
回答by Kevin
I made a generic ParamArray Function to pass in the parms I normally would pass into the ExecuteCommand. Then have it pass back the uninterpretted SQL parms and a list of objects actually passed in.
我制作了一个通用的 ParamArray 函数来传递我通常会传递给 ExecuteCommand 的参数。然后让它传回未解释的 SQL 参数和实际传入的对象列表。
Public Sub CommitRecords(ByVal InItems As List(Of Item)
Dim db As New DataContext(ConnectionString)
Try
For Each oItem In InItems
With oItem
Dim strParms As String = ""
Dim collParms = BuildExecuteCommandParms(strParms, .MapValue1, .MapValue2, .MapValue3, .MapValue4, .MapValue5, .MapValue6)
db.ExecuteCommand("Insert Into ItemTable (Value1, Value2, Value3, Value4, Value5, Value6)" & vbCrLf & _
"Values (" & strParms & ")", _
collParms.ToArray)
End With
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Public Function BuildExecuteCommandParms(ByRef strParms As String, ByVal ParamArray InParms As Object()) As List(Of Object)
Dim i As Integer = 0
Dim collOutParms As New List(Of Object)
For Each oParm In InParms
If i <> 0 Then strParms &= ", "
If oParm Is Nothing Then
strParms &= "NULL"
Else
strParms &= "{" & i & "}"
collOutParms.Add(oParm)
End If
i += 1
Next
Return collOutParms
End Function
回答by Dan
I usually use this sort of thing, not ideal but it's gets it done if you're stuck
我通常使用这种东西，不太理想，但如果你被卡住了，它就可以完成
if (myObject != null)
{
foreach (var p in ct.GetType().GetProperties())
{
if (p.GetValue(myObject , null) == null)
{
if (p.PropertyType == typeof(string))
{
p.SetValue(myObject , "Empty", null);
}
if (p.PropertyType == typeof(int))
{
p.SetValue(myObject , 0, null);
}
if (p.PropertyType == typeof(int?))
{
p.SetValue(myObject , 0, null);
}
}
}
}
This makes sure each value in the object has a value before you use the parameters in ExecuteCommand. Again, not ideal, but it works.
这确保在使用 ExecuteCommand 中的参数之前，对象中的每个值都有一个值。再次，不理想，但它的工作原理。
回答by Pedro
I didn't like using string.format since (as the current selected answer to this question says) you're opening yourself to SQL injection.
我不喜欢使用 string.format 因为（正如这个问题的当前选择的答案所说）你正在向 SQL 注入开放。
So I solved the problem by iterating through the parameters and if the parameter is null, I add NULL as a string to the command text, if it it not null, I add a placeholder that will be replaced (similar to string.format) with values by ExecuteQuery (which does the SQL injection checks).
所以我通过迭代参数解决了这个问题，如果参数为空，我将 NULL 作为字符串添加到命令文本中，如果它不为空，我添加一个将被替换的占位符（类似于 string.format） ExecuteQuery 的值（它执行 SQL 注入检查）。
private static T ExecuteSingle<T>(string connectionString, string sprocName, params object[] sprocParameters)
where T : class
{
var commandText = sprocName;
if (sprocParameters.Length > 0)
{
// http://stackoverflow.com/questions/859985/linq-executecommand-doesnt-understand-nulls
int counter = 0;
var nulledPlaceholders = sprocParameters
.Select(x => x == null ? "NULL" : "{" + counter ++ + "}");
commandText += " " + string.Join(",", nulledPlaceholders);
sprocParameters = sprocParameters.Where(x => x != null).ToArray();
}
var connection = new SqlConnection(connectionString);
var dc = new DataContext(connection);
return dc.ExecuteQuery<T>(commandText, sprocParameters).SingleOrDefault();
}
回答by cookbr
internal static class DataContextExtensions
{
public static int ExecuteCommandEx(this DataContext context, string command, params object[] parameters)
{
if (context == null)
throw new ArgumentNullException("context");
if (parameters != null && parameters.Length > 0)
parameters = parameters.Select(p => p ?? "NULL").ToArray();
return context.ExecuteCommand(command, parameters);
}
}
