ADO.NET学习笔记

紫川文秀

ADO.NET包含的类位于System.Data命名空间





DataTable类:


一个数据表由三个主要类组成,即DataTable、DataColumn、DataRow。三个类分别定义了表、表中的列和表中的数据行。


定义表：
DataTable someTable = new DataTable();
DataTable someTable = new DataTable("someName");

添加DataColumn实例填充DataTable的Columns集合,DataColumn类在表的架构内定义了一个列。
DataColumn key = new DataColumn("ID", typeof(long));
someTable.Columns.Add(key);

DataTable对象的Columns集合包含一个Add重载，可以直接将标准DataColumn参数传递给Add方法：
someTable.Columns.Add("name",typeof(string));

DataColumn类包括几个很有用的属性：

AllowDBNull       是否允许DBNull值

AutoIncrement
AutoIncrementSeed
AutoIncrementStep   控制列的自动递增功能

Caption      标题或描述

ColumnName    列名称,通常通过构造函数指定

DataType     列的数据类型,通常由构造函数指定

DateTimeMode    时区信息

DefaultValue     创建新行的默认值

MaxLength       文本数据列,设定文本最大长度.默认为-1,不限定长度.

ReadOnly         是否只读

Unique            布尔值,设置为True时,唯一值约束


DataTable类包含一个PrimaryKey属性,用于指定一列或多列构成表的主键.主键由DataColumn对象数组组成.
someTable.PrimaryKey = new DataColumn[] { someTable.Columns["ID"]};

可以用数据集设计器,可视化的设计DataTable.

使用DataRow类向表中添加数据行.
DataTable类中包含NewRow方法,用于生成一个表所特有的数据行.每次向表中添加新行,永远都是用NewRow方法生成一个新的DataRow
DataRow oneRow = someTable.NewRow();

DataRow类包含ItemArray属性,这也是默认属性(可以省略".ItemArray").用它可以访问定义的每个列,可以用列名称访问,也可以用从0开始的索引号访问,还可以按实例来访问.

oneRow["ID"] = 1;
oneRow[0] = 1;
DataColumn oneColumn = someTable.Columns["ID"];
oneRow[oneColumn] = 1;
oneRow.ItemArray[1] = "Michael";(这里设置会不成功,省略".ItemArray"就可以成功设置,但是读取的时候带".ItemArray"可以正常使用)

将必要的数据值指定给新行后,用DataTable的Rows.Add方法将新行加入表中
someTable.Rows.Add(oneRow);

这个Add方法有一个重载,可省略对象创建过程,直接以参数方式提供最终的字段值.所提供的全部值,和出现的顺序和位置都必须与表的DataColumn实例相同.
someTable.Rows.Add(2, "mike");

表数据的删除.DataTable.Rows集合有Remove(按实例)和RemoveAt(按索引)方法,IndexOf方法可以根据实例返回行索引号.InsertAt方法向表中插入行,有两个参数,第一个为行实例,第二个为插入位置.Clear方法可以一次性清空表格.虽然这些方法很方便,但不存留痕迹,无法执行特定操作(包括外部数据库管理),所以不常用.

常用的方法是批处理,推迟数据更改,或一次进行多项更改.ADO.NET包含表和行级别的功能.可以在保证数据完整性的前提下,整体接收或拒绝"所提议的"更改.
AcceptChanges方法和RejectChanges方法.
删除行用行的Delete方法(可同步数据库)

行状态.
DataRow.RowState属性,枚举值
DataRowState.Detached
任何一个未添加到表的行的默认状态

DataRowState.Added
添加的行,未确认更改

DataRowState.Unchanged
已经添加的行,上次确认更改后未做任何动作

DataRowState.Delete
已删除的行,但未确认

DataRowState.Modified
行的字段被更改

行版本.
对行内字段进行更改时,会保存每个更改值的多个副本.行版本适用于整行,仅有一个字段被更改也是如此.

DataRowVersion.Original
最近使用AcceptChanges之后的值

DataRowVersion.Proposed
有字段已更改,但未确认

DataRowVersion.Current
对于未确认更改时,与Proposed相同,确认后的与Original相同

DataRowVersion.Default
连接到表的行,与Current相同,未连接表的行与Proposed相同

行的ItemArray属性返回的是Current版本

