Filter Dynamically created datatables

[Imran Khan Pathan]

Hi friends I have little problem here. I am creating data tables dynamically. I want to filter it using sql query string. Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates. Now I want to filter all tables and want to get match records with the ServiceMethod. Suppose,four records in First table,three records in other three tables, and only two records(Service method) are same in all tables. I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist. Can any guide me how to filter more than one tables using sql query if data tables are created dynamically? Thanks in advance.

please don't forget to vote on the post that helped you.

[N a v a n e e t h]

Imran Khan Pathan wrote:

Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?

If you have multiple select statements in the query and you filled the results to a DataSet, obviously you will have multiple data tables.

Imran Khan Pathan wrote:

Now I want to filter all tables and want to get match records with the ServiceMethod.

You can use Select() method in the DataTable instance to select rows matching to the specified criteria.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia How to use google | Ask smart questions

[Imran Khan Pathan]

N a v a n e e t h wrote:

You can use Select() method in the DataTable instance to select rows matching to the specified criteria.

How can I use select method for more than one tables? We can use select method to filter just one table.If I have two tables and If I want to get relational data from these two tables,select method can not be useful.Am I right? Thanks

please don't forget to vote on the post that helped you.

[N a v a n e e t h]

You are right. Select() can't be used across multiple tables. If all these tables belongs to one DataSet, I think you can use DataRelation to link it. I have not done that before, just a wild guess. :)

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia How to use google | Ask smart questions

[Imran Khan Pathan]

Currently I am doing this job this way. I am passing Dataset to DistributeServices Method Is there any better way?

private int MergeCount = 0;
private void DistributeServices(DataSet dsServiceRates)
{
DataTable dtFinal = new DataTable();
int CountTables = dsServiceRates.Tables.Count;
if (CountTables > 1)
{
for (int i = 0; i < CountTables; i++)
{
if (MergeCount == 0)
{
dtFinal = MergeService(dsServiceRates.Tables[i], dsServiceRates.Tables[i + 1]);
i++;
}
else
{
dtFinal = MergeService(dsServiceRates.Tables[i], dtFinal);
}
}
if (dtFinal.Rows.Count != 0)
{
//Bind DropDownList with dtFinal Table
}
else
{
//No Bind Method
}

    }
    else if (CountTables != 0)
    {
        //Bind DrowDownList here with Tables 0
    }
    else
    {
       //No Bind Method
    }
}

private DataTable MergeService(DataTable dTable1, DataTable dTable2)
{
    DataTable dtNew = new DataTable();
    dtNew.Columns.Add("Method",System.Type.GetType("System.String"));
    dtNew.Columns.Add("Rate", System.Type.GetType("System.String"));
    
    foreach (DataRow dRow1 in dTable1.Rows)
    {
        foreach (DataRow dRow2 in dTable2.Rows)
        {
            if (dRow1\["Method"\].ToString() == dRow2\["Method"\].ToString())
            {
                MergeCount = 1;
                AddRows(dtNew, dRow1\["Method"\].ToString(), decimal.Parse(dRow1\["Rate"\].ToString()) + decimal.Parse(dRow2\["Rate"\].ToString()));
                 // Add Rows Method used to add Rows
                
            }
        }
    }
    return dtNew;
 }

please don't forget to vote on the post that helped you.