How can I capture the results of this Stored Procedure in a List<>?

Question

I have wrestled with this for at least 3 hours, and I am somewhat new to C#, especially with returning multiple rows from a stored procedure in SQL Server.

I would like to return the results of a stored procedure :

create procedure ingredientNames_givenDishId
    @dishid inT
AS
    SELECT 
        a.ingredientname --given dish id, return ingredientnames     
    FROM 
        dbo.purple_ingredients a 
    JOIN 
        dbo.purple_dish_ingredients b ON a.ingredientid = b.ingredientid
    WHERE
        b.dishid = @dishid

This is the result when I exectute the same procedure in SQL Server, with a 10 as dishid:

My C# code:

private void btnIngs3FromDishId_Click(object sender, EventArgs e) {
    List<Ingredient> someIngs = new List<Ingredient>();

    Purple_DataClasses1DataContext dbContextCreateMenu = 
        new Purple_DataClasses1DataContext(DataHandler.theConnectionString);
    foreach (var row in dbContextCreateMenu.ingredientNames_givenDishId(10))
            MessageBox.Show(row.ToString());

I have also tried:

     var thing = dbContextCreateMenu.ingredientNames_givenDishId(10);
     var anotherThing = thing.AsEnumerable();
        foreach (var subthing in anotherThing)

This last example actually showed the 3 string values when I set a breakpoint and hovered the mouse over "anotherThing":

I could drill down with my mouse and see the string values once, then I would get a notice on subsequent tries: {"The query results cannot be enumerated more than once."}

Any help would be greatly appreciated. Thanks!

@Enigmativity: That helped, and very simple to implement. Here is what I ended up with, that works just fine:

var thing= dbContextCreateMenu.ingredientNames_givenDishId(10);
var anotherThing = thing.ToList();
MessageBox.Show(anotherThing.ElementAt(0).ingredientname);

@Sharag & marc_s: got this older method to work too!! Much appreciated. A few items to note for others new to using this: I needed this line:

cmd.CommandType = CommandType.StoredProcedure; 

alsoI needed to explicitly set the @dishid as an int in this line or it would be considered an Object by the compiler:

cmd.Parameters.AddWithValue("@dishid", Convert.ToInt32(aValue));

Answer 1

The simplest thing to do is replace the .AsEnumerable() with .ToArray() in the following code:

 var thing = dbContextCreateMenu.ingredientNames_givenDishId(10);
 var anotherThing = thing.AsEnumerable();
    foreach (var subthing in anotherThing)

Make it:

 var thing = dbContextCreateMenu.ingredientNames_givenDishId(10);
 var anotherThing = thing.ToArray();
    foreach (var subthing in anotherThing)

The thing with .AsEnumerable() is that it is a fairly weak operator and does nothing more than perform a casting operation.

This is the decompiled source from "System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089":

[__DynamicallyInvokable]
public static IEnumerable<TSource> AsEnumerable<TSource>(this IEnumerable<TSource> source)
{
  return source;
}

It doesn't actually do anything to force execution of your query.

Calling .ToArray() does force the execution.

The other alternative is calling .ToList(). I don't advocate doing this unless you know that you want to change the membership of the list. The performance of each is virtually identical. Both .ToArray() and .ToList() add elements to an array that initially begins with 4 elements and then doubles the capacity each time the current capacity is reached. However, a call to .ToArray() will be more memory efficient as the last thing that it does before returning the array is truncate the buffer. So a List<T> of 1,025 items will internally contain 2,048 members, but a T[] of 1,025 elements will contain only 1,025 items - the buffer used in its construction of 2,048 members is immediately available for garbage collection.

Answer 2

I am no expert on LINQ, but if u want old way which always worked, its here:

List<String> lt = new List<string>();    
     try{

         if (con.State == System.Data.ConnectionState.Closed)
              con.Open();
               cmd.CommandText = qr;\\the query 
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adp.Fill(dt);
             for(int i=0;i<dt.Rows.Count;i++)
               {lt.Add(dt.Rows[i][0].toString());}
            }
            catch (SqlException exc)
            { MessageBox.Show(exc.Message);  }

Answer 3

On C# side, you can access using reader, datatable, adapter.

Example code for data reader is presented below

SqlConnection connection = new SqlConnection(ConnectionString);

command = new SqlCommand("TestProcedure", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();

reader = command.ExecuteReader();

List<Test> TestList = new List<Test>();

Test test;

while (reader.Read())
{
    test = new Test();
    test.ID = int.Parse(reader["ID"].ToString());
    test.Name = reader["Name"].ToString();

    TestList.Add(test);
}