基于字符串值搜索整数列

Question

基于字符串值搜索整数列

c#sqlsql-servert-sqlsql-server-2014

21

我在sql server 2014中有一个视图View_Booking:

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     1
4       Lunch     1
6       Dinner    0
7       Lunch     1

在c#中，我使用了一个gridview，并像这样将bkStatus转换为字符串：

<asp:Label ID="lblStatus" Text='<%# (Eval("bkStatus")+"" == "1") ? "Booked" : "Pending" %>'
    ... ></asp:Label>

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     Booked
4       Lunch     Booked
6       Dinner    Pending
7       Lunch     Booked

现在我正在使用以下查询搜索视图：

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus LIKE << ? >>

但是不知道如何搜索在c#中传递为字符串，而在sql中是整数的bkStatus？

- user1773603

9个回答

6

您需要一个搜索框，用户可以使用bkID、bkSlot或bkStatus进行搜索。如果搜索文本是Booked或Pending，我们必须为bkStatus添加过滤器，这将是数据库中的整数字段。对于查询的使用和参数化，我还要在这里提到一些事情，以实现更智能、更安全的执行方式。因此，我建议构建并执行以下查询：

int statusCode = -1;
if(keyword.ToLower() == "booked")
   statusCode = 1;
else if(keyword.ToLower() == "pending")
   statusCode = 0;
string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection("connectionString here"))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.int).value = statusCode;
        sqlCommand.ExecuteNonQuery();
     }
}

请注意以下事项：

如果您想为 book、Pend 等包括 bkStatus 过滤器，则必须相应地更改条件，使用 .Contains() 或 .StartsWith() 替换 .ToLower()
将 statusCode 初始化为 -1，以避免基于 bkStatus 的过滤器影响所有其他值

- sujith karivelil

3

使用CHOOSE()来解码bkStatus并使用TRY_CONVERT()测试bkID的另一种选项。

示例

Declare @KeyWord varchar(50) = 'Pending';

Select * 
 From  View_Booking 
 Where bkID = try_convert(int,@KeyWord)
    or bkSlot like '%'+@KeyWord+'%'
    or choose(bkStatus+1,'Pending','Booked')=@KeyWord

返回

bkID    bkSlot  bkStatus
6       Dinner  0

- John Cappelletti

@Asif.Ali 你使用了TRY_CONVERT吗？（WHERE语句的第一个条件） - John Cappelletti

这是一个指向 dbfiddle 的链接 http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=71a685dd956a309648abb100dcc0b2ab - John Cappelletti

1

@Asif.Ali 你确定是2014年吗？试试选择Select @@Version。 - John Cappelletti

返回

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)  Feb 20 2014 20:04:26  版权所有 (c) Microsoft Corporation 企业版 (64位) 在 Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 上运行

- user1773603

1

@Asif.Ali 很好。select name, compatibility_level from sys.databases 显示什么？ - John Cappelletti

显示剩余6条评论

3

如果关键词是状态名称而不是状态ID，我会创建一个BookingStatus表，在那里有bkStatus和bkStatusTitle列，并将其连接到View_Booking。然后你可以在bkStatusTitle上轻松地使用LIKE。

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(16)) LIKE '%' + @keyword + '%' 
OR bkSlot LIKE '%' + @keyword + '%' 
OR bkStatusTitle LIKE '%' + @keyword + '%'

如果keyword是bkStatus的字符串表示形式，我只需查看这些值是否相同。

顺便提一下，将用户输入与SQL查询连接起来构建SQL查询语句是一个不好的想法，比如'%' + keyword + '%'。这容易受到SQL注入攻击。最好使用SQL参数将用户输入传递给SQL查询。在SQL中使用'%' + @keyword + '%'，在C#中使用下面的示例代码会更安全。

sqlCommand.Parameters.Add("@keyword", SqlDbType.VarChar, 1000);
sqlCommand.Parameters["@keyword"].Value = searchText;

参数化查询还可以使同一查询文本适用于多个请求，这又使 SQL Server 可以缓存 SQL 执行计划并重复使用它们，从而提供略微更好的性能。

- Justinas Marozas

3

您可以使用declare函数创建一个临时表，其中包含bkStatus列表。

通过将bkstatus作为外键创建查询将更加容易。之后，您就不必再使用cast或like函数了，这会有点低效。

您可以尝试以下代码：

declare @bkstatus table (number int primary key , bkstatus varchar(10) )
insert into @bkstatus (number , bkstatus)
values ( 0 , 'Pending'), (1 , 'Booked')

然后使用以下查询：

SELECT * FROM View_Booking v
INNER JOIN @bkstatus b on v.bkstatus = b.number
WHERE b.bkstatus =  @keyword

- R. Firman Insan Muhammad

2

您的bkStatus是整数类型。在视图中，您将整数值转换为用户可理解的字符串。到这里为止一切都没问题。现在，为了让用户进行搜索，您只需要将字符串翻译回整数，并搜索整数即可。

保持简单

searchStatusKey = yourVariableHodingTheString == "Booked" ? 1 : 0;

然而，为了避免繁琐的错误和对代码多处无痛升级（比如因为他们决定在那里添加另一个状态）我建议在这里使用翻译表。类似于HashMap（关联数组）。

