现在我使用此代码在datagridview中创建过滤器
private void Button1_Click(object sender, EventArgs e)
{
SqlCommand selectCommand = new SqlCommand();
var filterConditions = new[] {
CreateSqlFilter("Name_Arabic", txtName_Arabic, selectCommand, false),
CreateSqlFilter("gender", CBgender, selectCommand, false),
CreateSqlFilter("CIVILIDD", txtCIVILIDD, selectCommand, true),
CreateSqlFilter("status", comboBox1, selectCommand, false),
CreateSqlFilter("username", txtusername, selectCommand, false),
CreateSqlFilter("City", comboBoxCity, selectCommand, false),
CreateSqlFilter("Governorate", comboBoxGovernorate, selectCommand, false),
CreateSqlFilter("confirmation", comboBox2, selectCommand, false),
CreateSqlFilter("NATIONALITY", CBNATIONALITY, selectCommand, false)
// etc.
};
string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["my"].ConnectionString))
{
selectCommand.Connection = connection;
selectCommand.CommandText = filterCondition == null ? "SELECT * FROM tabl2" : "SELECT * FROM tabl2 WHERE " + filterCondition;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
DataTable dataSource = new DataTable();
adapter.Fill(dataSource);
dataGridView1.DataSource = dataSource;
}
}
private string CreateSqlFilter(string fieldName, Control userInputControl, SqlCommand command, bool exactMatch)
{
string searchValue = null;
if (userInputControl is TextBox) searchValue = ((TextBox)userInputControl).Text;
if (userInputControl is ComboBox) searchValue = ((ComboBox)userInputControl).Text;
if (String.IsNullOrWhiteSpace(searchValue)) return null;
if (exactMatch)
{
command.Parameters.Add(new SqlParameter("@" + fieldName, searchValue));
return fieldName + " = @" + fieldName;
}
else
{
command.Parameters.Add(new SqlParameter("@" + fieldName, "%" + searchValue + "%"));
return fieldName + " LIKE @" + fieldName;
}
}
我想向其添加两个文本框以在同一列(年龄)中进行过滤,我想要的是在两个年龄之间进行过滤,例如20至30岁之间,如何将其添加到我的代码中
解决方法:
我认为很明显,您最终将需要以下SQL条件之一:
age BETWEEN @ageFrom AND @ageTo
// OR
age >= @ageFrom AND age <= @ageTo
但我不建议您扩展CreateSqlFilter方法,因为使所有内容通用不是一个好习惯.您可以简单地创建另一个方法来构建您的过滤条件.
另外,我建议您从查询生成器中删除Control userInputControl参数,而改为接受字符串值.这将使您的方法独立于WinForms命名空间.
更新
CreateRangeSqlFilter("age", tbAgeFrom.Text, tbAgeTo.Text, selectCommand),
private string CreateRangeSqlFilter(string fieldName, string from, string to, SqlCommand command)
{
command.Parameters.Add(new SqlParameter("@" + fieldName + "From", from));
command.Parameters.Add(new SqlParameter("@" + fieldName + "To", to));
return $"{fieldName} BETWEEN @{fieldName}From AND @{fieldName}To";
}
更新2
private string CreateSqlFilter(string fieldName, Control userInputControl, SqlCommand command, bool exactMatch,
bool isRange = false, Control userInputControl2 = null)
{
if (isRange)
{
string searchValue1 = null;
if (userInputControl is TextBox) searchValue1 = ((TextBox)userInputControl).Text;
if (userInputControl is ComboBox) searchValue1 = ((ComboBox)userInputControl).Text;
string searchValue2 = null;
if (userInputControl2 is TextBox) searchValue2 = ((TextBox)userInputControl2).Text;
if (userInputControl2 is ComboBox) searchValue2 = ((ComboBox)userInputControl2).Text;
if (String.IsNullOrWhiteSpace(searchValue1) && String.IsNullOrWhiteSpace(searchValue2)) return null;
if (!String.IsNullOrWhiteSpace(searchValue1) && !String.IsNullOrWhiteSpace(searchValue2))
{
command.Parameters.Add(new SqlParameter("@" + fieldName + "From", searchValue1));
command.Parameters.Add(new SqlParameter("@" + fieldName + "To", searchValue2));
return $"{fieldName} BETWEEN @{fieldName}From AND @{fieldName}To";
}
if (!String.IsNullOrWhiteSpace(searchValue1))
{
command.Parameters.Add(new SqlParameter("@" + fieldName + "From", searchValue1));
return $"{fieldName} >= @{fieldName}From";
}
command.Parameters.Add(new SqlParameter("@" + fieldName + "To", searchValue2));
return $"{fieldName} <= @{fieldName}To";
}
string searchValue = null;
if (userInputControl is TextBox) searchValue = ((TextBox)userInputControl).Text;
if (userInputControl is ComboBox) searchValue = ((ComboBox)userInputControl).Text;
if (String.IsNullOrWhiteSpace(searchValue)) return null;
if (exactMatch)
{
command.Parameters.Add(new SqlParameter("@" + fieldName, searchValue));
return fieldName + " = @" + fieldName;
}
else
{
command.Parameters.Add(new SqlParameter("@" + fieldName, "%" + searchValue + "%"));
return fieldName + " LIKE @" + fieldName;
}
}