CODEWITHSUNDEEP
c#linq-to-sqlnull
Samuel Carrijo
Samuel Carrijo

Reputation: 17929

Linq ExecuteCommand doesn't understand nulls

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

    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'."

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?

Upvotes: 22

Views: 11898

Answers (11)

Rm558
Rm558

Reputation: 4994

Kevin is right.

an example of his work around #1 in LinqPad. Need this (Object)s??DBNull.Value

string s = null; 

//ExecuteCommand("insert into T(C1) values({0})", s); //Exception

SqlCommand cmd= new SqlCommand(){
    CommandText = "insert into T(C1) values(@P0)",
    Connection = new SqlConnection(this.Connection.ConnectionString),
};
//cmd.Parameters.AddWithValue("@P0", s); //SqlException
cmd.Parameters.AddWithValue("@P0", (Object)s??DBNull.Value);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

Ts.OrderByDescending(t=>t.ID).Take(1).Dump();

Upvotes: 1

cookbr
cookbr

Reputation: 125

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

Upvotes: 0

Pedro
Pedro

Reputation: 1344

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

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

Upvotes: 0

Dan
Dan

Reputation: 1480

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.

Upvotes: 0

Kevin
Kevin

Reputation: 1

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.

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

Upvotes: 0

Mark
Mark

Reputation: 2926

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)

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

Upvotes: 0

BillJam
BillJam

Reputation: 221

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

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

Upvotes: 0

Kevin Berridge
Kevin Berridge

Reputation: 6303

This is a known bug and Microsoft does not intend to fix it...

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305114&wa=wsignin1.0

The work around is to either:

  1. Drop into ADO.NET and execute the SQL Command directly
  2. Format the string you're executing yourself and call ExecuteCommand with an empty object array (new object[0])

The second isn't a good idea as it opens you up to SQL inject attacks, but its a quick hack.

Upvotes: 19

andy
andy

Reputation: 8875

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.

Upvotes: -6

tim
tim

Reputation: 547

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

Upvotes: -1

northpole
northpole

Reputation: 10346

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

then add it to 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

Upvotes: 0

Related Questions