C＃中数据库的多个搜索条件 [英] Multiple search criteria for a database within C#
问题描述
我正在为数据库的前端创建一个应用程序，允许用户登录并购买特殊事件的票证/凭证。我已经完成了所有工作，但我在搜索页面遇到了麻烦。
基本上我有一些组合框，其中有选项允许用户指定他们的搜索。组合框被命名为，类别，流派，位置，价格范围等...
所有这些都有不同选项的下拉菜单。
我希望用户选择这些选项，然后点击搜索按钮缩小他们的搜索范围。
我遇到了麻烦。如果用户为一个组合框选择一个选项，我可以搜索缩小范围，但当他们在另一个组合框中选择另一个选项时，它会重置搜索并初步搜索组合框。
我是否继续为它们创建Select语句，或者我只是将搜索条件放在数据集上。？
这是代码搜索按钮，我相信大多数人都会体验程序员能够理解它，因为我是一种非常简单的工作和命名方式。
I'm creating an application for a front end side of a database that allows users to log in and buy tickets/vouchers for special events. I've got everything done, but im having trouble on the search page.
Basically I have a few combo boxes that have options within them that allow the user to specify their search. the combo boxes are named like , category, genre, location, price range etc...
all of which have drop down menus for different options.
I want the user to chose these options then click the search button to narrow their search down.
I'm having trouble with this. I can get the search to narrow it down if the user picks an option for one combobox, but when they chose another option in another combobox it resets the search and primiarly searching for the combo box.
Do i keep creating Select statements for them, or do i just put the search criteria on the dataset.?
Here is the code for the search button, im sure most of you experience programmers will be able to understand it, as im a very simple way of working and naming.
public void searchButton_Click(object sender, RoutedEventArgs e)
{
// SqlConnection _connection = SMUMomentsDB.GetConnection();
using (SqlConnection _connection = SMUMomentsDB.GetConnection())
{
_connection.Open();
using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment", _connection))
{
DataSet _ds = new DataSet();
_momentsTableAdapter.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
if (categoryComboBox.SelectedIndex == 0) // Entertainment Option
{
SqlDataAdapter _entertainmentCategory = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID in ('2', '3', '11', '14')", _connection);
// DataRow[] result = _ds.Tables[0].Select("WHERE GenreID in ('2', '3', '11', '14')");
// DataSet _ds = new DataSet();
_entertainmentCategory.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
}
if (locationComboBox.SelectedIndex == 0)
{
SqlDataAdapter locationAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE VenueLocation = 'Wales'", _connection);
{
// DataSet _ds = new DataSet();
_momentsTableAdapter.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
}
}
}
}
}
}
if (categoryComboBox.SelectedIndex == 0) // Entertainment Option
{
using (SqlConnection _connection = SMUMomentsDB.GetConnection())
{
_connection.Open();
using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID in ( '2', '3', '11', '14')", _connection))
{
DataSet _ds = new DataSet();
_momentsTableAdapter.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
}
}
}
if (genreComboBox.SelectedIndex == 0)
{
using (SqlConnection _connection = SMUMomentsDB.GetConnection())
{
_connection.Open();
using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE GenreID = '3'", _connection))
{
DataSet _ds = new DataSet();
_momentsTableAdapter.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
}
}
}
if (locationComboBox.SelectedIndex == 0)
{
using (SqlConnection _connection = SMUMomentsDB.GetConnection())
{
_connection.Open();
using (SqlDataAdapter _momentsTableAdapter = new SqlDataAdapter("Select * from SpecialMoment WHERE VenueLocation = 'Wales'", _connection))
{
DataSet _ds = new DataSet();
_momentsTableAdapter.Fill(_ds);
momentsDataGrid.ItemsSource = _ds.Tables[0].DefaultView;
momentsDataGrid.IsReadOnly = true;
}
}
}
if (categoryComboBox.SelectedIndex == 1)
{
MessageBox.Show("Food and Drink"); // Food and Drink Option
}
推荐答案
您可以在WHERE AND对多个项目进行搜索c>条款。
例如从specialmomnet中选择*其中genreid in（...）AND secondcolumn in（..）
你应该遍历集合控件（搜索条件）并将其存储到逗号分隔的字符串变量中。之后，您可以将其传递给存储过程 [ ^ ]（SP）。在SP内部，您可以将其拆分并用于搜索数据库。查看OriginalGriff的提示，了解如何执行此操作：使用逗号分隔SQL IN子句中的值参数字符串 [ ^ ]和将列中逗号分隔的数据转换为行以供选择 [ ^ ]。如果您熟悉 CTE [ ^ ]，您也可以使用它。
如需了解更多信息，请参阅：
如何：使用控件集合访问控件 [ ^ ]
如何：创建并执行不返回值的SQL语句 [ ^ ]
如何：创建并执行返回单个值的SQL语句 [ ^ ]
如何：创建并执行返回行的SQL语句 [ ^ ]
顺便说一句：我会强烈建议阅读有关 SQL注入的信息[ ^ ]。
如何：保护ASP.NET中的SQL注入 [ ^ ]
在停止之前停止SQL注入攻击 [ ^ ]
You should loop through the collection of controls (search criteria) and store it into comma delimited string variable. After that you can pass it into stored procedure[^] (SP). Inside SP, you can split it and use to search database. Check OriginalGriff's tips to find out how to do that: Using comma separated value parameter strings in SQL IN clauses[^] and Converting comma separated data in a column to rows for selection[^]. If you're familiar with CTE[^], you can use it too.
For further information, please see:
How to: Access Controls by using the Controls Collection[^]
How to: Create and Execute an SQL Statement that Returns No Value[^]
How to: Create and Execute an SQL Statement that Returns a Single Value[^]
How to: Create and Execute an SQL Statement that Returns Rows[^]
By The Way: i would strongly recommend to read about SQL Injection[^].
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
这篇关于C＃中数据库的多个搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
