none
How to filter “table_2” depending on the selected entry in “table_1”? RRS feed

  • Question

  • I'm using:
      - Entity Framework v6.2.0;
      - SQL Server 2012;
      - C#.
     
    I have these tables:
     - tbl_01_Groups;
    - tbl_02_Students;
    - tbl_03_GroupsStud;
    The tbl_01_Groups table is displayed in dataGridView1. One student can be in several groups.
    To solve this problem, I suspect that it is necessary to build the table of relations tbl_03_GroupsStud.
    Question: how to make dataGridView2 display table_2 records that belong to the group selected in dataGridView1?
    Requirements for table_2:
     1.  table_2 should contain the following fields:
    • [tbl_02_Students].[NameStud];
    • [tbl_02_Students].[Property_1];
    • [tbl_01_Groups].[nameGroup];
    • ... other columns as needed
    2. if changes are made in the table_2 record, then these changes are displayed in the original source;
    3. if an entry is added to the table_2, then this entry is displayed in the source;
    Table_2 is planned to be used in another query.
     
    How to make the table_2 meet the above requirements?
    Something like I managed to do with the help of queries in MSAccess:
    Request req_GroupsStud_Stud:
    1. SELECT tbl_03_GroupsStud.*, tbl_02_Students.NameStud  
    2. FROM tbl_03_GroupsStud  
    3. INNER JOIN tbl_02_Students  
    4. ON tbl_03_GroupsStud.id_stud = tbl_02_Students.id_stud;
    Request req_GroupsStud_CurGroup
    1. SELECT req_GroupsStud_Stud.*, req_GroupsStud_Stud.id_group  
    2. FROM req_GroupsStud_Stud  
    3. WHERE (((req_GroupsStud_Stud.id_group)=[Forms]![frm_00_00_MainForm]![id_group_Frm]));  
    From the form, using the expression [Forms]![Frm_00_00_MainForm]![Id_group_Frm], the parameter [id_group_Frm] is passed to the request.
    I don’t understand how to do the same thing using the entity-framework and MS SQL Serever.
     
     
     
    My code
    1. ContextDB cntxtDB;  
    2.   
    3. public Frm1UC()  
    4. {  
    5.     InitializeComponent();  
    6.   
    7.     cntxtDB = new ContextDB();  
    8. }  
    9.   
    10. private void Frm1UC_Load(object sender, EventArgs e)  
    11. {  
    12.     Fill_dataGridView1();  
    13. }  
    14.   
    15. public void Fill_dataGridView1()  
    16. {  
    17.     try  
    18.     {  
    19.         cntxtDB.tbl_01_Groups.Load();  
    20.         bs_Grid1.DataSource = cntxtDB.tbl_01_Groups.Local.ToBindingList();  
    21.         dataGridView1.DataSource = bs_Grid1;  
    22.     }  
    23.     catch (Exception ex)  
    24.     {  
    25.         string s = ex.Message;  
    26.         string t = ex.StackTrace;  
    27.         // throw;  
    28.         MessageBox.Show(s);  
    29.     }  
    30. }

    Saturday, February 23, 2019 7:07 AM