数据验证:
如果对所有赋值都进行异常处理,将大大影响性能.为了简化异常监视,DataRow类包含了BeginEdit方法.使用此方法,赋值时数据检查会被延迟,直到发出相应的DataRow.EndEdit()
添加列级别或行级别错误,会使DataRow.HasErrors和DataTable.HasErrors属性都设置为True,但不会触发异常.确认数据前,监视HasErrors属性(若有错误,使用DataRow.CancelEdit()方法返回原状态),以确保数据正确.ClearErrors会清除所有先前的错误通知.即使Endedit方法完成了对行的更改,这些更改依然未提交,必须调用AcceptChanges方法才会确认更改.
oneRow.BeginEdit();
try
{
  oneRow[0] = 2;
  oneRow.EndEdit();
  someTable.AcceptChanges();
}                 
catch(Exception ex)
{
  oneRow.CancelEdit();
  Console.WriteLine(ex.Message);
}

访问数据(单表)
DataTable.Rows的Find方法,可以根据主键查找某一行.
DataRow findRow = someTable.Rows.Find(2);

DataTable的Select方法,类似于SQL语句,方法有三个参数,第一个为筛选条件(类似SQL的where子句),第二个是排序规则(类似于SQL的Order by),第三个参数为行状态.
DataRow[] selectRows = someTable.Select("[ID]>10");

筛选表达式元素:

列名称   
内嵌空格或其他非文字字符时,用[列名称]

比较运算符
<; >; <=; >=; <>; =;

IN  (x,xx,xxx)
匹配集合

LIKE
通配符*(一个)和%(多个),只能在末尾

AND;  OR;  NOT;

括号 ()

常量
字符串/数字等等

CONVERT(expression,new-type)
强制转换类型

LEN  
字符串长度

ISNULL(列名,默认值)  
如果列值为DBNULL则返回默认值,否则返回列的值

IIF
条件函数

TRIM
去除末尾空格

substring
提取字符串

对比字符串时,Select方法默认忽略大小写,若要匹配大小写,可以设置CaseSensitive属性
someTable.Casesensitive=true;


添加表达式列
someTable.Columns.Add("LENG", typeof(int), "[ID]+len([name])");


DataSet类,表集合/表关系/字段约束条件

DataTable table2 = new DataTable("someAge");
table2.Columns.Add("ID", typeof(long));
table2.Columns.Add("AGE", typeof(int));
table2.Rows.Add(2, 50);
table2.Rows.Add(22, 80);
DataSet someSet = new DataSet("SetName");
someSet.Tables.Add(someTable);
someSet.Tables.Add(table2);
someSet.Relations.Add("OneToOne", someTable.Columns[0], table2.Columns[0]);

Relations类,表间关系,构造函数有7个重载,父表必须在前,子表字段必须在后.

DataRow类有两个方法,GetChildRows和GetParentRow,用于从链接关系的另一端提取相关行
Console.WriteLine(table2.Rows[1].GetParentRow("OneToOne")[1]);


在父子关系中实施串联删除：
找到定义链接关系的DataRelation实例，将DataRelation对象的DeleteRule设置为System.Data.Rule.Cascade


定义表约束:
Constraint类,用于表约束.有两种类型的约束,唯一列(System.Data.UniqueConstraint)和外键(System.Data.ForeignKeyConstraint).


聚合数据：
聚合函数由一组相关值返回一个计算值。共有7个聚合函数。
Sum  
数字求和

Avg  
数值的平均值

Min  
任意可排序值的最小值

Max  
任意可排序值的最大值

Count  
计数，只要非NULL就可以计数

StDev
  数字列的统计标准偏差

Var   
数字列的统计方差

聚合总是对单个DataTable列进行汇总。聚合函数仅考虑非NULL值，NULL值被排除在聚合之外。


DataTable对象的Compute方法，用于单一聚合计算。Compute方法有两个字符串参数，第一个是聚合函数表达式，第二个是筛选器，类似DataTable的Select方法中的筛选表达式。
Console.WriteLine(table2.Compute("Count([ID])",""));

使用聚合函数在父表中添加表达式列，聚合对应子表中的列。聚合函数只能对单个列操作，若要子表中多个列计算后聚合，必须先在子表添加相应表达式列。(Count(Child.[列名]))
在子表中可以添加表达式列，引用父表中某个字段。(Parent.[列名])

DataView类，类似SQL Server的视图。DataView用来公开一组DataRow对象，但它并不包含DataRow实例，而是包含DataRow的索引，这个索引就是DataRowView实例。ADO.NET使用DataRowView类来管理一个行的各个版本，特别是还没用DataTable.AcceptChanges方法确认更改时。DataRowView.Row属性根据DataRowView实例的设置返回实际行。

创建DataView:
DataView someView = new DataView(someTable);
someView.RowFilter = "ID>1";//筛选行
someView.Sort = "name desc";//行排序

