动态Linq到Sql与ComboBox和Column.Contains

I have a text box, combo box, button and DataGridView on a form that is used to search and return customer information from a MSSQL view (vCustomer). It works great, but I know my code can be more efficient. The four items in the combobox represent columns to search.

我在表单上有一个文本框,组合框,按钮和DataGridView,用于从MSSQL视图(vCustomer)搜索和返回客户信息。它工作得很好,但我知道我的代码可以更高效。组合框中的四个项目代表要搜索的列。

Is there a simple way of converting the following to dynamic LINQ to SQL? I am new to C#. I checked out some other posts, but I cannot seem to get it working.

有没有一种简单的方法将以下转换为动态LINQ to SQL?我是C#的新手。我检查了一些其他帖子,但我似乎无法让它工作。

public partial class MainForm : Form
{
    public MainForm()
    {
        InitializeComponent();
    }

    private void MainForm_Load(object sender, EventArgs e)
    {
        // columns to filter for
        string[] list = new string[4];
        list[0] = "Name";
        list[1] = "CustomerAccountNo";
        list[2] = "Telephone";
        list[3] = "Postal";

        // bind to combobox
        cboColumn.DataSource = list;
        cboColumn.SelectedIndex = 0;
    }

    private void btnSearch_Click(object sender, EventArgs e)
    {

        try
        {
            Cursor.Current = Cursors.WaitCursor; 
            CustomerSearchDataContext db = new CustomerSearchDataContext();
            IEnumerable<vCustomer> customerQuery = null;
            switch (cboColumn.SelectedIndex)
            {
                case 0:
                    customerQuery = from c in db.vCustomers
                                    where c.Name.Contains(txtSearch.Text)
                                    orderby c.CustomerAccountNo descending
                                    select c;
                    break;
                case 1:
                    customerQuery = from c in db.vCustomers
                                    where c.Name.Contains(txtSearch.Text)
                                    orderby c.CustomerAccountNo descending
                                    select c;
                    break;
                case 2:
                    customerQuery = from c in db.vCustomers
                                    where c.Telephone.Contains(txtSearch.Text)
                                    orderby c.CustomerAccountNo descending
                                    select c;
                    break;
                case 3:
                    customerQuery = from c in db.vCustomers
                                    where c.Postal.Contains(txtSearch.Text)
                                    orderby c.CustomerAccountNo descending
                                    select c;
                    break;
            }
            customerBindingSource.DataSource = customerQuery;
            dataGridView1.DataSource = customerBindingSource;
            dataGridView1.Columns["CustomerId"].Visible = false;
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            MessageBox.Show("An Error Occured - " + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            Cursor.Current = Cursors.Default; 
        }
    }
}

2 个解决方案

#1

Use [System.Linq.Dynamic][1].

Get the condition from a method and use it in a single query.

从方法中获取条件并在单个查询中使用它。

    switch (choice)
    {
        case case1:
            condition = string.Format("{0}.Contains({1})", "Column", "Value"
            break;

#2

Hey Rony. I tried your suggestion and re factored my code (see below). However, I receive an error: No property or field 'smith' exists in type 'vCustomer' . By the way, the MessageBox.Show(condition); line returns Name.Contains(smith) which looks correct.

嘿罗尼。我尝试了你的建议并重新考虑了我的代码(见下文)。但是,我收到一个错误:'vCustomer'类型中没有属性或字段'smith'。顺便说一句,MessageBox.Show(条件); line返回Name.Contains(smith)看起来正确。

What am I doing wrong? Sorry for being a noob and thanks for your help.

我究竟做错了什么?对不起是一个菜鸟,谢谢你的帮助。

Figured it out... needed to wrap search string with double quotes! Code has been edited.

想出来......需要用双引号包装搜索字符串!代码已被编辑。

public partial class MainForm : Form
{
    public MainForm()
    {
        InitializeComponent();
    }
    private void MainForm_Load(object sender, EventArgs e)
    {
        // data column to filter against
        string[] list = new string[4];
        list[0] = "Name";
        list[1] = "CustomerAccountNo";
        list[2] = "Telephone";
        list[3] = "Postal";
        cboColumn.DataSource = list;
        cboColumn.SelectedIndex = 0;

        // left, right or middle search
        string[] list2 = new string[3];
        list2[0] = "Contains";
        list2[1] = "StartsWith";
        list2[2] = "EndsWith";
        cboFilterAtt.DataSource = list2;
        cboFilterAtt.SelectedIndex = 0;
    }

    private void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            Cursor.Current = Cursors.WaitCursor; 
            CustomerSearchDataContext db = new CustomerSearchDataContext();
            //string condition = string.Format("{0}.{1}({2})", cboColumn.SelectedValue, cboFilterAtt.SelectedValue, txtSearch.Text);
            string condition = string.Format("{0}.{1}({2})", cboColumn.SelectedValue, cboFilterAtt.SelectedValue, "\"" + txtSearch.Text + "\"");
            MessageBox.Show(condition);
            var customerQuery = db.vCustomers.Where(condition).OrderBy("CustomerAccountNo");
            customerBindingSource.DataSource = customerQuery;
            dataGridView1.DataSource = customerBindingSource;
            dataGridView1.Columns["CustomerId"].Visible = false;
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            MessageBox.Show("An Error Occured - " + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            Cursor.Current = Cursors.Default; 
        }
    }
}