C#---ADO.net

Ado.net实际上就是一个用于访问数据的类组，包括所有的System.data名称空间以及嵌套的名称空间，还有System.Xml名称空间中的一些与数据访问相关的专用库，其位于System.data.dll程序集和相关的System.data.xxx.dll程序集中。

提供者对象：
1.连接对象:提供到数据源的基本连接。
2.命令对象：用次对象向数据源发出命令，对不同的提供者，该对象的名称也不同，如Sqlcommand,OdbcCommand,OledbCommand.
3.CommandBuilder对象：用于构建SQL命令。
4.DataReader对象：读取数据。
5.DataAdapter对象：可以执行对数据源的各种操作。

用户对象：
1.DataSet对象：一般表示一组相关表，包括Datatable和DataRelation对象
2.DataTable对象：代表DataSet中的一个表，包括DataColumn和DataRow对象。
3.DataRelation对象:代表有公共列的两个表之间的关系

微软的数据访问简史：
每个数据库系统都有自己的函数组，用于访问数据
ODBC的开发
OLE DB的开发
旧式的ActiveX Data Object是（ADO）
ADO.net在.net Framework 1.0中引入，随其发展
ADO.net for Entities是面向专家级的高级接口

1、用DataReader读取数据

---

//连接数据源，包括SQL server,Oracle,ole db,odbc,其它内置的数据提供者
SqlConnection thisConnection = new SqlConnection(
//代表正在访问的SQL Server名称，格式为“计算机名\实例名”。
//句点表示当前服务器实例，也可用名称或计算机你的网络名称代替它。
@”Data Source=.\SQLEXPRESS;”
+@”AttachDbFilename=’C:\SQL Server 2000 Sample Databases\NORTHWND.MDF’;”
+ @”Integrated Security=True;Connect Timeout=30;User Instance=true”);
thisConnection.Open()；//打开连接
//创建命令对象，并给它提供SQL命令，执行数据库操作
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText = “SELECT CustomerID, CompanyName from Customers”;

// 读取数据，DataReader是一个轻量级的对象，只能读取不能更新数据
SqlDataReader thisReader = thisCommand.ExecuteReader();

// DataReader的Read（）方法从查询结果中读取一行数据，若有则返回TRUE，否则false。
while (thisReader.Read())
{
// Output ID and name columns
Console.WriteLine(“\t{0}\t{1}”,
thisReader[“CustomerID”], thisReader[“CompanyName”]);
}
//关闭读取器对象和连接对象
thisReader.Close();
thisConnection.Close();
Console.Write(“Program finished, press Enter/Return to continue:”);
Console.ReadLine();

2.用DataSet读取数据

---

    SqlConnection thisConnection = new SqlConnection(
      @"Data Source=.\SQLEXPRESS;" +
     @"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +
      @"Integrated Security=True;Connect Timeout=30;User Instance=true");

        SqlDataAdapter thisAdapter = new SqlDataAdapter(
        "SELECT CustomerID, ContactName FROM Customers", thisConnection);
        DataSet thisDataSet = new DataSet();

//用DataAdapter对象填充DataSet
thisAdapter.Fill(thisDataSet, “Customers”);
foreach (DataRow theRow in thisDataSet.Tables[“Customers”].Rows)
{
Console.WriteLine(theRow[“CustomerID”] + “\t” +
theRow[“ContactName”]);
}

        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

3.更新数据库
（1）用数据库中要使用的数据填充DataSet
（2）修改存储在DataSet中的数据，如更新，插入，删除
（3）把DataSet中修改的内容返回到数据库中

---

SqlConnection thisConnection = new SqlConnection(
@”Data Source=.\SQLEXPRESS;” +
@”AttachDbFilename=’C:\SQL Server 2000 Sample Databases\NORTHWND.MDF’;” +
@”Integrated Security=True;Connect Timeout=30;User Instance=true”);

        SqlDataAdapter thisAdapter = new SqlDataAdapter(
             "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

        //SqoCommandBuider对象用于负责生成用于更新数据库的SQL语句，不必自己创建这些语句
        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "Customers");

        Console.WriteLine("name before change: {0}",
             thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);

        thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc.";
        //Updata()方法自动便利DataTable中的行，以找出需要对数据库作出的变动。Rows集合中的每个DataRow对象
        //都具有属性RowState,可以跟踪此行是否已删除，添加，修改。所作的任何变化都会反映到数据库中
        thisAdapter.Update(thisDataSet, "Customers");

        Console.WriteLine("name after change: {0}",
             thisDataSet.Tables["Customers"].Rows[9]["CompanyName"]);

        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