DataView 实例的RowStateFilter属性,指定公开的行状态.默认状态显示所有满足RowFilter准则的可用行.DataViewRowState枚举值:

DataViewRowState.None
所有行,不考虑状态,在实际应用中,会筛选掉所有行,一个不留

DataViewRowState.Unchanged
只包含没有更改数据和状态的行

DataViewRowState.Added
已经被添加但还没确认的行

DataViewRowState.Deleted
已经被删除但还没确认的行

DataViewRowState.ModifiedCurrent
已经被修改的行,公开的行是修改后的数值

DataViewRowState.ModifiedOriginal
已经被修改的行,公开的行是修改前的原始数据

DataViewRowState.OriginalRows
未被更改的行,包括已删除的行

DataViewRowState.CurrentRows
所有当前状态为未删除的行,包括新行在内,一般用这个状态作为构造函数参数

每次修改RowFilter/Sort/RowStateFilter字段时,DataView会重建底层DataTable索引,即使以前未设置这些属性也会重建.为了减少重建次数,DataView类构造函数的一个重载,接受起始的RowFilter/Sort/RowStateFilter属性值.
DataView someView = new DataView(someTable, "[ID]>1", "[name] desc", DataViewRowState.CurrentRows);

DataView类包含三个布尔属性.用来限制通过此视图针对数据行执行的操作.这些限制仅用于视图,不影响底层表操作.

AllowNew
是否允许新建行
AllowEdit
是否允许编辑行
AllowDelete
是否允许删除行

使用DataView:
DataView.Count属性,返回应用了RowFilter和RowStateFilter属性之后,符合条件的行总数.

DataView.ToTable方法,生成新的DataTable对象,它只包含经过筛选的行和指定的列
DataTable filterTable = someView.ToTable("filterTable", true, new string[]{"name"}); //第一个参数为新生产表的名称,第二个参数为是否去除重复,第三个数组参数为制定要的列名称
Console.WriteLine(filterTable.Rows[0][0]);


将数据集(DataSet)以XML格式导出到文件
1.创建DataSet实例
2.添加相关DataTable,DataRelation等
3.调用DataSet的WriteXml方法,将文件名作为参数传递给这个方法

将XML文件导入一个新的数据集中
1.创建一个DataSet实例
2.调用DataSet的ReadXml方法,文件名作为参数

生成分层的父子数据
1.创建DataSet实例
2.添加相关DataTable,DataRelation等对象
3.将DataRelation.Nested属性设为TRUE
4.调用DataSet.WriteXml,生成XML内容

将DataSetColumn存储为一个基于XML的特性:

将DataColumn.ColumnMapping属性设置为MappingType.Attribute


补充内容 (2014-6-18 20:17):
学习笔记的第二部分(连接外部数据),放在4楼.

补充内容 (2014-6-18 20:20):
因字数限制,DataAdapter类的内容放在5楼.

zhaogang1960

谢谢分享
放在VBA板块会很快被淹没的，如果楼主愿意，帮你转到VSTO子板块去

紫川文秀

zhaogang1960 发表于 2014-1-2 15:48
谢谢分享
放在VBA板块会很快被淹没的，如果楼主愿意，帮你转到VSTO子板块去

那就麻烦版主移一下贴

紫川文秀

第二部分:外部数据连接及应用
      外部连接主要包括:Connection,Command,DataReader,DataAdapter.
   细分的命名空间:System.Data.SqlClient(sql server),System.Data.OleDb(OleDb包括access,excel等),System.Data.Odbc(Odbc连接类型),本笔记的例子均为SQL SERVER作为外部连接数据库.
  1.连接字符串
要访问外部数据存储,必须告诉ADO.NET如何找到这些资源,告诉它需要使用哪种数据格式,并提供访问过程所需要的安全凭据.这些信息是通过"连接字符串"来传递的.所谓连接字符串,就是一些特定格式的文本字符串.一个连接字符串包含多个由分号分隔的元素.
连接字符串的示例网站:www.connectionstrings.com
连接字符串里的几个主要键值:
Data Source        要访问的数据库服务器名称或地址
Initial Catalog        指定默认连接数据库
Integrated Security        布尔值,默认为False,设为True时,使用windows安全凭据访问数据库(即windows当前账号)
User ID和Password        当Integrated Security为False时,指定连接数据库使用的用户名和密码
Connection Timeout        规定查询或更新的超时时间,默认为15秒
MultipleActiveResultSets        默认为Flase,如果设为True,将允许对数据库打开多个Select查询,甚至允许在一个Select查询活动期间,运行Insert,Update和Delete命令.
Encrypt和TrustServerCertificate        一同协作,支持加密数据库访问

