30、ADO.NET、事务、DataSet

2019-11-14 15:50:29

字体：大中小

来源：转载

供稿：网友

ADO.NET

ADO.NET是一组用于和数据源进行交互的面向对象类库。通常数据源是数据库，但也可以是文本文件、Excel表格、xml文件。

说白了就是使用.net操作数据库的一套类库。

ADO.NET 命名空间

System.Data; //描述数据的命名空间

System.Data.SqlClient; //针对SQL Server的命名空间

System.Data.OleDB; //access用这个

System.Data.Odbc;

System.Data.OracleClinet; //Oracle用这个

ADO.NET 四大类库

Connection //用来和数据库建立连接。

Command //执行查询、修改、删除等命令。

　　ExecuteNonQuery() //执行非查询语句，返回受影响行数如果执行非增、删、改操作返回-1。

　　ExecuteScalar() //返回第一行第一列结果返回Object值

　　ExecuteReader() //读取多行数据

DataReader //读取一串数据，从DataReader返回的数据都是快速的只是向前的数据流。

DataAdapter //读取后缓存下来，相当于离线数据库，包含对连接对象以及当对数据库进行读取或者写入的时候自动的打开或者关闭连接的引用

OledbConnection、OdbcConnection、SqlConnection 用来链接不同的数据库，换个前缀即可。

Connection 类

connection 用于和数据源建立连接，在执行任何操作之前必须建立连接。

创建Connection对象时，需要提供连接字符串，连接字符串是用；分号分割的一系列名称/值的选项，用来描述连接的基本信息。

IDbConnection 接口定义了核心的connection属性和方法，该接口由所有connection类实现。

1、使用当前登陆到windows的用户身份访问数据库，无需输入密码。

string conStr = "Data Source=.; Initial Catalog=hzsweb; Integrated Security=SSPI";

2、使用账号密码连接数据库。

string conStr = "Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;";

连接字符串中需要指定数据库所在的服务器（locahost 和. 都是本地的意思），数据库名称，以及验证方式。

因为数据库放到服务器后 ip 数据库名都要经常改变，所以常把连接字符串放在配置文件中。

设置web.config

<configuration>  <connectionStrings>    <add name="connStr" connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;"/>  </connectionStrings></configuration>

添加引用

引用->右键->添加->框架->选择->System.Configuration。

在ConnectionStrings集合中获取连接字符串。

var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();

以后如果数据库参数有变动，只需要修改配置文件即可。

测试连接

var conStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();//创建connection对象System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();con.ConnectionString = conStr; //也可以使用用构造方法写在括号中//打开连接con.Open();context.Response.Write(con.State.ToString()); //输出连接状态//关闭连接con.Close();

结果为Open，已连接状态。需要注意的是，每次使用完毕需要关闭连接，释放资源。con.Close()效果等同于con.Dispose()。

使用using语句自动释放资源

