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#入门经典》清华大学出版社