如鹏网学习笔记（六）ADO.Net基础
ADO.Net基础
一、ADO.Net简介
1，程序要通过SQL语句自动化的操作数据库，必须要用一个类库，
类库要提供execute("insert into ...")/executeQuery("select * from ...")类似的方法
2，ADO.Net是.Net中提供的标准访问数据库的接口，访问不同的DBMS的底层方法是不一样的，ADO.Net把访问数据库的方法进行了统一，
访问MYSql、Oracle、SqlServer等不同数据库的方法几乎是一样的
3，ADO.Net是规范，被不同的数据库厂商提供ADO.Net的实现，称之为ADO.Net驱动，每个厂商提供的驱动可以用来操作自己的数据库
二、ADO.Net连接MYSQL
1，安装MySql的.Net驱动mysql-connector-net-***.msi添加到项目的库中。
如果安装遇到问题，则直接下载mysqlnetconnection（V4.5）.zip。
2，新建项目，添加引用——“扩展”，添加Mysql.Data;如果是直接解压版，然后直接添加对MySql.Data.dll的文件的引用
static void Main(string[] args)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "insert into t_fuxi ";
int count = cmd.ExecuteNonQuery();
Console.WriteLine(count+"受到影响");
Console.ReadKey();
}
}
代码解释：
1，MySQLConnection、MySQLCommend实现了IDisposable接口，因此使用using进行资源回收
2，"Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"叫连接字符串，
Server是Mysql服务器的地址，Database是连接的数据库，uid、pwd是用户名和密码，采用utf8编码
3，conn.Open();在执行MySqlCommand之前一定要先打开数据库连接，否则会报错
4，ExecuteNoQuery是执行Update、Insert、Delete等非查询语句，返回值为受影响的行数
3，ExecuteScalar
执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他行列。一般用来简单的获得自由一行一列的查询结果的值
案例1
cmd.CommandText = "Select count(*) from T_Users";
long count = (long)cmd.ExecuteScalar();
案例2
cmd.CommandText = "Select Password from T_Users where UserName = 'admin'";
string pwd = (string)cmd.ExecuteScalar();
if(string.isNullOrEmpty(pwd))
{
Console.WriteLine("找不到admin");
}
else
{
Console.WriteLine("admin的密码："+pwd);
}
4，执行查询 MySqlDataReader
cmd.CommandText = "select * from T_Users";
using(MySqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
long id = reader.GetInt64("Id");
string userName = reader.GetString("UserName");
string passWord = reader.GetString("Password");
Console.WriteLine("id="+id+";UserName="+userName+";Password="+passWord);
}
}
注意：
Reader的遍历、读取时需要Connection保持连接，如果关闭了Connection，使用会出错
也可以根据列序号获取列的值，效率略高，不过程序不容易读；通过reeder.GetOrdinal("Age")获取列名对应的序列号
三、SQL注入漏洞
1，是由于Sql语句的拼接造成的，
在程序接收用户的输入时，需要考虑用户输入的内容对数据库操作产生的影响，
2，对应的解决方法，参数化查询
cmd.CommandText = "select count(*) from TUsers where username=@username and password=@password";
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@UserName", Value = username });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Password", Value = password });
为什么这样可以避免“SQL注入漏洞”，
因此使用参数化查询，就可以对用户输入的内容进行判断和处理了，本质上是参数赋值
注意：
1，所有SQL中都可以使用参数化查询传递；表名、字段名等不能使用参数化进行替换
2，不要用SqlParameter(string parameterName,object value)这个构造函数，
因为("Age",0)会被匹配成Sqlparameter(string parameterName,SqlDbType dbType)这个构造函数
四、基本数据类型为空的问题
1，把T_Persons表的Name、Age列修改为“允许为空”，插入一条Name、Age为空的行
执行后发现，对于空数据reader.Get**方法会抛异常SqlNullValueException，
相当于问数据库“当前行的Name”是什么，数据库告诉你“不知道”。
怎么解决？
使用ISDBNull获取指定序号的列的值是否为null
int? age = null;
if(!reader.IsDBNull(reader.GetOrdinal("Age")))
{
age = reader.GetInt32("Age");
}
五、离线结果集DataSet
DATAReader是服务器结果集游标的体现，所有查询出来的数据都在MySql服务器上。
好处是：当查询结果数据量打的时候避免占用本地内存。
不过大部分项目中都会避免大查询结果，因此缺点就明显了
读取的时候必须保持Connection，不仅用起来麻烦，而且会较长时间占用MySql服务器的连接资源
DataSet是一个离线结果集容器，它把结果数据放到本地内存中。
因为查询结果可能会包含多个表，因此DataSet包含若干DataTable（ds.Tables），
DataTable包含若干DataRow(dt.Rows)
用法1：
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(ds);
DataTable table = ds.Tables[0];
六、DataTable
DataSet可以盛放多个查询结果集到DataTable
DataAdapter还可以对结果进行傻瓜化更新、删除、修改。
我们一般查询结果集就一个DataTable，DataAdapter的傻瓜化更新不适合正式的项目，因此有更简单的用法
DataTable dt = new DataTable();
dt.Load(reader);
注意： 把DataTable声明到using外，using外再使用查询结果
遍历DataTable
for(int i =0;i<dt.Rows.Count;i++)
{
DataRow row = dt.Rows[i];
string name = row.IsNull("name")?null:(string)row["Name"];//NULL处理
Console.WriteLine("name="+name);
}
七、封装一个库：分析
1，ADO.Net的连接字符串写到配置文件中。
2，每次操作数据库都要写一坨代码，太累，因此封装一个简化ADO.Net操作的库出来：
配置文件的设置连接字符串；简化连接的创建；简化SQL的执行
3，如果一个操作要执行多条SQL语句，如果每条都打开一个连接——执行——关闭连接的话，效率会非常低，而且会有“事务”的问题。
因此应该提供“打开、执行、关闭”这样的方法，也要提供“使用后现有连接执行的方法”。
4，参数化查询的查询参数个数不确定，可变长度参数会更方便
5，为了方便大部分情况下的小结果集，执行查询返回DataTable
八、MySqlHelper方法规划
1，public static MySqlConnection CreateConnection()
2，public static int ExecuteNonQuery(MySqlConnection conn,string sql,params MySqlParameter[] parameters)
3，public static int ExecuteNonQuery(string sql,params MySqlParameter[] parameters)
4，public static object ExecuteScalar(MySqlConnection conn,string sql,params MySqlParameter[] parameters)
5，public static object ExecuteScalar(string sql,params MySqlParameter[] parameters)
6，public static DataTable ExecuteQuery(MySqlConnection conn, string sql, params MySqlParameter[] parameters)
7，public static DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)
九、实现MySqlHelper
1，连接字符串一般配置到App.config（网站是Web.config）中的<connectionStrings>段中
然后使用ConfigurationManager类(需要添加对System.Configuration的引用)读取
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString
注意：
一定要保证代码中的名字和配置文件中的名字是一致的
2，其他方法的实现
3，测试几个方法，调用不需要自己控制连接的，再调用公用一个连接的
十、获得自动增长字段的值
1，不能用插入后获取最大值的方法，有并发问题
2，要在同一个连接中：select LAST_INSERT_ID()
3，可以Insert、LAST_INSERT_ID()在同一个连接中单独执行，也可以把LAST_INSERT_ID()放到insert语句后面用；
分割（使用ExecuteScalar执行即可）
十一、事务基础
1，有一个需求，类似于转账，从Tom的工资上转走10元，转到Jerry的工资上增加10元
Update T_Employees Set Salary = Salary - 10 where Name = 'Tom'
Update T_Employees Set Salary = Salary + 10 where Name = 'Jerry'
如果执行从Jerry账上加10元的时候执行出错（使用SQL语法写错误模拟），那么就会出现总体丢失10元的问题，如果是转账呢？
2，事务（Transaction）有四大特征：
原子性、一致性、隔离性、持久性
原子性指的是：几个操作要么都成功，要么都失败
十二、ADO.Net事务
1，要在一个连接中（否则要涉及到分布式事务）
MySqlTransaction tx = conn.BeginTransaction();
操作结束后执行tx.Commit()提交事务；
如果执行出错，则tx.Rollback()回滚（当前事务的操作全部取消）
示例代码：
MySqlTransaction tx = conn.BeginTransaction();
try
{
MySqlHelper.ExecuteNonQuery(conn,"Update T_Accounts set Amount = Amount-1000 where Number ='0001'");
string s = null;
s.ToLower();//制造异常
MySqlHelper.ExecuteNonQuery(conn, "Update t_accounts Set Amount=Amount+1000 where Number='0002'");
tx.Commit();
}
catch(Exception ex)
{
tx.Rollback();
}
事务还有隔离级别、嵌套事务等问题
十三、SQLServer的使用
1，安装，版本：2008有兼容性的问题，有bug、因此推荐安装SQLServer 2008 R2
2，Management Studio的使用
SQLServer的两种连接方式：
Windows 身份验证（互相信任的局域网中）；
SQLServer身份验证（使用SQLServer用户名密码验证，密码要复杂一点）。
Windows 身份验证还有一个用途：忘了sa密码，可以本机进去改。
3，新建数据库、新建表
SQLServer的数据类型
（varchar和nvarchar；nvarchar(n)和nvarchar(MAX)；
long是bigint；
获取前10条数据： select top 10 * from t_persons ）、
SQLServer的自动增长（是标识）、不需要特殊指定编码
4，保存表设计修改的时候，如果报错“不允许保存更改”：
工具→选项→Designers→把“阻止保存要求重新创建表的更高”勾选掉。
遇到报错信息：要先仔细阅读。
5，执行SQL语句（数据库上点右键“新建查询”，不要点【调试】）
十四、ADO.Net连接SQLServer
1，ADO.Net如何连接SQLServer：
SQLServer驱动内置
2，连接字符串：
server=ip;user id =sa;passWord = 密码;database = db1
3，SQLHelper：
把MySql替换成Sql就可以了
4，获得自动增长列的值：
Insert into t1(...) output insert.Id values(....)
5，如果基于接口编程，只要改动CreateConnection就可以了，查询参数以Directory<string,object>传递
如果使用Provider，连代码都不用改，改配置文件即可
