blocks|key|1343279|text|你有没有试过给那些为空的值赋值？含义(伪)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343280|如果address为空，则address+=+"“，或者如果age+<0，则age+=0|1343281|然后将其添加到myList|1343282|或者，您可以始终使用三元运算符：|1343283|name+=+name.Length+<+1+?+""+:+name;
age+=+age+<+1+?+Int32.MinValue+:+age;|code-block|syntax|javascript|1343284|1343285|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|T|8|@]|9|@]|A|$K|L]]|$1|M|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|O|$]]

have you tried assigning a value to those that are null? Meaning (pseudo):

If address is null then address = ""
or
If age is &lt; 0 then age = 0

then add it to myList

or you could always use a Ternary operator:

<pre><code>name = name.Length &lt; 1 ? "" : name;
age = age &lt; 1 ? Int32.MinValue : age;
</code></pre>

then add it to myList

blocks|key|1343311|text|为什么不使用可以为空的值？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343312|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());
}|code-block|syntax|javascript|1343313|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

why not use nullable values?

<pre><code>public void InsertCostumer(string? name, int? age, string? address)
{
 List&lt;object&gt; myList = new List&lt;object&gt;();

 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());
}
</code></pre>

blocks|key|1343387|text|在.NET中，null/nothing字符串不会计算为空字符串，即"“。如果你想要""，那么它必须是字符串的值，或者如果你想在SQL中表示null/nothing，那么如果你的.NET字符串实际上是null/nothing，你就必须手动写出"NULL“。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343388|execute命令所做的全部工作就是执行您以字符串形式提供的SQL查询。就SQL字符串而言，它不做任何其他特殊操作。|1343389|因此，要使Execute+Command起作用，您必须传入一个有效的SQL字符串，您必须手动正确地构造该字符串。|1343390|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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.

So, for the Execute Command to work, you have to pass in a valid SQL string, you have to manually construct the string correctly.

blocks|key|1343461|text|我也有同样的问题。太愚蠢了，微软居然不解决这个问题。这是我的解决方案，虽然我并不支持所有的参数类型，但是你已经明白了。我把它放在DataContext类中，这样看起来就像是内置在Linq+:)中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343462|++++public+int+ExecuteCommandEx(string+sCommand,+params+object[]+parameters)
++++{
++++++++object[]+newParams+=+new+object[parameters.Length];

++++++++for+(int+i+=+0;+i+<+parameters.Length;+i%2B%2B)
++++++++{
++++++++++++if+(parameters[i]+==+null)
++++++++++++++++newParams[i]+=+"NULL";
++++++++++++else+if+(parameters[i]+is+System.Guid+%7C%7C+parameters[i]+is+System.String+%7C%7C+parameters[i]+is+System.DateTime)
++++++++++++++++newParams[i]+=+string.Format("'{0}'",+parameters[i]);
++++++++++++else+if+(parameters[i]+is+System.Int32+%7C%7C+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));
++++}|code-block|syntax|javascript|1343463|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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 :) .

<pre><code> public int ExecuteCommandEx(string sCommand, params object[] parameters)
 {
 object[] newParams = new object[parameters.Length];

 for (int i = 0; i &lt; 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));
 }
</code></pre>

blocks|key|1559019|text|我使用这样的东西(注意我使用的是SO+"IDE“，所以我不能保证这将被正确编译或工作，但是你会明白的)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1559020|++++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("{"+%2B+i+%2B+"},+");
++++++++++++++++i%2B%2B;
++++++++++++}
++++++++}

++++++++queryInsert.Remove(queryInsert.Length+-+2,+2);
++++++++queryInsert.Append(")");

++++++++this.myDataContext.ExecuteCommand(queryInsert.ToString(),+myList.ToArray());
++++}|code-block|syntax|javascript|1559021|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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)

<pre><code> public void InsertCostumer(string name, int age, string address)
 {
 List&lt;object&gt; myList = new List&lt;object&gt;();

 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());
 }
</code></pre>

