How to pass int parameters in Sql commandText

Question

How to pass an integer value like SQL command parameters?

I am trying like this:

cmd.CommandText = ("insert_questions '" + 
cmd.Parameters.AddWithValue(store_result,store_result) + "','" + 
cmd.Parameters.AddWithValue(store_title, store_title) + "', '" + 
cmd.Parameters.AddWithValue(store_des, store_des) + "'");

store_result is int and other 2 parameter are string type.

store_result is giving a error message like below.

Argument 1: cannot convert from 'int' to 'string'

in SP ,there is a another int type variable which will get store_result's value.

What is correct syntax for passing int parameters?

Thank you.

Answer 1

the correct way to go is

using(var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using(var command = new SqlCommand("SELECT * FROM Table WHERE ID=@someID",connection))
    {
        command.Parameters.AddWithValue("someID",1234);
        var r = command.ExecuteQuery();
    }
}

this means it works even with text queries. it's even easier with stored procedures - instead of sql query you just provide stored procedure name:

using(var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using(var command = new SqlCommand("insert_sproc",connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("someID",1234);
        var r = command.ExecuteQuery();
    }
}

Answer 2

it should be like this,

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ("insert_questions") ;
cmd.Parameters.AddWithValue("@value", valueHere);
cmd.Parameters.AddWithValue("@value2", valueHere);

note that @value and @value2 are the parameters declared in your stored procedure.

Answer 3

Try this:

        cmd.CommandText = ("insert_questions @store_result, @store_title, @store_des");
        cmd.Parameters.AddWithValue("@store_result", store_result);
        cmd.Parameters.AddWithValue("@store_title", store_title);
        cmd.Parameters.AddWithValue("@store_des", store_des);

Answer 4

You don't concatenate the SqlParameter instances; instead:

cmd.CommandText = "insert_questions @store_result, @store_title, @store_des";
cmd.Parameters.AddWithValue("store_result", store_result);
cmd.Parameters.AddWithValue("store_title", store_title);
cmd.Parameters.AddWithValue("store_des", store_des);

The names used in AddWithValue are used in the TSQL as @store_result, etc.

If we assume that inert_questions is actually a proc, then it is even simpler:

cmd.CommandText = "insert_questions";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("store_result", store_result);
cmd.Parameters.AddWithValue("store_title", store_title);
cmd.Parameters.AddWithValue("store_des", store_des);

Alternatively, if all of that seems tedious, tools like dapper-dot-net make this easier:

someOpenConnection.Execute("insert_questions",    
      new { store_result, store_title, store_des },
      commandType: CommandType.StoredProcedure);

Answer 5

 private void ProcessData(MerchantLead data)
        {            
            var cmdCardJournal = new SqlCommand { CommandText = "BusinessLeadsInsert" };
            var sql = new Sqlquery();
            sql._cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            cmdCardJournal.Parameters.AddWithValue("@Name", data.FirstName);
            cmdCardJournal.Parameters.AddWithValue("@LastName", data.LastName);
            cmdCardJournal.Parameters.AddWithValue("@BusinessName", data.BusinessName);
            cmdCardJournal.Parameters.AddWithValue("@PhoneNumber", data.PhoneNumber);
            cmdCardJournal.Parameters.AddWithValue("@Email", data.Email);
            cmdCardJournal.Parameters.AddWithValue("@Website", data.Website);
            cmdCardJournal.Parameters.AddWithValue("@OTP", data.OTP);
            cmdCardJournal.Parameters.AddWithValue("@OTPExpired", DateTime.UtcNow.AddMinutes(30));

            sql.SpCommandExeNon(cmdCardJournal);
}