var statusMap = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked" },
    /* can always add more pairs in the future as needed */
};

现在，假设你的参数存储在一个名为searchStatus的变量中。

searchStatusKey = statusMap.FirstOrDefault(x => x.Value == searchStatus).Key;

现在只需在where部分提供参数，其值为searchStatusKey即可完成。

select * from View_Booking where bkStatus = << ? >>

- ptheofan

1

看起来你想在多个列中自由搜索。这是一个很常见的问题，真正的解决方案可以在动态搜索条件的www.Sommarskog.se找到。

你的解决方案似乎容易受到SQL注入攻击。我建议你实现类似于存储过程search_orders_3的东西。

- Henrik Staun Poulsen

最好将重要细节包含在答案中。 - user1773603

@AsifAli72090；嗯，我认为我的指向Sommarskog是很重要的，如果你关心查询的速度的话。这可能不是你1GB测试数据的问题，但一旦你的数据库比你的内存大，你可能必须等待SQL执行。Sommarskog展示了如何避免这个雷区。 - Henrik Staun Poulsen

@AsifAli72090，请检查您的代码是否避免了SQL注入。这很困难。这仍然是OWASP列表的首要问题；https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project - Henrik Staun Poulsen

1

我将专注于您问题的这一部分（它是问题本身吗？）：

但不知道如何搜索从C#传递为字符串而在SQL中为整数的bkStatus？

在SQL中处理这个问题的一种方法是使用CASE子句。在您的特定情况下，您可以（并不意味着应该）做以下事情：

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus = CASE '%"+keyword+"%' WHEN 'Booked' THEN CAST(1 AS INT) WHEN 'Pending' THEN CAST(0 AS INT) WHEN ... THEN ... ELSE ... END'

但是我建议按照@un-lucky的回答所示使用参数。在这里，我们还可以讨论更多关于最佳实践的内容，因此我建议您查看以下文章：

查找表格：您说bkStatus是INT类型，因此我假设您可能有更多选项，例如：已预订或已取消。在这种情况下，随着每个选项的添加，您的实际代码可能会变得越来越凌乱。
使用 ADO.NET 的最佳实践：您没有指定如何从前端访问数据库。即使这篇文章已经存在多年，其中大部分内容仍然是当前的。我认为这可能会有所帮助。
构建更好的 Entity Framework：如果您正在使用Entity Framework访问数据库。

希望能有所帮助。

- Emilio Lucas Ceroleni

1

为BookingStatus创建一个枚举，并编写一个函数，接受字符串并返回枚举值。请参考以下代码。

public enum BookingStatus {
    [Description("Pending")]
    Pending = 0,
    [Description("Booked")]
    Booked = 1
}

现在的函数如下：

    public static T GetValueFromDescription<T>(string p_description)
    {
        var type = typeof(T);
        if (!type.IsEnum) throw new InvalidOperationException();
        foreach (var field in type.GetFields())
        {
            var attribute = Attribute.GetCustomAttribute(field,
                typeof(DescriptionAttribute)) as DescriptionAttribute;
            if (attribute != null)
            {
                if (attribute.Description == p_description)
                    return (T)field.GetValue(null);
            }
            else
            {
                if (field.Name == p_description)
                    return (T)field.GetValue(null);
            }
        }
        throw new ArgumentException("Not found.", "description");
        // or return default(T);
    }

现在在SQL查询的参数中，使用参数"Booked"或"Pending"调用此函数，它将返回枚举BookingStatus.Booked。您可以轻松提取其中的int值。

(int)BookingStatus.Booked // will give 1

- DhavalR

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Hadi · Accepted Answer

一些建议

您提供的查询需要优化:

首先，使用 CAST(bkID AS NVARCHAR(MAX)) 会影响查询性能，因为它不会使用任何索引，同时转换为 NVARCHAR(MAX) 会降低性能。
bkStatus是一个数值列，所以必须使用 = 运算符和数值 (0 或 1 或 ...) 进行比较，文本值是在 asp 标记中定义的，而不是在数据库中定义的，因此它们用于应用程序级别而不是数据级别。
如果您使用 CAST(bkID AS NVARCHAR(MAX)) 搜索包含特定数字的 bkid 列 (例如: 搜索 1 -> 结果 1,10,11,...)，那么请尝试转换为特定大小 (例如: CAST(bkID as NVARCHAR(10))
建议使用参数化查询以获得更好的性能，并防止SQL注入攻击。请参考 @un-lucky 的答案
您可以使用字典对象存储与关键字相关联的ID值

示例

注意：使用CAST和Like将不使用任何索引，此示例基于您的要求（我试图将我提供的建议与其他建议相结合）

var dicStatus = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked"  },
    { 2, "Cancelled" }
    // ...
};

string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(10)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.Int).value = dicStatus.FirstOrDefault(x => x.Value == keyword).Key;
        sqlCommand.ExecuteNonQuery();
     }
}

如果BkID是一个整数列，最好使用

sqlCommand.Parameters.Add("@bkID", SqlDbType.Int).value = (Int)keyword ;

基于字符串值搜索整数列

一些建议

示例

参考文献和有用链接