今天主要和大家探讨一下在C#中ADO.NET在数据访问中担当的角色以及如何与SQL SERVER之间进行数据传输访问。
ADO.NET是.NET FRAMEWORK中核心的组成部分,担当着数据访问的重要角色。那么,它是如何与SQL SERVER之间进行通信访问的呢?我们先来认识两个类库:System.Data、System.Data.SqlClient。
首先介绍一下在System.Data命名空间中我们常用的一些类以及一些方法使用。在System.Data中我们常用的类有:DataSet、DataTable、DataColumn、DataRow、DataView等。这几个类在我们日常开发中发挥着重要的数据存储以及传输的工作,那么,我们先来认识DataSet。在MSDN中,DataSet的定义如下:
[SerializableAttribute]
public class DataSet : MarshalByValueComponent, IListSource,
IXmlSerializable, ISupportInitializeNotification, ISupportInitialize, ISerializable
DataSet 由一组 DataTable 对象组成,您可使这些对象与 DataRelation 对象互相关联。DataSet是我们常用的数据源之一。
接下来我们看DataTable。DataTable 是 ADO.NET 库中的核心对象。其他使用 DataTable 的对象包括 DataSet 和DataView。当访问DataTable 对象时,请注意它们是按条件区分大小写的。 例如,如果一个 DataTable 被命名为“mydatatable”,另一个被命名为“Mydatatable”,则用于搜索其中一个表的字符串被认为是区分大小写的。但是,如果“mydatatable”存在而“Mydatatable”不存在,则认为该搜索字符串不区分大小写。 一个 DataSet 可以包含两个 DataTable 对象,(它们具有相同的TableName 属性值和不同的 Namespace 属性值)。那么我们如何通过代码编程的形式来创建一个DataTable呢?#假设我们已经创建了一个DataTable,名字叫做dataTable。(创建方式:DataTable dataTable=new DataTable();)#如果正在以编程方式创建DataTable,则必须先通过将 DataColumn 对象添加到DataColumnCollection(通过 Columns 属性访问)中来定义其架构。即
DataColumn column=new DataColumn("C1",typeof(System.Int32));
dataTable.Columns.Add(column);
在定义了DataColumn之后,我们就可以填充数据了,具体方式为:
DataRow dr1 = dataTable.NewRow();
//dr1["C1"] = "R1";----这里的索引既可以是数字索引即列索引也可以是列名
dr1[0]="R1";
dataTable.Rows.Add(dr1);
这样,一个包含数据的完整DataTable就构造出来了。下面我们看一个MSDN上的一个完整的例子:
// Put the next line into the Declarations section.
private System.Data.DataSet dataSet;
private void MakeDataTables()
{
// Run all of the functions.
MakeParentTable();
MakeChildTable();
MakeDataRelation();
BindToDataGrid();
}
private void MakeParentTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
DataRow row;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
column.ReadOnly = true;
column.Unique = true;
// Add the Column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ParentItem";
column.AutoIncrement = false;
column.Caption = "ParentItem";
column.ReadOnly = false;
column.Unique = false;
// Add the column to the table.
table.Columns.Add(column);
// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["id"];
table.PrimaryKey = PrimaryKeyColumns;
// Instantiate the DataSet variable.
dataSet = new DataSet();
// Add the new DataTable to the DataSet.
dataSet.Tables.Add(table);
// Create three new DataRow objects and add
// them to the DataTable
for (int i = 0; i<= 2; i++)
{
row = table.NewRow();
row["id"] = i;
row["ParentItem"] = "ParentItem " + i;
table.Rows.Add(row);
}
}
private void MakeChildTable()
{
// Create a new DataTable.
DataTable table = new DataTable("childTable");
DataColumn column;
DataRow row;
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
// Add the column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
// Create third column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ParentID";
column.AutoIncrement = false;
column.Caption = "ParentID";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
dataSet.Tables.Add(table);
// Create three sets of DataRow objects,
// five rows each, and add to DataTable.
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 0 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 5;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 1 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 10;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 2 ;
table.Rows.Add(row);
}
}
private void MakeDataRelation()
{
// DataRelation requires two DataColumn
// (parent and child) and a name.
DataColumn parentColumn =
dataSet.Tables["ParentTable"].Columns["id"];
DataColumn childColumn =
dataSet.Tables["ChildTable"].Columns["ParentID"];
DataRelation relation = new
DataRelation("parent2Child", parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}
private void BindToDataGrid()
{
// Instruct the DataGrid to bind to the DataSet, with the
// ParentTable as the topmost DataTable.
dataGrid1.SetDataBinding(dataSet,"ParentTable");
}
示例中创建了两个 DataTable 对象和一个 DataRelation 对象,并将这些新对象添加到 DataSet 中。 这些表随后会显示在 DataGridView 控件中。
private void DemonstrateDataView()
{
// Create one DataTable with one column.
DataTable table = new DataTable("table");
DataColumn colItem = new DataColumn("item",
Type.GetType("System.String"));
table.Columns.Add(colItem);
// Add five items.
DataRow NewRow;
for(int i = 0; i <5; i++)
{
NewRow = table.NewRow();
NewRow["item"] = "Item " + i;
table.Rows.Add(NewRow);
}
// Change the values in the table.
table.Rows[0]["item"]="cat";
table.Rows[1]["item"] = "dog";
table.AcceptChanges();
// Create two DataView objects with the same table.
DataView firstView = new DataView(table);
DataView secondView = new DataView(table);
// Print current table values.
PrintTableOrView(table,"Current Values in Table");
// Set first DataView to show only modified
// versions of original rows.
firstView.RowStateFilter=DataViewRowState.ModifiedOriginal;
// Print values.
PrintTableOrView(firstView,"First DataView: ModifiedOriginal");
// Add one New row to the second view.
DataRowView rowView;
rowView=secondView.AddNew();
rowView["item"] = "fish";
// Set second DataView to show modified versions of
// current rows, or New rows.
secondView.RowStateFilter=DataViewRowState.ModifiedCurrent
| DataViewRowState.Added;
// Print modified and Added rows.
PrintTableOrView(secondView,
"Second DataView: ModifiedCurrent | Added");
}
private void PrintTableOrView(DataTable table, string label)
{
// This function prints values in the table or DataView.
Console.WriteLine("\n" + label);
for(int i = 0; i<table.Rows.Count;i++)
{
Console.WriteLine("\table" + table.Rows[i]["item"]);
}
Console.WriteLine();
}
private void PrintTableOrView(DataView view, string label)
{
// This overload prints values in the table or DataView.
Console.WriteLine("\n" + label);
for(int i = 0; i<view.Count;i++)
{
Console.WriteLine("\table" + view[i]["item"]);
}
Console.WriteLine();
}
文中示例部分取自MSDN。