c#-如何在一列中的两个文本框之间创建SqlSqlFilter

发布时间：2020-12-15 07:50:42 所属栏目：百科来源：网络整理

导读：现在我使用此代码在datagridview中创建过滤器 private void Button1_Click(object sender,EventArgs e) { SqlCommand selectCommand = new SqlCommand(); var filterConditions = new[] { CreateSqlFilter("Name_Arabic",txtName_Arabic,selectCommand,false

现在我使用此代码在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,CreateSqlFilter("CIVILIDD",txtCIVILIDD,true),CreateSqlFilter("status",comboBox1,CreateSqlFilter("username",txtusername,CreateSqlFilter("City",comboBoxCity,CreateSqlFilter("Governorate",comboBoxGovernorate,CreateSqlFilter("confirmation",comboBox2,CreateSqlFilter("NATIONALITY",CBNATIONALITY,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岁之间,如何将其添加到我的代码中

my form look like this

最佳答案

我认为很明显,您最终将需要以下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,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;
     }
 }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!