Filter DataTableCollection : DataTableCollection : ADO.Net C# Examples

    
using  System;
using  System.Data;
using  System.Data.SqlClient;

class  MainClass
{
      static  void  Main(string[]  args)
      {
            string  connString  =  @"server  =  .\sqlexpress;integrated  security  =  true;database  =  northwind";

            string  sql1  =  @"select  *  from  customers";

            string  sql2  =  @"select  *  from  products  where  unitprice  <  10";

            string  sql  =  sql1  +  sql2;

            SqlConnection  conn  =  new  SqlConnection(connString);

            try
            {
                  SqlDataAdapter  da  =  new  SqlDataAdapter();
                  da.SelectCommand  =  new  SqlCommand(sql,  conn);

                  DataSet  ds  =  new  DataSet();
                  da.Fill(ds,  "customers");

                  DataTableCollection  dtc  =  ds.Tables;

                  Console.WriteLine("CompanyName".PadRight(20)  +"ContactName".PadLeft(23)  +  "\n");

                  string  fl  =  "country  =  'Germany'";

                  string  srt  =  "companyname  asc";

                  foreach  (DataRow  row  in  dtc["customers"].Select(fl,  srt))
                  {
                      Console.WriteLine("{0}\t{1}",
                            row["CompanyName"].ToString().PadRight(25),
                            row["ContactName"]);
                  }

                  Console.WriteLine("ProductName".PadRight(20)  +"UnitPrice".PadLeft(21));

                  foreach  (DataRow  row  in  dtc[1].Rows)
                  {
                        Console.WriteLine("{0}\t{1}",
                              row["productname"].ToString().PadRight(25),
                              row["unitprice"]);
                  }
            }
            catch(Exception  e)
            {
                  Console.WriteLine("Error:  "  +  e);
            }
            finally
            {
                  conn.Close();
            }
      }
}