如何处理动态SQL参数 [英] How to handle dynamic sql parameters

问题描述

什么是处理动态SQL参数的好办法？

What is a good way to handle dynamic sql parameters?

我有一个搜索形式，发生在一大堆不同的搜索参数。如果参数是空的，我在SQL字符串参数将其拧或减慢查询？

I have a search form that takes in a whole bunch of different search parameters. If the parameters are empty and I have the parameter in the sql string will it screw or slow down the query?

推荐答案

根据具体实施中，我们必须把这个问题两种常用的方法：

Depending on the specific implementation, we have two general approaches to this problem:

1）动态地建立在代码中的SQL查询跳过是空的任何参数筛选语句。这是最好的办法，如果你允许用户选择一列多个值（即选择50个州的0个或多个过滤数据）。

1) Dynamically build the filter statement for the SQL query in code skipping any parameters that are empty. This is the best approach if you allow the user to select multiple values for a single column (i.e. select 0 or more of the 50 states to filter the data).

有关例如：

假设txtCondition1和txtCondition2是文本框：

Assuming txtCondition1 and txtCondition2 are textboxes:

        // Assuming conn is an open SqlConnection

        System.Text.StringBuilder sbSQL = new StringBuilder(500);

        List<SqlParameter> cParameters = new List<SqlParameter>();

        // Add a default condition of 1=1 so that all subsequent conditions can be added 
        // with AND instead of having to check to see whether or not any other conditions
        // were added before adding AND.
        sbSQL.Append("SELECT * FROM MyTestTable WHERE 1 = 1 ");

        if (!String.IsNullOrEmpty(txtCondition1.Text)) {
            sbSQL.Append(" AND Column1 = @Column1");
            cParameters.Add(new SqlParameter("@Column1", txtCondition1.Text));
        }
        if (!String.IsNullOrEmpty(txtCondition1.Text))
        {
            sbSQL.Append(" AND Column2 = @Column2");
            cParameters.Add(new SqlParameter("@Column2", txtCondition2.Text));
        }

        SqlCommand oCommand = new SqlCommand(sbSQL.ToString, conn);
        if (cParameters.Count != 0) 
        {
            oCommand.Parameters.AddRange(cParameters.ToArray());
        } 

        // Do something with oCommand

的东西

2）如果这些值更多的限制，我们通常将它们传递到一个存储的程序，这是负责确定该值是否是通过测试参数为emptinesss进行评估，无论是空，空字符串，0为数字，等

2) If the values are more constrained, we usually pass them to a stored procedure, which is responsible for determining whether or not the value is to be evaluated by testing the parameter for "emptinesss", either null, empty string, 0 for numerics, etc.

这篇关于如何处理动态SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！