ADO.NET数据操作摘录

一、连接
ADO.NET最大的特色就在于支持在断开连接的情况下对数据库里的内容进行操作，这样可以大大的节约过多连接带来的消耗，前面的那一篇文章中已经给了一个具体的例子说明ADO.NET的这种特性。我们可以在从数据库里获得数据的时候打开连接，在得到数据之后就断开连接，对dataset里面的数据进行操作，然后在把dataset里的内容更新到数据库里面的时候再打开连接。对于dataReader则必须一直保持连接。
使用这种特性的时候有几点要注意一下：
（1）更改连接属性的时候必须断开连接
（2）切换数据库的时候选择conn.changeDatabase(dbName)，减少断开连接与新建连接往返带来的消耗
ADO.NET同时支持数据库自带连接池。在一个连接关闭之后，连接会在池中保持一段时间，然后才实际的关闭，如果在超时之前，有人请求建立相同的连接，就将打开的连接分配给请求者，这对于经常进行打开和断开的连接可以减少很多的消耗。不过在SQL SERVER 2000中采用集成安全性的连接无法入池。
连接涉及到的事件有Dispose,InfoMessage,StateChange，在MSDN可以查到，不再赘述。
模板代码：
Dim conn As SqlConnection
conn=New SqlConnection("……") '里面为连接字符串
conn.open()
'进行相应的操作
conn.close()

二、Command对象
ADO.NET允许以三种不同的方式获取数据库里面的数据Command，DataSet，DataReader，Command是最基本的方法，通过执行SQL命令的形式获得数据。

（1）创建 可以用两种方式创建
a、创建新的Command对象
Dim cmd As New SqlCommand
cmd.connection=conn
cmd.CommandText="Select * FROM Customer"
b、获得对conn中command对象的引用
Dim cmd As SqlCommand
cmd=conn.createCommand();
cmd.CommandText="Select * FROM Customer"
推荐第二种方法

（2）执行 四种执行方式
ExecuteNonQuery() 返回受命令影响的行数
ExecuteScalar() 返回第一行第一列（使用与集函数）
ExecuteReader() 返回一个DataReader对象
ExecuteXmlReader()返回一个XmlReader对象

（3）参数 主要是用在存储过程中，有复杂和精简两种形式
复杂方法：
Dim param As new SqlParameter("@Return",SqlDbType.Int)
param.Direction=ParameterDirection.ReturnValue
cmd.Parameters.Add(param)
精简方法
cmd.Parameters.Add("@Return_value",DbType.Int32).Direction=ParameterDirection.ReturnValue
建议：如果需要处理输出值时的时候使用参数，只处理输入值的时候就不用使用参数了。

（4）事务
用SQL语句：
Begin TRAN
SQL操作
If @@ERROR <>0
Begin
RollBack TRAN
Return @@ERROR
End
Commit TRAN
Return 0
在ADO.NET中编写事务
cmd.Transaction = conn.BeginTransaction()
try
{
cmd.CommandText="..."
cmd.ExecuteNonQuery()
cmd.Transaction.commit()
}
catch(Exception ex)
cmd.Transaction.Rollback()
End try
如果希望将数据库事务处理与一些外部系统结合起来（比如在数据库更新时同时进行了WEB更新，如果WEB更新失败希望回滚事务时），选择客户端编写事务处理（用ADO.NET编写）
仅仅做数据库事务处理的话就直接在服务端写事务语句（在SQL SERVER2000 中写事务）
在事务中可以创建SavePoint来实现部分事务回滚
cmd.Transaction.save("New Customer")
cmd.Transaction.Rollback("New Customer")

（5）批处理查询 如果有多条SQL语句并且可以一起执行的话，就可以进行批处理查询。在DataReader中支持批处理查询获得的数据集。
cmd.CommandText="Select * FROM Customer;Select * FROM Inovince;"
Dim rdr As SqlDataReader
rdr=cmd.ExecuteReader()
rdr中即包含两个SQL语句执行的结果

