ASP、C# 和SQL复杂参数/查询示例请
我正在构建一个网页，上面有一些控件，例如 CheckBoxList 和 Listbox（启用了多项选择）。这些控件将链接到一个 SQL 数据库表，一个用于颜色，一个用于尺寸。设计查询的最佳方法是什么，主要是“where”语句来过滤网格视图中列出的与用户选择的尺寸和颜色相匹配的衬衫。例如，如果用户从colorsCheckBoxList中检查红色和蓝色，从sizeListbox中检查中号和大号，则gridview将仅显示中号和大号的红色衬衫以及中号和大号的蓝色衬衫。我已经完成了大部分工作，但我只是想不出设计“where”子句的最佳方法。如果我只允许每个控件进行一项选择，我可以轻松创建它，但我更愿意允许多项选择。
这是一个 .aspx 页面，页面“代码隐藏”(C#)。我读过使用参数是干净代码和安全性的最佳方法，但我想听听其他人的想法。
请让我知道您可能需要的任何进一步的详细信息，我非常感谢您在这个问题上花费的时间。
I am building a webpage that has a few controls on it such as CheckBoxList and Listbox (with multi selection enabled). These controls will be linked to a sql database table, one for colors and one for sizes. What is the best way to design the query, primarily the 'where' statement to filter the shirts listed in a gridview that match the selected sizes and colors made by the user. For example, if the user checks red and blue from the colorsCheckBoxList and medium and large from the sizesListbox, then the gridview will display only shirts that are red and are in medium and large as well as shirts in blue that are in medium and large. I've done most of the work, I just can't figure the best way to design the 'where' clause. I can create it easily if I allow only one selection per control but I'd prefer to allow multi-selection.
This is an .aspx page with 'code behind' the page (C#). I've read using parameters is the best method for clean code and security but I'd like to hear what others think.
Please let me know any further details you may need and I greatly appreciate any time spent regarding this question.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，始终使用 SQL 参数来消除 SQL 注入的风险。如果不查看数据库模式，很难判断您需要什么。这就是我在你的情况下会做的事情。
您可以将选择框中的 ID 设置为 REDSHIRTID 和 BLUESHIRTID。
Yes always use SQL Parameters to remove the risk of SQL Injection. It's hard to tell what you need with out seeing a database schema. Here is what I would do in your case.
You would put your IDs from your selectbox as REDSHIRTID and BLUESHIRTID.
您可以使用表值或 XML 参数传入尺寸和颜色的多个 ID。我个人会选择表值参数。
You can use Table-valued or XML parameters to pass in mutliple IDs for the size and color. I would opt in favor of Table-valued parameters, personally.
选择将根据大小和颜色的不同而有所不同，对两者进行多个或单个选择。您可以使用存储过程进行锻炼。（而且它会更快）将参数传递给 sp 时，您可以将两者的选择作为带有字符分隔的字符串传递逗号 e,g 表示尺寸“红色、蓝色、绿色”（如果选择这三个）。颜色相同。
在 sp 中，您可以首先通过拆分 ',' 来分隔参数，然后查询为
//create sp name
//passparameter color and size
//begin sp
//write for splitting paramter code might in for statements then
// end sp
The selection will vary depending on the size and color multiple or single selection for both.You can workout with stored procedure.(Also it will be faster) While passing the parameter to the sp you can pass the selections for both as string with character seperation of comma e,g for size 'red,blue,green' if selected this three.Same for color.
In sp you can first seperate the parameter by splitting ',', and querying as
//create sp name
//pass parameter color and size
//begin sp
//write for splitting paramter code maybe in for statement then
// end sp