本文详细介绍了 ADO.NET 的核心概念与使用方法，包括连接数据库、执行 SQL 命令、使用数据适配器填充数据集、数据读取器的应用场景及性能对比等内容。
Ado.Net学习——基础知识记录
ADO.Net
一种数据库访问技术，应用程序可以连接到数据库，并以各种方式来操作其中的数据。一个COM组件库，.Net中访问数据，优先选择的数据访问接口。
ADO.Net封装在System.Data.dll中，System.Data命名空间，提供了不同的ADO.Net类
ADO与ADO.Net的区别
ADO使用的时oledb接口，基于COM技术访问数据。
ADO.Net拥有自己的接口，基于.Net体系架构访问数据。
ADO.Net组成
- DataSet：非连接的核心组件。独立于任何数据源的数据访问，可以适用于多种不同的数据源。
- Data Provider：数据提供程序。用于连接数据库、执行命令、检索结果。
- Sql Server数据提供程序：System.Data.SqlClient命名空间
- OLEDB数据提供程序：System.Data.Oledb命名空间
- ODBC数据提供程序：System.Data.Odbc命名空间
- Oracle数据提供程序：System.Data.OracleClient命名空间
- Connection：提供与数据源的连接。
- SqlConnection，适用于SQL Server数据库
- Command：用于执行数据库命令的对象
- SqlCommand：适用于SQL Server数据库
- DataReader：从数据源中提供快速的、只读的数据流
- SqlDataReader：适用于SQL Server数据库
- DataAdapter：提供DataSet对象与数据源的连接桥梁。
ADO.Net访问数据的步骤
连接到数据库—>打开连接—>创建执行命令对象（创建命令）—>执行命令—>关闭连接
SqlConnection
SqlConnection类继承于DbConnection类。DbConnection类是一个抽象基类，不能被实例化。提供与SQL Server数据库的连接。
常用属性
- Database：要连接的字符串的名称
- DataSource：要连接的数据库的实例名称（数据源：local或具体的IP地址）
- State：连接的状态。
- Closed：关闭
- Open：打开
- Connecting：正在连接
- Executing：正在执行命令
- Fetching：正在检索
- Broken：连接中断
- ConnectionTimeout：尝试连接的时间，默认15s
常用方法
sqlConnection.Open();//打开连接
sqlConnection.CreateCommand(); //创建一个与conn关联的SqlCommand对象
sqlConnection.Close();//关闭连接。连接字符串仍然存在，可以重新连接。
sqlConnection.Dispose();//释放连接。释放了连接字符串。要重新连接的话，需要重新建立连接字符串。
数据库连接字符串
是一组格式化的键值对：数据源、数据库名、访问信任级别、其它相关信息
格式：一组元素组成。各元素用 ; 隔开
SqlServer身份验证：Data Source=数据源;Initial Catalog=数据库名;User Id=账号;Password=密码
替代写法：server=数据源;database=数据库名;uid=账号;pwd=密码
Windows身份验证：Data Source=数据源;Initial Catalog=数据库名;Integrated Security=True/SSPI
Data Source=数据源;Initial Catalog=数据库名;Trusted_Connection=True
构建连接字符串的类
SqlConnectionStringBuilder。继承自DbConnectionStringBuilder类。
SqlConnection sqlConnection = new SqlConnection();
//SqlConnectionStringBuilder生成字符串
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder();
connStrBuilder.DataSource = ".";
connStrBuilder.InitialCatalog = "Test";
connStrBuilder.UserID = "sa";
connStrBuilder.Password = "123456";
connStrBuilder.Pooling = false; //禁用链接池
sqlConnection.ConnectionString = connStrBuilder.ConnectionString;
配置文件中配置连接字符串
App.config
<connectionStrings>
<add name="connStr" connectionString="Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
<appSettings>
<add key="connStr" value="Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI"/>
</appSettings>
C#
//1、创建连接
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = "Date Source=.;Initial
//读取ConnectionStrings节点：推荐使用
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//读取AppSettings
connStr = ConfigurationManager.AppSettings["connStr"].ToString();
sqlConnection.ConnectionString = connStr;
//2、打开连接
sqlConnection.Open();//打开连接
//3、创建执行命令的对象
sqlConnection.CreateCommand(); //创建一个与conn关联的SqlCommand对象
//4、执行命令
//5、关闭连接
sqlConnection.Close();//关闭连接。连接字符串仍然存在，可以重新连接。
sqlConnection.Dispose();//释放连接。释放了连接字符串。要重新连接的话，需要重新建立连接字符串。
两种方法进行数据库连接
-
使用try…catch…
#region 第一种方法连接数据库 SqlConnection connection = null; try { string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //创建连接 //第一种 //SqlConnection conn = new SqlConnection(); //conn.ConnectionString = connStr; //第二种：推荐使用 //SqlConnection connection = new SqlConnection(connStr); connection = new SqlConnection(connStr); connection.Open(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { connection.Close(); connection.Dispose(); } #endregion -
使用using{}
#region 第二种方法连接数据库 string conneciontStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; SqlConnection sqlConnection = new SqlConnection(conneciontStr); //using语句块之外，SqlConnection对象会自动释放。 //使用using进行释放的对象，它必须继承于IDisposable接口 //using语句块相当于一个 try--finally 语句块的操作，没有catch捕获处理异常 using (sqlConnection = new SqlConnection(conneciontStr)) { sqlConnection.Open(); Console.WriteLine($"State:{sqlConnection.State}"); } Console.WriteLine($"State:{sqlConnection.State}"); #endregion
数据库连接池
什么是连接池？
- 可以看作是一个容器，存放了一定数量的与数据库服务器的物理连接。当需要使用时，从数据库中取出一条空闲的连接，而不是创建新的连接，从而实现对数据库连接的重复利用，达到优化的效果。
- ADO.Net默认是启用连接池的。
- 连接字符串是可以控制连接池的行为的。
- 完全相同的连接字符串才会共用同一个连接池。
作用：
- 减少连接数据库的开销，提高应用程序性能。
分类：
- 同一时刻同一应用程序可以有多个不同类型的连接池。通过进程、应用程序域、连接字符串、windows标识共同组成的签名来标识区分
- 同一程序中，由连接字符串来区分。打开一条连接，如果这条连接的类型签名与现有连接不匹配，就会创建新的连接池；反之则不会创建，共用一个连接池。
如何分配：
- 根据连接请求类型，找到与它相匹配的连接池，尽力分配一个空闲的连接。
- 有空闲的连接，则返回这条连接。
- 无空闲连接，创建一个新的连接添加到连接池中。
- 连接池中连接数量已达到最大值，则等待，直到有空闲连接可用。
移除无效连接
- 不能正确连接到数据库服务器的连接时无效连接。
- 连接池存储的数据库服务器的连接数量时有限的，无效连接应当移除。
- 连接池管理器会处理无效连接的问题。不需要我们考虑。
回收连接
- 数据库连接关闭并释放后，变为空闲状态，重新回到连接池供使用。
连接池的三个属性
-
Max Pool Size：最大连接数 100
-
Min Pool Size：最大连接数 0
-
Pooling：是否启用连接池（true/false）。可用来禁止启用连接池。
-
启用连接池可以大大增加数据库连接的效率。
string connStrPool = "Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI;Max Pool Size=5"; //string connStrPool = "Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI;Max Pool Size=5;Pooling=false"; //禁止启用连接池，此时Max Pool Size=5无效。 for (int i = 0; i < 10; i++) { SqlConnection sqlConnectionPool = new SqlConnection(connStrPool); sqlConnectionPool.Open(); Console.WriteLine($"第{i}个连接已经打开"); }//只能打开5个连接。说明ADO.Net是默认启用连接池的。 string connStrPool02 = "Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI;Pooling=false"; //禁止启用连接池，此时Max Pool Size=5无效。 Stopwatch sw = new Stopwatch(); sw.Start(); for (int i = 0; i < 100; i++) { SqlConnection sqlConnectionPool = new SqlConnection(connStrPool02); sqlConnectionPool.Open(); sqlConnectionPool.Close(); } sw.Stop(); Console.WriteLine($"不启用连接池，耗时{sw.ElapsedMilliseconds} ms!");
SqlCommand
SqlCommand：Ado.Net执行数据库命令的对象。
重要属性
- Connection：SqlCommand对象使用的SqlConnection
- CommandText：获取或设置要执行的T-SQL语句或存储过程名
- CommandType：枚举值
- CommandType.Text：执行的是一个Sql语句（可以省略）
- CommandType.StoredProcedure：执行的是一个存储过程（必须设置）
- Parameters：SqlCommand对象的命令参数集合。默认为空集合。
- Transaction：获取或设置要在其中执行的事务。
ExecuteNonQuery
执行T-SQL语句，并返回受影响的行数。
命令类型：插入、更新、删除。DML。
执行条件：连接对象（conn）必须是Open状态
ExecuteScalar
执行查询语句或存储过程，返回查询结果集中的第一行第一列的值，忽略其他行或列。
返回值为Object类型。
适用：作查询，并仅返回一个值作为结果。如，统计数据量；插入数据后，想返回自动生成的标识的列的值。
命令类型：查询类型。DQL（数据查询语句）
ExecuteReader
执行查询语句，返回一个对象（SqlDataReader）。只读。
SqlDataReader：实时读取，读取方式固定，不灵活（只进不出，只能前进，不能后退）。
适用：只读，不做数据修改的情况下；数据量小。
【注意】
-
使用ExecuteReader读取数据的时候，要即时保存。因为会读一条，丢一条。
conn.Open(); SqlDataReader dr = sqlCommand.ExecuteReader(); //ExecuteReader读取数据的时候，要即时保存。因为会读一条，丢一条。 while (dr.Read()) //是否可以读取下一条数据 { //保存数据操作 int userId = int.Parse(dr["UserId"].ToString()); } dr.Close(); conn.Close(); -
设置DataReader与Connection对象关联关闭：如下代码，当关闭dr或conn其中一个对象，二者同时关闭。
conn.Open(); SqlDataReader dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { //保存数据操作 int userId = int.Parse(dr["UserId"].ToString()); } //dr.Close(); conn.Close();
SqlParameter
表示SqlCommand对象的参数，或与DataSet中列的映射。
常用属性：
- DbType：表示参数的数据类型（数据库中的类型）
- Direction：参数的类型（输入、输出、输入输出、返回值参数）
- ParameterName：参数的名称
- Size：参数最大大小，以字节为单位
- Value：参数值
- SqlValue：作为Sql类型的参数的值
//参数的构造方法
//1、参数
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@userName";
sqlParameter.SqlDbType = SqlDbType.VarChar;
sqlParameter.Value = "admin";
sqlParameter.Size = 20;
//2、参数名，值
SqlParameter sqlParameter2 = new SqlParameter("@Age", 24);
//3、参数名，SqlDbType
SqlParameter sqlParameter3 = new SqlParameter("@DeptID", SqlDbType.Int);
//4、参数名，类型
SqlParameter sqlParameter4 = new SqlParameter("@UserPwd", SqlDbType.NVarChar, 50);
//5、参数名，类型，大小，源列名（对应DataTable中的列名）
SqlParameter sqlParameter5 = new SqlParameter("@UserPwd", SqlDbType.NVarChar, 50, "UName");
SqlCommand中添加参数
添加参数的方法
- command.Parameters.Add();
- command.Parameters.AddWithValue();
- command.Parameters.AddRange();
通过SqlParameter参数传递，字符转义不需要考虑。如下"ling’Ping"可以正常插入到数据库中
using (sqlConnection = new SqlConnection(conneciontStr))
{
sqlConnection.Open();
string sql = "select * from UserInfors where UserName = @UserName";
SqlCommand command = new SqlCommand(sql, sqlConnection);
//通过SqlParameter参数传递，字符转义不需要考虑。如下"ling'Ping"可以正常插入到数据库中
//单个参数
//1、
command.Parameters.Add(new SqlParameter("@UserName", "ling'Ping"));
//2、
command.Parameters.AddWithValue("@UserName", "ling'Ping");
//3、
SqlParameter sqlParameter1 = new SqlParameter("@UserName", "ling'Ping");
command.Parameters.Add(sqlParameter1);
//多个参数
SqlParameter[] paras = {
new SqlParameter("@UserName", "ling'Ping")
};
command.Parameters.AddRange(paras);
}
参数的使用
输入参数
- 参数化SQL语句或存储过程，默认使用的参数
以下三种一般使用在存储过程里
输出参数
-
存储过程中用output标记。程序中可以接收到存储过程中返回的值，而不需要return操作（存储过程中）。
-
paraName.Direction = ParameterDirection.Output;
using (sqlConnection = new SqlConnection(conneciontStr)) { SqlCommand command = new SqlCommand("GetDeptName", sqlConnection);//GetDeptName为存储过程 //输出参数是不传入值的，它只是输出 SqlParameter paraName = new SqlParameter("@DeptName", SqlDbType.NVarChar, 50); paraName.Direction = ParameterDirection.Output; command.Parameters.Add(paraName); sqlConnection.Open(); command.ExecuteScalar(); sqlConnection.Close(); Console.WriteLine(paraName.Value.ToString()); }
输入输出参数
-
存储过程中用output标记。需要传入值，页输出值。不需要return操作（存储过程中）。
-
paraName.Direction = ParameterDirection.InputOutput;
using (sqlConnection = new SqlConnection(conneciontStr)) { SqlCommand command = new SqlCommand("GetDeptName", sqlConnection);//GetDeptName为存储过程 //输入输出参数，是一个双向参数。既传入值，也输出值 SqlParameter paraName = new SqlParameter("@DeptName", SqlDbType.NVarChar, 50); paraName.Value = "部门"; paraName.Direction = ParameterDirection.InputOutput; command.Parameters.Add(paraName); sqlConnection.Open(); command.ExecuteScalar(); sqlConnection.Close(); Console.WriteLine(paraName.Value.ToString()); }
返回值参数
-
存储过程中使用return进行返回值。
-
return值可以返回int类型的数值，若需要返回其它类型参数，请使用”输出参数“返回。
-
paras[1].Direction = ParameterDirection.ReturnValue;//返回值参数
using (sqlConnection = new SqlConnection(conneciontStr)) { SqlCommand command = new SqlCommand("GetUserAge", sqlConnection);//GetUserAge为存储过程 //返回值参数 SqlParameter paraId = new SqlParameter("@UserId", 31); command.Parameters.Add(paraId); SqlParameter[] paras = { new SqlParameter("@UserID", 31), new SqlParameter("@UserValue", SqlDbType.Int, 4) }; paras[1].Direction = ParameterDirection.ReturnValue;//返回值参数 command.Parameters.AddRange(paras); sqlConnection.Open(); command.ExecuteScalar(); sqlConnection.Close(); }
SqlDataReader
提供一种从SQL Server数据库中读取只进的行流的方式。
特点
- 快速的、轻量级、只读的
- 遍历访问每一行数据的数据流。只能单方向逐行遍历读取，不能修改。
缺点
- 不灵活，只适合数据小的情况
- 读取数据时，一直占用连接。
读取方式
- 调用Read()方法逐行读取，若有数据读取，则返回true，并再次调用Read()方法；否则返回false。
常用属性
-
Connection：获取与Reader对象相关的SqlConnection
-
FiedCount：当前行中的列数
-
HasRows：reader是否包含一行或多行
-
IsClosed：read对象是否已关闭（true/false）
-
Item[int]：给定列序号的情况，获取指定的值。如dr[1]获取第2列的值，返回Object。
-
Item[Sting]：给定列名的情况，获取指定的值。
常用方法
- Close()：关闭reader
- GetInt32(列序号)：根据数据类型相匹配的方法
- GetDataType(列序号)：获取数据类型的Type
- GetName(列序号)：获取指定列的列名
- GetOrdinal(列名)：获取指定列的列序号
- Read()：是reader对象前进到下一条记录
- NextResult()：是reader前进到下一个结果
注意
- 连接对象必须一致保持Open状态
- 使用完成后，必须立刻关闭，不然会一直占用连接。
- 创建方式：不能直接构造，通过cmd.ExecuteReader()来创建。cmd.ExecuteReader(CommandBehaiour.CloseConnection)可以关联reader和command同时关闭。
- 读取时尽量使用与数据库字段类型相匹配的方法来取得对应的值，会减少因类型不一致而增加类型转换操作性能损耗。
- 没有读取到末尾时，就要关闭reader对象时，先调用cmd.Cancel()方法，然后调用reader.Close()方法。
- cmd.ExecuteReader()获取存储过程的返回值或输出参数，先调用reader.Close()方法，再获取参数的值。
string conneciontStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(conneciontStr);
using (sqlConnection = new SqlConnection(conneciontStr))
{
string sql = "select * from UserInfors";
SqlCommand command = new SqlCommand(sql, sqlConnection);
sqlConnection.Open(); //必须再读取之前
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
//DataTable dataTable = new DataTable();
//dataTable.Load(dr); //将dr对象加载到dataTable中
if (dr.HasRows)
{
int indexId = dr.GetOrdinal("UserId"); //获取指定列名的列序号
string idName = dr.GetName(0); //获取指定列序号的列名
//读取过程中要读一行，存一行。因为SqlDataReader是读一行，丢一行
//可以保存为List集合
List<UserInfoModel> list = new List<UserInfoModel>();
while (dr.Read()) //检测是否有数据
{
int userId = (int)dr[0]; //通过列序号读取
userId = dr.GetInt32(indexId); //int类型。不用再进行拆箱操作
string userName = dr["UserName"].ToString(); //通过列名读取
UserInfoModel model = new UserInfoModel();
model.userId = dr.GetInt32(indexId); //int类型。不用再进行拆箱操作
model.userName = dr["UserName"].ToString(); //通过列名读取
list.Add(model);
}
//dr.NextReader(); //当查询出多个结果集时，可以用此方法进入下一个结果集处理
}
dr.Close();
}
DataTable
DataSet：Ado.Net的核心组件。数据在内存中的缓存的一个数据库。
DataTable：数据缓存在内存中的一个表。
应用
-
DataTable常作为DataSet中的成员进行使用，也可以独立创建和使用。
-
结合适配器DataAdapter使用
架构
- 通过列和约束来表示。（DataColumn）
成员
- 成员即为数据行（DataRow）
构造函数
- DataTable()
- DataTable(表名)
常用属性
- Columns：列集合
- Rows：行集合
- Contraints：约束的集合
- DataSet：DataTable所属的DataSet集合
- PrimaryKey：主键。列的数组。
- TableName
方法
- AcceptChanges()：提交更改
- RejectChanges()：回滚；取消更改
- Clear()：清空数据
- Copy()：复制架构和数据
- Clone()：值复制架构，不包含数据
- Load(IDataReader)：通过提供的IDataReader，用某个数据源的值来填充DataTable
- Merge(DataTable)：合并。将指定的DataTable合并到当前的DataTable
- NewRow()：创建一个DataRow，与DataTable具有相同的架构
- Reset()：将DataTable重置到最初状态
- Select()：没带参数，获取DataTable所有行的数组。也可以设置条件、排序。
//独立创建和使用
DataTable dt = new DataTable("UserInfo"); //表是空的，没有架构/列/约束/主键等
//添加列
DataColumn dc = new DataColumn();
dc.ColumnName = "UserID";
dc.DataType = typeof(int); //Type.GetType("System.Int32");
dt.Columns.Add(dc);
dt.Columns.Add("UserName", typeof(string)); //添加一列（推荐）
dt.PrimaryKey = new DataColumn[] { dt.Columns[0] }; //设置主键
dt.Constraints.Add(new UniqueConstraint(dt.Columns[1])); //添加唯一约束
//架构定义好了，添加数据
DataRow dr = dt.NewRow(); //创建的DataRow与DataTale具有相同的架构
dr[0] = 1;
dr["UserName"] = "admin";
//DataRow的DataState状态
dt.Rows.Add(dr); //添加了数据，dr为Added状态
dt.AcceptChanges(); //提交更改 dr为UnChange状态
dr["UserName"] = "ww"; //修改 dr为Modified状态 已修改
dt.AcceptChanges(); //提交更改 dr为UnChange状态
//dr.Delete(); //dr为Deleted状态
//dt.AcceptChanges(); //dr为Detached状态，表示孤立的，不属于任何
dt.Rows.Remove(dr); //dr为Detached状态。Remove相当于Delete + AcceptChanges
//dt.Merge(dt1); //合并相同结构的数据。合并时，数据完成相同时会自动去重。
DataRow[] rows = dt.Select(); //获取所有的行
DataRow[] rows01 = dt.Select("UserID>1", "UserID desc"); //按条件筛选排序
DataSet
Ado.Net的核心组件。数据在内存中的缓存，即内存中的一个数据库。数据库中的数据加载到内存中处理。
成员
- 由一组DataTable组成，DataRelation相互关联，一起实施了数据的完整性
应用
- 结合DataAdapter使用，DataAdapter将数据填充到DataSet中
- DataAdapter将DataSet中的数据更改提交到数据库
- XML文档或文本加载到DataSet中
作用
- 将数据加载到内存中执行，提高了数据访问的速度，提高硬盘数据的安全性
特性
- 独立性：数据库中的数据加载到内存中处理，不依赖于任何数据源
- 支持离线和连接
- 数据操作灵活
创建
- DataSet()
- DataSet(名称)
常用属性
- DataSetName：DataSet的名称
- Tables ：DataSet中DataTable集合
- Relations：DataSet中的关系集合
方法
- ds.AcceptChanges()：提交
- ds.RejectChanges()：回滚
- ds.Clear()：清除所有表中的所有数据
- ds.Copy()：克隆表结构和数据
- ds.Clone()：克隆架构，不包含数据
- ds.Merge(rows/DataTable/DataSet)：合并
- ds.Reset()：重置。返回未初始化的状态。
- ds.Load(IDataReader)：将数据源的值填充到当前ds中
DataRelation
DataSet本质是一个数据库，DataTable本质可以看作DataSet中的表。故可以正常进行数据库的操作。
默认情况下：建立关系，就自动为父表中的列建立唯一约束，子表中外键列建立外键约束。可以通过关系，相互获取子父表数据
DataRelation dataRelation = new DataRelation("dataRelation", dt2.Columns[0], dt1.Columns[3], true);
ds.Relations.Add(dataRelation);
InitData(dt1, dt2); //此处为自定义的另外一个方法，用于为两个表添加数据
//使用关系
//通过父表读取子表中的数据
foreach (DataRow dr in dt2.Rows)
{
DataRow[] rows = dr.GetChildRows(dataRelation);//通过父表读取子表中的数据
foreach (DataRow row in rows)
{
Console.WriteLine($"UserID:{row[0].ToString()},UserName:{row[1].ToString()}")；
}
}
//子表读取父表的数据
DataRow[] rows = dt1.Rows[0].GetParentRow(dataRelation);
Console.WriteLine($"DeptId:{rows[0].ToString()},DeptName:{rows[1].ToString()}");
SqlDataAdapter
适配器（桥接器）：DataSet与数据之间用于检索和保存数据的桥梁。
SqlDataAdapter类：填充DataSet以及更新数据源的一组数据库命令和一个数据连接。
SqlDataAdapter：DataSet和SQL Server之间的桥接器
如何进行桥接
- Fill()：将执行T-SQL命令检索的数据填充到DataSet中
- Update()：将DataSet中更改的数据提交到数据库，从而使数据库保持一致。
- 通常与SqlConnection、SqlCommand一起使用，提高访问速度
4个重要属性
- SelectCommand：设置查询命令，生成SqlCommand对象。
- InsertCommand：插入记录，生成SqlCommand对象。
- UpdateCommand：更新数据库，生成SqlCommand对象。
- DeleteCommand：删除记录，生成SqlCommand对象。
SqlDataAdapter对数据的操作也是建立在SqlCommand基础之上。
string connStr = "Date Source=.;Initial Catalog=Test;Integrated Security=True/SSPI;Max Pool Size=5";
SqlConnection sqlConnection = new SqlConnection(connStr);
string sql = "select * from UserInfo";
//1、设置SelectCommand
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sql, sqlConnection);
//2、通过一个SqlCommand来实例化一个SqlDataAdapter
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
//3、通过查询语句和连接对象实例化一个SqlDataAdapter
SqlDataAdapter sqlData = new SqlDataAdapter(sql, sqlConnection);
//4、通过查询语句和连接字符串构建SqlDataAdapter
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(sql, connStr);
以上方法中，若使用不带参数的T-SQL语句，推荐使用第三种；若SQL语句带参数，需要操作SqlCommand，推荐使用第二种或第一种。
填充数据
-
直接使用Fill填充数据，在多个结果集时，表名会自动设置为为Table,Table1,Table2…
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection); DataSet ds = new DataSet(); sqlDataAdapter.Fill(ds);//填充数据 //多个结果集，则表名会自动设置为为Table,Table1,Table2...... -
使用TableMappings设置DataSet与DataTable的表名映射
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection); DataSet ds = new DataSet(); //表名映射要在填充之前设置 sqlDataAdapter.TableMappings.Add("Table","Users"); sqlDataAdapter.TableMappings.Add("Table1","Dept"); sqlDataAdapter.Fill(ds);//填充数据 //单个结果集可以如下设置 //sqlDataAdapter.Fill(ds, "Users");
【说明】
- 以上例子均为填充DataSet数据。同样，SqlDataAdapter也可以填充DataTable数据。
- 断开式：使用Fill填充数据，没有显式地实现数据库的连接。数据库的连接由SqlDataAdapter自动通过sqlConnection完成。在Fill前后sqlConnection均为Closed状态。上述代码均为断开式。
- 连接式：在Fill前后，自主调用Open()和Close()控制数据库连接状态
- 区别：在填充时，连接式速度比断开式更快。
- 尽量使用连接式。
更新数据
更新方式：Update(dataSet/dataTable/Rows)。
RowState：Unchaged/Added/Modified/Deleted
- Unchaged：未更改的
- Modified：已修改的——da.UpdateCommand
- Added：已添加的——da.InsertCommand
- Deleted：已删除的的——da.DeleteCommand
配置对应的命令属性的方式
- SqlCommandBuilder：自动为SqlDataAdapter配置相关的命令。
- 手动配置SqlCommand
DataAdapter与DataReader对比
相同点
- 通过SqlCommand执行查询，将数据读取出来
不同点
- SqlDataReader提供一种从数据库中读取数据的流的方式
- SqlDataAdapter是DataSet与数据库之间的一个桥接器（Fill()/Update()）
性能对比
| SqlDataReader | SqlDataAdapter | |
|---|---|---|
| 速度 | 快 | 慢 |
| 数据量 | 适用数据量小 | 适用数据量大 |
| 占用内存 | 小 | 大 |
| 连接 | 一直占用 | 断开与连接 |
| 读取方式 | 从头到尾/逐条读取/读一条丢一条（即使存储）/不灵活/只读，不能修改 | 所有一次性加载到内存中/任意读取/灵活/可读可写 |
- SqlDataReader：不要求随意读取，不修改，数据量小的情况下选用。速度快，占用内存小。
- SqlDataAdapter：要求随意读取，可以修改，数据量大的情况下选用。灵活性大，占用内存大。
ADO.Net调用数据库事务
简单调用数据库事务
MSSQL数据库事务代码的写法
begin tran
begin try
--这里写逻辑
commit tran
end try
begin catch
rollback tran
end catch
C#程序如何调用数据库事务：将事务放在存储过程里，C#程序调用存储过程
create proc AddUserByTran
@UserName varhcar(50),
@UserPwd varhcar(50)
as
begin
begin tran
begin try
--这里写逻辑
commit tran --提交
return 1;
end try
begin catch
rollback tran --回滚
end catch
end
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
//调用存储过程
SqlCommand command = new SqlCommand("AddUserByTran", conn); //AddUserByTran是数据库中存储过程的名称
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Clear();
SqlParameter[] paras =
{
new SqlParameter("@UserName","Wangwei"),
new SqlParameter("@UserPwd", "123456"),
new SqlParameter("@reValue", SqlDbType.Int, 2)
};
command.Parameters.AddRange(paras);
//执行
conn.Open();
command.ExecuteNonQuery();
int state = int.Parse(paras[2].Value.ToString());
if (state == 1)
{
Console.WriteLine("Success");
}
else
{
Console.WriteLine("fail");
}
}
上述方法仅适用于调用一个普通的数据库事务存储过程，但是当需要批量导入数据时，涉及上千参数时，则不具备可行性。此时可以使用Ado.Net中的SqlTransaction开启事务，通过C#代码中的循环操作，传入不同的参数值实现。
使用SqlTransaction实现
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction(); //开启一个事务
SqlCommand command = conn.CreateCommand();
command.Transaction = tran; //设定要执行的事务
//定义要执行的操作
command.CommandText = "insert into User(UserName) values(@UserName)";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@UserName", "wangwei"));
command.ExecuteNonQuery();
command.Parameters.Clear(); //每一步操作之后即使清空参数集合
//下一步操作
//。。。。。。
tran.Commit(); //事务提交
}
catch (Exception ex)
{
tran.Rollback(); //事务回滚
}
finally
{
tran.Dispose();
conn.Close();
}
}
DbHelper类（封装的数据库操作通用类）
作用：代码重复利用，提高效率，不再考虑交互过程，逻辑很清晰
内容规划：
- 连接钥匙：连接字符串→建立连接→连接释放（using()）
- SqlCommand三种执行方法
- 增删改：ExecuteNoQuery()，执行T-SQL，返回受影响的行数
- 查询，返回一个值：ExecuteScalar()，执行查询，返回结果集的第一行第一列(object类型的值)，忽略其它行其它列
- 读取数据，且不做修改：ExecuteReader()，执行查询，生成SqlDataReader
- SqlDataAdapter填充DataSet或DataTable结果集
- 处理事务使用SqlTransaction
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace AdoNetCourse
{
public class DbHelper
{
//连接字符串
private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
SqlConnection conn = null;
/// <summary>
/// 执行T-SQL，返回受影响的行数。适用于增删改。
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType">表示CommandType，2为存储过程</param>
/// <param name="paras">sql需要的参数列表，必须在函数形参的最后一个，其后不可再接形参。传入实参可以是一个参数数组，也可以是多个参数用逗号隔开，具体情况因形参的定义类型决定</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, int cmdType, params SqlParameter[] paras)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
return count;
}
/// <summary>
/// 执行查询，返回结果集的第一行第一列(object类型的值)
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras)
{
object o = null;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
o = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
}
return o;
}
public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras)
{
SqlDataReader dr = null;
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
try
{
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //关联释放。ExecuteReader会持续连接，使用时不可使用using进行释放，故采用try-catch。
cmd.Parameters.Clear();
}
catch (Exception ex)
{
conn.Close();
throw new Exception("执行查询异常", ex); //抛出异常给上层捕获
}
finally
{
//此处不可以使用finally语句块进行释放。因为如此在其它地方调用ExecuteReader生成的SqlDataReader就无法继续使用数据
//因为连接对象在执行玩方法后默认释放了
//conn.Close();
}
return dr;
}
/// <summary>
/// 填充DataSet。一个或多个结果集使用。
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(connStr))
{
//SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(ds);
conn.Close();
}
return ds;
}
/// <summary>
/// 填充DataTable。一个结果集使用。
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] paras)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
//SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(dt);
conn.Close();
}
return dt;
}
/// <summary>
/// 事务操作：执行一系列的Sql语句
/// </summary>
/// <param name="listSql"></param>
/// <returns></returns>
public static bool ExecuteTrans(List<string> listSql)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
for (int i = 0; i < listSql.Count; i++)
{
cmd.CommandText = listSql[i];
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (SqlException ex)
{
trans.Rollback();
throw new Exception("事务除左", ex);
}
conn.Close();
}
return false;
}
}
}
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
