在带有FROM子句中参数的存储过程中使用LIKE和％ [英] Using LIKE and % in a stored procedure with parameter in FROM clause

问题描述

我正在创建一个网页来托管数据库.

I am creating a web page to host a database.

我希望此网页具有搜索框功能，可以为Visual Studio 2017中的SSMS 2014中的表更新GridView.

I want this web page to have a search box feature that can update a GridView in visual studio 2017 for tables in SSMS 2014.

我希望此GrideView是动态的，因为最终用户可以选择一个表，一个列，然后指定"searchString"以应用于列中的数据.

I want this GrideView to be dynamic in that a end user could select a table, a column, and then specify a "searchString" to apply to the data in the column.

网页如下:

转到代码.

在搜索按钮单击事件上，我希望将三个文本框中的每个值都传递到存储过程中.

On the search button click event I want the values in each of the three text boxes to be passed into a stored procedure.

这是按钮单击事件的当前代码.

Here is the current code for the button click event.

protected void btnSearch_Click(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CID1ConnectionString"].ConnectionString))
    {
        SqlDataAdapter searchAdapter = new SqlDataAdapter("Search", con);
        searchAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        searchAdapter.SelectCommand.Parameters.AddWithValue("@TableName", TableSearchBox.Text.Trim());   // passing text in first text box in
        searchAdapter.SelectCommand.Parameters.AddWithValue("@columnSpecifier", ColumnSearchBox.Text.Trim());   // passing text in second text box in
        searchAdapter.SelectCommand.Parameters.AddWithValue("@searchString", searchStringBox.Text.Trim());   // passing text in third text box in

        DataTable temptable = new DataTable(); //table to have data that satisfies searchString copied to
        searchAdapter.Fill(temptable); //filling table from adapter

        tableDisplay.DataSource = temptable;
        //tableDisplay.Columns[0].Visible = false;
        tableDisplay.DataBind();//bind step
    }
}

这是我当前的存储过程:

Here is my current stored procedure:

ALTER PROCEDURE dbo.Search 
    (@tableName NVARCHAR(50),
     @columnSpecifier NVARCHAR(50),
     @searchString NVARCHAR(50)) 
AS 
     EXEC('SELECT * FROM ' + @tableName + ' WHERE ' + @columnSpecifier + ' LIKE '' + @searchString + %''')

如果最终用户在第一个文本框中填充"Basic_Info"，第二个文本框填充"Name"，最后一个文本框填充"M"，则应该实现与此类似的查询.

Which is supposed to achieve a query similar to this if the end user populated the first text box with "Basic_Info", the second text box with "Name", and the final text box with "M".

SELECT Name 
FROM Basic_Info 
WHERE Name LIKE 'M%'

它出现是因为我在FROM子句中使用了动态表名，所以我需要使用动态SQL.我将查询放在EXEC块中，并用单引号(')将SQL语法引起来.这些单引号似乎无法在我的LIKE子句中使用％运算符，但也许我只是看不到它.

It appears since I am using a dynamic table name in the FROM clause I need to use dynamic SQL. I have put my query in an EXEC block and surrounded my SQL syntax with single quotes ('). These single quotes appear to make using the % operator in my LIKE clause impossible but maybe I'm just not seeing it.

是否有实现此功能的方法?我应该备份并以其他方式执行此操作吗?我读过，这可能会导致SQL注入，这听起来像是要避免的事情.即使是关于此帖子及其格式的任何建议，我们也应感谢.这是我关于堆栈溢出的第一个问题！

Is there a way of achieving this functionality? Should I back up and do this another way? I have read that this might cause SQL injection which sounds like something to be avoided. Any advice is appreciated even about this post and its format. This is my first question on stack overflow!

显示存储过程，不需要使用参数.我的最终按钮单击事件看起来像这样，只是获取文本框文本值来填写查询.

Turns out the stored procedure and parameter use is unnecessary. My final button click event looks like this and just gets the textbox text value to fill out the query.

        protected void btnSearch_Click(object sender, EventArgs e)
    {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CID1ConnectionString"].ConnectionString))
            {
            DataTable temptable = new DataTable(); //table to have data that satisfies searchString copied to
            SqlDataAdapter searchAdapter = new SqlDataAdapter("SELECT * FROM " + TableSearchBox.Text.Trim() + " WHERE " + ColumnSearchBox.Text.Trim() + " LIKE '"  + searchStringBox.Text.Trim() + "%'", con);
            searchAdapter.Fill(temptable); //filling table from adapter
            tableDisplay.DataSource = temptable;
            tableDisplay.DataBind();//bind step
            }
    }

推荐答案

这是一个参数化的动态SQL示例，使用QUOTENAME作为标识符:

Here's a parameterized dynamic SQL example, using QUOTENAME for identifiers:

CREATE PROCEDURE dbo.Search
    @tableName sysname,
    @columnSpecifier sysname,
    @searchString nvarchar(50)
AS
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@tableName) + N' WHERE ' + QUOTENAME(@columnSpecifier) + N' LIKE @searchString + ''%'';';
EXEC sp_executesql
      @SQL
    , N'@searchString nvarchar(50)'
    , @searchString = @searchString;
GO

我建议通常使用避免AddWithValue ，因为它会从中推断出SQL数据库类型提供的.NET类型.尽管这里没有关系，因为您使用的是存储过程，并且System.String映射到SQL Server nvarchar，但是最好显式指定所需的SqlDbType和长度(或精度和小数位数).下面是执行此操作的一种方法.

I suggest one generally avoid AddWithValue because it infers the SQL database type from the provided .NET type. Although this isn't a concern here because you are using a stored procedure and System.String maps to SQL Server nvarchar, it is best to specify the desired SqlDbType and length (or precision and scale) explicitly. Below is one method to do that.

searchAdapter.SelectCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 128).Value = TableSearchBox.Text.Trim());   // passing text in first text box in
searchAdapter.SelectCommand.Parameters.Add("@columnSpecifier", SqlDbType.NVarChar, 128).Value = ColumnSearchBox.Text.Trim());   // passing text in second text box in
searchAdapter.SelectCommand.Parameters.Add("@searchString", SqlDbType.NVarChar, 50).Value = searchStringBox.Text.Trim());   // passing text in third text box in

这篇关于在带有FROM子句中参数的存储过程中使用LIKE和％的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！