动态筛选和排序数据集中的数据
// 动态筛选和排序数据集中的数据
// FilterSort.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace Ch13
{
class FilterSort
{
static void Main( string[] args)
{
string strConn = "server=.\\MSSQL2012;integrated security=true;database=Northwind;";
string sql = "select * from customers;"
+ "select * from products where unitprice<10;" ;
SqlConnection conn = new SqlConnection(strConn);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(sql,conn);
DataSet ds = new DataSet();
da.Fill(ds, "cust" );
// 获取表的集合
DataTableCollection dtc = ds.Tables;
// 输出表头
Console.WriteLine( "customers表中的结果" );
Console.WriteLine( "公司名称" .PadRight(25) + "\t联系人");
// 设置显示过滤条件
string f1 = "country='Germany'" ;
// 设置排序
string srt = "companyname asc" ;
// 显示过滤并排序第一张表后的数据
// 注意:这里是用Select方法指定过滤和排序条件
foreach (DataRow row in dtc[ "cust" ].Select(f1, srt))
{
Console.WriteLine( "{0}\t{1}" ,
row[ "CompanyName" ].ToString().PadRight(25),
row[ "ContactName" ]);
}
// 显示第二张表中的数据
Console.WriteLine( "" .PadRight(40, '-' ));
Console.WriteLine( "Products表中的结果" );
Console.WriteLine( "产品名称" .PadRight(25) + "\t价格");
// 注意:这里是在SQL的where条件中过滤
foreach (DataRow row in dtc[1].Rows)
{
Console.WriteLine( "{0}\t{1}" ,
row[ "ProductName" ].ToString().PadRight(25),
row[ "UnitPrice" ]);
}
}
catch ( Exception ex)
{
Console.WriteLine( "发生错误:" + ex);
}
finally
{
conn.Close();
Console.ReadLine();
}
}
}
}
----------------------------------------
customers表中的结果
公司名称 联系人
Alfreds Putterkiste Maria Anders
Blauer See Delikatessen Hanna Moos
Die Wandernde Kuh Rita Müller
Drachenblut Delikatessen Sven Ottlieb
Frankenversand Peter Franken
K?niglich Essen Philip Cramer
Lehmanns Marktstand Renate Messner
Morgenstern Gesundkost Alexander Feuer
Ottilies K?seladen Henriette Pfalzheim
QUICK-Stop Horst Kloss
Toms Spezialit?ten Karin Josephs
----------------------------------------
Products表中的结果
产品名称 价格
Konbu 6.0000
Teatime Chocolate Biscuits 9.2000
Tunnbr?d 9.0000
Guaraná Fantástica 4.5000
Geitost 2.5000
Jack's New England Clam Chowder 9.6500
Rogede sild 9.5000
Zaanse koeken 9.5000
Filo Mix 7.0000
Tourtière 7.4500
Rh?nbr?u Klosterbier 7.7500
