Recursive Search With Yield in C#

 

 

Requirement :

Have the following Table

 

partno    designnumber    code1    code2    code3    module
11111    x    x    x    x    NM63-1
(NU12)050201    NULL    NULL    NULL    NULL    NM63-1
22222    NU12    05    02    01    NULL
(NK12)060302    NU12    05    02    01    NULL
33333    NK12    06    03    02    NULL
4444    NK12    06    03    02    NULL
(MJ12)070403    NK12    06    03    02    NULL
5555    MJ12    07    04    03    NULL

 

(NU12)050201 is referred partno which need to find

22222    NU12    05    02    01    NULL

 

(NK12)060302    NU12    05    02    01    NULL
->

33333    NK12    06    03    02    NULL
4444    NK12    06    03    02    NULL
(MJ12)070403    NK12    06    03    02    NULL

->

5555    MJ12    07    04    03    NULL

 

Final Result:

11111    x    x    x    x    NM63-1
22222    NU12    05    02    01    NULL
33333    NK12    06    03    02    NULL
4444    NK12    06    03    02    NULL
5555    MJ12    07    04    03    NULL

 

Solution 1  : not using Yield

……………..

//define a global variable

public List<DataRow> final = new List<DataRow>();

……………..

public void GetRecursivePartListNotUsingYield(DataRow dr)
   {
       string partnonew = dr["partno"].ToString();
           if (partnonew.Length == 12) // validation function , will use different
           {               DataTable dt2 = DatabaseFactory.CreateDatabase("ETAConnectionString").ExecuteDataSet(CommandType.Text, "select * from [0228test] where designnumber like '" + partnonew.Substring(1, 4) + "' and code1='" + partnonew.Substring(6, 2) + "' and code2='" + partnonew.Substring(8, 2) + "' and code3='" + partnonew.Substring(10, 2) + "'").Tables[0];

               foreach (DataRow dr2 in dt2.Rows)
               {
                   GetRecursivePartListNotUsingYield(dr2);
               }

           }
           else
           {

               final.Add(dr);
           }

   }

 

………………………….

 

test :

 

DataTable dt = DatabaseFactory.CreateDatabase("ETAConnectionString").ExecuteDataSet(CommandType.Text, "select * from [0228test] where module='NM63-1'").Tables[0];

      foreach (DataRow dr in dt.rows

      {
          GetRecursivePartListNotUsingYield(dr);

      }

// Check final list

foreach(DataRow dr in final)

{

 

 

}

 

Solution 2:

 

public IEnumerable<DataRow> GetRecursivePartListUsingYield(DataTable dt)
  {
      foreach (DataRow dr in dt.Rows)
      {

          string partnonew = dr["partno"].ToString();
          if (partnonew.Length != 12)
          {
              yield return dr;
          }
          else
          {
              DataTable dt2 = DatabaseFactory.CreateDatabase("ETAConnectionString").ExecuteDataSet(CommandType.Text, "select * from [0228test] where designnumber like '" + partnonew.Substring(1, 4) + "' and code1='" + partnonew.Substring(6, 2) + "' and code2='" + partnonew.Substring(8, 2) + "' and code3='" + partnonew.Substring(10, 2) + "'").Tables[0];
              if (dt2.Rows.Count > 0)
              {
                  foreach (DataRow dr2 in GetRecursivePartListUsingYield(dt2))
                  {
                      yield return dr2;

                  }

              }

          }
      }

  }

Test:

 

DataTable dt = DatabaseFactory.CreateDatabase("ETAConnectionString").ExecuteDataSet(CommandType.Text, "select * from [0228test] where module='NM63-1'").Tables[0];

         foreach (DataRow dr in GetRecursivePartListUsingYield(dt))
         {
             Debug.WriteLine(dr["partno"].ToString());
         }