如何向/从SQL Server存储过程发送和接收参数

IN THE LAST PART I WROTE THE working solution: I have this stored procedure in SQL Server :

在最后一部分我WROTE工作解决方案:我在SQL Server中有这个存储过程:

alter PROCEDURE [dbo].[ProcedureName]
    @v nvarchar(10),
    @L NVarChar(2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT B,M,V
    FROM XXXX
    WHERE V = @v and L = @L
END

and I am passing the parameters but I cannot retrieve the SELECT part I need to retrieve B,M,V of Select B,M,V also

我传递参数,但我无法检索我需要检索选择B,M,V的B,M,V的SELECT部分

SqlCommand Cmd = new SqlCommand("ProcedureName", cnn);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@v", SqlDbType.NVarChar, 10).Value = v;
Cmd.Parameters.Add("@L", SqlDbType.NVarChar, 2).Value = lo;

if (Cmd.Connection.State == ConnectionState.Closed)
{
   Cmd.Connection.Open();
}

Cmd.ExecuteNonQuery();

THIS IS THE WOKING SOLUTION THANKS TO THE HELP I GOT HERE :

这是我在这里给予帮助的解决方案:

SqlCommand Cmd = new SqlCommand("ProcedureName", cnn);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@v", SqlDbType.NVarChar, 10).Value = v;
Cmd.Parameters.Add("@L", SqlDbType.NVarChar, 2).Value = lo;

if (Cmd.Connection.State == ConnectionState.Closed)
{
   Cmd.Connection.Open();
}

using (SqlDataReader reader = Cmd.ExecuteReader())
{
   if (reader.HasRows)
   {
      while (reader.Read())
      {
         ret = new MYCLASS();
            ret.B = reader.GetString(0);
            ret.M = reader.GetString(1);
            ret.V = reader.GetString(2);
       }
    }
 }

2 个解决方案

#1

You'll need to make use of SqlDataReader to achieve this. Also make use of using block to ensure the connection object is closed and disposed correctly.

您需要使用SqlDataReader来实现此目的。还可以使用块来确保连接对象已正确关闭和处理。

From MSDN

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

要确保始终关闭连接,请打开using块内部的连接,如以下代码片段所示。这样做可确保在代码退出块时自动关闭连接。

You can change your code to something like:

您可以将代码更改为:

using(var con = new SqlConnection("ConnectionString")) {
using(var cmd = new SqlCommand("ProcedureName", con)) {

//Params here

con.Open();

using(var reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
        var bValue = reader.GetString(0);
        //Same for the next two values
        }
     }
   }
}

#2

You're almost there, now if you pay close attention to your code you're not using the correct method for your procedure. This can be easily achieved with:

你几乎就在那里,现在如果你密切注意你的代码,你没有使用正确的方法来处理你的程序。这可以通过以下方式轻松实现:

ExecuteReader Since you're only reading from your database.

ExecuteReader因为您只是从数据库中读取。

instead of:

ExecuteNonQuery which is commonly used for UPDATE, INSERT, or DELETE statements

ExecuteNonQuery,通常用于UPDATE,INSERT或DELETE语句

#1