三、DataReader对象
DataReader对象只能对查询获得的数据集进行自上而下的访问，但效率很高。如果仅仅是访问数据的话，可以使用DataReader。但DataReader要求一直连接，所以将结果的一小部分先放在内存中，读完后再从数据库中读取一部分，相当于一个缓存机制。这对于查询结果百万级的情况来说，带来的好处是显而易见的。
模板代码：
Do While rdr.Read()
Console.WriteLine(rdr(0)) '也可输出rdr("CustomerID")
Loop
如果要进行类型限制的话可以输出(String）rdr(0)或rdr.GetString(0)

从DataReader里读出数据的时候要注意属性是否为空，如果属性可以为空，则读出数据时应进行判断
If Not rdr.IsDBNull(0)
Console.writeLine(...)

用DataReader读取记录时，数据库默认上锁，可以通过更改DataReader的默认属性改变。

如果DataReader里面的数据是批处理语句执行得到的话，可以通过NextResult访问
模板代码：
Do
Do While rdr.Read()
Console.WriteLine(rdr(0))
Loop
Loop While rdr.NextResult()

处理元数据（显示每个属性的情况）
Dim schema As DataTable
schema=rdr.GetSchemaTable() '得到元数据表，表里的每列对应每个属性的特性集合
对于每一列属性对应的row，通过row("DataType")获得这列属性的数据类型。
例子：
Sub Main()
Dim conn As New SqlConnection("data source=localhost;initial catalog=StudentCourse;" & _
"User ID=;Password=;")
Dim cmd As SqlCommand
conn.Open()
cmd = conn.CreateCommand
cmd.CommandText = "Select * From Student"
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader
Dim schema As DataTable
schema = rdr.GetSchemaTable
Dim i As Integer
i = 0
Debug.WriteLine(schema.Rows(i)("ColumnName"))
Debug.WriteLine(schema.Rows(i)("DataType"))
Debug.WriteLine(schema.Rows(i)("ColumnSize"))
conn.Close()
End Sub

四、DataSet
在ADO.NET中DataSet的作用是为数据源提供一个断开式的存储，而不必关心数据源，操作只用在DataSet中进行就行了。
有三种方法可以创建DataSet:1、通过DataAdapter 2、通过XML 文件 3、用人工方法确定架构，然后逐行输入数据。
主要介绍第一种方法。
DataAdapter用于将DataSet连接到基本数据存储，本质上是一种元Command对象。
它包括SelectCommand对象，InsertCommand对象，UpdateCommand对象，DeleteCommand对象。
模板代码：
Dim dataAdpater As New SqlDataAdapter("Select * From Student",conn)
Dim dataSet As New DataSet()
dataAdapter.Fill(dataSet)
这时dataSet的表名默认为Table
如果使用批处理查询并将得到的结果填入dataSet中则表名默认为Table，Table1，Table2……

TableMappings：
表名映射：
生成dataAdapter之后再进行表名映射
dataAdapter.TableMappings.Add("Table","Customer")
dataAdapter.Fill(dataSet)
这时Table的别名就变为Customer（对dataSet用Table或Customer操作都可以），dataSet.Tables("Customer")就可以引用到这个表
或
dataAdapter.TableMappings.Add("ADONET","Customer")
dataAdapter.Fill(dataSet,"ADONET")
列名映射：
dataAdapter.TableMappings.Add("Table","Customer")
dataAdapter.TableMappings("Customer").ColumnMappings.Add("CustomerID","ID)
dataAdapter.FIll(dataSet,"Customer")
在dataAdapter中添加表名映射后，相对有两个表，一个表是原先的表，另一个表是映射后的表，列名也经过相应的映射，因此在Fill时要 指定是哪个表，否则显示的时候就不会显示映射后的列名。另外在表名映射后，在数据绑定时只能指定dataMember为Student。
例子（绑定到DataGrid）
Dim conn As New SqlConnection("data source=localhost;initial catalog=StudentCourse;" & _
"User ID=;Password=;")
conn.Open()
Dim dataAdapter As New SqlDataAdapter("Select * From Student", conn)
conn.Close()
Dim dataSet As New DataSet
dataAdapter.TableMappings.Add("Table", "Student")
dataAdapter.TableMappings.Add("Student", "Student") //这一步不能省，否则在下面的列名映射时会提示找不到映射名为Student表
dataAdapter.TableMappings("Student").ColumnMappings.Add("Sno", "学号")
dataAdapter.TableMappings("Student").ColumnMappings.Add("Sname", "姓名")
dataAdapter.TableMappings("Student").ColumnMappings.Add("sex", "性别")
dataAdapter.TableMappings("Student").ColumnMappings.Add("classID", "班级号")
dataAdapter.TableMappings("Student").ColumnMappings.Add("age", "年龄")
dataAdapter.TableMappings("Student").ColumnMappings.Add("address", "地址")
dataAdapter.Fill(dataSet, "Student")
DataGrid1.DataSource = dataSet
DataGrid1.DataMember = "Student"
另一种添加映射的方法是创建DataTableMapping对象并把它添加到DataAdapter中
Dim conn As New SqlConnection("data source=localhost;initial catalog=StudentCourse;" & _
"User ID=sa;Password=firerainbow;")
conn.Open()
Dim dataAdapter As New SqlDataAdapter("Select * From Student", conn)
conn.Close()
Dim dataSet As New DataSet
Dim myMap As New DataTableMapping("Table", "Student")
myMap.ColumnMappings.Add("Sno", "学号")
myMap.ColumnMappings.Add("Sname", "姓名")
myMap.ColumnMappings.Add("sex", "性别")
myMap.ColumnMappings.Add("classID", "班级号")
myMap.ColumnMappings.Add("age", "年龄")
myMap.ColumnMappings.Add("address", "地址")
dataAdapter.TableMappings.Add(myMap)
dataAdapter.Fill(dataSet, "Table")
DataGrid1.DataSource = dataSet
DataGrid1.DataMember = "Student"

架构（Schema），通过FillSchema添加架构
1、添加主键
customerTable.PrimaryKey=New DataColumn[]{CustomerTable.Columns("CustomerID")} （通过数组的形式添加主键）

2、添加关系
dataSet.Relations.Add("Customers_Invoices",dataSet.Tables("Customers").Columns("CustomerID"),dataSet.Tables ("Invoinces").Columns("CustomerID"),true)

3、添加约束
有两种主要约束：唯一约束、外码约束（UniqueConstraint，ForeignKeyConstraint）
其中唯一约束又分为DeleteRule（级联删除约束）、UpdateRule（级联更新约束）、AcceptRejectRule（调用AcceptChanges或 RejectChanges时的约束）

4、添加触发器
可以对6中DataSet事件添加触发器
RowChanging，RowChanged,ColumnChanging,ColumnChanged,RowDeleting,RowDeleted

5、列架构
比如添加列的属性：customerTable.Columns("CustomerID").ReadOnly=true
或添加AutoIncrement列：
customerTable.Columns("CustomerID").AutoIncrement=true
customerTable.Columns("CustomerID").AutoIncrementSeed=1 （列起始位置）
customerTable.Columns("CustomerID").AutoIncrementStep=1 （列递增步长）
可能有人会说这一切在DBMS里面做不就行了吗，干吗那么大费周折的在ADO.NET对Dataset再写一遍呢？
这主要是出于对效率方面的考虑，如果客户端的错误输入能在客户端就被发现出来，而不用传到服务端进行验证的话就可以减少不必要的传输了。

表达式列：
Dim exColumn As New DataColumn("LineTotal")
exColumn.DataType=typeof(float)
exColumn.Expression="((price-(price*Discount))*Quantity)"
dataSet.Tables("items").Columns.Add(excolumn)

五、操纵dataset
在DataSet中DataRow是其所有数据的基本存放位置，它主要是由一个值数组组成，代表DataTable单独一行。
DataRow中主要包括一下几种信息：1、行中每一列的当前值，2、行中每一列的原始值，3、行状态，4、父行与子行间的链接

初始化一个DataRow：
DataTable dataTable＝dataSet.Tables[0];
DataRow newRow=dataTable.NewRow(); //用dataTable生成DataRow可以利用dataTable里面的模式
dataTable.Rows.Add(newRow);

删除行：
DataTable.Rows.Remove(行实例);
DataTable.Rows.RemoveAt(行号);
DataRow.Delete(); //行自身移除

读写DataRow的值：
row["列名"]，row[列号]均可引用其中的一个属性
DataColumn a=dataTable.Columns("列名"); //可以获得一个列

对行进行批处理更改：
BeginEdit()开始更改，EndEdit()结束更改，同时将更改结果写入DataSet，CancelEdit()，取消更改
例如：
row.BeginEdit();
对row进行更改
row.EndEdit();

将数据批量加载到DataTable
dataTable.BeginLoadData();
dataTable.LoadDataRow(row1,false); //第二个参数为true时，调用dataTable.AcceptChanges()时接受更改，为false直接添加
……
dataTable.EndLoadData();
使用这种数据加载方式可以在数据加载期间屏蔽所有的数据约束，索引也不会予以维护，极大的加快了数据加载速度

行的版本：
current：当前值
default：根据操作的不同决定行的default值
original：最后一次调用AcceptChanges()之后的值
proposed：调用AcceptChanges()之前被更改的值
例如要获得行的original值：
String oldString=row("FirstName",DataRowVersion.original);

行的状态：
row.RowState获得行的状态，例如删除后变成Deleted，数据存储更新后变为unchanged

六、DataSet导航
在ADO.NET中每个表都保持其相对独立性，允许在行级上导航不同表之间的相关行（向下导航到子行，向上导航的父行）
如DataRow[] invoiceRows=custRow.GetChildRows("Customer_invoice"); //通过关系导航到子行

七、DataView
DataView就时数据视图，为数据库结构提供了外模式的实现。
同时DataView也可以为窗体控件和Web控件提供数据绑定功能，在每一个DataTable中内建了一个DataView为：DataTable.DefaultView();

创建DataView
DataView sortedView=new DataView(dataTable);

对DataView进行排序
dataTable.DefaultView.sort="lastName";
dataTable.DefaultView.sort="lastName,FirstName DESC";

对DataView进行筛选：
1、通过对其中的RowFilter属性设置可以实现筛选
dataTable.DefaultView.RowFilter="Vendor='Rawlings'";
不过筛选表达式只能设置成比较简单的表达式，功能有限，不过可以满足基本的要求。
同样在DataTable里面也可以进行简单的搜索，返回一个DataRow数组，例：
DataRow[] compoundRows=dataTable.select("Vendor='wilson' AND price>20.00)
2、通过RowState来筛选
dataTable.DefaultView.RowStateFilter="DataViewRowState.originalRows"可以筛选出符合要求状态的row

对DataView进行搜索：
相对于DataView使用RowFilter进行筛选得到一个矩形数据集，使用Find、FindRows可以更准确的查找到与特定键相匹配的行
搜索的时候必须首先设置DataView的sort属性：
int found=dataTable.DefaultView.Find("wilson"); //获得行的位置
DataRowView[] rows=dataTable.DefaultView.FindRows("Rawlings") //过得一个row数组

八、更新DB
在DataSet中，每一个DataTable对应着一个DataAdapter，DataAdapter.Update()时，DataTable自动更新。
更新的时候可以使用CommandBuilder自动根据DataSet的变化生成更新的SQL命令
SqlCommandBuilder bldr=new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(custTable);
不过Update接受DataSet参数并不更新DataSet而是更新DataSet中的一个叫"Table"的表
使用CommandBuilder进行更新的时候要注意一下几点：
1、SelectCommand必须有效
2、必须有主码
3、若SelectCommand填充DataTable后架构发生改变，应该在Update()之前调用CommandBuilder.RefreshSchema();
4、更新DB时不受关系、约束或者DataSet中其他表的影响
虽然使用CommandBuilder比较方便，不用自己写更新命令，但自动生成的命令性能不高，这时可以考虑自己编写存储过程或直接使用带参数的sql语句，例如：
String insQry="Insert into Customer(CustomerID) Values (@Customer)";
SqlCommand insCmd=conn.CreateCommand();
insCmd.CommandText=insQry;
SqlParameterCollection insParams=insCmd.Parameters;
insParams.Add("@CustomerID",SqlDbType.UniqueIdentifier,0,"CustomerID");
dataAdapter.InsertCommand=insCmd;
dataAdapter.Update();
在dataAdapter.Update()更新时还可以控制更新的范围：
dataAdapter.Update(invTable.GetChanges(DataRowState.Deleted); //只更新被删除的部分

九、事务
tx=conn.BeginTransaction(IsolationLevel.Serializable);
invDA.SelectCommand.Transaction=tx;
事务操作
tx.Commit();提交 //tx.Rollback();事务回滚

十、数据绑定
简单版本：（对文本框、标签等）
{Controls}.DataBindings.Add("{Property}",{dataSource},"{dataMember}");
其中Property为待绑定的属性，dataSource为DataView或DataTable，dataMember为dataSource其中的某个属性

复杂版本：（对ListBox、ComboBox等）
要分别设置各个属性实现绑定：
DataSource=支持IList的一个对象（DataTable或DataView）
DisplayMember：待显示的DataSource中的一个属性
ValueMember：确定在DataSource中引用哪一个数据行，即实现与DisplayMember的名值对应

DataGrid绑定：
1、可以设置DataSource属性实现静态绑定
2、可以使用SetDataBinding函数实现动态绑定

同时DataGrid支持主控/详细表显示
masterGrid.setDataBinding(customerTable,"");
detailGrid.setDataBinding(customerTable,"Customer_Invoices") //第二个属性要设置成关系约束
这样在主表中选择一行，在子表中就根据主表行中外码在子表中找到相应行

绑定之后，绑定项中就有一个CurrencyManager属性实现游标功能
BindingContext[CustomerTable]返回一个CurrencyManager对象，其中的Position属性可以更改，实现游标的移动。