C# Sql参数集合

Sql Parameter Collection

提问by Hyman

I have 5 parameters and I want to send them to the method:

我有 5 个参数，我想将它们发送到该方法：

public static SqlCommand getCommand(string procedure, SqlParameter[] parameter)
{
   Sqlcommand cmd;
   return cmd
}

Can I send these paramters at one time like this?

我可以像这样一次性发送这些参数吗？

SqlParameterCollection prm;
prm.Add(p1);
prm.Add(p2);
prm.Add(p3);
prm.Add(p4);
prm.Add(p5);
sqlcommand cmd = getCommand(prm);

采纳答案by Drejc

Or create an array of parameters by hand:

或者手动创建一个参数数组：

SqlParameter[] parameter = {
new SqlParameter(...), 
new SqlParameter(...), 
new SqlParameter(...)
};

But I don't see what should be wrong with your approach. It simple, readable and understendable.

但我不明白你的方法应该有什么问题。它简单、易读且易于理解。

回答by Damien

I don't see what's wrong with that? You do know that, if this is .NET, you need to attach the parameters to the SqlCommandobject?

我不明白这有什么问题？您知道，如果这是 .NET，您需要将参数附加到SqlCommand对象吗？

So:

所以：

SqlCommand query = new SqlCommand(sqlString, Connection);
query.Parameters.AddWithValue(parameter,valueToPass);

etc?

等等？

Sorry if that's not related, not completely sure on your question? Your method doesn't really do anything, I take you left out the code and just put in a dummy for the purposes of asking the question? You can pass an array as an arguement so you just need to spilt it up?

抱歉，如果这不相关，对您的问题不完全确定？您的方法并没有真正做任何事情，我带您省略了代码，只是为了提出问题而放入了一个假人？您可以将数组作为参数传递，因此您只需要将其溢出即可？

回答by BFree

Well, that won't compile because in your call to getCommand you're not passing in a string with the procedure, but as far as the array, that should work no problem.

好吧，这不会编译，因为在您对 getCommand 的调用中，您没有传递带有过程的字符串，但就数组而言，这应该没问题。

回答by Dave Lucre

Using thisas inspiration, this code worked for me:

使用此为灵感，此代码为我工作：

List<SqlCeParameter> parameters = new List<SqlCeParameter>();

parameters.Add(new SqlCeParameter("@Username", NewUsername));
parameters.Add(new SqlCeParameter("@Password", Password));

cmd.Parameters.AddRange(parameters.ToArray());

回答by Stringlive

Form1.cs

表格1.cs

    static private void FunctionCall()
    {

        string connectionString = "DATA Source=nwind;server=GRAPHICS\SQLEXPRESS;Persist Security Info=False;Integrated Security=SSPI;Connect Timeout=30";
        string sSqlQuery;

        DataSet ds;
        DataTable dt;

        // Prepare SQL Query
        sSqlQuery = @"
        select content " +
        "from " +
        "[TBL] where id = '000-000'";

        SqlParameter[] sqlParams = {
                new SqlParameter("",SqlDbType.Int), 
                new SqlParameter("",SqlDbType.VarChar), 
                new SqlParameter("",SqlDbType.VarChar)
        };

        // Read from database
        ds = SqlHelper.ExecuteNonQuery(connectionString, sSqlQuery, CommandType.Text, sqlParams);
        dt = ds.Tables[0];
     }

SqlHelper.cs

SqlHelper.cs

// Executes a non query

// 执行非查询

public static int ExecuteNonQuery (string connectionString, string cmdText, CommandType type, SqlParameter[] prms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    cmd.CommandType = type;

                if (prms != null)
                {
                    foreach (SqlParameter p in prms)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
    }

回答by Umut OVECOGLU

Here is my code.You can use all parameter properties like name,value,type etc.

这是我的代码。您可以使用所有参数属性，如名称、值、类型等。

int SelectedListID = 6;
string selectedPrefix = "IP";
string sqlQuery = "select * from callHistory where ImportID=@IMPORTID and Prefix=@PREFIX"

SqlParameter[] sParams = new SqlParameter[2]; // Parameter count

sParams[0] = new SqlParameter();
sParams[0].SqlDbType = SqlDbType.Int;
sParams[0].ParameterName = "@IMPORTID";
sParams[0].Value = SelectedListID;

sParams[1] = new SqlParameter();
sParams[1].SqlDbType = SqlDbType.VarChar;
sParams[1].ParameterName = "@PREFIX";
sParams[1].Value = selectedPrefix;


SqlCommand cmd = new SqlCommand(sqlQuery, sConnection);

if (sParams != null)
{
    foreach (SqlParameter sParam in sParams)
    {
        cmd.Parameters.Add(sParam);
        Application.DoEvents();
    }
}

回答by Kashif Faraz

1.

1.

public IEnumerable<SqlParameter> GetAndSetParameters(List<Tuple<string, string>> parameters){
            List<SqlParameter> paramlist = new List<SqlParameter>();

            foreach (var item in parameters)
        {
            paramlist.Add(new SqlParameter(item.Item1, item.Item2));
        }
        return paramlist;
    }

2. pass parameters

2.传递参数

 public List<Tuple<string, string>> GetUserParameter(){
        List<Tuple<string, string>> list = new List<Tuple<string, string>>();
                list.Add(new Tuple<string, string>("@User",user.UserID));
                   return list;
        }

3. finally use it:

3.最后使用它：

 SqlCommand oCmd = new SqlCommand(oString, myConnection);
                oCmd.Parameters.AddRange(GetAndSetParameters(GetUserParameter()).ToArray());