SqlDataAdapter、DataSet、DataTable使用
原文链接：https://blog.csdn.net/zhang_hui_cs/article/details/7327395
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)) // dtc["Customers"] 是DataTable , 看为内存的一张数据库表 ， 按照条件+排序查询
{
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();
}
}
}
}
