如何使用SQL参数进行选择查询? [英] How to use sql parameters for a select query?

问题描述

我需要基于对一组记录的喜欢"匹配来获取记录，

I need to fetch the records based on a 'like' match against a set of records,

以下使用im的查询不起作用.有人知道查询出了什么问题吗?

The below query im using is not working . Does anyone knows what's wrong with the query?

 sqlCommand.CommandText =String.Format("SELECT * FROM Customer" +
                " WHERE (Name like @Name)","'%" +searchString.Trim()+"%'");
            sqlCommand.Parameters.AddWithValue("Name", searchString);

此查询未获取所需的记录.

This query isnt fetching the desired records.

在运行上述代码段时出现以下错误:

I'm getting the following error while running the above snippet:

Must declare the scalar variable "@Name".

推荐答案

这种方式会发生什么?

sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", "%" + searchString + "%");

您还可以按以下方式对其进行编码，以避免首先使用所有通配符格式:

You could also code it as follows to avoid all the wildcard formatting in the first place:

sqlCommand.CommandText = "SELECT * FROM Customer WHERE CHARINDEX(@Name, Name) > 0;";
sqlCommand.Parameters.AddWithValue("@Name", searchString);

如果您要坚持以不安全的方式进行操作，请至少将在 searchString 中找到的所有单引号加倍，例如

If you're going to insist on doing it the unsafe way, at the very least double-up any single quotes found in searchString, e.g.

searchString.Replace("'", "''")

这篇关于如何使用SQL参数进行选择查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！