SqlCommand.Prepare方法要求所有参数有一个明确设置类型 [英] SqlCommand.Prepare method requires all parameters to have an explicitly set type

问题描述

我有下面的代码片段在建立根据提供的字典的值的格式一套那里的条件我的WCF web服务。

I have the following snippet of code in my WCF web service that builds a set of where conditions according to the formatting of the values of a provided dictionary.

public static Dictionary<string, string>[] VehicleSearch(Dictionary<string, string> searchParams, int maxResults)
{
    string condition = "";
    foreach (string key in searchParams.Keys)
    {
        //Split out the conditional in case multiple options have been set (i.e. SUB;OLDS;TOY)
        string[] parameters = searchParams[key].Split(';');
        if (parameters.Length == 1)
        {
            //If a single condition (no choice list) check for wildcards and use a LIKE if necessary
            string predicate = parameters[0].Contains('%') ? " AND {0} LIKE @{0}" : " AND {0} = @{0}";
            condition += String.Format(predicate, key);
        }
        else
        {
            //If a choice list, split out query into an IN condition
            condition += string.Format(" AND {0} IN({1})", key, string.Join(", ", parameters));
        }
    }

    SqlCommand cmd = new SqlCommand(String.Format(VEHICLE_QUERY, maxResults, condition));
    foreach (string key in searchParams.Keys)
        cmd.Parameters.AddWithValue("@" + key, searchParams[key]);
    cmd.Prepare();

请注意，在字典中的值显式设置为字符串，他们是正在发送的唯一项目到 AddWithValue 语句。这会产生这样的SQL：

Note that the values in the dictionary are explicitly set to strings and that they are the only items being sent into the AddWithValue statements. This produces SQL like this:

SELECT TOP 200 MVINumber AS MVINumber
    , LicensePlateNumber
    , VIN
    , VehicleYear
    , MakeG
    , ModelG
    , Color
    , Color2
FROM [Vehicle_Description_v]
WHERE 1=1 AND VIN LIKE @VIN

和错误出来说，

的 System.InvalidOperationException：SqlCommand.Prepare方法需要
的所有参数有一个明确设置类型的

所有的搜索，我已经做了说，我要告诉 AddWithValue 我正在准备值的类型，但我所有的准备值都是字符串和我见过当他们处理字符串不进行任何额外的例子。 ？我想什么

All searching that I have done says that I need to tell AddWithValue the type of values that I'm preparing, but all of my prepared values are strings and all examples I've seen don't perform anything extra when they're dealing with strings. What am I missing?

推荐答案

而不是这样的：

cmd.Parameters.AddWithValue("@" + key, searchParams[key]);

您需要使用这样的：

cmd.Parameters.Add("@" + key, SqlDbType.******).Value = searchParams[key];

您需要能够以某种方式确定的数据类型您的参数将有成为。

You need to be able to somehow determine what datatype your parameters will have to be.

这可能是这样的：

• SqlDbType .INT 的整数值


• SqlDbType.VarChar 非Unicode字符串（不要忘记指定字符串的长度！）


• SqlDbType.UniqueIdentifier 为的GUID结果
  的等的

• SqlDbType.Int for integer values
• SqlDbType.VarChar for non-Unicode strings (don't forget the specify a length of the string!)
• SqlDbType.UniqueIdentifier for Guids
  etc.

使用 AddWithValue 便利 - 但它留给了ADO.NET猜测数据类型的基础上，传递的价值大多数时候，这些猜测都还不错 - 。但有时，他们可以关闭

Using AddWithValue is convenient - but it leaves it up to ADO.NET to guess the datatype, based on the value passed in. Most of the time, those guesses are pretty good - but at times, they can be off.

我建议您始终明确说你想要的数据类型。

I would recommend that you always explicitly say what datatype you want.

这篇关于SqlCommand.Prepare方法要求所有参数有一个明确设置类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！