ASP.NET calling stored proc with LINQ and passing in DataTable

Question

What am I doing wrong? Trying to pass in my DataTable to a stored proc using LINQ. Below is my code.

var sqlCommand = new System.Data.SqlClient.SqlCommand {
    CommandType = System.Data.CommandType.StoredProcedure,
    CommandText = "UserIdList"
};

var dataTable = new System.Data.DataTable("IdList");
dataTable.Columns.Add("AttributeIds", typeof(Int32));
dataTable.Rows.Add(26);
dataTable.Rows.Add(40);
dataTable.Rows.Add(41);
dataTable.Rows.Add(45);
dataTable.Rows.Add(78);
dataTable.Rows.Add(33);
dataTable.Rows.Add(36);

//The parameter for the SP must be of SqlDbType.Structured 
var parameter = new System.Data.SqlClient.SqlParameter { 
    ParameterName = "@AttributeIds",
    SqlDbType = System.Data.SqlDbType.Structured,
    TypeName =  "ecs.IDList",
    Value = dataTable,
};

sqlCommand.Parameters.Add(parameter);

var user = myDC.DC.ExecuteQuery("exec ecs.udpUserAttributeDetails {0}, {1}", sqlCommand, userId).SingleOrDefault();

@InSane - Error msg is: A query parameter cannot be of type 'System.Data.SqlClient.SqlCommand'. — Mark

Jagmag Jagmag · Accepted Answer · 2010-12-08T01:33:59

This seems to be the problem

var user = myDC.DC.ExecuteQuery("exec ecs.udpUserAttributeDetails {0}, {1}", sqlCommand, userId).SingleOrDefault();

In your code, you are passing a sqlCommand object as the first parameters and the userId as the 2nd parameter.

A data context ExecuteQuery method has 2 overloads

ExecuteQuery<TResult>(String, Object[]) 
ExecuteQuery(Type, String, Object[])

You seem to be using Overload 1 - i.e. ExecuteQuery<TResult>(String, Object[]) but in that case you need to specify the type of the returned object

eg :-

 var customers = db.ExecuteQuery<Customer>(@"SELECT CustomerID, CompanyName, ContactName FROM dbo.Customers WHERE  City = {0}", "London");

NOTE: db.ExecuteQuery<Customer> in the above example is what I am referring to.

I think this might be the cause of the error as the compiler is mapping your request to overload 2 instead which doesnt return any values but takes in 3 parameters resulting in your A query parameter cannot be of type 'System.Data.SqlClient.SqlCommand error.

ASP.NET calling stored proc with LINQ and passing in DataTable

1 Answers