none
StoredProcedure to DataTable problem RRS feed

  • Question

  • I am using a stored procedure to get data because I want to concatenatetwo fields and present an ID, and the concatenated fields for a combo box

    I also need to convert this to a datatable
    this was working fine before I added the Stored procedure but now GetCommand does not work

    var Ing = from i in db.IngFORcombo(CorN, tb.Text, iUse)
    select new
    {i.LiqId,LiqItemName};

    //send to a DataTable
    SqlCommand dBcmd = db.GetCommand(Ing) as SqlCommand;
    DataTable IngTable = new DataTable();
    SqlDataAdapter memAdapter = new SqlDataAdapter(dBcmd);
    memAdapter.Fill(IngTable);

    It looks as though I have to either convert 'Ing' or use a different command but I am not having any luck


    jnc
    Friday, March 6, 2009 6:57 PM

Answers

  • GetCommand takes an IQueryable, but because stored procedures are not composable by SQL Server, queries using them will return an IEnumerable instead of IQueryable. There is an IEnumerable.AsQueryable() method, which will allow your code to compile, but it doesn't produce an IQueryable that LINQ to SQL can actually use to produce a query for you.

    If you have to use a stored procedure here, I don't know of any way to get this to work with GetCommand. You could always build the command yourself instead of getting it from the LINQ query. Another thought is that table-valued functions are composable, so if that is an option instead of a stored proc, it should give you what you want.

    Thanks,
    Sarah
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 6, 2009 11:53 PM
    Moderator

All replies

  • GetCommand takes an IQueryable, but because stored procedures are not composable by SQL Server, queries using them will return an IEnumerable instead of IQueryable. There is an IEnumerable.AsQueryable() method, which will allow your code to compile, but it doesn't produce an IQueryable that LINQ to SQL can actually use to produce a query for you.

    If you have to use a stored procedure here, I don't know of any way to get this to work with GetCommand. You could always build the command yourself instead of getting it from the LINQ query. Another thought is that table-valued functions are composable, so if that is an option instead of a stored proc, it should give you what you want.

    Thanks,
    Sarah
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 6, 2009 11:53 PM
    Moderator
  •  Thanks, I thought that would be the case, not very satisfactory tho'


    jnc
    Saturday, March 7, 2009 10:24 AM