How to search from database with multiple tables into multiple datagridviews-CodePudding

I'm trying to implement a search function which returns multiple tables from a database into multiple datagridviews.

This is my form:

I'm using each stored procedure to access each table Each database table will correspond to one datagridview

Stored procedure to get the Style table

CREATE PROCEDURE [dbo].[FetchCS_V2] @Keyword nvarchar(30)
AS
 SELECT [RID]
      ,[CustomerBrand]
      ,[CustomerStyle]
      ,[ProductName]
      ,[ProductType]
      ,[ValidityDateFrom]
      ,[ValidityDateTo]
      ,[Colorway]
      ,[Season]
      ,[Factory]
  FROM [dbo].[CostSheet_Mst]
  WHERE RID Like '%'   @Keyword   '%'
  OR CustomerStyle Like   '%'  @Keyword   '%'
  OR ProductName Like   '%'  @Keyword   '%'
  OR ProductType Like   '%'  @Keyword   '%'
  OR ValidityDateFrom Like   '%'  @Keyword   '%'
  OR ValidityDateTo Like   '%'  @Keyword   '%'
  OR Colorway Like   '%'  @Keyword   '%'
  OR Season Like   '%'  @Keyword   '%'
  OR Factory Like   '%'  @Keyword   '%'

GO

Stored procedure to get the FOB table

CREATE PROCEDURE [dbo].[FetchCS_FOB_V2] @Keyword nvarchar(30)
AS
 SELECT [FobRID]
      ,[RID]
      ,[FOBType]
      ,[Amount]
      ,[Currency]
  FROM [dbo].[CostSheet_FOB]
  WHERE RID IN
  (SELECT  
       [RID]
      FROM CostSheet_Mst
      where [RID] LIKE   '%'   @Keyword   '%')
    OR [FOBType] LIKE   '%'   @Keyword   '%'
    OR [Amount] LIKE   '%'   @Keyword   '%'
    OR [Currency] LIKE   '%'   @Keyword   '%'


GO

This is the function that searches from the database

        private void searchFromDB() 
        {

            try
            {
                string mainconn1 = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
                SqlConnection sqlconn = new SqlConnection(mainconn1);
                SqlCommand sqlcomm1 = new SqlCommand("exec [dbo].[FetchCS_V2] '" searchTextBox.Text "'", sqlconn); //stored procedure for master database table
                SqlCommand sqlcomm2 = new SqlCommand("exec [dbo].[FetchCS_FOB_V2] '"   searchTextBox.Text   "'", sqlconn); //stored procedure for FOB tablew
                //sqlcomm1.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da1 = new SqlDataAdapter();
                SqlDataAdapter da2 = new SqlDataAdapter();
                da1.SelectCommand = sqlcomm1;
                da2.SelectCommand = sqlcomm2;

                DataTable dt1 = new DataTable();
                DataTable dt2 = new DataTable();
                da1.Fill(dt1);
                da2.Fill(dt2);
                dataGridViewStyleSearch.DataSource = dt1;
                dataGridViewFOBSearch.DataSource = dt2;
                sqlconn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("There's an error: {0}", ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }<br>

Currently, the search function can return rows in multiple datagridviews if the search keyword is the RID enter image description here ]

However, if I search a field only present in the FOB table, then it'll only return the rows in that datagridview

Also, I wonder if using one stored procedure to select fields from all tables would be better?

Then I can put that into one datatable and split it (not sure how to implement that tho)

CodePudding user response：

If you are going to search more than one table, it makes more sense to do all of them in different stored procedures.

CodePudding user response：

There are some articles talking about master-detail datagrid:

http://www.codeproject.com/Articles/4094/A-Master-Detail-DataGrid

http://www.codeproject.com/Articles/7941/How-to-create-a-simple-Master-Slave-DataGrid

Also, check this out.

https://www.codeproject.com/Questions/5309418/Filter-search-on-datagridview-in-VB-NET