using (con){    //打开连接    con.Open();}context.Response.Write(con.State.ToString()); //输出连接状态

输出结果为Closed，已关闭。可以看出在using语块后是自动释放资源的。必须实现IDispose接口，才可以使用using语句自动释放。

连接池

连接池保持已经打开的数据库连接，这些连接在使用相同数据源的会员间共享，这样就省了不断创建和销毁连接的时间。

当客户端调用open()方法请求打开连接时，连接直接由连接池提供而不是再次创建，当调用Close()方法释放时，它并没有被释放，而是重新回到池中等待下一次请求。

<add name="connStr" connectionString="Server=localhost;DataBase=hzsweb;uid=sa;pwd=123;pooling=true;min pool size=5;max pool size=10"/>

pooling ：是否打开连接池，默认为true。

min pool size ：连接池中最小连接数。

max pool size ：连接池中允许最大连接数。

查看连接池状态可以使用sql系统存储过程sp_who2如：

exec sp_who2

如果设置最小连接池数量为5，则数据库中就有 5 个对应数据库名的连接，如果没有操作，其状态就会处于 sleeping 状态。

Command 类

command类可以执行所有类型的SQL语句，和connection类一样，实现了IDbCommand接口。

在使用command类操作数据时，需要指明三个属性。CommandType，CommandText和Connection。

CommandType 枚举值：Text（一条SQL语句，默认值），StoredPRocedure（存储过程），TableDirect（表的名称，获取表的所有记录）。

使用ExecuteNonQuery()插入一条数据

int result;var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();//创建connection对象using (var con = new SqlConnection(conStr)){    //创建command对象    using (var cmd = new SqlCommand())    {        cmd.CommandType = System.Data.CommandType.Text; //默认值 sql语句        cmd.CommandText = "insert into article ([title],[content],[createTime]) values ('title','content','2015-08-31')"; //执行脚本        cmd.Connection = con; //cmd使用的连接        con.Open(); //打开连接        result = cmd.ExecuteNonQuery(); //返回受影响行数 插入、修改、删除 都用这个    }}context.Response.Write(result);

返回结果为1。

使用ExecuteScalar()返回表中的总记录数

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();int count;using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select count(*) from news", con)) //构造函数 type默认就是text    {        con.Open();        count = Convert.ToInt32(cmd.ExecuteScalar()); //返回第一行第一个字段的值    }}

DataReader 类

可以读取多条数据，从DataReader返回的数据都是快速的只是向前的数据流。

DataReader的常用方法：

Read() ：判断下一行是否读取到数据，如果有返回true，否则为false。

GetValue() : 返回当前行中指定需要的字段值。

getValues() : 将当前行中的值保存到数组中。可以使用DataReader.FieldCount属性确定一行记录的列数。

NextResult() : 多个结果集时，用来获取下一个结果集

Close() ：关闭Reader

使用ExecuteReader()方法查询表中所有记录

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();System.Text.StringBuilder sb = new System.Text.StringBuilder();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select * from news", con))    {        con.Open();        var rd = cmd.ExecuteReader();        while (rd.Read()) //一直读取到没有为止        {            //sb.Append(rd["title"]);            sb.Append(rd.GetValue(1));            sb.Append(rd.GetString(2));            sb.Append("<br/>");        }
　　　　 rd.Close();    }}

CommandBehavior 自动关掉关联连接

ExecuteReader()方法有一个重载，可以在关闭DataReader的同时，自动关掉关联的连接。//con.Close()

var rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);

封装为方法调用时，很有用。

多个结果集

