ADO.NET操作数据库(二)

本文核心内容：SqlHelper的封装、配置文件的使用、事件监视器、ADO.NET数据库连接池、可变参数、异常捕获、SQL漏洞注释攻击、使用带参数的sql语句、本文中出现的类的用法。

reader身上的方法IsDBNull()判断第几列的是是否为空。

添加对包的引用，再添加对类的引用。

ConfigurationManager SqlParameter Dispose()  SqlDbType AddWithValue() Elapsed <connectionStrings> ExecuteScalar() Exception throw ExecuteNonQuery()

一：关于SqlHelper的封装
使用SqlHelper工具类，帮助我们操作：
1)经常爆出的一个错：类型初始化设定项引发异常，在配置文件中找不到对应名称的配置。
2)SqlHelper工具类的使用特别好，一定要学会封装。
3)SqlDataReader类的逻辑：先判断是否读取到记录；再进行循环遍历(每次遍历都要创建一个对象)；获取列中值的方式。
4)在获取列值的时候，进行非空判断，IsDBNull()值是否为空？
5)创建List集合，并指定泛型类型。

        private static void queryAllData()        {            List<Teacher> list = new List<Teacher>();            string sql = "select  * from Teacher";            SqlDataReader reader = SqlHelper.ExecuteReader(sql);            if (reader.HasRows)            {                while (reader.Read())                {                    //每次循环都会创建一个记录                    Teacher teacher = new Teacher();                    //获取数据 id 姓名  性别 年龄                    teacher.TeaID = reader.GetInt32(0);                    teacher.TeaName = reader.GetString(1);                    teacher.TeaGender = reader.GetBoolean(2);                    teacher.TeaAge = reader.GetInt32(3);                    teacher.TeaBirthdat = reader.GetDateTime(4).ToString();                    //地址需要判断是否为空                     teacher.TeaAddress = reader.IsDBNull(5) ? "NULL" : reader.GetString(5);                    //右键判断是否为空                    teacher.TeaEmail = reader.IsDBNull(6) ? "NULL" : reader.GetString(6);                    teacher.TeaSalary = reader.GetInt32(7);                    list.Add(teacher);                }            }            else            {                Console.WriteLine("没有查询到记录！");            }        }

封装帮助类：封装SqlHelper:把常用的代码封装到一个类中：连接数据库，增删改查操作就这五个。

封装成静态类：静态类调用起来更简单。

readonly：关键字的使用，readonly修饰的变量是只读变量，只能在声明的时候赋值或者构造函数中赋值，其他时候只能读取值不能设置值。目的是：限制不能随便修改;

using System;using System.Collections.Generic;using System.Configuration;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace ado.net练习三{    /// <summary>    ///数据库操作工具类    /// </summary>    public static class SqlHelper    {        //连接字符串是在被类中访问的，所以是私有的；而其他的方法要被其他类调用，所以是公共的。        //读取配置文件中的连接字符串        //如何添加引用(添加引用后，就可以导入命名空间了)：先添加引用再导入命名空间        private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;        //1)执行增(insert into)删(delete from)改(update set)的方法，调用方法ExecuteNonQuery()        //在静态类中的方法也必须是静态的，因为要被外界访问，所以也需要是公共的        //参数：待执行的sql语句,可变参数        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                using (SqlCommand comm = new SqlCommand(sql, conn))                {                    if (pms != null)                    {                        comm.Parameters.AddRange(pms);                    }                    conn.Open();                    return comm.ExecuteNonQuery();                }            }        }        //2)执行查询(select from)的方法，返回单个值，调用方法ExecuteScalar()        public static Object ExecuteScalar(string sql, params SqlParameter[] pms)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                using (SqlCommand comm = new SqlCommand(sql, conn))                {                    if (pms != null)                    {                        comm.Parameters.AddRange(pms);                    }                    conn.Open();                    return comm.ExecuteScalar();                }            }        }        //3)执行查询(select from)的方法，返回多行，调用方法ExecuteReader()        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)        {            SqlConnection conn = new SqlConnection(connStr);            using (SqlCommand comm = new SqlCommand(sql, conn))            {                if (pms != null)                {                    comm.Parameters.AddRange(pms);                }                try                {                    conn.Open();                    //这样处理是有错误的，写在using代码块中，此时返回SqlDataReader后，连接对象已经关闭，就不能读取其中的每一行记录了。                    //这个枚举参数的含义是：将来关闭read对象的同时，在SqlDataReader内部也会将关联的SqlConnection对象进行关闭掉。                    //如果最后一行代码抛出了异常，但是此时连接已经打开怎么办                    return comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);                }                catch                {                    //如果最后一行代码抛异常，那就直接释放资源                    conn.Close();                    conn.Dispose();                    throw;//抛出了当前catch中捕获的异常                }            }        }    }}

二：关于配置文件的使用
1)如果是新添加配置文件：新建项--->常规--->应用程序配置选项。
2)配置文件的名字是App.config，不能修改。
3)实用配置文件的好处：把可变化的东西写到配置文件当中，将来需要修改时，只修改配置文件即可，不需要再修改源代码，编译发布。
4)配置文件中的内容，要注意两个节点：<connectionStrings>与<add>
5)如果报了一个错是：类型初始值设定项引发异常，说明在配置文件里面找不到对应的配置。

