如何从存储过程中获取结果并将结果保存在类属性中

I have following code:

我有以下代码:

public static void executeStoredProcedure(SqlCommand sp)
{
           SqlConnection conn = new SqlConnection();
           conn.ConnectionString=Connection.getConnection();
           conn.Open();
           sp.CommandType = CommandType.StoredProcedure;
           sp.Connection = conn;
           sp.ExecuteNonQuery();
           conn.Close();
}

This code executes the stored procedure.

此代码执行存储过程。

But my stored procedure is

但我的存储过程是

Create procedure [dbo].[selectAllItems]
(@ItemCode varchar(50) )
as
begin
    select * from Item where ItemCode  = @ItemCode
end

It will return rows but how to get this result in above c# code

它将返回行,但如何在上面的c#代码中获得此结果

3 个解决方案

#1

You need to use a SqlDataReader to read the result set returned by the stored procedure:

您需要使用SqlDataReader来读取存储过程返回的结果集:

using (SqlConnection conn = new SqlConnection(Connection.getConnection()))
using (SqlCommand sp = new SqlCommand("dbo.selectAllItems", conn))
{
       sp.CommandType = CommandType.StoredProcedure;
       sp.Parameters.Add("@ItemCode", SqlDbType.Int).Value = your-item-code-value-here;

       conn.Open();

       using (SqlDataReader rdr = sp.ExecuteReader())
       {
          while (rdr.Read())
          {
             // read the values from the data reader, e.g.
             // adapt to match your actual query! You didn't mentioned *what columns*
             // are being returned, and what data type they are
             string colValue1 = rdr.GetString(0);
             int colValue2 = rdr.GetInt(1);
          }
       }

       conn.Close();
}

With those values read from the SqlDataReader, you could e.g. create an object type and set its properties - or something like that - totally depends on what you want to do.

使用从SqlDataReader读取的值,您可以例如创建一个对象类型并设置它的属性 - 或类似的东西 - 完全取决于你想做什么。

And of course : using an ORM like Entity Framework would save you from having to write a lot of this type of code - EF would handle this for you - automagically .

当然:使用像Entity Framework这样的ORM可以避免编写大量此类代码--EF可以自动处理这个问题。

#2

you need to parse the parameter to you stored procedure like below

您需要将参数解析为您的存储过程,如下所示

sp.Parameters.AddWithValue("@ItemCode", itemcode);

sample code

public DataTable SelectAllItems(string itemCode)
{
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(Connection.getConnection()))
    using (SqlCommand cmd = new SqlCommand("selectAllItems", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ItemCode", itemCode);
        conn.Open();

        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(dt);
        }

    }
    return dt;
}

#3

You can use SQL Data reader Check the following example.

您可以使用SQL数据阅读器检查以下示例。

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read.aspx

#1