How To Get Set Value From Stored procedure in C#

Question

I have a stored procedure that client provided me

Like:

ALTER Proc [dbo].[XYZ]
    @Parameter varchar(100),
    @Parameter1 nvarchar(4000) out

   SET @APIString = "Test Test"

I have no rights to change this procedure.

When I execute procedure through C# I get a blank string from procedure

How to get the @Parameter1 value in my project?

C# Code:

SqlCommand cmd = new SqlCommand("dbo.XYZ", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Parameter", Parameter);

cmd.Parameters.Add("@Parameter1", SqlDbType.VarChar,4000);
cmd.Parameters["@Parameter1"].Direction = ParameterDirection.Output;

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    String = reader["@Parameter1"] != null ? reader["@Parameter1"].ToString() : "";
}

conn.Close();

Show how you "execute the procedure through C#"

har07
– har07

2015-04-17 04:37:46 +00:00
Commented Apr 17, 2015 at 4:37 — har07
– har07, Commented Apr 17, 2015 at 4:37
stackoverflow.com/questions/706361/…

Nagaraj S
– Nagaraj S

2015-04-17 04:42:18 +00:00
Commented Apr 17, 2015 at 4:42 — Nagaraj S
– Nagaraj S, Commented Apr 17, 2015 at 4:42

Dirk · Accepted Answer · 2015-04-17 04:40:38Z

7

@Parameter1 is an output parameter. You can get its value the same way you set the values for input parameters, e.g.

var cmd = new SqlCommand("dbo.XYZ", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Parameter", inputValue);

// add the output parameter
cmd.Parameters.Add("@Parameter1", SqlDbType.NVarChar).Direction =
    ParameterDirection.Output;

cmd.ExecuteNonQuery();

string parameter1 = (string)cmd.Parameters["@Parameter1"].Value;

You should also use ExecuteNonQuery unless the store procedure returns values with a select statement.

answered Apr 17, 2015 at 4:40

Dirk

11k2 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How To Get Set Value From Stored procedure in C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related