类型初始值设定项引发异常：配置文件里找不到对应的连接字符串设置

App.config配置文件：

<?xml version="1.0" encoding="utf-8" ?><configuration>  <!--连接字符串-->  <connectionStrings>    <!--内容-->    <add name="connStr" connectionString="Data Source=172.16.20.1\dev;Initial Catalog=MyFirstData;User ID=****;PASSWORD=****"/>  </connectionStrings></configuration>

三：事件监视器类的使用：Stopwatch
两个方法，一个属性，用于统计一个事件的执行时间。

案例：统计在不使用ado.net连接池的情况下，连接SQL Server所需要的时间。

        private static void calcConnTime()        {            string connStr = @"data source=172.16.20.1\dev;initial catalog=MyFirstData;User ID=****;PASSWORD=****;pooling=false";            //事件监视器,Stopwatch            Stopwatch watch =  new Stopwatch();            watch.Start();            using (SqlConnection  conn=new SqlConnection(connStr))            {                conn.Open();                conn.Close();            }            watch.Stop();            Console.WriteLine(watch.Elapsed);            Console.ReadKey();        }

四：ado.net数据库连接池
1)默认情况下ado.net启动了数据库连接池。
2)禁用ado.net连接池的情况下，连接所需要的时间比启用连接池要长，禁用连接池的关键是 pooling=false,在创建连接字符串的时候设置。
3)为什么启用连接池，连接打开速度比较快？ 
当启用ado.net连接池时，只会创建一个连接对象， 并且该连接对象默认不会立刻关闭，后面的其他所有连接都使用这一个连接对象。只有当应用程序退出时，连接才会被关闭。
而：禁用了连接池后，每次都会创建一个连接对象，然后进行关闭连接，此时会真正的创建多个连接对象并每次登陆登出。(每次创建连接、关闭连接太费时了)
4)启用链接池后，连接对象调用Close()方法做了哪些事？ conn.Close()
当启用ado.net连接池后，调用Close()方法时，并不会关闭连接对象，而是把当前连接对象放回到连接池里面了。
5)什么情况下，需要用到数据库连接池？需要用到数据库连接池，一般会存在两种情况：1)创建对象比较费时2)创建对象比较频繁。
6)数据库连接池的作用是：提高了连接对象的使用效率。
7)当第一次连接时，因为连接池中没有现成可用的连接对象，所以需要创建一个连接对象。当使用完毕后，放回到连接池中。
当下次再需要连接数据库的时候，首先会检查所使用的连接字符串，是否和连接池中现有的连接对象所使用的连接字符串一样，如果一样，就可以去除此连接对象，直接使用。如果连接字符串不一样，就会创建新的连接对象。
连接池中连接对象的数量限制，每个用户都会占用一个连接对象;当用户连接超出连接对象的数量时，只能进行排队。
8)连接池中的连接对象一直保持着连接状态，没有关闭。
9)数据库对连接人数是有限制的，需要进行并发操作。