为了确保安全,使用连接字符串生成器类.
SQL Server的连接字符串生成器类位于System.Data.SqlClient.SqlConnectionStringBuilder.

SqlClient.SqlConnectionStringBuilder builder= new SqlClient.SqlConnectionStringBuilder();
builder.DataSource=@"127.0.0.1";
builder.InitialCatalog="test";
builder.IntegratedSecurity=True;
return builder.ConnectionString;

OleDb和ODBC的连接字符串生成器类分别为System.Data.DleDb.OleDbConnectionStringBuilder和System.Data.ODBC.OdbcConnectionStringBuilder.

   2.Connection类
连接到外部数据库需要三个条件:一个活动的Sql Server数据库,SqlClient.SqlConnection的一个实例和一个有效的连接字符串.
要创建一个新的数据库连接,需将一个有效的连接字符串传递给SqlConnection构造函数.创建实例后,必须专门打开,关闭和释放此连接.连接对象超过作用域并不会自动关闭数据库连接,必须以手动方式关闭它.尽可能晚的open连接,尽可能早的close连接.
SqlConnection linkToDb=new SqlConnection(builder.ConnectionString);
linkToDb.Open();
//…//代码块
linkToDb.Close();
linkToDb.Dispose();

也可用using语句来自动释放连接对象
Using(SqlConnection linkToDb=new SqlConnection(builder.ConnectionString))
{
linkToDb.open();
//…//代码块
}

连接池的概念:
Sql Server根据不同的连接字符串以及其他妨碍实现共享连接的因素来维护不同的连接池.一个连接池中可能包括不止一个活动连接,每个连接都等待代码发出对SqlConnection对象的新Open方法调用.
在连接字符串中包含Pooling=False键值,可以关闭一个特定连接的池.SqlConnection类还包括两个方法:ClearPool和ClearAllPools,分别用来清除相关池或全部池.

    3.Command类
SqlCommand类使用过程:
1)创建SqlCommand实例
2)将一个有效的SQL语句指定给这个对象的CommandText属性
3)将这个对象的Connection属性设置为一个打开的SqlConnection实例
4)根据需要指定其他可选属性
5)调用这个对象的众多同步或异步"执行"方法之一

SqlCommand的构造函数有许多重载版本,可以将SQL语句文本和ADO.NET连接指定为它们的参数.
String sqltext=@"update  worktable  set  ProcessedOn=getdate()  where  ProcessedOn  is  NULL";
Sqlcommand dataAction=new Sqlcommand(sqlText,linkToDB);

SqlCommand.CommandText属性接收两种类型的字符串数据,默认是标准的SQL语句,还一种是存储过程名称.若要使用存储过程,必须将SqlCommand.CommandType属性设置为CommandType.StoredProcedure.

SqlCommand类的各种"执行"方法:
1)ExecuteNonQuery方法
运行非查询语句,命令文本为标准SQL语句或存储过程名称.调用对象的ExecuteNonQuery方法
try
{
dataAction.ExecuteNonQuery();
}
catch  (Exception   ex)
{
MessageBox.Show("Failure: " + ex.Message);
}
ExecuteNonQuery是同步的方法.有非查询的异步方法:BeginExecuteNonQuery和EndExecuteNonQuery.

2)ExecuteScalar方法
返回单个值的SQL语句或存储过程,使用对象的ExecuteScalar方法.
string  sqlText=@"select  count(*)  from worktable";
SqlCommand dataAction=new SqlCommand(sqlText,linkToDB);
int  totalItems=(int)dataAction.ExecuteScalar();
因为ExecuteScalar方法返回System.Object类型的数据,所以必须进行转换.
从SQL SERVER 2005开始,对INSERT语句引入了OUTPUT关键字,用于返回自动生成的主键值.将OUTPUT关键字和ExecuteScalar方法结合起来,可以轻松获取自动生成的主键值.
string sqlText=@"insert   into   worktable   (…)   output    inserted.ID  values  (…)"
SqlCommand   dataAction =new  Sqlcommand(sqlText,linkToDB);
int newID=(int)dataAction.ExecuteScalar();

3)ExecuteReader方法
要处理由select查询或生成行的存储过程所返回的一个或多个数据行,可以用ExecuteReader方法.此方法返回一个System.Data.SqlClient.SqlDataReader对象.这个数据读取器是轻量级的,读取速度很快,只能前向访问数据行,是一种单向的一次性读取构造.
String sqlText=@"Select top 5 * from Customer;Select  top 3 *　from BillList";
SqlCommand DataAction=new SqlCommand(sqlText,linkToDB);
SqlDataReader dr=dataAction.ExecuteReader();

