SqlDataAdapter、DataSet、DataTable使用

using System.Data;  
using System.Data.SqlClient;  
  
//SqlDataAdapter的MSDN网站：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx    
//DataTable的MSDN网站：http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx    
//DataSet的MSDN网站: http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx    
//DataSets, DataTables, and DataViews (ADO.NET)： http://msdn.microsoft.com/en-us/library/ss7fbaez.aspx    
namespace Chapter13  
{  
    class FilterSort  
    {  
        static void Main(string[] args)  
        {  
            // connection string    
            string connString = @"    
                                server = .;    
                                integrated security = true;    
                                database = northwind    
                             ";  
  
            // query 1    
            string sql1 = @"    
                            select    
                               *    
                            from    
                               customers    
                         ";  
  
            // query 2    
            string sql2 = @"    
                            select    
                               *    
                            from    
                               products    
                            where    
                               unitprice < 10    
                         ";  
  
            // combine queries    
            string sql = sql1 + sql2;  
  
            // create connection    
            SqlConnection conn = null;  
  
            try  
            {  
                conn = new SqlConnection(connString);  
                // create data adapter    
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);  
  
                // create and fill data set    
                DataSet ds = new DataSet();  
  
                /*  
                 * 如果SqlDataAdapter对应的sql语句返回多个数据集，  
                 * 则SqlDataAdapter对应的DataTable名  
                 * 会被依次命名为"Table", "Table1","Table2"……  
                 * 如下调用得到的将会把"Table"对应着"Customers"， 
                 * "Table1"对应着"Products"                   
                 */  
                da.TableMappings.Add("Table", "Customers");  
                da.TableMappings.Add("Table1", "Products");  
  
                da.Fill(ds);  
  
                // get the data tables collection    
                DataTableCollection dtc = ds.Tables;  
  
                //输出DataTableCollection中所有表的表名。    
  
                System.Console.WriteLine("DataTable in the DataTableCollection: ");  
                foreach (DataTable dt in dtc)  
                {  
                    System.Console.WriteLine("\t{0}", dt.ToString());  
                }  
                // display data from first data table    
                //    
                // display output header    
                System.Console.WriteLine("Results from Customers table:");  
                System.Console.WriteLine(  
                   "CompanyName".PadRight(20) +  
                   "ContactName".PadLeft(23) + "\n");  
  
                // set display filter    
                string fl = "country = 'Germany'";  
  
                // set sort    
                string srt = "companyname asc";  
                foreach (DataRow row in dtc["Customers"].Select(fl, srt))  
                {  
                    System.Console.WriteLine(  
                       "{0}\t{1}",  
                       row["CompanyName"].ToString().PadRight(25),  
                       row["ContactName"]);  
                }  
  
                // display data from second data table    
                //    
                // display output header    
                System.Console.WriteLine("\n----------------------------");  
                System.Console.WriteLine("Results from Products table:");  
                System.Console.WriteLine(  
                   "ProductName".PadRight(20) +  
                   "UnitPrice".PadLeft(21) + "\n");  
  
                //下句中dtc["Products"]中还可以按照表索引值访问，等价于dtc[1]    
                foreach (DataRow row in dtc["Products"].Rows)  
                {  
                    System.Console.WriteLine("{0}\t{1}",  
                       row["productname"].ToString().PadRight(25),  
                       row["unitprice"]);  
                }  
            }  
            catch (System.Exception e)  
            {  
                System.Console.WriteLine("Error: " + e);  
            }  
            finally  
            {  
                // close connection    
                conn.Close();  
            }  
        }  
    }  
}