找到你要的答案

Q:How can I capture the results of this Stored Procedure in a List?

Q:如何在列表中捕获此存储过程的结果?

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: enter image description here

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": looks like I could get the data!

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

我已经在这至少3个小时,我有些新的C #,特别是从存储过程中返回多行SQL服务器。

我想返回存储过程的结果:

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: enter image description here

我#代码C:

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

我也试过:

     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": looks like I could get the data!

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

任何帮助将不胜感激。谢谢!

@ Enigmativity:帮助,很简单的实现。这里是我结束了,那工作只是罚款:

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

@ Sharag &;marc_s:得到这个老方法工作太!不胜感激。需要注意的几个事项:我需要这行:

cmd.CommandType = CommandType.StoredProcedure; 

我需要明确设置“dishid作为int本线或将被编译一个对象:

cmd.Parameters.AddWithValue("@dishid", Convert.ToInt32(aValue));
answer1: 回答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.

最简单的办法就是更换。asenumerable(),在下面的代码toarray():

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

成功:

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

这件事。asenumerable()是相当弱算子和不超过执行铸造作业。

这是从“system.core反编译的源码,版本= 4.0.0.0,文化=中性、PublicKeyToken = b77a5c561934e089”:

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

它实际上没有做任何强制执行您的查询。

toarray()迫使执行调用。

另一种方法是tolist()调用。我不主张这样做,除非你知道你想改变名单的成员。每个性能几乎是相同的。两。toarray()和添加元素。tolist()最初始于4元素然后双打能力每一次电流容量达到一个数组。然而,一个电话。toarray()将更高效的内存,它在返回数组截断缓冲的最后一件事。所以清单& lt;T & gt;1025个项目将在内部包含2048个成员,但T [ ] 1025元素只包含1025项用于2048名施工缓冲是立即可用的垃圾收集。

answer2: 回答2:

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

C #的一面,你可以访问使用的读者,DataTable,适配器。

下面是数据阅读器的示例代码

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);
}
answer3: 回答3:

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

我在LINQ不是专家,但如果你想旧路总是工作,它在这里:

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);  }
c#  sql-server  tsql  stored-procedures  linq-to-sql