blocks|key|1343565|text|我创建了一个通用的ParamArray函数来传入通常会传递到ExecuteCommand中的参数。然后让它传回未解释的SQL参数和实际传入的对象列表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343566|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+%2B=+1
++++Next
++++Return+collOutParms
End+Function|code-block|syntax|javascript|1343567|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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)" &amp; vbCrLf &amp; _
 "Values (" &amp; strParms &amp; ")", _
 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 &lt;&gt; 0 Then strParms &amp;= ", "
 If oParm Is Nothing Then
 strParms &amp;= "NULL"
 Else
 strParms &amp;= "{" &amp; i &amp; "}"
 collOutParms.Add(oParm)
 End If
 i += 1
 Next
 Return collOutParms
End Function
</code></pre>

blocks|key|1559142|text|我通常使用这类东西，不是很理想，但如果你遇到困难，它就能解决问题|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1559143|++++++++++++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);
++++++++++++++++++++++++}

++++++++++++++++++++}
++++++++++++++++}
++++++++++++}|code-block|syntax|javascript|1559144|这确保在使用ExecuteCommand中的参数之前，对象中的每个值都有一个值。再说一次，这不是理想的，但它是有效的。|1559145|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I usually use this sort of thing, not ideal but it's gets it done if you're stuck

<pre><code> 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);
 }

 }
 }
 }
</code></pre>

This makes sure each value in the object has a value before you use the parameters in ExecuteCommand. Again, not ideal, but it works.

blocks|key|1343691|text|我不喜欢使用string.format，因为(就像这个问题的当前选择答案所说的那样)您正在向SQL注入敞开大门。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1343692|因此，我通过迭代参数解决了这个问题，如果参数为null，我将NULL作为字符串添加到命令文本中，如果不是null，则添加一个占位符，该占位符将被ExecuteQuery+(执行string.format注入检查)的值替换(类似于SQL值)。|1343693|++++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"+:+"{"+%2B+counter+%2B%2B+%2B+"}");

++++++++++++commandText+%2B=+"+"+%2B+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();
++++}|code-block|syntax|javascript|1343694|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I didn't like using string.format since (as the current selected answer to this question says) you're opening yourself to SQL injection. 

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).

<pre><code> private static T ExecuteSingle&lt;T&gt;(string connectionString, string sprocName, params object[] sprocParameters)
 where T : class
 {
 var commandText = sprocName;
 if (sprocParameters.Length &gt; 0)
 {
 // http://stackoverflow.com/questions/859985/linq-executecommand-doesnt-understand-nulls
 int counter = 0;
 var nulledPlaceholders = sprocParameters
 .Select(x =&gt; x == null ? "NULL" : "{" + counter ++ + "}");

 commandText += " " + string.Join(",", nulledPlaceholders);
 sprocParameters = sprocParameters.Where(x =&gt; x != null).ToArray();
 }
 var connection = new SqlConnection(connectionString);
 var dc = new DataContext(connection);
 return dc.ExecuteQuery&lt;T&gt;(commandText, sprocParameters).SingleOrDefault();
 }
</code></pre>

blocks|key|1559253|text|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);
++++}
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1559254|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &amp;&amp; parameters.Length &gt; 0)
 parameters = parameters.Select(p =&gt; p ?? "NULL").ToArray();
 return context.ExecuteCommand(command, parameters);
 }
}
</code></pre>

I'm having a problem when passing nulls to a ExecuteCommand() method using linq. My code is similar to the one that follows:

<pre><code> public void InsertCostumer(string name, int age, string address)
 {
 List&lt;object&gt; myList = new List&lt;object&gt;();

 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());
 }
</code></pre>

But, when a parameter is null (address, for instance), I get the following error: "A query parameter cannot be of type '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?

Linq ExecuteCommand doesn't understand nulls

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在使用linq将空值传递给ExecuteCommand()方法时，我遇到了一个问题。我的代码类似于下面的代码： public void InsertCostumer(string name, int age, string address) { List<object> myList = ne...

问Linq ExecuteCommand不理解空值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Linq ExecuteCommand不理解空值EN