如何将国家字符集应用于动态查询

Question

我正在尝试在变量名称之前应用N Unicode如如何将"LIKE"语句与 unicode 字符串一起使用中所述？

使用以下代码，我收到以下错误。这里需要纠正什么？

例外：列名称"N@input"无效。

string commandText = @"SELECT AccountType,* 
                          FROM Account 
                          WHERE AccountType LIKE N@input ";

法典

    static void Main(string[] args)
    {
        string result = DisplayTest("Daily Tax Updates:  -----------------        Transactions");
    }
    private static string DisplayTest(string searchValue)
    {
        string test = String.Empty;
        string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string commandText = @"SELECT AccountType,* 
                          FROM Account 
                          WHERE AccountType LIKE N@input ";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddWithValue("@input", "%" + searchValue + "%");
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            test = reader.GetString(0);
                        }
                    }
                }
            }
        }
        return test;
    }

Answer 1

我看到了一些问题。

   string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE N@input";

应该是

   string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE @input";
...
 command.Parameters.Add("@input",System.Data.SqlDbType.NVarChar,<<size>>);
 command.Parameters[0].Value = "%" + searchValue + "%";

Answer 2

我看到您正在尝试使用 nvarchar 参数。我认为 .net 默认这样做.AddWithValue

我不确定为什么你需要 nvarchar 的类型转换，没有"N"部分你应该没问题。

当您想要指定字符串文本应被视为 nvarchar 而不是 varchar 时，您需要的那部分

，如 SELECT * from Table where field like N'%VALUE%'

否则，您只需将变量/参数声明为 nvarchar

Answer 3

取

自此堆栈 堆栈溢出

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;

Answer 4

试试这个 -

private static string DisplayTest(string searchValue)
{
    string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string commandText = @"SELECT AccountType,* FROM Account WHERE AccountType LIKE @input";
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.Add("@input", SqlDbType.NVarChar);
            command.Parameters["@input"].Value = string.Format("%{0}%", searchValue);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        return reader.GetString(0);
                    }
                }
            }
        }
    }
    return String.Empty;
}