SQL Server中使用ADO.NET(学习笔记整理-2)
一、概述-ADO.NET的结构
ADO.NET，一组数据访问服务的类，为创建分布式数据共享应用程序提供了一组丰富的组件，提供了对关系数据、XML、和应用程序数据的访问。
ADO.NET提供了俩个组件用来访问和处理数据：.NET Framework数据提供程序，DataSet。.NET Framework数据提供程序是专门为数据处理以及快速地只读、只进访问数据。而，DataSet是专门为独立于任何数据源的数据访问而设计的，可以用于多种不同的数据源，用于XML数据或用于管理应用程序本地的数据。
注：.NET Framework数据提供程序是联机访问数据，而DataSet是脱机访问数据。DataSet一次性将数据载入到内存中，访问DataSet时只访问内存，而不会再访问数据库。
二、建立与管理连接—ADO.NET执行数据处理的第一步：建立与数据库的连接
1、连接字符串
2、使用SqlConnection建立与断开连接
SqlConnection类表示SQL Server数据库的一个打开链接，构造SqlConnection对象时需指定连接字符串。
如：string str =”server=MS-STUDYZY;database=TestDB1;uid=sa;pwd=p@ssw0rd”;
SqlConnection sconnection=new SqlConnection(str);
sc.Open();
sc.Close();
确保最后关闭数据库连接的方法有：
（1）使用using语句
string str =”server=MS-STUDYZY;database=TestDB1;uid=sa;pwd=p@ssw0rd”;
using(SqlConnection sconnection=new SqlConnection(str))
{
sc.Open();
}
（2）使用try-finally
string str =”server=MS-STUDYZY;database=TestDB1;uid=sa;pwd=p@ssw0rd”;
SqlConnection sc=new SqlConnection(str);
try{
sc.Open();
}
catch{
//捕获异常
}
finally
{
if(sr!=null)
sr.Close();
}
3、数据库连接池
三、使用SqlCommand创建命令并执行数据操作
SqlCommand类 ：表示要对 SQL Server 数据库执行的一个 Transact-SQL 语句或存储过程。
建立与数据源的连接后，就需要用Command对象来执行命令并从数据源中返回结果。创建命令的方法有：
（1）使用Command构造函数来创建命令,如SqlCommand的构造函数，采用在数据源、Connection对象和Transaction对象中执行的SQL语 句的可选参数；
（2）使用Connection的CreateCommand()方法来创建用于特定连接的命令。
1、构造SqlCommand对象执行一个SQL语句
string str1=”select * from Person.AddressType”;
Sqlcommand scom=new Sqlcommand (str1,sconnection);
2、构造SqlCommand对象执行一个存储过程
SqlCommand cmd=new SqlCommand(“StoredProcedure_Name”,sconnection);
cmd.CommandType=CommandType.StoredProcedure;//将CommandType属性设置为CommandType.StoredProcedure
cmd.Parameters.Add(new SqlParameter(“@StoreProcedure_parameter_Name”,“”) //将Parameter属性设置为调用存储过·程时使用的参数。
SqlParameters outParameter=new SqlParameter(“@outParameter_Name”,SqlDbType.Int);//有输出参数的存储过程
outParameters.Direction=ParameterDirection.Output;//将输出参数的Direction属性设置为Output。
cmd.Parameter.Add(output);
3、执行数据库操作
用于执行数据库操作的方法有：
(1)SqlCommand. ExecuteReader():执行返回行的命令，返回一个SqlDataReader对象，通过该对象可以以流的形式读取数据库返回的结果。若用于执行命令（如SQL语句）则肯能不会产生预期的效果。
(2)SqlCommand.ExcuteNonQuery()：执行insert,delete,update,set等语句命令,用于获的数据库受影响的行数，也就是数据库中被修改、插入、或删除的行数。
(3)SqlCommand.ExecuteScalar():从数据库中检索单个值，用于返回数据库执行后的一个单值。
(4)SqlCommand.ExecuteXmlReader():将CommandText发送到Connection并生成一个XmlReader对象，即返回一个XmlReader对象。
(5)SqlCommand.BeginExecuteNonQuery():启动此SqlCommand描述的T-SQL语句或存储过程的异步执行，执行insert,delete,update,set语句等命令。
(6)SqlCommand.BeginExecuteReader(): 启动此SqlCommand描述的T-SQL语句或存储过程的异步执行，并从服务器中检索一个或多个结果集。
(7)SqlCommand.BeginExecuteXmlReader():启动此SqlCommand描述的T-SQL语句或存储过程的异步执行。
四、使用SqlDataReader读取数据
使用SqlCommand实例的ExecuteReader方法将返回一个SqlDataReader对象。可以使用DataReader从数据库中检索只读、只进的数据流。SqlDataReader类提供一种从 SQL Server 数据库读取行的只进流的方式。
1、获取数据流
（1）SqlDataReader.Read():从查询结果中获取行。使 SqlDataReader前进到下一条记录。返回值为BOOL值，如果存在多个行，则为 true；否则为 false
注：每次使用完SqlDataReader对象后都应调用Close()方法,SqlCommand如果包含输出参数或返回值，那么在SqlDataReader关闭之前，将无法访问这些输出参数或返回值。
（2）SqlDataReader.NextResult()方法：按顺序循环访问多个结果集，当读取批处理 Transact-SQL 语句的结果时，使数据读取器前进到下一个结果。返回一个BOOL值，如果存在多个结果集，则为 true；否则为 false
。
（3）SqlDataReader.GetSchemaTable():检索有关当前结果集的架构信息。返回一个填充了行和列的 DataTable对象。它描述 SqlDataReader的列元数据。
datatable schemaTalbe=reader.GetschemaTable();
foreach(datarow row in schemaTable.Rows)
{
foreach(datacolumn column in schemaTable.Columns
)
{
console.writeline(column.ColumnName+"=”+row[column];
console.readline();
}
}
reader.Close();
2、获取对象-将SqlDataReader转换为实体对象
使用SqlDataReader读取的数据分为俩种：一种是数据库中最多找到一行数据读取出一个对应的实体实例，另外一种是数据库中返回了0到多行数据，读取出的就是一个实体实例的集合。
（1）定义将SqlDataReader转换为实体对象的函数：
如要获得AddressType对象，在获得了SqlDataReader后，需要将其转换为对象的函数为
private AddressType GetAddressTypeByReader(IDataReader reader)//传入DataReader
｛//读取SqlDataReader为对象的每个属性赋值
AddressType addressType=new AddressType();
addressType.AddressTypeID=(int)reader[“AddressTypeID”];
addressType.Name=reader[“Name”].Tostring();
addressType.rowguid=reader[“rowguid”].Tosting();
addressType.ModifiedDate=(DataTime)reader[“ModifiedDate”];
return addressType;
｝
获得单个对象实例：
String sql =”select * from Person.AddressType Where AddressTypeID=”+id;
SqlCommand cmd=new SqlCommand(sql,conn);
SqlDataReader reader=cmd.ExecuteReader();
if(reader.Read())
{
AddressType addressType=GetAddressTypeByReader(reader);
reader.Close();
}
获得对象实例集合：
String sql =”select * from Person.AddressType Where AddressTypeID=”+id;
SqlCommand cmd=new SqlCommand(sql,conn);
SqlDataReader reader=cmd.ExecuteReader();
List<AddressType> addressTypes=new List<AddressType>();
while(reader.read())
{
addressTypes.Add(GetAddressTypeByReader(reader));
}
reader.Close();
五、使用DataSet填充SqlDataAdapter
DataSet类：表示数据的内存中缓存，提供一致的关系编程模型，表示整个数据集，包含对数据进行包含、排斥和约束的表以及表之间的关系。
SqlDataAdapter类：表示用于填充 DataSet和更新 SQL Server 数据库的一组数据命令和一个数据库连接，用于从数据源检索数据并填充DataSet中的表。
使用DataSet的方法有：
（1）以编程的方式在DataSet中创建DataTable,DataRelation,Constraint，并使用数据填充表。(编程方式)
（2）通过SqlDataAdapter用现有关系数据源中的数据表填充DataSet。（SqlDataAdapter）
（3）使用XML加载 保持DataSet内容。（XML）
1、DataSet
(1)DataSet的结构：DataSet中可以存储多个查询的结果，一个结果在DataSet中使用DataTalbe来表示。
（2）DataTable:一个表对应一个查询结果。
String sql=”select* from Person.AddressType;select * from Person.ContactType”;
SqlConnection conn=new SqlConnection(sql);
SqlCommand cmd=new SqlCommand(sql,conn);
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
adapter.Fill(ds);//填充数据
Console.WriteLine(ds.Talbe.Count);
DataTable dt1=new DataTable();
adapter.Fill(dt1);
dt1.TableName=”AddressType”;
ds.Talbe.Add(dt1);//添加一个DataTable
Console.WriteLine(ds.Table.Count):
（3）DataRow：表示表中包含的实际数据。DataRow及其属性和方法用于检索、计算和处理表中的数据。
DataRow dr=ds.Tables[0].Rows[0];//获得其中第一个表的第一个DataRow实例。
object obj=dr[0];//获得第一行第一列的数据
object obj1=dr[“Name”];//获得第一行Name列的数据。
//输出所有的DataRow
foreach(DataRow dr in ds.Talbes[0].Rows)
{
console.writeline(dr[“Name”]);
}
（4）DataColumn
DataColumn dc=ds.Tables[0].Columns[0];//获得其中第一个表的第一列的DataColumn实例
DataColumn dc=ds.Tables[0].Column[“Name”];//获得第一个表中Name列的DataColumn实例
//输出DataColumn的所有列名和数据类型
foreach(DataColumn dc in ds.Talbes[0].Columns)
{
console.writeline(dc.ColumnName+””+dc.DataType);
}
（5）DataView:
DataView能够创建DataTable中所存储的数据的不同视图，这种功能常用于数据绑定应用程序。使用DataView，可以使用不同排序顺序显示表中的数据，并且可以按行状态或基于刷选器表达式来刷选数据。DataView提供基础DataTalbe中数据的动态视图：内容、排序和成员关系会实时反映其更改。因此DataView的动态功能能使其成为绑定应用程序的理想选择。但是DataView不能作为表来对待，无法提供联接表的视图，以及不能排除存在于原表中的列，也不能追加不存在于原表中的列。
DataView dv=new DataView(ds.Talbes[0]);
dv.Sort=”Name”;//根据Name列进行排序
for(int i=0;i<dv.Count;i++)//循环读取DataView中的每一行
{
cosole.writeline(dv[i].Row[“AddressTypeID”]+””+dv.[i].Row[“Name”]);
}
//使用DataView分页
DataView dv=new DataView(ds.Tables[0]);
dv.RowFilter=”AddressTypeID<5 and AddressTypeID>2”;//设置过滤条件
for(int i=0;i<dv.Count;i++)//输出DataView中的每一行
{
console.Writeline(dv[i].Row[“AddressTypeID”]+””+dv[i].Row[“Name”]);//DataView中的每一行的内容
}
六、事务处理
1、事务的含义：
事务是合并成逻辑工作单位的操作组。用于控制和维护事务中每个操作的一致性和整体性，而不管系统中可能发生的错误。在一个事务中要么所有的数据操作全部成功，要么所有的数据操作全部失败，避免了部分操作成功部分操作失败而造成的不一致性。
2、使用SqlTransaction处理事务
使用SqlTransaction进行事务处理操作的主要步骤有：
（1）创建SqlTransaction实例：使用SqlConnection实例提供的BeginTransaction（）方法创建一个SqlTransaction实例。
（2）添加SqlTransaction实例：创建SqlCommand实例时将已创建的SqlTransaction实例添加到构造函数中。
（3）提交事务：所有的SqlCommand命令执行完成后，执行SqlTransaction实例的Commit()方法提交事务。
（4）若SqlCommand在执行中发生了异常，使用Try-Catch语句块捕捉异常并在Catch语句中执行SqlTransaction实例的Rollback()方法回滚整个事务。
如：
using(SqlConnection conn=new SqlConnection(sqlconn))
{
conn.Open();
SqlTransaction trans=conn.BeginTransaction();//创建事务
try
{
string sql1=”xxxxxxxxxx”;
SqlCommand cmd1=new SqlCommand(sql1 ,conn,trans);//添加事务
cmd1.ExecuteNonQuery();
string sql2=”xxxxxxxxx”’
SqlCommand cmd2=new SqlCommand(sql2,conn,trans);
cmd2.ExecuteNonQuery();
trans.Commit();//提交事务
console.writeline(“执行成功”);
}
catch(Exception ex)
{
trans.Rollback();//回滚事务
console.writeline(“执行失败”）；
}
}
3、使用TransactionScope处理分布式事务
TransactionScope类： 使代码块成为事务性代码。TransactionScope类通过隐式在分布式事务中登记连接，使代码块事务化。
必须在TransactionScope块的结尾调用Complete()方法，然后离开该代码块，并调用Dispose()方法。若引发的异常造成代码离开范围，将认为事务已经中止。
若TransactionScope中发生异常，事务将标记为不一致并被弃用，在TransactionScope断开后，事务将回滚。若事务未发生异常，则将 的事务提交。
TransactionScope并不像SqlTransaction一样面向某个数据库连接，在TransactionScope中可以使用不同的数据库连接操作分布式事务。若TransactionScope中使用的几个连接指向同一个数据库实例，则其中的事务处理和SqlTransaction相同。
try
{
using(TransactionScope trans=new TransactionScope ())//使用TransactionScope启动事务
{
using(SqlConnection conn=new SqlConnection (sqlconn))
{
conn.Open();
string sql =”xxxxxxx”;
SqlCommand cmd=new SqlCommand(sql,conn);
cmd.ExecuteNonQuery();
}
using(SqlConnection conn=new SqlConnection(sqlconn))
{
conn.Open();
string sql2=”xxxxxxx”;
SqlCommand cmd2=new SqlCommand(sql2,conn);
cmd2.ExecuteNonQuery();
}
trans.Complete();//提交事务
Console.WriteLine(“操作成功”);
}
}
catch（Exception ex）
{
console.writeline(ex.Message);
}
七、使用LINQ操作数据库
LINQ语言集成查询，引入了标准、易学的数据查询和更新模式，该技术可以扩展为几乎支持任何类型的数据存储。Visual Studio 包含 LINQ 提供程序的程序集，借助这些程序集，就能将 LINQ 用于 .NET Framework 集合、SQL Server 数据库、ADO.NET 数据集和 XML 文档、以及支持
IEnumeralbe或泛型IEnumerable<T>接口的任意对象集合。在数据库SQL Server 2008中使用LINQ可以实现程序中的对象与关系数据库的映射；能够自动生成SQL语句，完成数据库操作。
LINQ可以查询的数据可以是：XML（LINQ to XML）、Database（LINQ to SQL、LINQ to Dataset、LINQ to Entities）、和对象（LINQ
to Object）
LINQ to SQL 提供了用于将关系数据作为对象管理的运行时基础结构。在 LINQ to SQL 中，关系数据库的数据模型映射到用开发人员所用的编程语言表示的对象模型。 当应用程序运行时，LINQ to SQL 会将对象模型中的语言集成查询转换为 SQL，然后将它们发送到数据库进行执行。当数据库返回结果时，LINQ to SQL 会将它们转换回您可以用您自己的编程语言处理的对象。LINQ to SQL生成表示数据的.NET Framework类，这些生成的类直接映射到数据库表、视图、存储过程、和用户定义的函数，而不映射到概念数据模型。
(注：关系数据显示为由二维表（关系或平面文件）组成的集合，其中公共列将表互相关联起来。 若要有效地使用 LINQ to SQL，您必须略为熟悉关系数据库的基本原理)
