none
C# - How to Filter the row within the row in the range of Minimum and Maximum value RRS feed

  • Question

  • Hi,

    I want to filter row within the row in the range of Minimum and Maximum value

    I done some code using LINQ, but i want simplified code either in C# or LINQ

     protected void Button_Click(object sender, EventArgs e)
        {

            
            string string1 = "SUB C=100#SUB D=200";

            string FinalOutput = "GRADE#SUB B";

            DataTable dt = new DataTable();
            dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
    new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)") ,new DataColumn("SUB C"),new DataColumn("FROM"),new DataColumn("TO") });
            dt.Rows.Add(101,"A", "100", "100", "50", "200", "250", "700", 20, "100");
            dt.Rows.Add(101, "B", "100", "150", "70", "250", "200", "100", 20, "100");
            dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", null, 20, "100");
            dt.Rows.Add(101, null, "100", "200", "300", "100", "200", null, 20, "100");



       I will explain step by step.


    1. I have two input strings.
        1-  string1 = "GRADE#SUB B";
        2-  string string2 = "SUB C=100#SUB D=300";

    2. I have the following conditions.
        First i want to get the row which contains in the range between within 100 ((i.e) SUB C (MIN) and SUB C (MAX)) (take string2 and split the string and take first in that. SUB C=100).

       so i may get below two records. (for condition SUB C=100)
      
               dt.Rows.Add(101,"A", "100", "100", "50", "200", "250", "700", 20, "100");
               dt.Rows.Add(101, "B", "100", "150", "70", "250", "200", "100", 20, "100");

    3. After that, with in the above two records, i want to filter one more condition need to check. (i.e : for SUB D=300
        This is also need to check within Min and Max of SUB D=300.

        So finally ia may get only one records.

              dt.Rows.Add(101,"A", "100", "100", "50", "200", "250", "700", 20, "100");

    4. In that, finally i want to get the records for the field of ( GRADE and SUB B).

    So the final output would be

    GRADE=A
    SUB B=100.

    Note : Filter and check row within the row using string2 and display the result in the form of string1.

    pls. let us know if you need any more details.

    The output i want either simple C# or Linq Query..???
          
          
    • Edited by Gani tpt Monday, October 9, 2017 1:12 PM
    Monday, October 9, 2017 1:12 PM

Answers


  • Hi Gani tpt,

    >>if there is no matching column in datatable then it should check remaining parameter and matching column and then finally get the count.

    I think you can try the following way.

    datatable:

       dt.Columns.AddRange(new[] { new DataColumn("Studentno",typeof(int)),new DataColumn("GRADE"),new DataColumn("SUB B",typeof(int)), new DataColumn("SUB C(MIN)",typeof(int)), new DataColumn("SUB C(MAX)",typeof(int)),
            new DataColumn("SUB D(MIN)",typeof(int)) ,new DataColumn("SUB D(MAX)",typeof(int)), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)",typeof(int)), new DataColumn("SUB E(MAX)",typeof(int)) });
                dt.Rows.Add(101, "A", 100, 80, 200, 200, 550, 20, 100, 200, 500);
                dt.Rows.Add(101, "B", 100, 90, 300, 270, 200, 20, 100, null, null);
                dt.Rows.Add(101, "B", 100, 200, 300, 100, 250, 20, 100, 100, 100);
                dt.Rows.Add(101, null, 100, 200, 300, 100, 200, 20, 100, null, null);
    
    
    
                string string2 = "SUB C=100#SUB D=250#SUB E=300";
    
                string[] paras = string2.Split('#');
                List<DataRow> list = new List<DataRow>();
                string sqlcmd = "";
    
                for (int i = 0; i < paras.Length; i++)
                {
                    string[] par = paras[i].Split('=');
                    string mixpar = par[0] + "(MIN)";
                    string maxpar = par[0] + "(MAX)";
                    int mvalue = Convert.ToInt32(par[1]);
                    sqlcmd += " ( [" + mixpar + "] <  " + mvalue + "  and  [" + maxpar + "] >  " + mvalue + " ) ";
                    if ((i+1)!= paras.Length)
                    {
                        sqlcmd += " and ";
                    }
    
    
                }
    
                DataRow[] result = dt.Select(sqlcmd);
                foreach (DataRow row in result)
                {
                    Console.WriteLine("{0}, {1}", row[0], row[1]);
                }



    DataTable.Select Method (String, String)
    https://msdn.microsoft.com/en-us/library/way3dy9w(v=vs.110).aspx


    Note: Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    If you have any question about ADO.NET. Please go to the  ADO.NET Entity Framework and LINQ to Entities forum for getting suitable help.

    Best Regards,

    Yohann Lu


    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.

    • Proposed as answer by Fei HuModerator Wednesday, October 11, 2017 11:46 AM
    • Marked as answer by Gani tpt Thursday, October 12, 2017 6:49 AM
    Wednesday, October 11, 2017 11:42 AM
    Moderator

All replies

  • Hi Gani tpt,

    >>The output i want either simple C# or Linq Query..???

    I have made a sample on my side. You can refer the following code.

       DataTable dt = new DataTable();
            private void FilterDataTableTest_Load(object sender, EventArgs e)
            {
               
                dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
     new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)") ,new DataColumn("SUB C"),new DataColumn("FROM"),new DataColumn("TO") });
                dt.Rows.Add(101, "A", "100", "100", "50", "200", "250", "700", 20, "100");
                dt.Rows.Add(101, "B", "100", "150", "70", "250", "200", "100", 20, "100");
                dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", null, 20, "100");
                dt.Rows.Add(101, null, "100", "200", "300", "100", "200", null, 20, "100");
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
                IEnumerable<DataRow> productsQuery =
        from Studentinfors in dt.AsEnumerable()
        select Studentinfors;
    
                IEnumerable<DataRow> SelectStudentinfors =
                    productsQuery.Where(p => (( p.Field<string>("SUB C(MIN)") == "100") && (p.Field<string>("SUB D(MAX)") == "250")));
    
              
    
                foreach (DataRow product in SelectStudentinfors)
                {
                    //Console.WriteLine(product.Field<string>("GRADE"));
                    MessageBox.Show(product.Field<string>("GRADE")+" : "+ product.Field<string>("SUB B"));
                }
    
            }

    You can refer the following article for getting detailed information.

    Queries in LINQ to DataSet:
    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/queries-in-linq-to-dataset


    If you want to Learn more about ADO.NET and LINQ, you can visit the ADO.NET Entity Framework and LINQ to Entities forum for getting suitable help.

    Best Regards,

    Yohann Lu



    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.


    Tuesday, October 10, 2017 2:42 AM
    Moderator
  • Thanks for your code and help...

    if datatable column is there then the parameter also be there.

    if there is no matching column in datatable then it should check remaining parameter and matching column and then finally get the count.

    So, our case need to form for the dynamic parameter.

    i have given sample datatable for 3 parameters..if it 4 parameter then the column not found, we need to get the result count for remaining parameter.

    Note : Anyhow, column always there based on the parameter...

    see below sample code i have added SUB E(Min) and SUB E(Max).

           string string2 = "SUB C=100#SUB D=250#SUB E=300";

     DataTable dt = new DataTable();
            dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
            new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)"), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)"), new DataColumn("SUB E(MAX)") });
            dt.Rows.Add(101, "A", "100", "80", "200", "200", "550", 20, "100", "200", "500");
            dt.Rows.Add(101, "B", "100", "90", "300", "270", "200", 20, "100", null, null);
            dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", 20, "100", "100", "100");
            dt.Rows.Add(101, null, "100", "200", "300", "100", "200", 20, "100", null, null);
            DataView dataView = dt.DefaultView;

    if SUB G=400, then definitely this column will be there in the datatable.

    Ans : The row filter shoud check one by one parameter and then finally get one single row only always..

    pls. let us know whether you can undarestand or not the scenario...





    • Edited by Gani tpt Wednesday, October 11, 2017 9:34 AM
    Tuesday, October 10, 2017 4:23 AM
  • Any update..this scenario is little bit tricky and finding the exact solution...
    • Edited by Gani tpt Wednesday, October 11, 2017 4:19 AM
    Tuesday, October 10, 2017 9:20 AM
  • updated and corrected datatable...

    DataTable dt = new DataTable();
            dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
            new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)"), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)"), new DataColumn("SUB E(MAX)") });
            dt.Rows.Add(101, "A", "100", "80", "200", "200", "550", 20, "100", "200", "500");
            dt.Rows.Add(101, "B", "100", "90", "300", "270", "200", 20, "100", null, null);
            dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", 20, "100", "100", "100");
            dt.Rows.Add(101, null, "100", "200", "300", "100", "200", 20, "100", null, null);

    Wednesday, October 11, 2017 10:01 AM

  • Hi Gani tpt,

    >>if there is no matching column in datatable then it should check remaining parameter and matching column and then finally get the count.

    I think you can try the following way.

    datatable:

       dt.Columns.AddRange(new[] { new DataColumn("Studentno",typeof(int)),new DataColumn("GRADE"),new DataColumn("SUB B",typeof(int)), new DataColumn("SUB C(MIN)",typeof(int)), new DataColumn("SUB C(MAX)",typeof(int)),
            new DataColumn("SUB D(MIN)",typeof(int)) ,new DataColumn("SUB D(MAX)",typeof(int)), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)",typeof(int)), new DataColumn("SUB E(MAX)",typeof(int)) });
                dt.Rows.Add(101, "A", 100, 80, 200, 200, 550, 20, 100, 200, 500);
                dt.Rows.Add(101, "B", 100, 90, 300, 270, 200, 20, 100, null, null);
                dt.Rows.Add(101, "B", 100, 200, 300, 100, 250, 20, 100, 100, 100);
                dt.Rows.Add(101, null, 100, 200, 300, 100, 200, 20, 100, null, null);
    
    
    
                string string2 = "SUB C=100#SUB D=250#SUB E=300";
    
                string[] paras = string2.Split('#');
                List<DataRow> list = new List<DataRow>();
                string sqlcmd = "";
    
                for (int i = 0; i < paras.Length; i++)
                {
                    string[] par = paras[i].Split('=');
                    string mixpar = par[0] + "(MIN)";
                    string maxpar = par[0] + "(MAX)";
                    int mvalue = Convert.ToInt32(par[1]);
                    sqlcmd += " ( [" + mixpar + "] <  " + mvalue + "  and  [" + maxpar + "] >  " + mvalue + " ) ";
                    if ((i+1)!= paras.Length)
                    {
                        sqlcmd += " and ";
                    }
    
    
                }
    
                DataRow[] result = dt.Select(sqlcmd);
                foreach (DataRow row in result)
                {
                    Console.WriteLine("{0}, {1}", row[0], row[1]);
                }



    DataTable.Select Method (String, String)
    https://msdn.microsoft.com/en-us/library/way3dy9w(v=vs.110).aspx


    Note: Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    If you have any question about ADO.NET. Please go to the  ADO.NET Entity Framework and LINQ to Entities forum for getting suitable help.

    Best Regards,

    Yohann Lu


    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.

    • Proposed as answer by Fei HuModerator Wednesday, October 11, 2017 11:46 AM
    • Marked as answer by Gani tpt Thursday, October 12, 2017 6:49 AM
    Wednesday, October 11, 2017 11:42 AM
    Moderator
  • Excellent Yohann Lu.!!.cudoos..

    is it possible to add where condition in that for each loop.

    i want to more filter like below.. (using linq is used earlier)

    where

     let FrS = Convert.ToDouble(row.Field<double>("FROM"))
     let ToS = Convert.ToDouble(row.Field<double>("TO"))
     row.Field<String>("GRADE") == grd &&
                                                         val >= FrS && val <= ToS


    How to change your code  like above conditions...?????




    • Edited by Gani tpt Wednesday, October 11, 2017 12:47 PM
    Wednesday, October 11, 2017 12:05 PM

  • Hi Gani tpt

    The code examples provided above give you ideas, and you need to try to write and debug your code (Rather than let others write code for you). This is very important.

    From the further discussion, you need to ask your question in the ADO.NET Entity Framework and LINQ to Entities forum. This forum will give your more appropriate help.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools .

    Your understanding and cooperation will be grateful.

    Best Regards,

    Yohann Lu


    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.

    Thursday, October 12, 2017 1:53 AM
    Moderator