4.给数据库添加行

4.1 不考虑原表中是否已有相同的主键
SqlConnection thisConnection = new SqlConnection(
@”Data Source=.\SQLEXPRESS;” +
@”AttachDbFilename=’C:\SQL Server 2000 Sample Databases\NORTHWND.MDF’;” +
@”Integrated Security=True;Connect Timeout=30;User Instance=true”);

        SqlDataAdapter thisAdapter = new SqlDataAdapter(
             "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();
        thisAdapter.Fill(thisDataSet, "Customers");

        Console.WriteLine("# rows before change: {0}",
                           thisDataSet.Tables["Customers"].Rows.Count);

        DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
        thisRow["CustomerID"] = "ZACZI";
        thisRow["CompanyName"] = "Zachary Zithers Ltd.";
        thisDataSet.Tables["Customers"].Rows.Add(thisRow);

        Console.WriteLine("# rows after change: {0}",
                          thisDataSet.Tables["Customers"].Rows.Count)

        thisAdapter.Update(thisDataSet, "Customers");

        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

4.2考虑添加的行是否与原表冲突

---

        SqlConnection thisConnection = new SqlConnection(
            @"Data Source=.\SQLEXPRESS;" +
            @"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +
            @"Integrated Security=True;Connect Timeout=30;User Instance=true");

        SqlDataAdapter thisAdapter = new SqlDataAdapter(
             "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "Customers");

        Console.WriteLine("# rows before change: {0}",
             thisDataSet.Tables["Customers"].Rows.Count);
        DataColumn[] keys = new DataColumn[1];
        keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
        thisDataSet.Tables["Customers"].PrimaryKey = keys;

        DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");

        if (findRow == null)
        {
            Console.WriteLine("ZACZI not found, will add to Customers table");

            DataRow thisRow = thisDataSet.Tables["Customers"].NewRow();
            thisRow["CustomerID"] = "ZACZI";
            thisRow["CompanyName"] = "Zachary Zithers Ltd.";
            thisDataSet.Tables["Customers"].Rows.Add(thisRow);
            if ((findRow =
                 thisDataSet.Tables["Customers"].Rows.Find("ZACZI")) != null)
            {
                Console.WriteLine("ZACZI successfully added to Customers table");
            }
        }
        else
        {
            Console.WriteLine("ZACZI already present in database");
        }

        thisAdapter.Update(thisDataSet, "Customers");

        Console.WriteLine("# rows after change: {0}",
        thisDataSet.Tables["Customers"].Rows.Count);

        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

5.删除行

---

        SqlConnection thisConnection = new SqlConnection(
            @"Data Source=.\SQLEXPRESS;" +
            @"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +
            @"Integrated Security=True;Connect Timeout=30;User Instance=true");


        SqlDataAdapter thisAdapter = new SqlDataAdapter(
             "SELECT CustomerID, CompanyName FROM Customers", thisConnection);


        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);


        DataSet thisDataSet = new DataSet();

        thisAdapter.Fill(thisDataSet, "Customers");

        Console.WriteLine("# rows before change: {0}",
             thisDataSet.Tables["Customers"].Rows.Count);

        DataColumn[] keys = new DataColumn[1];
        keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
        thisDataSet.Tables["Customers"].PrimaryKey = keys;

        DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI");

        if (findRow != null)
        {
            Console.WriteLine("ZACZI already in Customers table");
            Console.WriteLine("Removing ZACZI  . . .");
            //delete()方法并不执行删除操作，它仅仅标记要删除的行，delete()方法将行的RowState
            //设置为Deleted，然后Update（）就从数据库中删除Rows集合中标记为Deleted的行
            findRow.Delete();

            int rowsAffected = thisAdapter.Update(thisDataSet, "Customers");
            Console.WriteLine("Deleted {0} rows.", rowsAffected);

        }

        Console.WriteLine("# rows after change: {0}",
             thisDataSet.Tables["Customers"].Rows.Count);
        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

6.在DataSet中访问多个表
Ado.net与旧式的相比最大一个优点就是DataSet对象可以跟踪多个表和它们之间的关系，这里研究Customers和Orders表。

---

        SqlConnection thisConnection = new SqlConnection(
            @"Data Source=.\SQLEXPRESS;" +
            @"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +
            @"Integrated Security=True;Connect Timeout=30;User Instance=true");

        SqlDataAdapter thisAdapter = new SqlDataAdapter(
             "SELECT CustomerID, CompanyName FROM Customers", thisConnection);

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        DataSet thisDataSet = new DataSet();

        SqlDataAdapter custAdapter = new SqlDataAdapter(
             "SELECT * FROM Customers", thisConnection);
        SqlDataAdapter orderAdapter = new SqlDataAdapter(
             "SELECT * FROM Orders", thisConnection);
        custAdapter.Fill(thisDataSet, "Customers");
        orderAdapter.Fill(thisDataSet, "Orders");

        //创建DataRelation对象，得到的关系命名为CustOrders
        DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
             thisDataSet.Tables["Customers"].Columns["CustomerID"],
             thisDataSet.Tables["Orders"].Columns["CustomerID"]);

        foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
        {
            Console.WriteLine("Customer ID: " + custRow["CustomerID"] +
                              " Name: " + custRow["CompanyName"]);
            //指定customers为父表，orders为子表，给定父表中的一行，使用DataRow对象
            //的GetChildRows（）方法提取子表中与其对应的所有行
            foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
            {
                Console.WriteLine("  Order ID: " + orderRow["OrderID"]);
            }
        }
        /*
         * to implement writexml as instructed in chapter 24 Writing XML from a DataSet
         * custOrderRel.Nested = true;
         *
         * thisDataSet.WriteXml(@"c:\northwind\nwinddata.xml");
         * Console.WriteLine(
         *   @"Successfully wrote XML output to file c:\northwind\nwinddata.xml");
         */
        /*
         * to implement writexml as instructed in chapter 24 Writing XML from a DataSet*/
         custOrderRel.Nested = true;

          thisDataSet.WriteXml(@"c:\northwind\nwinddata.xml");
          Console.WriteLine(
             @"Successfully wrote XML output to file c:\northwind\nwinddata.xml");


        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();

