程序从开始到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#入门经典》清华大学出版社