CODEWITHSUNDEEP
c#sql-serverstored-procedures
Bruno
Bruno

Reputation: 6469

How do I add parameters to the following stored procedure call?

How do I add parameters to the following stored procedure call?

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}

Upvotes: 0

Views: 1153

Answers (7)

Ravi Gadag
Ravi Gadag

Reputation: 15881

You can use SqlCommand.Parameters Property.

command.Parameters.Add("@SomeParmeter", SqlDbType.Int); //SqlDbType is enum

Upvotes: 1

radu florescu
radu florescu

Reputation: 4363

This might be a solution:
This parameter should be the exact name of the parameter from your stored procedure("yourParameter").

using (var conn = new SqlConnection(connectionString))
{
var command = new SqlCommand("ProcedureName", conn){CommandType = CommandType.StoredProcedure };
command.Parameters.AddWithValue("@yourParameter", "someValue");
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}

Upvotes: 1

aked
aked

Reputation: 5835

For more details go thru this link : http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

below code is copied from the above posted link 

static void GetSalesByCategory(string connectionString,string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;

        command.CommandText = "SalesByCategory";       
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

Upvotes: 0

Ravi Vanapalli
Ravi Vanapalli

Reputation: 9950

You can use command.Parameters.AddWithValue("@number", TextBox1.Text)

Happy coding!!

Upvotes: 1

vulkanino
vulkanino

Reputation: 9134

command.Parameters.Add(
        new SqlParameter("@customerId", custumerId));

Upvotes: 1

SimSimY
SimSimY

Reputation: 3686

I thing you need to be more specific.

What is the problem with using command.Parameters.AddWithValue ?

Upvotes: 1

Yuck
Yuck

Reputation: 50865

Like this:

// this would work for a varchar or nvarchar parameter
command.Parameters.AddWithValue("@yourParameter", "someValue");
// this would work for an integer parameter
command.Parameters.AddWithValue("@someInt", 1234);

Obviously you need any code to add parameters to the Parameters collection before you try to call command.ExecuteNonQuery();.

Upvotes: 2

Related Questions