7.处理更多的关系

---

        SqlConnection thisConnection = new SqlConnection(
            @"Data Source=.\SQLEXPRESS;" +
            @"AttachDbFilename='C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +
            @"Integrated Security=True;Connect Timeout=30;User Instance=true");

        DataSet thisDataSet = new DataSet();
        SqlDataAdapter custAdapter = new SqlDataAdapter(
             "SELECT * FROM Customers", thisConnection);
        custAdapter.Fill(thisDataSet, "Customers");

        SqlDataAdapter orderAdapter = new SqlDataAdapter(
             "SELECT * FROM Orders", thisConnection);
        orderAdapter.Fill(thisDataSet, "Orders");

        SqlDataAdapter detailAdapter = new SqlDataAdapter(
             "SELECT * FROM [Order Details]", thisConnection);
        detailAdapter.Fill(thisDataSet, "Order Details");

        SqlDataAdapter prodAdapter = new SqlDataAdapter(
             "SELECT * FROM Products", thisConnection);
        prodAdapter.Fill(thisDataSet, "Products");

        DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
                    thisDataSet.Tables["Customers"].Columns["CustomerID"],
                    thisDataSet.Tables["Orders"].Columns["CustomerID"]);

        DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail",
                    thisDataSet.Tables["Orders"].Columns["OrderID"],
                    thisDataSet.Tables["Order Details"].Columns["OrderID"]);

        DataRelation orderProductRel = thisDataSet.Relations.Add(
          "OrderProducts", thisDataSet.Tables["Products"].Columns["ProductID"],
           thisDataSet.Tables["Order Details"].Columns["ProductID"]);

        foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
        {
            Console.WriteLine("Customer ID: " + custRow["CustomerID"]);

            foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
            {
                Console.WriteLine("\tOrder ID: " + orderRow["OrderID"]);
                Console.WriteLine("\t\tOrder Date: " + orderRow["OrderDate"]);

                foreach (DataRow detailRow in
                         orderRow.GetChildRows(orderDetailRel))
                {
                    Console.WriteLine("\t\tProduct: " +
                    detailRow.GetParentRow(orderProductRel)["ProductName"]);
                    Console.WriteLine("\t\tQuantity: " + detailRow["Quantity"]);
                }
            }
        }
        thisConnection.Close();
        Console.Write("Program finished, press Enter/Return to continue:");
        Console.ReadLine();
    }