可以执行多个Select查询或多个返回行的存储过程,DataReader用HasRows属性指明查询是否返回了行.
SqlDataReader一次只公开一个数据行,可以使用默认item属性访问字段值(即列值,返回System.Object类型),调用Read方法(返回一个布尔值)访问下一行,若到达最后一行,此方法会返回False.调用NextResult方法访问第二个查询结果集.
If(dr.HasRows)
  {
While(dr.Read())
{
Console.WriteLine(dr[0]);
}
dr.NextResult();
while(dr.Read())
     {
      Console.WriteLine(dr[0]);
     }
  }
     dr.Close();
数据读取完成后,一定要调用Close或Dispose方法.
一个读取器关闭时,相关连接依然是打开的.可以在执行ExecuteReader方法时传递参数CommandBehavior.CloseConnection,在读取器关闭时也关闭连接.
SqlDataReader dr=dataAction.ExecuteReader(CommandBehavior.CloseConnection);
//any code
dr.Close();//the Connection  closes as  well
DataReader类还给出了各种get方法,用于获取相应类型字段值.还有GetName,GetDataTypeName,GetFiledType,GetValue,FieldCount等方法,用于对行中字段属性进行访问.

  4.Parameter类
SQL Server提供程序中,参数显示为System.Data.SqlClient.SqlParameter类.
在OLE DB中,为System.Data.OleDb.OleDbParameter类.
ODBC对应类为System.Data.Odbc.OdbcParameter.
它们都派生自System.Data.Common.DbParameter类.

1)标准语句中的参数
SQL语句中需要用户输入的部分以已命名占位符(@name 元素)的形式存在,在传递给数据库之后,这一元素会被类型专有的最终数据值所替代.这个过程提供了 一种一般性的命令文本,在命令以及其数据之间提供了一个逻辑分隔.这样做还可以提高SQL Server的性能(可以减少重复编译).

UPDATE Employee SET Salary=@NewSalary WHERE  ID=@EmployeeID;

在标准的SQL语句中(除存储过程之外的所有语句),每个占位符都必须以"@"符号开头,然后是一个唯一的名字.参数名是不区分大小写的.
和非参数化查询一样,经过增强的语句被包装在一个SqlCommand对象中.

String sqlText=@"UPDATE Employee SET Salary=@NewSalary WHERE  ID=@EmployeeID";
SqlCommand salaryUpdate= new SqlCommand(sqlText,linkToDB);

SqlCommand包括一个Parameters集合,将每个参数包装在一个SqlParameter实例中,根据需要设置其属性,并把它添加到SqlCommand.Parameters集合.

SqlParameter  paramValue = new SqlParameter("@NewSalary",SqlDbType.Money);
paramValue.Value=50000m;
salaryUpdate.Parameters.Add(paramValue);

如果数据需求简单,可以让SqlCommand.Parameters集合来定义参数的数据类型.集合的AddWithValue方法接收参数名和期望值,并使用特定的设置项命令中添加新的SqlParameter实例.

salaryUpdate.Parameters.AddWithValue("@EmployeeID",25L);

参数就绪后,就可以执行命令的Execute方法之一,像非参数化查询一样返回任意结果.

salaryUpdate.ExecuteNonQuery();

OLE DB和ODBC也提供对参数化查询的支持.但每个可替换元素没有在占位符名称前面加"@",而是在命令文本中显示为一个无名字的"?"号.添加到OleDbCommand或OdbcCommand的参数必须按照占位符指定的顺序添加.尽管命令文本未包含参数名称,但添加每个参数实例仍然应当包含以"@"为前缀的名称.
String sqlText=@"UPDATE  Employee  SET  Salary  =  ?   WHERE ID = ?";
OleDbCommand salaryUpdate=new OleDbCommand (sqlText,linkToDB);
salaryUpdate.Parameters.AddWithValue("@NewSalary",50000m);
salaryUpdate.Parameters.AddWithValue("@EmployeeID",25L);

2)存储过程中的参数
在存储过程中使用参数有4点主要区别.
     i>     确保将SqlCommand对象的CommandType属性设置为CommandType.StoredProcedure
    ii>     命令对象的CommandText只包含存储过程的名称
   iii>     参数的命名,每个参数包含一个以"@"为前缀的名称和数据类型,再加上其他配置.定义的名称必须和SQL Server定义存储过程时使用的参数名称完全匹配.
  iV>     参数的方向.SqlParameter类包含一个Direction属性.共有4种可供使用的System.Data.ParameterDirection选项.
              ParameterDirection.Input      此参数值被看作输入,从应用程序流向存储过程.这是默认选择.
              ParameterDirection.Output   此参数被用于从存储过程中检索数据,类似于out函数参数
              ParameterDirection.InputOutput   输入和输出方向的组合.应用程序提供一个可以有存储过程修改后返回的输入值.
              ParameterDirection.ReturnValue    对于存储过程或其他具有返回值的数据库功能,这种参数类型可以让用户手机该返回值.

