C# 2005 访问数据库（三）

       前面讲了添加、更新，这回讲数据库的查找和删除。查找、删除的实现与添加、更新类似，也是使用DataSet对象。

       程序从开始到Fill()方法调用都与前面的范例相同，而在这里使用Fill()之前需要构建主键码。主键码是用于搜索的键码，由表的一列或几列构成，包含可以在表中唯一的标识行的值或值集合。故通过键码可以找到一行且仅可以找到一行。Customers表中CustomerID列作为主键码。

       首先创建DataColumns数组。因为键码就是一列或几列，所以要使用数组结构；DataColumn数组为Keys。接着将键码数组key[0]的第一个元素赋给Customers表中的CustomerID列的值。最后将Keys赋给Customers DataTable对象的PrimaryKey属性。

       注：另一种方法是从数据库里加载键码，但必须显示的告诉ADO.NET加载主键码的信息。方法是在填充DataSet之前设置DataAdapter MissingSchemaAction属性为MissingSchemaAction.AddWithKey。

       Find()带有的参数是要查找的值，这个值可以是多键码的对象数组。如果Fill()定位了一个匹配行，则返回匹配该行的一个DataRow对象。如果没有匹配行，则返回null引用。

下面为查找代码，关键代码已经加粗：

using System;

using System.Data;

using System.DataSqlClient;

class DataAddFindExample{

    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("count before add:{0}",thisDataSet.Tabels["Customers"].Rows.Count);

        // Set up keys object for define primary key

        DataColumn[] keys = new DataColumn[1];

        keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];

        thisDataSet.Tables["Customer"].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 Customer tables.");

                 }

        } else{

                Console.WriteLine("ZACZI already present in database.");

         }

        // Call Update command

        thisAdapter.Update(thisDataSet,"Customers");

        Console.WriteLine("count after add:{0}",thisDataSet.Tabels["Customers"].Rows.Count);

        thisConnection.Close();

    }

}

       删除记录前首先要查找该记录是否存在，执行完删除操作以后在查找该记录是否已经删除。下面为删除代码，关键代码已经加粗：

using System;

using System.Data;

using System.DataSqlClient;

class DataDeleteExample{

    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 delete

        Console.WriteLine("count before delete:{0}",thisDataSet.Tabels["Customers"].Rows.Count);

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

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

        // Call Update command to mark delete in table

        thisAdapter.Update(thisDataSet,"Customers");

        Console.WriteLine("count after delete:[0]",thisDataSet.Tabels["Customers"].Rows.Count);

        thisConnection.Close();

    }

}

<注：>本文参考文献

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