C# : how to use parameters for SQL Server stored procedure?

Question

I want to use stored procedures in C# to get data from a SQL Server table. I want to print the city with the id that I'll pass as a parameter, but I don't know the correct syntax of using parameters like that for a stored procedure in C#.

This is the C# code that I'm using:

string connString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    SqlCommand command = new SqlCommand("SP_GetCityByID where id = 2", conn);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int);
    param.Direction = ParameterDirection.Input;

    command.ExecuteNonQuery();

    Console.WriteLine(param.Value);
}

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 13, 2017 at 13:44

Akshey Bhat · Accepted Answer · 2017-05-13 13:27:38Z

2

SqlCommand command = new SqlCommand("SP_GetCityByID ", conn);

You don't put a where condition when you call a stored procedure. where condition needs to be inside the body of stored procedure which should compare the id column of your city table with @ID parameter you are passing to stored procedure. Secondly, ExecuteNonQuery function which you have written at the end will not serve your purpose. Use ExecuteScalar function instead as given below:

String cityName= command.ExecuteScalar();

I am assuming your stored procedure accepts parameter @ID and returns matching city name in the form of table.

answered May 13, 2017 at 13:27

Akshey Bhat

8,6872 gold badges24 silver badges20 bronze badges

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

6 Comments

Dolev Over a year ago

ok i do remember hearing about ExecuteScalar but i dont remeber what it is used for. i kinda know not to put the parameter where i called the stored procedure but where DO i call the parameter? i need a place to write something like: "where id = 2"

Akshey Bhat Over a year ago

The place to write the where condition is inside the stored procedure. And you should not write where id= 2 because that would always return same result. Instead write where id = @ID.

Akshey Bhat Over a year ago

ExecuteScalar is used get single value results from database. Inside your stored procedure write a query to get city name on the basis of @ID parameter

Dolev Over a year ago

yeah i know that, i have that part in the stored procedure. but can't i give the parameter to the stored procedure in the visual studio using c#? in the code i have up there?

Akshey Bhat Over a year ago

For sending value add line param.Value= <your id value> before the statement executing the command. Also you should use AddWithValue instead of Add function for specifying parameters

|

Kannan Kandasamy · Accepted Answer · 2017-05-13 13:28:16Z

0

provide parameter as below:

  string connString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        SqlCommand command = new SqlCommand("SP_GetCityByID", conn);
        command.CommandType = CommandType.StoredProcedure;
        SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int).Value = 2;
        //param.Direction = ParameterDirection.Input;
        command.ExecuteNonQuery();
        Console.WriteLine(param.Value);

    }

answered May 13, 2017 at 13:28

Kannan Kandasamy

14.1k3 gold badges28 silver badges40 bronze badges

1 Comment

Dolev Over a year ago

not working. visual studio throw me red error when i type the value like you suggested

Collectives™ on Stack Overflow

C# : how to use parameters for SQL Server stored procedure?

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related