紫川文秀

   5.DataAdapter类
ADO.NET的一大优点就是它能够断开连接，以表为核心的方式来管理外部数据，DadaAdapter类实现了此功能。DataAdapter利用DataReader展现的简单数据连接线，并将它与数据集中的高级数据管理功能结合在一起。通过创建几个简单对象，设计少量的SQL语句，就可以放心为数据集提供一些工具，用来使它与相关联的外部数据源保持同步。
DataAdapter包含4个SQL语句，分别是填充用的Select，和更新用的Insert、Update和Delete语句。
System.Data.SqlClient.SqlDataAdapter类适用于SQL Server。System.Data.OleDb.OleDbDataAdapter和System.Data.Odbc.OdbcDataAdapter分别支持OLE DB和ODBC。这些类都派生于System.Data.Common.DbDataAdapter，而这个类又派生自System.Data.Common.DataAdapter。

SqlDataAdapter提供了三种一般的支持功能
1>记录检索
用数据库记录填充DataTable是数据适配器的最低功能。在内部，SqlDataAdapter使用一个DataReader实例从数据库中检索记录，所以必须为它提供一个SELECT语句和一个连接字符串。返回数据行的存储过程也是有效的，适配器将会正确处理查询返回的多个记录集。
2>记录更新
更新需要三个语句来完成工作：INSERT、UPDATE和DELETE。可以手工编写这些语句，也可以使用一个“命令生成器”，根据原SELECT查询自动生成这些语句。
3>表名与列名映射
每个数据适配器都包括一个映射层，当在本地存储区和远程存储区传递数据时，会自动根据需要对表和列进行重命名。

1）将数据从源移动到内存中
       SqlDataAdapter.Fill方法是用一个有效的Select语句或一个选择数据的存储过程从SQL Server请求数据。在它通过一个内部SqlDataReader访问数据后，将这些记录移到所选择的Data Table或DataSet中。

将数据移动到DataTable中:
DataTable targetTable=new DataTable();
SqlDataAdapter    workAdapter= new SqlDataAdapter(" Select  *  from  Customer  order   by   LastName",connectionString);
workAdapter.Fill(targetTable);
     
     SqlDataAdapter类没有连接字符串或连接属性,所以如果没有向它们提供构造函数,就需要用一个SqlCommand实例来包含它们,这个实例就是直接指定给SqlDataAdapter.SelectCommand属性的实例.

DataTable  targetTable  =   new   DataTable();
using   (SqlConnection   linkToDB=  new SqlConnection(connectionString))
{
SqlDataAdapter   workAdapter=new  SqlDataAdapter();
workAdapter.Selectcommand=new  SqlCommand("Select   *    from   Customer   order  by  LastName",linkToDB);
workAdapter.Fill(targetTable);
}
    两个例子都没有显式打开连接，Fill方法会打开连接，并在完成操作后自动关闭连接。
    读入数据时，会查看数据的架构，根据需要生成DataTable实例的列和属性。如果Datatable已经拥有匹配（名字和数据类型均匹配），会使用这些列。任意新列都是在已有列旁边生成的。如果没有表映射，DataTable.TableName属性会被设为“Table”，不论Fill前TableName为何值。
    SelectCommand属性是一个标准的SqlCommand实例，所以可以使用命令对象的任一功能来访问远程数据，包括SqlParameter和存储过程。

String  SqlText="dbo.GetOrdersForCustomer";
SqlCommand  commandWrapper=new  SqlCommand(sqlText,linkToDB);
commandWrapper.CommandType=CommandType.StoreProcedure;
CommandType.Parameters.AddWithValue("@customerID",ActiveCustomerID);

SqlDataAdapter   workAdapter=new  SqlDataAdapter(commandWrapper);
DataTable orders=new DataTable();
workAdapter.Fill(orders);

将数据移动到DataSet中:
DataSet   targetSet = new DataSet();
SqlDataAdapter  workAdapter=new SqlDataAdapter("select  *  from  Customer  order  by  LastName",connectionString);
workAdapter.Fill(targetSet);
    和DataTable加载一样,Fill的DataSet版本也会自动生成架构.如果希望预先配置DataSet架构,可以采用人工方式生成表,或在调用Fill前调用SqlDataAdapter.FillSchema方法.