五：可变参数params
params修饰符：修饰数组类型即可
一般来说，参数个数都是固定的，定义为数组类型的参数可以实现可变数目参数的目的，但是.NET提供了更灵活的机制来实现可变数目参数，这就是使用params修饰符。
可变数目参数的好处就是在某些情况下可以方便地对参数个数不确定情况的实现，例如计算任意数字的加权和，链接任意字符串为一个字符串等。 

六：异常捕获：
在catch(Exception e)代码块中进行异常捕获
1)如果用不到异常对象e,catch后面的小括号不用写。
2)如果一个方法有返回值，那么在捕获异常的时候，必须写throw，把"当前异常"抛出当做返回值，把当前catch捕获的异常抛出。
3)如果是throw new Exception("抛出了自己的异常"); 抛出自定义异常，和catch捕获的异常没有关系。
4)Exception类的使用

七:SQL语句注入漏洞攻击
登录判断：select * from User where UserName='{0}' and UserPassword='{1}'; 
将参数拼接到sql语句中；恶意构造的Password：hello' or 1=1--  （其中有一个单引号）

if(reader.Read()){
    Console.WriteLine("登录成功")
}else{
    Console.WriteLine("登录失败")
}

两种解决方法：防止注入攻击的方法:不使用SQL语句进行拼接，通过参数进行赋值(使用带参数的sql语句)或者使用存储过程。
sql语句注入攻击的原理：abcde' or 1=1--
sql语句变成了：select * from User where LoginID='abcde' or 1=1 --and password=****; 
(1)后面的判断密码逻辑被-- 注释掉了，(2)而判断用户名的代码 or永远为真，所以登录成功。

八:带参数的SQL语句
1)sql语句中会出现参数
2)如果sql语句中有参数，那么必须在SqlCommand对象中提供对应的参数和值。
创建两个参数对象
3)带参数的sql语句内部调用了存储过程。

常用的类是：SqlCommand类中的Parameter属性，AddRange()方法，comm.Parameters.AddRange(pms);

代码如下：

        private static void safetyLogin()        {            string connStr = @"data source=;initial catalog=MyFirstData;User ID=;PASSWORD=;            using (SqlConnection conn = new SqlConnection(connStr))            {                //把变量作为sql语句的参数,在sql语句中变量名必须以@符号开头。                //带参数的sql语句背部调用了存储过程。                string sql = "select count(*) from Teacher where TeaName=@name and TeaAge=@age";                using (SqlCommand comm = new SqlCommand(sql, conn))                {                    //第一个参数为sql语句的参数；第二个参数为对应的数据类型(枚举类型)；第三个为数据类型的长度                    SqlParameter paramsName = new SqlParameter("@name", System.Data.SqlDbType.VarChar, 20)                    {                        //对象初始化器                        Value = "陈诗音"                    };                    SqlParameter paramsAge = new SqlParameter("@age", System.Data.SqlDbType.Int)                    {                        //对象初始化器                        Value = 28                    };                    //1)创建两个参数对象                    //2)把参数对象添加到SqlCommand里面                    comm.Parameters.Add(paramsName);                    comm.Parameters.Add(paramsAge);                    //-------------------------简化版的写法(工作中最常用的写法)------------------------------                    //创建一个参数对象数组                    SqlParameter[] param = new SqlParameter[] {                        new SqlParameter("@name", System.Data.SqlDbType.VarChar, 20) { Value = "陈诗音"},                        new SqlParameter("@age", System.Data.SqlDbType.Int){ Value = 28}                    };                    //参数对象数组添加到SqlCommand里面                    comm.Parameters.AddRange(param);                    //-------------------------简化版的写法------------------------------                    comm.Parameters.AddWithValue("@name", "陈诗音");                    comm.Parameters.AddWithValue("@age", 28);                    conn.Open();                    int row = (int)comm.ExecuteScalar();                    if (row > 0)                    {                        Console.WriteLine("登录成功！");                    }                    else                    {                        Console.WriteLine("登录失败！");                    }                }            }        }