数据集的筛选和排序

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace PopDataSet
...{
    class Program
    ...{
        static void Main(string[] args)
        ...{
            string connString = "Data Source=(local)/SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=12345";
            string sql1 = @"select * from customers ";//此处要有个空格，否则会报错。
            string sql2 = @"select * from products where unitprice<10";
            string sql = sql1 + sql2;
            SqlConnection conn = new SqlConnection(connString);
            try
            ...{
                //conn.Open();不用显示的打开连接
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "customers");//调用Fill方法时若连接未打开，Fill方法会自动打开它。在填充完数据集后，该方法将关闭连接。
                                         //但是，如果在调用Fill方法时打开了连接，该方法就使用该连接，使用完后也不会关闭连接。
                                         //应在Finally块中进行标准的连接关闭操作，因为它在已关闭的连接上调用也不会出错。
                //Get the data tables collection
                DataTableCollection dtc = ds.Tables;
                Console.WriteLine(
                    "CompanyName".PadRight(20) +
                    "ContactName".PadLeft(23) + " "
                );
                //Set display filter
                string f1 = "country='Germany'";
                //Set sort
                string str = "companyname asc";
                //Display filtered and sorted data
                foreach(DataRow row in dtc["customers"].Select(f1,str))
                ...{
                    Console.WriteLine(
                        "{0} {1}",
                        row["CompanyName"].ToString().PadRight(25),
                        row["ContactName"]
                    );
                }
                //Display data from second data table
                Console.WriteLine(" --------------------------------");
                Console.WriteLine(
                    "ProductName".PadRight(20) +
                    "UnitPrice".PadLeft(21) + " "
                );
                //Display data
                foreach (DataRow row in dtc[1].Rows)//第一个表命名为customers，第二个使用默认名称customers1
                ...{
                    Console.WriteLine(
                        "{0} {1}",
                        row["productname"].ToString().PadRight(25),
                        row["unitPrice"]
                    );
                }
            }
            catch(SqlException e)
            ...{
                Console.WriteLine(e);
            }
            finally
            ...{
                conn.Close();
            }
        }
    }
}