向 IDbCommand 添加参数 [英] Adding parameters to IDbCommand

问题描述

我正在创建一个小的辅助函数来返回一个 DataTable.我想在 ADO.Net 支持的所有提供程序中工作，所以我想尽可能使用 IDbCommand 或 DbCommand.>

我遇到了以下代码的绊脚石:

 private static DataTable QueryImpl(ref IDbConnection conn, String SqlToExecute, CommandType CommandType, Array Parameters){设置连接(参考连接)；//将容量设置为 20，因此前 20 个分配更快...数据表 dt = 新数据表();使用 (IDbCommand cmd = conn.CreateCommand()){cmd.CommandText = SqlToExecute;cmd.CommandType = 命令类型；if (Parameters != null && Parameters.Length > 0){for (Int32 i = 0; i 

执行此代码时，我收到一个 InvalidCastException，其中说明以下内容:

<块引用>

SqlParameterCollection 只接受非空的 SqlParameter 类型对象，不接受 String 对象.

代码掉线了:

cmd.Parameters.Add(Parameters.GetValue(i));

有什么想法吗?

感谢对上述代码的任何改进.

<小时>

实际解决方案:

 private static readonly Regex regParameters = new Regex(@"@w+", RegexOptions.Compiled);私有静态 DataTable QueryImpl(ref DbConnection conn, String SqlToExecute, CommandType CommandType, Object[] 参数){设置连接(参考连接)；数据表 dt = 新数据表();使用 (DbCommand cmd = conn.CreateCommand()){cmd.CommandText = SqlToExecute;cmd.CommandType = 命令类型；if (Parameters != null && Parameters.Length > 0){MatchCollection cmdParams = regParameters.Matches(cmd.CommandText);列表<字符串>param = new List();foreach(cmdParams 中的变量){if (!param.Contains(el.ToString())){param.Add(el.ToString());}}Int32 i = 0;IDbDataParameter dp;foreach(参数中的字符串 el){dp = cmd.CreateParameter();dp.ParameterName = el;dp.Value = 参数[i++];cmd.Parameters.Add(dp);}}dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);}返回 dt;}

感谢您的想法/链接等:)

解决方案

我相信 IDbCommand 有一个 CreateParameter() 方法:

var parameter = command.CreateParameter();parameter.ParameterName = "@SomeName";参数值 = 1;命令.Parameters.Add(参数);

I am creating a small helper function to return a DataTable. I would like to work across all providers that ADO.Net supports, so I thought about making everything use IDbCommand or DbCommand where possible.

I have reached a stumbling block with the following code:

    private static DataTable QueryImpl(ref IDbConnection conn, String SqlToExecute, CommandType CommandType, Array Parameters)
    {
        SetupConnection(ref conn);
        // set the capacity to 20 so the first 20 allocations are quicker...
        DataTable dt = new DataTable();
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = SqlToExecute;
            cmd.CommandType = CommandType;
            if (Parameters != null && Parameters.Length > 0)
            {
                for (Int32 i = 0; i < Parameters.Length; i++)
                {
                    cmd.Parameters.Add(Parameters.GetValue(i));
                }
            }
            dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);
        }
        return dt;
    }

When this code is executed, I receive an InvalidCastException which states the following:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

The code falls over on the line:

cmd.Parameters.Add(Parameters.GetValue(i));

Any ideas?

Any improvements to the above code is appreciated.

---

Actual solution:

    private static readonly Regex regParameters = new Regex(@"@w+", RegexOptions.Compiled);
    private static DataTable QueryImpl(ref DbConnection conn, String SqlToExecute, CommandType CommandType, Object[] Parameters)
    {
        SetupConnection(ref conn);
        DataTable dt = new DataTable();
        using (DbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = SqlToExecute;
            cmd.CommandType = CommandType;
            if (Parameters != null && Parameters.Length > 0)
            {
                MatchCollection cmdParams = regParameters.Matches(cmd.CommandText);
                List<String> param = new List<String>();
                foreach (var el in cmdParams)
                {
                    if (!param.Contains(el.ToString()))
                    {
                        param.Add(el.ToString());
                    }
                }
                Int32 i = 0;
                IDbDataParameter dp;
                foreach (String el in param)
                {
                    dp = cmd.CreateParameter();
                    dp.ParameterName = el;
                    dp.Value = Parameters[i++];
                    cmd.Parameters.Add(dp);
                }
            }
            dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);
        }
        return dt;
    } 

Thanks for ideas/links etc. :)

解决方案

I believe IDbCommand has a CreateParameter() method:

var parameter = command.CreateParameter();
parameter.ParameterName = "@SomeName";
parameter.Value = 1;

command.Parameters.Add(parameter);

这篇关于向 IDbCommand 添加参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！