C# 2005 访问数据库（二）

 前一回讲述了如何连接数据库，下面将讲述数据的更新、添加。首先介绍一下常用对象。

       DataAdapter对象，此对象可用于对数据进行的许多不同的操作，包括：查询、更新和删除，使用SQL查询初始化它。

       CommandBuilder对象 此对象在更新单一表的简单情况下，不需要知道如何编写SQL语句就可完成更新。CommandBuilder对象可以自动创建正确的SQL语句，并将其自动与DataAdapter相关联。DataAdapter作为参数传递给CommadBuilder构造函数。当创建CommandBuilder对象时，构造函数就生成SQL命令，并将其与传递进来的DataAdapter关联。

       DataSet对象是ADO.NET中的关键对象，所有复杂的操作都要使用它。DataSet包含一组相关的DataTable对象，代表要使用的数据库表。每一个DataTable对象都有子DataRow和DataColumn对象，分别代表数据库表的行和列。通过这些对象可以获取表、行和列的所有元素。下图为DataSet的结构演示：

 

使用DataSet对像操作数据的流程为：连接数据库－>提取数据－>存储数据—>操作数据—>提交操作－>关闭连接。下面为更新数据范例：（关键代码字体已经加粗）

using System;

using System.Data;

using System.DataSqlClient;

class DataUpdateExample{

    public static void Main(){

        //Specify SQL Server-specific connection string

        SqlConnection thisConnection = new SqlConnection(

        @"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");

        // Open connection

        thisConnection.Open();

        // Create DataAdapter object for update and other operations

        SqlDataAdapter thisAdapter=new SqlDataAdapter(

        "SELECT CustomerID,CompanyName FROM Customers",thisConnection);

        // Create CommandBuilder object to build SQL commands

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        // Create DataSet to contain related data tables,rows,and columns

        DataSet thisDataSet = new DataSet ();

        // Fill DataSet using query defined previously for DataAdapter

        thisAdapter.Fill(thisDataSet,"Customers");

        // Show data before change

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

        // Change data in Customer table,row 9,CompanyName column

        thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme.Inc";

        // Call Update command to mark change in table

        thisAdapter.Update(thisDataSet,"Customers");

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

        thisConnection.Close();

    }

}

下面为添加数据范例，关键代码已经加粗：

using System;

using System.Data;

using System.DataSqlClient;

class DataAddRowExample{

    public static void Main(){

        //Specify SQL Server-specific connection string

        SqlConnection thisConnection = new SqlConnection(

        @"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");

        // Open connection

        thisConnection.Open();

        // Create DataAdapter object for update and other operations

        SqlDataAdapter thisAdapter=new SqlDataAdapter(

        "SELECT CustomerID,CompanyName FROM Customers",thisConnection);

        // Create CommandBuilder object to build SQL commands

        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

        // Create DataSet to contain related data tables,rows,and columns

        DataSet thisDataSet = new DataSet ();

        // Fill DataSet using query defined previously for DataAdapter

        thisAdapter.Fill(thisDataSet,"Customers");

        // Show data before add

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

        // Add data in Customer table

        DataSet thisRow=thisDataSet.Tables["Customers"].NewRow();

        thisRow["CustomerID"] = "ZACZI";

        thisRow["CompanyName"] = "Zachary Zithers Ltd.";

        thisDataSet.Tables["Customers"].Rows.Add(thisRow);

        // Show data after add

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

        // Call Update command to mark Add in table

        thisAdapter.Update(thisDataSet,"Customer");

        thisConnection.Close();

    }

}

<注：>本文参考文献

《C#入门经典》清华大学出版社