通过SQL查询过滤DataGridView表 [英] Filter DataGridView Table by SQL Query

问题描述

我正在制作一个程序，用户可以在其中向数据网格视图添加成员，编辑该网格中的成员并删除成员.我能够轻松地添加成员，但是当我想过滤DataGridView结果(默认为名为"MembersTable"的表中的所有数据)时，问题就出现了，以便用户无需搜索即可编辑特定数据整个表格的结果.

I am making a program in which a user can add a member to a data grid view, edit the members in that grid and delete members. I'm able to add members easily enough, but my problem arrives when I want to filter the DataGridView results (which default to all the data in a table named 'MembersTable') so that the user can edit specific data without having to search through the entire table results.

以下是名为"MemberClass"的类中的方法

The following is the method in a class called 'MemberClass'

public static List<MemberClass> SearchMember(string MemberIdentifier)
{
    using (SqlConnection connection = new SqlConnection(_connectionstring))
    {
        List<MemberClass> MemberList = new List<MemberClass>();

        //connection open
        connection.Open();

        string sqlQuery = string.Format("SELECT * FROM MembersTable WHERE MemberID = '{0}'", MemberIdentifier);

        SqlCommand searchForMember = new SqlCommand(sqlQuery, connection);

        SqlDataReader sqlDataReader = searchForMember.ExecuteReader();

        while (sqlDataReader.Read())
            {
                MemberClass newMemberClass = new MemberClass((int)sqlDataReader["MemberID"], (string)sqlDataReader["FirstName"], (string)sqlDataReader["LastName"], (string)sqlDataReader["MemberAddress"], (string)sqlDataReader["TypeOfMembershipID"], (string)sqlDataReader["PhoneNumber"], (DateTime)sqlDataReader["MembershipStart"], (DateTime)sqlDataReader["MembershipEnd"], (string)sqlDataReader["Notice"], (string)sqlDataReader["TypeOfPaymentID"]);
                MemberList.Add(newMemberClass);
            }
    connection.Close();
    //connection close

    return MemberList;
}

在主屏幕"表单中，我使用按钮后面的以下代码根据用户输入的数据(在本例中为MemberID)搜索结果.

In my Main Screen form I use the following code behind a button to search through the results based upon data (in this case the MemberID) entered by the user.

List <MemberClass> MemberList = MemberClass.SearchMember(textBox1.Text);

我的问题是，如何过滤DataGridView框的结果以仅显示通过上述代码和用户输入的MemberID过滤的结果?

My question is, how do I filter the results of the DataGridView box to only display results filtered by the above code and the MemberID entered by the user?

抱歉，我对DataGridView相对陌生.

Apologies for my ignorance, I'm relatively new to DataGridView.

推荐答案

当我需要过滤datagridview时，我会在columnheader下创建一个textBox.然后，我捕获了适当的textBox.TextChanged事件，并使用第二个查询重新加载了datagridview，这是带有WHERE子句的默认查询.

When i need to filter my datagridview, i create a textBox under the columnheader. Then, i catch the appropriate textBox.TextChanged event and i reload my datagridview with a second query, which is the default query with a WHERE clause.

这是我的VB.NET代码，但它可能有助于您理解.

This is my VB.NET code but it might help you to understand.

我使用 dataBinding :

Private WithEvents datasBindingSource As New BindingSource()

txtChanged事件:

Private Sub txtfilterMemberID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtfilterMemberID.TextChanged
    filterDatas()
End Sub

筛选子:

Private Sub filterDatas()
    ListObjets = _dao.YourQuery
    datasBindingSource.DataSource = ListObjets
    dtg.DataSource = datasBindingSource

    Dim query =
    From row In ListObjets
    Where row.MemberID.ToString.Contains(txtfilterMemberID.Text) 
    Select row

    If query.Count > 0 Then
        If ListObjets.Count = query.Count Then
            datasBindingSource.DataSource = ListObjets
        Else
            datasBindingSource.DataSource = query
        End If
    Else
        MessageBox.Show("No data found !")
        If txtfilterMemberID.Focused = True Then
            txtfilterMemberID.ResetText()       
    End If
End Sub

Row.MemberID是您需要过滤的列.filterDatas方法将遍历您的所有数据，并仅在列中显示包含txtBox.Text值的数据.

Row.MemberID is the column that you need to filter. The filterDatas method will loop through all your Datas and only display those containing the txtBox.Text value in the column.

我希望它能帮上忙.

这篇关于通过SQL查询过滤DataGridView表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！