DataContext.ExecuteQuery and Null values

Question

So my problem is this. When using the Data Context ExecuteQuery(string query, params object[] parameters) (here), I am unable to pass through a null in any way shape or form. The parameter that started this off was a SQL nvarchar parameter, which needs to allow nulls.

I read this article on stackoverflow which had "use DBNull.Value" marked as the answer - so I tried that, and nothing (so wondering how that can be the answer, if it doesn't work??!).

Here is the sample code I am trying to run (note, this is just to test the concept):

var db = new Test1DataContext(Properties.Settings.Default.TestConnectionString);
var query = "EXEC UpInsertTest4 {0}, {1}";

// Works fine
var list1 = new List<object> { 1, "1" };
db.ExecuteQuery<UpInsertTest4Result>(query, list1.ToArray());

// Doesn't work
var list2 = new List<object> { 1, DBNull.Value };
db.ExecuteQuery<UpInsertTest4Result>(query, list2.ToArray());

Here are the various errors I receive :

null                : A query parameter cannot be of type 'System.Object'.
default(string)     : A query parameter cannot be of type 'System.Object'.
DBNull.Value        : Unexpected type code: DBNull (which give this as a stack trrace)


at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2005Provider.From(Type type, Nullable`1 size)
   at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2008Provider.From(Type type, Nullable`1 size)
   at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Type type)
   at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Object o)
   at System.Data.Linq.SqlClient.SqlFactory.ValueFromObject(Object value, Type clrType, Boolean isClientSpecified, Expression sourceExpression)
   at System.Data.Linq.SqlClient.QueryConverter.VisitConstant(ConstantExpression cons)
   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
   at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp)
   at System.Data.Linq.SqlClient.QueryConverter.VisitUserQuery(String query, Expression[] arguments, Type resultType)
   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
   at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters)
   at ConsoleApplication1.Program.Main(String[] args) in E:\TestBed\Testbed\ConsoleApplication1\Program.cs:line 36
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

From what I've read, I could be fighting a loosing battle with this! This is my last ditch attempt!

Many thanks,
Chris.

Yes I have, should have included that, sorry! It doesn't work for a string - compile time error : "The type 'string' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 'System.Nullable'". — Bertie, Feb 09 '12 at 10:03

Arion · Accepted Answer · 2013-02-11T14:50:53.407

6

You could do it something like this:

db.ExecuteQuery<UpInsertTest4Result>(string.Format(query,1,"null"));

Update

Yes I have tested the solution. It will not contain @p1=N'null'. Because I am using sql as a string directly. So a prof of concept.

Store procedure:

CREATE PROCEDURE testas
@test1 INT,  
@test2 INT 
AS
SELECT 1 test

class for testing the output:

public class testout
{
    public int test { get; set; }
}

Linq query with the db context:

var query="exec testas {0}, {1}";
db.ExecuteQuery<testout>(string.Format(query,"0","null"));

If you look in linqpad the sql output is like this:

exec testas 0, null

The string format is just altering the string. This would be the same as writing this:

db.ExecuteQuery<testout>("exec testas 0, null")

edited Feb 11 '13 at 14:50

answered Feb 09 '12 at 10:26

Arion

31,011
10
70
88

Ha, and so you can! And not one answer I've seen has suggested that! I've tried it with string, int & bit nullable columns and so far so good! Nicely done sir!! – Bertie Feb 09 '12 at 10:54
Yeah it quite strange and not what you might expect. Thanks glad to help. – Arion Feb 09 '12 at 11:35
Did you try the solution? The output SQL will contain something like `,@p1=N'null'` instead of `,@p1=null`. – UserControl Feb 11 '13 at 12:10
This is very limited example. Consider changing argument types to `nvarchar()` or `datetime` and you are in escaping hell. – UserControl Feb 11 '13 at 18:00
Yes but that was not what the OP was asking. He was asking about how to supply null values to a execute store procedure in a executequery – Arion Feb 11 '13 at 21:34
1

Just to follow on from this as it is a year old - the answer I marked as correct did work and has been in several production systems since. The solution fitted like the proverbial glove! – Bertie Apr 19 '13 at 13:33

score 1 · Answer 2 · answered Dec 15 '15 at 08:51

I had the same problem and I found a solution on this forum: https://social.msdn.microsoft.com/Forums/en-US/20b318bc-32cf-466b-972e-6cd37e625cd6/a-query-parameter-cannot-be-of-the-type-systemobject?forum=linqprojectgeneral

Basically, the goal is to create an extension method that will analyse parameters and replace all null parameters directly into the request.
Example: "EXEC MyStoredProcedure {0}, {1}, {2}, {3}" with second parameter value null will become "EXEC MyStoredProcedure {0}, NULL, {1}, {2}" and second parameter will be removed from the array parameters.

Here is the method:

internal static IEnumerable<TResult> ExecuteQueryNullSafe<TResult>(this System.Data.Linq.DataContext context, string command, params object[] parameters)
{
  var list = new List<object>();
  var listVals = new List<bool>();

  for (int x = 0; x < parameters.Count(); x++)
  {
    if (parameters[x] == null || parameters[x] is System.DBNull)
    {
      command = command.Replace("{" + x + "}", "NULL");
      listVals.Add(false);
    }
    else
    {
      list.Add(parameters[x]);
      listVals.Add(true);
    }
  }

  int nextId = 0;
  for (int i = 0; i < listVals.Count; i++)
  {
    var isUsed = listVals[i];
    if (!isUsed)
      continue;
    if (nextId != i)
      command = command.Replace("{" + i.ToString() + "}", "{" + nextId.ToString() + "}");
    nextId++;
  }

  return context.ExecuteQuery<TResult>(command, list.ToArray());
}

score 0 · Answer 3 · answered Mar 12 '13 at 16:44

try this Chris:

void Main()
{
    var db = new Test1DataContext(Properties.Settings.Default.TestConnectionString);
    db.UpInsertTest4Result(1,"1");
    db.UpInsertTest4Result(1,null);
    db.UpInsertTest4Result(null,"1");
    db.UpInsertTest4Result(null,null);
}

public class Test1DataContext : DataContext
{
   public Test1DataContext(string connStr) : base(connStr) { }

   [Function(Name = "UpInsertTest4")]
   public IEnumerable<UpInsertTest4Result> UpInsertTest4Result(
    [Parameter(Name = "par1", DbType = "Int")] int? par1,
    [Parameter(Name = "par2", DbType = "VarChar")] string par2)
   {
       var result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), par1,par2);

       return (IEnumerable<UpInsertTest4Result>)result.ReturnValue;
   }
}

DataContext.ExecuteQuery and Null values

3 Answers3