SqlDataAdapter、DataSet、DataTable使用

来源：互联网发布：网络信息安全专业编辑：程序博客网时间：2024/04/29 02:31
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();              }          }      }  }