通过表单将多个值传递给关键字中的SQL [英] Passing multiple values through form into SQL in keyword

问题描述

大家好，



我有3个字段的表格。 1是动态卡号，用户可以在点击添加更多按钮时添加更多卡号。其他2个字段是开始日期和结束日期。

我想要的是当用户在填写所有细节后点击提交按钮，这些值将通过后端数据库中的参数传递并获取记录并显示在前端gridview中。

但问题是我无法通过参数传递多个值。

比如提交了3张卡号和日期。

Sql查询应该像

select * from table where cards in（@card） @startdate和@enddate之间的日期。

因此，在上面的示例中，我如何在一个参数（@card）中传递多个卡号，而我不知道将提交多少个卡号。他们可以是3,4,5等等。

请帮助。



我尝试过的事情：



谷歌搜索了很多关于这一点，人们发布使用表值参数。但我不知道如何使用它们，也不知道它们是否适用于这种情况，因为在这种情况下卡的数量不固定。

Hello guys,

I have a form with 3 fields. 1 is card number which is dynamic that is user can add more card numbers when they click on add more button. And the other 2 fields are start date and end date.
What i want is when the user clicks submit button after filling up all details the values will pass through parameters in backend database and fetch the records and will display in frontend gridview.
But the problem is i am unable to pass multiple values through parameter.
Like if 3 card numbers and dates are submitted.
Sql query should be like
select * from table where cards in (@card) and date between @startdate and @enddate.
So in above example how can i pass multiple card numbers in a single parameter(@card) and i do not know that exactly how many card numbers will be submitted. They can be 3, 4, 5 and so on.
Please help.

What I have tried:

Googled alot about this and people have posted to use table valued parameters. But i am not sure how to use them and also do not know if they will work in this scenario as the number of cards is not fixed in this case.

推荐答案

在你的情况下它是数字，所以你可以把它作为逗号分隔列表传递：

In your case it's numbers, so you could just pass it as a comma separated list:

List<int> data = new List<int>() {1, 2, 3};
string sqlCmd = string.Format("SELECT * FROM MyTable WHERE Cards IN ({0}) AND [Date] BETWEEN @startdate AND @enddate", String.Join(",", data));

但这一般不是一个好的方法，因为它让你对SQL注入开放。

这是它的一种方式：在C＃中使用带有SQL的IN子句的SqlParameter | SvenBit - Sebastian Hadinata [ ^ ]

But that's not a good approach in general as it leaves you open to SQL injection.
This is one way round it: Using SqlParameter with SQL's IN Clause in C# | SvenBit - Sebastian Hadinata[^]

1。创建XML类型的SQLParameter @cardxml。将所有用户选择的卡片放入参数中，格式为< value> 1< value> 2 .....等。

2.使用此类查询

'从表格中选择*

，其中卡片来自@cardsxml（

选择n.value（'。/ text（）[1]'，'int'）。节点（'./value'）为v（n）

）

和@startdate和@enddate之间的日期'

3.最好使用SP将其关闭。我假设您使用SQL Server作为后端。



其他方法 -

将逗号分隔的卡值列表发送到后端然后使用 sql server - 在T-SQL中等效的拆分函数描述的技术在后端解析它？ - 堆栈溢出 [ ^ ]

1. Create a SQLParameter @cardxml of XML type. Place all the user selected cards into the parameter in the format <value>1<value>2.....etc.
2. Use a query like this
'select * from table
where cards in (
select n.value('./text()[1]','int') from @cardsxml.nodes('./value') as v(n)
)
and date between @startdate and @enddate'
3. Preferably use a SP to pull this off. I am assuming you are using SQL Server as backend.

Other approaches-
Send a comma separated list of card values to the backend and then parse it in the backend using techniques described sql server - Split function equivalent in T-SQL? - Stack Overflow[^]

以下是如何使用表值参数。 （以上解决方案也可以使用）

1.在数据库中创建表类型：

Here's how to use table-valued parameters. (above solutions can work as well)
1. Create a table type in your database:

CREATE TYPE CardTableType AS TABLE   
( Card VARCHAR(50)  )  

2.您创建一个接受表值参数的存储过程：

2. You create a stored procedure that accepts table-valued parameter:

CREATE PROCEDURE dbo. usp_GetCards 
    @Cards CardTableType READONLY  
    AS   
    SET NOCOUNT ON  
    Select ... Where Card in (Select card From @Cards)..

3.在你的.net代码中：

3. In your .net code:

param = new SqlParameter("@Cards", dataTableCardType);
                param.TypeName = "CardTableType";
                param.SqlDbType = SqlDbType.Structured;
                cmd.Parameters.Add(param);

更多信息：使用表值参数（数据库引擎） [ ^ ]

这篇关于通过表单将多个值传递给关键字中的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！