All replies

  • Hi prog1003,

    According to your description, you want to filter the table_2 records via click the row in table_1. it seems that we could filter the records via the following event.

    private void bs_Grid1_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
    {
        // For any other operation except, StateChanged, do nothing
        if (e.StateChanged != DataGridViewElementStates.Selected) return;
    
        // filter the records here.
    }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 27, 2019 7:45 AM
    Moderator
  • Hi prog1003,

    According to your description, you want to filter the table_2 records via click the row in table_1. it seems that we could filter the records via the following event.

    private void bs_Grid1_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
    {
        // For any other operation except, StateChanged, do nothing
        if (e.StateChanged != DataGridViewElementStates.Selected) return;
    
        // filter the records here.
    }
    My main difficulty is related to filtering.

    How to make the filtering run optimally?
    1. On the server "SQL Server"?
    How to fulfill the "Requirements for table_2" (see question)?

    OR

    2. Or fulfill the "Requirements for table_2" (see question) in the application through the "Entity Framework" and the application logic?
    Friday, March 1, 2019 7:27 AM
  • Hi,

    The dataGridView2 may contain multiple data sources. You can bind the data source to the specified column by the following code.

        dataGridView1.AutoGenerateColumns = false;
        this.dataGridView2.DataSource = dataTable;
        this.dataGridView2.Columns["Column"].DataPropertyName = dataTable.Columns["ColumnName"].ToString();

    As to "changes are displayed in the original source", do you mean updating the database dynamically? If so, you can refer to this document:
    DbDataAdapter.Update Method.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 1, 2019 9:46 AM
    Moderator
  • Hi,

    The dataGridView2 may contain multiple data sources. You can bind the data source to the specified column by the following code.

        dataGridView1.AutoGenerateColumns = false;
        this.dataGridView2.DataSource = dataTable;
        this.dataGridView2.Columns["Column"].DataPropertyName = dataTable.Columns["ColumnName"].ToString();

    As to "changes are displayed in the original source", do you mean updating the database dynamically? If so, you can refer to this document:
    DbDataAdapter.Update Method.

    As to "changes are displayed in the original source", do you mean updating the database dynamically? 

    I do not quite understand your question.
    I use the `Entity Framework v6.2.0;`.
    If I understand the articles and documentation correctly, the Entity Framework is the most effective and up-to-date database tool.

    If I understand your sentence correctly, then you suggest using the following code (see below).
    But this code also does not save the changes in the request `req_GroupsStud_CurGroup`.
    Questions
    1.  Do I understand your question correctly?
    2. Maybe I need to perform the query `req_GroupsStud_CurGroup` in the application using `Link`?
    3. Or do I need to make changes through the logic of the code, directly making changes to the tables of which the query consists ("tbl_03_GroupsStud" and "tbl_02_Students")?

    public DataTable CreateCmds()
            {
                table_2 = new DataTable();
                try
                {
                    string connectionString = @"Data Source=.\SQLEXPRESS1;Initial Catalog=Prb;Integrated Security=True";              
    
                    string queryString = "SELECT * FROM req_GroupsStud_CurGroup";
    
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        adapter = new SqlDataAdapter();
                        adapter.SelectCommand = new SqlCommand(queryString, connection);
                        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    
                        connection.Open();
    
                        adapter.Fill(table_2);
                    }
    
                }
                catch (Exception ex)
                {
    
                    string s = ex.Message;
                    string t = ex.StackTrace;
                    // throw;
                }
    
                return table_2;
            }
    
            public void Save()
            {
                string connectionString = @"Data Source=.\SQLEXPRESS1;Initial Catalog=Prb;Integrated Security=True";
    
                string queryString = "SELECT * FROM req_GroupsStud_CurGroup";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(queryString, connection);
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    
                    connection.Open();
                                    
                    adapter.Update(table_2);
                }
            }

    Monday, March 4, 2019 7:23 AM
  • Hi prog1003,

    First, sorry for my mistake that provided the wrong document.

    If you want to update the database by updating the data in the DataGridView directly. You can refer to the following code:

        SqlDataAdapter adapter = null;
    
        DataSet dSet = null;
    
        private static string strConn = "connection string";
    
        private void Form3_Load(object sender, EventArgs e)
        {
            adapter = new SqlDataAdapter("select * from UserInfo", strConn);
            dSet = new DataSet();
            adapter.Fill(dSet);
    
            DGVMain.DataSource = dSet.Tables[0];
        }
    
        private void button1_Click(object sender, EventArgs e)
        {
            SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
    
            //Update
            try
            {
                adapter.Update(dSet);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 6, 2019 9:48 AM
    Moderator