C# 如何在Sql commandText中传递int参数

How to pass int parameters in Sql commandText

提问by user1627138

How to pass an integer value like SQL command parameters?

如何传递像 SQL 命令参数这样的整数值？

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 是 int ，其他 2 个参数是 string 类型。

store_result is giving a error message like below.

store_result 给出如下错误消息。

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

参数 1：无法从“int”转换为“string”

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

在 SP 中，还有一个 int 类型变量将获得 store_result 的值。

What is correct syntax for passing int parameters?

传递 int 参数的正确语法是什么？

Thank you.

谢谢你。

采纳答案by John Woo

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 @valueand @value2are the parameters declared in your stored procedure.

请注意，@value和@value2是在您的存储过程中声明的参数。

回答by Ivan G

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

回答by Marc Gravell

You don't concatenate the SqlParameter instances; instead:

您不连接 SqlParameter 实例；反而：

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 AddWithValueare used in the TSQL as @store_result, etc.

中使用的名称在AddWithValueTSQL 中使用为@store_result等。

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

如果我们假设这inert_questions实际上是一个过程，那么它甚至更简单：

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:

或者，如果所有这些看起来很乏味，像 dapper-dot-net 这样的工具可以让这变得更容易：

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

回答by aiodintsov

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:

这意味着它甚至可以用于文本查询。使用存储过程更容易 - 而不是 sql 查询，您只需提供存储过程名称：

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

回答by Ram moorti

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