当在一个sql语句中使用；分号分割多个查询结果集时，可以使用rd.Nextresult()来找到下一个结果集。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();System.Text.StringBuilder sb = new System.Text.StringBuilder();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("select top 10 * from news;select top 10 *  from article", con))    {        con.Open();        var rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);        int i = 1;        do        {            sb.Append("<h2>第" + i + "个结果集：</h2>");            while (rd.Read())            {                for (int j = 0; j < rd.FieldCount; j++)                {                    sb.Append("<li>");                    sb.Append(rd.GetName(j).ToString()); //获取字段名                    sb.Append("：");                    sb.Append(rd.GetValue(j).ToString()); //获取字段值                    sb.Append("</li>");                }            }            i++;        } while (rd.NextResult());        rd.Close();    }}

结果为拆分的两组前10条数据。

SQL注入

由于拼接字符串的不安全，前台输入追加条件 or '1' = '1' 则永远为真。以及输入些其他对数据库表操作的语句。或者输入--把你后面的代码都注释掉。

解决办法1： string.Replace("/'","/'/'"); 将所有1个单引号转换为两个单引号

解决方法2：参数化编程，将需要拼接值的地方，用一个参数变量表示，而操作数据库的时候，给这个参数赋值。

参数化编程

1、将需要拼接字符串的值，用一个@引导的变量名代替。

2、使用SqlParameter类型将参数变量与值绑定在一起。

3、将SqlParameter对象交给Command对象的Prarmeters集合。

using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("delete from article where id = @id", con))    {        con.Open();        cmd.Parameters.Add("@id", 1197);        sb.Append(cmd.ExecuteNonQuery());    }}

调用存储过程

创建一个存储过程，添加指定数据，并返回新增的id。

use hzswebgocreate proc InsertArticle    @title varchar(250),    @content varchar(250),    @createTime datetime,    @lastId int outputasbegin    insert into article (title,content,createTime)        values (@title,@content,@createTime);    set @lastId = @@IDENTITY;end

在c#中调用存储过程

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    using (var cmd = new SqlCommand("InsertArticle", con))    {        cmd.CommandType = CommandType.StoredProcedure; //存储过程        con.Open();        cmd.Parameters.AddRange(new SqlParameter[]{            new SqlParameter("@title","标题"),            new SqlParameter("@content","内容"),            new SqlParameter("@createTime",DateTime.Now.ToString()),            new SqlParameter("@lastId",SqlDbType.Int,4)        });        cmd.Parameters["@lastId"].Direction = ParameterDirection.Output; //设置为 输出参数        int result = cmd.ExecuteNonQuery();        int lastId = (int)cmd.Parameters["@lastId"].Value;        context.Response.Write(lastId);    }}

事务

事务是一组必须全部成功或全部失败的操作，事务的目标是保证数据总能处于有效一致的状态。

事务有4个被称为ACID属性的特征。

Atomic（原子性）：事务中的所有步骤必须同时成功或失败。

Consist（一致性）：事务使底层数据库在稳定状态间转换。

lsolated（隔离性）：每个事务都是独立的实体，不会互相影响。

Durable（持久性）：在事务成功前，事务产生的变化永久的存储在硬盘上。

使用事务时不要在事务中间使用select语句返回数据，应该在事务开始前返回数据，这样可以减少事务锁定数据的数目。

在T-SQL中使用事务

在T-SQL中使用Begin transaction 开始事务，使用commit提交事务，使用rollback回滚事务。

create proc updateSort(    @sortA int,    @sortB int,    @id_a int,    @id_b int)as    begin try        begin transaction            update article set sort = @sortA where id = @id_a;            update article set sort = @sortB where id = @id_b;        commit --提交    end try    begin catch        if(@@trancount>0)            rollback --回滚    end catchexec updateSort 50,51,1,2

在C#中使用事务

使用connection对象.BeginTransaction()方法返回一个transaction对象，用于管理事务。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    var cmd1 = new SqlCommand("update article set sort = @sortA where id = @idA", con);    var cmd2 = new SqlCommand("update article set sort = @sortB where id = @idB", con);    cmd1.Parameters.AddRange(new SqlParameter[] {         new SqlParameter("@sortA",100),        new SqlParameter("@idA",1)    });    cmd2.Parameters.AddRange(new SqlParameter[] {         new SqlParameter("@sortB",101),        new SqlParameter("@idB",2)    });    SqlTransaction tran = null;    try    {        con.Open();        tran = con.BeginTransaction();        cmd1.Transaction = tran;        cmd2.Transaction = tran;        cmd1.ExecuteNonQuery();        cmd2.ExecuteNonQuery();        tran.Commit();    }    catch    {        tran.Rollback();    }}

可以使用tran.save("abc");方法指定保存点，并使用tran.Rollback("abc");回滚到某保存点。回滚全部无需输入参数。

DataSet

DataSet（数据集）内存中的数据库。

在SQL Server中

数据库对象（实例/Sqlexpress）

架构（dbo，表的命名空间）

表（列数据）

行

DataSet中

DataSet实例（new DataSet()）

Tables表集合 newDataTable（表名，命名空间）

DataColumn （DataTable中的列）

DataRow（DataTable中的行）

DataAdapter 类

要在DataSet中提取记录并将它们填入表中，需要使用DataAdapter对象，它含有查询和更新的全部命令。

SelectCommand 查询数据。

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    //创建DataAdapter对象，并设置查询语句和数据库连接    SqlDataAdapter sda = new SqlDataAdapter();    //或直接使用构造方法new SqlDataAdapter("sql",con);    sda.SelectCommand = new SqlCommand();    sda.SelectCommand.Connection = con;    sda.SelectCommand.CommandText = "select * from article";    DataSet ds = new DataSet();    //将数据填充到数据集，使用Fill()时自动打开连接    sda.Fill(ds);    DataTable dt = ds.Tables[0]; //第一个数据表    foreach (DataRow row in dt.Rows)    {        context.Response.Write(row[0]);        context.Response.Write(row[1]);        context.Response.Write(row[2]);        context.Response.Write(row[3]);        context.Response.Write("<hr>");    }}

DeleteComand 删除数据

var conStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();using (var con = new SqlConnection(conStr)){    SqlDataAdapter sda = new SqlDataAdapter("select top 10 id,title from article",con);    DataSet ds = new DataSet();    sda.Fill(ds);    sda.DeleteCommand = new SqlCommand("delete  from article where id = 1",con);    sda.Update(ds);    DataTable dt = ds.Tables[0];    foreach (DataRow row in dt.Rows)    {        context.Response.Write(row[0]);        context.Response.Write(row[1]);        context.Response.Write("<hr>");    }}

修改使用UpdateCommand，添加使用InsertCommand 不要忘记Update(ds) 否则不会更新。

手动创建一个DataSet

var ds = new DataSet("web");var dt = new DataTable("table1");var colId = new DataColumn("id", typeof(int));var colName = new DataColumn("name", typeof(string));var colSex = new DataColumn("sex", typeof(string));colId.AutoIncrement = true; //自增colId.AutoIncrementSeed = 1; //起始1colId.AutoIncrementStep = 1; //递增colId.Unique = true; //唯一dt.Columns.Add(colId);dt.Columns.Add(colName);dt.Columns.Add(colSex);ds.Tables.Add(dt);var row = dt.NewRow();row[0] = 1;row[1] = "奉先";row[2] = "男";dt.Rows.Add(row);foreach (DataRow r in dt.Rows){    context.Response.Write(r[0]);    context.Response.Write(r[1]);    context.Response.Write(r[2]);    context.Response.Write("<hr>");}

SQLHelper

将一些重复的数据库连接Connection,Command,DataReader等封装成一个类，调用方法时只需要传入一些参数和数据库连接字符串就可以访问数据库了。

public static class SQLHelper{    public static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();    /// <summary>    /// 执行增、删、改操作，返回受影响行数    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static int ExecuteNonQuery(string sql, params SqlParameter[] cmdParams)    {        using (var con = new SqlConnection(conStr))        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteNonQuery();            }        }    }    /// <summary>    /// 返回首行首列信息    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static object ExecuteScalar(string sql, params SqlParameter[] cmdParams)    {        using (var con = new SqlConnection(conStr))        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteScalar();            }        }    }    /// <summary>    /// 返回只进的读取流    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] cmdParams)    {        var con = new SqlConnection(conStr);        try        {            using (var cmd = new SqlCommand(sql, con))            {                cmd.Parameters.AddRange(cmdParams);                con.Open();                return cmd.ExecuteReader(CommandBehavior.CloseConnection);            }        }        catch (Exception e)        {            con.Close();            throw e;        }    }    /// <summary>    /// 返回DataTable    /// </summary>    /// <param name="sql"></param>    /// <param name="cmdParams"></param>    /// <returns></returns>    public static DataTable getDataTable(string sql, params SqlParameter[] cmdParams)    {        var ds = new DataSet();        using (var sda = new SqlDataAdapter(sql, conStr))        {            sda.SelectCommand.Parameters.AddRange(cmdParams);            sda.Fill(ds);        }        return ds.Tables[0];    }}

调用

//修改string sql = "update article set sort = @sort where id = @id";var prams = new SqlParameter[]{    new SqlParameter("@sort",15),    new SqlParameter("@id",3)};int result = SQLHelper.ExecuteNonQuery(sql);//条数string sqlCount = "select count(*) from article";var count = SQLHelper.ExecuteScalar(sqlCount);//DataReaderstring readerSql = "select id,title,createTime from article";using (var rd = SQLHelper.ExecuteReader(readerSql)){    while (rd.Read())    {        ////    }}//DataTablestring dtSql = "select id,title,createTime from article";var dt = SQLHelper.getDataTable(dtSql);foreach (DataRow row in dt.Rows){    ///}