How to send and receive parameters to/from SQL Server stored procedure

Question

IN THE LAST PART I WROTE THE working solution: I have this stored procedure in 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

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

Answer 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.

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.

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

Answer 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.

instead of:

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