首页 > 代码库 > ADO.NET
ADO.NET
ADO.NET
什么是ado.net
微软为net程序通过编程操作各种数据库，提供的一套基于统一接口，但有不同实现的类库
程序员通过调用这个类库的相关类，就可以自己编程操作数据库
不仅仅可以操作mssqlsserver数据库，其他数据库都可以操作，oracle db2 access 等
System.Data.SqlClient
Select
Int num=comm.ExcuteNonQuery();
If(num==1)
…..
Else
………………..
ADO.NET组成
- 数据提供程序 用来查找数据 Connection Command DataReader DataAdapter
- 数据集 查找到数据后放到这里，然后传递 DataSet(DataTabel DataRow)
- 连接式的操作：connection commmand来执行增加删除和修改，以及返回结果集的第一个值
- 断开式的操作：通过数据适配器将数据读取到客户端的内存中
ADO.NET中的其他常见类
ConnectionStringBuilder//自动生成连接字符串
Parameter//带参数的SQL语句
Transaction//在ADO.NET中使用事务
与DataSet相关的类：
- *DataView//视图类，DataTable中的数据以不同的视角查看
- *DataRowView//DataView中的行。
- DataTable //DataSet中的数据表
- DataRow//DataTable中的行
- DataColumn//DataTable中的列
- *DataRealation//DataTable与DataTable的关系
- *Constraint//DataTable中建立的约束
第一个对象Connection连接SQLServer
//1.新建连接通道实例
SqlConnection conn = new SqlConnection();
//2. 告诉连接通道要连接到哪里
//server：数据库所在电脑的连接地址，可以是ip。，本机就用.+实例名，如果实例名就是"MSSQLSERVER"那么可以忽略
conn.ConnectionString = "server=.;database=HeiMaBlog;uid=sa;pwd=suncoder";
conn.StateChange += new StateChangeEventHandler(ConnStateChange);
//3.打开连接通道
conn.Open();
//4.State表示当前这个连接通道的状态，是一个枚举
MessageBox.Show(conn.State.ToString());
//5.关闭连接通道，这里只是关闭了，但是可以再打开,可以多次打开关闭
conn.Close();
conn.Open();
//不能重复打开，这里会报错
//conn.Open();
conn.Close();
//可以重复关闭
conn.Close();
//6.清楚连接通道其他占用的资源,上面仅仅是关闭了通道，通道本生还是存在的，所以要销毁，但是销毁了就不能在打开了
conn.Dispose();
conn.StateChange += new StateChangeEventHandler(ConnStateChange);
private void ConnStateChange(object sender, StateChangeEventArgs e)
{
Console.WriteLine("本来是"+e.OriginalState+"......现在变成了"+e.CurrentState);
}
ADO.NET连接池
因为，每次连接数据库都会创建connection对象，执行查询，然后注销用户，建立连接是非常耗时的，所以ado.net对connection对象做了特殊的优化，每一次new对象的时候，如果不是特殊指定连接字符串里面pooling为false，那么默认会启用连接池，在连接池中保存new出来的对象，并且以连接字符串做区分，当我们以相同的连接字符串创建第二个connection对象的时候，就会直接从连接池里面取出这个对象，而不是创建一个，
性能消耗差别很大
System.Diagnostics.Stopwatch wh1 = new System.Diagnostics.Stopwatch();
wh1.Start();
for (int i = 0; i < 10000; i++)
{
using (SqlConnection conn = new SqlConnection("server=.;…………;pooling=false"))
{ conn.Open(); }
}
wh1.Stop();
Console.WriteLine(wh1.ElapsedMilliseconds);
wh1.Restart();
for (int i = 0; i < 10000; i++)
{
using (SqlConnection conn = new SqlConnection("server=.;…………;pooling=true"))
{ conn.Open(); }
}
wh1.Stop();
Console.WriteLine(wh1.ElapsedMilliseconds);
如何手动清理连接池
SqlConnection conn = new SqlConnection("server=.;database=…………;pooling=true")
SqlConnection.ClearPool(conn);//清除与这个连接相关的池对象
SqlConnection.ClearAllPools();//清除所有对象
第二个对象Command
操作数据库的命令对象，
创建这个对象，并且告诉它，要走什么路去数据库(connection),
去数据库干什么(CommandText),
要回来得到什么结果(NonQuery, Scalar, Reader, DataSet)
Command命令对象不是真正执行命令的对象，而只是传递命令到服务器同时可以通过命令对象的方法接收从服务器返回的对应的值
ExecuteNonQuery()方法只能接收到命令语句中的受影响行数，如果有多句受影响行数据返回那么就得到累加值
ExecuteScalar()只能得到结果集的第一行第一列，如果有多个结果集那么也只能接收到第一个结果集的第一行第一列
ExecuteReader()可以返回一个只读只进的读取器对象：这个对象可以一次读取一行记录到数组中，通过索引器进行访问
下面会先介绍前三种方式的实际作用
ExecuteNonQuery
执行增删改语句，并且返回受影响行数
//1.打开一个连接通道，准备连接到数据库
SqlConnection conn = new SqlConnection("server=.;database=HeiMaBlog;uid=sa;pwd=suncoder;pooling=false"))
//2.准备一个执行命令的对象，准备根据命令操作数据库
SqlCommand cmd = new SqlCommand();
//3.指定连接通道
cmd.Connection = conn;
//4.指定要执行的命令
//cmd.CommandText = "insert into Score values(1,89,95)";
//cmd.CommandText = "insert into Score select SId, English, Math from score";
cmd.CommandText = "delete from score where id > 100";
//cmd.CommandText = "update score set english=english+1,math=math+1";
//5.打开连接通道(一般打开通道是在要正真执行查询之前打开，不要大开的太早)
cmd.Connection.Open();
//6.调用执行命令
MessageBox.Show(cmd.ExecuteNonQuery().ToString());
SqlCommand cmd = new SqlCommand("select count(*) from teacher", conn);
对象提供了可以简化代码的创建方式，直接在构造函数里面传入sql语句和连接对象即可
注：如果执行的是查询语句，那么返回的就是-1 因为没有影响数据表的任意一行
练习1：对班级表操作
- 1.增加一个班级
- 2.修改一个班级的名称
- 3.删除一个班级的信息。
写死即可
ExecuteScalar
返回查询结果集的第一行第一列的值，由于可能有很多种类型所以返回的object
using (SqlConnection conn = new SqlConnection("server=.;database=heimablog;uid=sa;pwd=suncoder"))
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from teacher", conn);
object obg = cmd.ExecuteScalar();
Console.WriteLine(obg);
输出：5 ，数据表里面一共只有5条数据
SqlConnection conn = new SqlConnection("server=.;database=heimablog;uid=sa;pwd=suncoder"))
conn.Open();
SqlCommand cmd = new SqlCommand("select * from teacher", conn);
object obg = cmd.ExecuteScalar();
Console.WriteLine(obg);
输出：1
查询的结果集第一行第一列
练习2：新增学员得到ID
添加学生：新增完后要获得id
添加班级窗体。返回最新添加的班级的自动编号Id
代码见：code\c02-1-练习1 c02-2-增删改学生
ExecuteReader
//1.创建连接通道对象
SqlConnection conn = new SqlConnection(connStr);
//2.创建命令对象
SqlCommand cmd = new SqlCommand("select * from teacher", conn);
//3.打开通道
conn.Open();
//4.调用命令对象创建读取器，读取器本生不能被new
SqlDataReader dr = cmd.ExecuteReader();
//5.循环调用读取器的read方法，让dr本生代表的数据改变，看图详解
while (dr.Read())
{
Console.WriteLine(dr[0] + "|" + dr[1] + "|" + dr[2] + "|" + dr[3]);
Console.WriteLine(dr["id"] + "|" + dr["name"] + "|" + dr["gender"] + "|" + dr["age"]);
}
//6.读取器需要单独关闭
dr.Close();
//关闭通道
conn.Close();
conn.Dispose();
datareader 取值可以用数字也可以用结果集的列名，由于结果集列名可以取别名，那么这里的代码中的列名就是别名
练习3：用户界面中进行登录判断。
新建窗体，用户输入账号密码判断登陆成功与否
异常处理
当连接数据库网络断了，sql语句写错了，这些错误都会影响程序的执行，那么我们需要保证程序的运行，不能崩溃，出错了要怎么处理
SqlConnection conn = null;
SqlDataReader dr = null;
try
{
//1.创建连接通道对象
conn = new SqlConnection(connStr);
//2.创建命令对象
SqlCommand cmd = new SqlCommand("select * from teacher", conn);
//3.打开通道
conn.Open();
//4.调用命令对象创建读取器，读取器本生不能被new
dr = cmd.ExecuteReader();
//5.循环调用读取器的read方法，让dr本生代表的数据改变，看图详解
while (dr.Read())
{
Console.WriteLine(dr[0] + "|" + dr[1] + "|" + dr[2] + "|" + dr[3]);
}
//6.读取器需要单独关闭
}
catch (SqlException ex) { Console.WriteLine(ex.Message); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
finally
{
if (dr != null) { dr.Close(); }
conn.Dispose();
Console.WriteLine("不管报不报错，我都要执行");
}
在try语句中任何一行代码发生错误，都会中断整个try语句的执行，后面的代码都不会被执行了，然后直接就转到catch语句，参数中包含了错误的描述信息
多个catch类型范围必须从小到大，如列子上，第一个catch只捕捉数据库相关的错误，第二个捕捉任何错误，如果发生一个错误，被第一个catch捕捉了，那么后面的所有catch都不会再捕捉执行了
finall语句中的代码，不管try语句块有没有发生异常都会被执行
SQL注入漏洞攻击
如果sql语句的字符串是：
”select * from users where uid =N‘” + input + “’ and pwd = N‘“ + input + “’”
如果用户输入账号：admin 密码admin ，则最终会形成字符串如下
”select * from users where uid =N‘admin’ and pwd = N‘admin‘
但是如果用户输入账号或者密码输入 1’ or ‘1’=’1 另外一个随便输入
则最终会形成：
”select * from users where uid =N‘admin’ and pwd = N‘1’ or ‘1’=’1 ‘
这样，这条语句执行后会返回表中所有行数据，如果只判断是否有行数的话没那么就悲剧了
查询参数
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from users where uname=@uname and pwd=@upwd";
//1.创建一个参数对象，构造函数给参数名字和值，参数的名字就是在查询字符串里面定义好的
SqlParameter sp = new SqlParameter("@uname", "admin");
SqlParameter sp1 = new SqlParameter("@upwd", "1‘ or ‘1‘=‘1");
//2.用命令对象的参数集合的add方法添加参数对象
cmd.Parameters.Add(sp);
cmd.Execute……();
在sql语句中用 @参数名 代替原来+号连接的地方，然后为每个参数都创建sqlparameter对象，并且在command对象执行前就添加到command对象的parameters集合中
使用了参数化查询后，数据库会直接把整个参数的值拿来比较，所以就算用户输入的字符有多特殊，都不会出现前面的注入情况了
案例
- V1.1：输错三次禁止登陆，15分钟后才能继续。用数据库记录ErrorTimes，最后出错时间lastErrorDateTime。to: code\c03-案例1
- 数据导入：从文本文件导入用户信息
- 数据导出：将用户信息导出到文本文件
案例(省市联动)
普通联动，无极
配置文件
通过在配置文件中添加连接字符串，可以不用重复写很多次
步骤：
- 为程序项目添加配置文件“app.config”
- 在配置文件的configuration节点下加入
<connectionStrings>
<add name="conStr" connectionString="server=.;……这里是连接字符串……;pwd=suncoder"/>
</connectionStrings>
- 项目添加对System.Configuration 程序集的引用
- 通过System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString 取得连接字符串
DataSet结构
DataSet是ado提供的一种离线的，本地的数据库机制，可以把从数据库查询到的结果集数据，全部一次性的转移到本地内存中，这样就算数据库断网了，也不会影响本地的程序的运行了
可放大
适配器
在程序中使用DataAdapter可以自动化的填充本地的数据集
SqlConnection conn = new SqlConnection("server=.;database=heimablog;uid=sa;pwd=suncoder");
string sql = "SELECT id, Name, Pid, Note FROM NotesClass";
adapter = new SqlDataAdapter(sql, conn);
ds = new DataSet();
adapter.Fill(ds,"tmptable");
在适配器内部，其实是自动化的生成了connection和command，然后自动打开数据连接，调用command返回datareader，再调用datareader读取数据之后，把数据写到本地DataSet数据对象
adapter内部具有sql操作的四大对象，InsertCommand，SelectCommand，UpdateCommand，DeleteCommand，这些对象其实就是SqlCommand对象，我们可以在调用fill方法之前操作这个对象达到添加参数的目的
adapter.SelectCommand.Parameters.AddWithValue("@id",id)
*简单dgv：dgvClass.DataSource = ds.Tables[0];
DataGridView控件可以快速的展示数据
*简单分页fill
在fill内部我们可以调用重载方法，传递参数做到分页的效果
adapter.Fill(ds, 2, 10, "tmptable");
第一个参数：要装数据的数据集
第二个参数：从第几条开始
第三个参数：从开始位置，取几条数据
第四个参数：填充之后表的名字
分页原理，缺点
1． 调用command对象查询服务器，得到需要的数据的结果集，这个结果集在数据库
2． 调用datareader到服务器读取结果集根据第二个和第三个参数取出想要的行
缺点，数据很多的时候在服务器缓存，会产生一个庞大的结果集
在本地也会有相同的一个结果集，
所以，不推荐使用这种分页方式，最好是在服务器端，只查询想要的数据就够了
SQLHelper
三大方法
ExecuteDataTable(string sql,params SqlParameter[] parameters)、
用来查询并返回想要的数据集
ExecuteNonQuery(string sql,params SqlParameter[] parameters)、
用来执行增删改语句
ExecuteScalar(string sql,params SqlParameter[] parameters)
用来执行有一个返回值的语句
代码实现：code\c05-数据集
目的，将重复性的代码封装到静态方法中，做成全局方法，方便调用和修改
ADO.NET