workAdapter.FIllSchema(targetSet,SchemaType.Source);
//将SchemaType.Mapped作为参数可以支持"映射"架构的创建
workAdapter.Fill(targetSet);
   Fill生成的表名依然是"Table",如果想指定表名,可以添加参数
workAdapter.Fill(targetSet,"Customer");
   如果SelectCommand包含多个Select语句,或者包含一个返回多个结果集的存储过程,Fill方法会导入多个表,创建的第一个表名为"Table"(默认),第二个表名为"Table1",以此类推.任何一个表中的重复列名都会以相同方式处理,在列名后加后缀,第一个是1,下一个加上2,以此类推.
   
2）将数据由内存移动到源中
      可以使用获取数据的同一个SqlDataAdapter将修改后的数据带回数据源中。这需要编写适当的数据操作语句，并调用SqlDataAdapter.Update方法。
      返回数据需设置三个不同属性：InsertCommand、UpdateCommand和DeleteCommand。和SelectCommand一样，这三个属性都是SqlCommand实例，每个属性中包含一个SQL语句（或存储过程）、一个SqlConnection引用和参数。在SelectCommand实例中，参数是可选的，但它们在三个更新命令中是基本组成部分。

SqlDataAdapter  unitAdapter=new SqlDataAdapter();
SqlCommand unitCommand=new SqlCommand("select * from UnitOfMeasure",linkToDB);
unitAdapter.SelectCommand=unitCommand;

unitCommand=new SqlCommand(@"insert  into  UnitOfMeasure (ShortName,FullName)  Values  (@ShortName,@FullName);Set @ID =@@IDENTITY;",linkToDB);
unitCommand.Parameters.Add("@ShortName",SqlDbType.VarChar,15,"ShortName");
unitCommand.Parameters.Add("@FullName",SqlDbType.VarChar,50,"FullName");
SqlParameter  param=unitCommand.Parameters.Add("@ID",SqlDbType.BigInt,0,"ID");
Param.Direction=ParameterDirection.Output;
unitAdapter.InsertCommand=unitCommand;

unitCommand=new  SqlCommand(@"update  UnitOfMeasure  set  ShortName=@ShortName,FullName=@FullName   where  ID=@ID",linkToDB);
unitCommand.Parameters.Add("@ShortName",SqlDbType.VarChar,15,"ShortName");
unitCommand.Parameters.Add("@FullName",SqlDbType.VarChar,50,"FullName");
param=unitCommand.Parameters.Add("@ID",SqlDbType.BigInt,0,"ID");
param.SourceVersion=DataRowVersion.Original;
unitAdapter.UpdateCommand=unitCommand;

