CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
lol
lol

Reputation: 93

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

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

Upvotes: 5

Views: 128

Answers (2)

Izzy
Izzy

Reputation: 6866

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

Upvotes: 5

Just Do It
Just Do It

Reputation: 486

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

Upvotes: 1

Related Questions