unitCommand=new SqlCommand("delete  from  UnitOfMeasure  where  ID=@ID,linkToDB);
param=unitCommand.Parameters.Add("@ID",SqlDbType.BigInt,0,"ID");
param.SourceVersion=DataRowVersion.Original;
unitAdapter.DeleteCommand=unitCommand;

      以上代码三处主要改进:
1>参数列的制定
unitCommand.Parameters.Add("@ShortName",SqlDbType.VarChar,15,"ShortName");
结尾的"ShortName"参数指出在相关DataTable中引用的列名.这样就将更新命令与本地内容具体列关联起来.
2>在插入时检索键
InsertCommand的SQL语句实际由两个语句组成.
insert  into  UnitOfMeasure (ShortName,FullName)  Values  (@ShortName,@FullName);
Set @ID =@@IDENTITY;
第二个语句检索新纪录的主键。目的是检查新纪录标记符，从而正确刷新本地DataTable中的记录。@ID占位符相关SqlParameter实例的Direction属性需设置为Output。
Param.Direction=ParameterDirection.Output;
如果仅计划更新一次数据，然后销毁相关DataTable，就不一定要检索这个键值了，但如果还需要对新插入记录执行后续更新删除操作，就需要这个ID。
3>在更新和删除时使用原映像
在DeleteCommand中为@ID添加SqlParameter实例
param=unitCommand.Parameters.Add("@ID",SqlDbType.BigInt,0,"ID");
在进行更新时,DataTable中记录的"当前"视图已经被删除,没法提供ID列值。要找到这个ID，需要将参数的访问源版本设为“原始”版本。
param.SourceVersion=DataRowVersion.Original;
UpdateCommand部分包含了类似的代码，用于处理DataTable中的标识字段还没有被修改的情况。

    设置好三个命令实例后，调用Update方法即可回传修改后的数据。必须指出Update方法是用哪个本地源来更新，本地源可以是DataSet（更新集合中所有表）、DataTable或DataRow对象的一个数组。
workAdapter.Update(localTable);
   
    Update方法查看指定本地源中的每一个DataRow,判断哪些需要操作。对于要更新的每个行，适配器触发自己的OnRowUpdating事件，然后马上发出SQL命令。在数据库相应行进行更改后，触发相关的OnRowUpdated事件。这些事件使用户在发生更新时监视每一行，甚至可以跳过或修改特定行的更新计算。如，在OnRowUpdating事件中，传递给程序的事件参数公开一个Status属性，将其设置为UpdateStatus.SkipCurrentRow,即可放弃指定行的更新。
    在更新过程中发生任何错误都会引发异常。可以将SqlDataAdapter.ContinueUpdateOnError属性设为true,禁止异常,如果进行了这一设置，一定要监视OnRowUpdated事件，以便人工处理数据库报告的任何错误。

SqlCommandBuilder类
      ADO.NET包含一个“命令生成器”，可以代替用户编写数据修改命令。
     为SQL Server准备的命令生成器位于System.Data.SqlClient.SqlCommandBuilder中。使用方法：创建一个适配器，提供至少一个SelectCommand，然后创建SqlCommandBuilder实例，把数据适配器传递给它。
SqlDataAdapter   workAdapter=new SqlDataAdapter("select * from Customer  order  by  LastName",connectionString);
SqlCommandBuilder   customerBuilder=new  SqlCommandBuilder(workAdapter);
     这个生成器是通过运行select查询,查看数据源返回记录的架构来生成合适命令的。
     使用的局限性：
1>只能与单表查询一起使用
2>记录的架构必须包含至少一个主键或唯一列值.没有定义主键或唯一列值的表不能使用命令生成器
3>数据适配器修改了SelectCommand后,必须调用SqlCommandBuilder.RefreshShema方法调整所生成的命令
4>命令生成器只会为还SqlDataAdapter中还没有定义的命令生成命令。如，如果适配器中定义了SelectCommand和InsertCommand，而没有其他两个命令，生成器只会负责UpdateCommand和DeleteCommand处理。
5>命令生成器不能处理非标准外部表或列，如果字段名称中包含空格字符，则需要人工设计更新语句。

3）表映射与列映射
     当SqlDataAdapter从数据库检索结果时，它将第一个集合命名为“Table”，第二个命名为“Table1”，以此类推。
     如果使用Fill方法的DataSet/String语法：
workAdapter.Fill(targetSet,"Grid");
     targetSet中第一个表为"Grid",第二个为"Grid1",以此类推.
     要强制数据适配器将输入记录移动到正常表中，可以在调用Fill前，向表映射集合添加System.Data.Common.DataTableMapping对象。
workAdapter.TablMappings.Add("Table","Employee");

DataTableMapping  nameChange=new  DataTableMapping();
nameChange.SourceTable="Table1";
nameChange.DataSetTable="Customer";
workAdapter.TableMappings.Add(nameChange);

     可以映射的不仅是表名,还支持列名映射.
DataTableMapping   employeeMap=workAdapter.TableMappings.Add("Table","Employee");

employeeMap.ColumnMappings.Add("Employee ID","ID");
employeeMap.ColumnMappings.Add("Current  Department","DeptID");

     并不需要对所有输入表或列都设置映射。数据适配器包含两个属性，用于确定目标缺失情况的处理。
     SqlDataAdapter.MissingMappingAction属性决定当表和列映射规则没有包含输入表或列时,怎么做.这个属性为MissingMappingAction枚举值:
MissingMappingAction.Passthrough      这是默认设置.没有映射也会使用默认名称加入目标架构中.
MissingMappingAction.Ignore      忽略不匹配项目,丢弃数据.
MissingMappingAction.Error        发现不匹配产生一个异常.

    SqlDataAdapter.MissingSchemaAction属性决定当目标DataSet或DataTable还没有包含输入的映射或未映射的表或列名称时,如何处理.也是枚举值:
MissingSchemaAction.Add    默认设置。缺少项都会被自动添加。
MissingSchemaAction.AddWithKey   与Add选项相同,但主键及其他约束设置会随基本架构一起导入。
MissingSchemaAction.Ignore   任何未在目标架构中找到的输入表或列名称都将被丢弃.
MissingSchemaAction.Error     出现任何缺失都会产生异常.根本就不会对目标架构进行修改.

hnoyz

帖子不错，顶起

xd3210

好资料，谢谢分享！

cooloaky

这资料，果然是极好的……