【原】关于使用DataReader的三个很奇怪的难点,ADO.NET 命名空间

夜里三点多,刚形成微博观众精灵V3.763的博客园升级,上来探望,刚看到一篇小说:【原】关于利用DataReader的2个很想获得的难题,不应有用DataReader?

ADO.NET

于是乎准备花点时间解答下,顺便为那些月扩充一篇文章。

ADO.NET是一组用于和数据源进行交互的面向对象类库。平常数据源是数据库,但也能够是文件文件、Excel表格、XML文件。

 

简易正是使用.net操作数据库的一套类库。

有关DataReader,从前写过一篇作品,可参考:DataReader 链接关闭解惑篇 

ADO.NET 命名空间

 

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

上面将对初稿,解答三个难题:

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

 

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

一: DataReader、DataTable、DataSet 的回顾关联:

System.Data.Odbc;

 

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

那里先取原著的第三句话:.net读取数据集有二种格局:DataSet 和 DataReader

ADO.NET 四大类库

解答:.net
的Command操作里,默许能够有二种回到:Data里德r、DataTable、DataSet

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

 

Command //执行查询、修改、删除等一声令下。

那三者的差不离关联为:

  ExecuteNonQuery() //执行非查询语句,重临受影响行数
如若推行非增、删、改操作再次回到-1。

 

  ExecuteScalar() //重回第①行第3列结果 再次回到Object值

DataReader 急忙只前进读的流,供给开发者自身去关闭流。

  ExecuteReader() //读取多行数据

DataTable 一个容器,把DataReeader读到DataTable容器后关闭流。

DataReader
//读取一串数据,从DataReader重返的数目都是便捷的只是上前的数据流。

DataSet 二个大容器,里面可停放四个DataTable。

DataAdapter
//读取后缓存下来,也正是离线数据库,包涵对连年对象以及当对数据库实行读取或然写入的时候自动的打开也许关闭连接的引用

因而开发者,首先要领悟那三者的简约关联,才不至于乱猜。

奥莱dbConnection、OdbcConnection、SqlConnection 用来链接不一致的数据库,换个前缀即可。

 

Connection 类

二:数据库链接、链接池、close与dispose

connection 用于和数据源建立连接,在执行其它操作以前必须建立连接。

 

创制Connection对象时,须要提供连接字符串,连接字符串是用;分号分割的一名目繁多名称/值的选项,用来描述连接的为主音信。

此地再取最初的小说的第N句话:在sqlserver(两千)中查阅进程,有充足多的sleeping进度,直到最后打开页面,提醒超时,说连接池满。

IDbConnection 接口定义了着力的connection属性和方法,该接口由拥有connection类完成。

 

一 、使用当前登陆到windows的用户地点访问数据库,无需输入密码。

解答:暗许mssql的链接数是少数的,大体在100八个,一句话来说您能够open
100多次而不闭馆,超越后再open就会产出链接池不够的现象。

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

那里面的简要关联为:

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

 

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

率先次Open出链接时,连接数据库(由于是第贰次建立链接,需求开首化先多音讯,正是风传的属性难点)

总是字符串中须求钦赐数据库所在的服务器(locahost 和. 都以地点的趣味),数据库名称,以及表达办法。

接下来你Close时,数据库断开链接,同时把链接状态改成sleep,不过不销毁(为了下次建立链接时省掉开端化,幸免遗闻中的质量难点),那一个不销毁的链接,放到一个池里面,被叫做链接池。

因为数据库放到服务器后 ip
数据库名都要时时转移,所以常把连接字符串放在配置文件中。

设若您Dispose(),则会把链接从池里销毁,当然下次链接就会起来新的初阶化。

设置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();

假如应用时三个流程下来,是按顺序的open->close-open->close
状态,那么始终只用到多个链接(从连接池里进进出出),基本没有过多的开端化难题,好玩的事品质好。

然后借使数据库参数有转移,只须要修改配置文件即可。

 

测试连接

事态二:三个线程的不健康操作:

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()。

一旦你三个链接,在Open状态,那时候另贰个线程也要开拓链接,发现链接池没有时,就会重新到数据库建两个链接(又起来发轫化,一堆音讯,最终产生二个链接)。

选取using语句自动释放财富

若是你拥有的链接全在Open,不停歇,恐怕时间拖的很多,在其他线程要链接的时候,你的链接还没停歇,在爆发100五个链接后,数据库最大数满了,就会油不过生“链接池已满”的境况。 

using (con)
{
    //打开连接
    con.Open();
}

context.Response.Write(con.State.ToString()); //输出连接状态

 

出口结果为Closed,已关门。能够看看在using语块后是电动释放财富的。必须贯彻IDispose接口,才方可运用using语句自动释放。

场地三:七个线程的常规操作:

连接池

 

连接池保持已经打开的数据库连接,那些连接在采取同样数据源的会员间共享,那样就省了不停创设和销毁连接的光阴。

假诺您懂的每一种链接打开后,都快速关闭,经常3个健康语句的操作时间在0.001-0.1秒左右,那样关闭的链接就赶回池里,能够飞速须求别的线程使用。

当客户端调用open()方法请求打开连接时,连接直接由连接池提供而不是再次成立,当调用Close()方法释放时,它并没有被假释,而是重新回来池中等待下3次呼吁。

这般,仅要求几十二个链接,就能够循环的选用处理三十二线程难题。 

<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 : 连接池中型小型小的连接数。

比方多少个操作open到close,用时0.1秒二回,
那么一个链接在1秒内能够处理13次操作。而九贰十个链接最大数就能够扶助1000次操作。

max pool size : 连接池中允许最地拉那接数。

简易的说正是1秒能够出现操作1000次以上,那对中型小型网站来说,处理是一定不难的事。

翻看连接池状态能够选用sql系统存款和储蓄进度sp_who2如:

而新手容易犯的谬误,正是链接打开后,长日子的不停歇,最终造成在八线程的景况下,把数据库链接能源用尽了,出现了不当而不得知。 

exec sp_who2

 

要是设置最小连接池数量为5,则数据库中就有 四个照应数据库名的连年,假使没有操作,其状态就会处于 sleeping 状态。

特别的借使

Command 类

在诸多的程序处理中,对数据库的操作时间,往往是不平均的,这就须求有点经历的次序开发者,花点时间,来做好那最大产出难题,常常是:

command类能够实施全部体系的SQL语句,和connection类一样,达成了IDbCommand接口。

对长日子查询的,使用缓存(制止二遍询问),只怕集中使用队列(因为使用队列,就叁个链接就足以消除了,反正是开了读和关,然后下2个又是开了读和关,始终是2个链接),当然使用队列也要看事态。

在行使command类操作数据时,供给指明多少个性情。CommandType,CommandText和Connection。

对此时间短的,直接处理就能够了。

CommandType 枚举值:Text(一条SQL语句,暗中同意值),StoredProcedure(存款和储蓄进度),TableDirect(表的称呼,获取表的兼具记录)。

 

使用ExecuteNonQuery()插入一条数据

实质上简单,越往上,就特别“看事态处理”,没有何定律,假诺有,那就是数据库的最大链接数,无论你怎么耍流氓,反正你无法让它2回性给用完。 

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()再次回到表中的总记录数

在sqlserver(两千)中查阅进程,有尤其多的sleeping进程,直到最后打开页面,提示超时,说连接池满,为何,既然已经了好多
sleeping 的数据库连接, 为啥还会并发 不可能一连数据库
的标题吧??

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重回的数目都以便捷的只是上前的数据流。

对于ADO.NET,它有一种机制,来分析是采用产生新的对话还是直接从链接池中回到,例如,不一样的数据库链接,它会时有发生新的链接,而不会从原有链接池里重返。

DataReader的常用方法:

而对此mssql,只怕某种检查和测试机制原因(分歧的线程或进度链接),mssql分析后一直是为你生出新的链接,而不是从sleep状态的链接池重临,因而,原来的sleep不可能复用,再一次发生新的链接,就报链接池满了。

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);

包裹为方式调用时,很有用。

三个结实集

当在3个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(“\'”,”\’\'”); 将全体3个单引号转换为三个单引号

竭泽而渔办法2:参数化编制程序,将需求拼接值的地点,用多少个参数变量表示,而操作数据库的时候,给那个参数赋值。

参数化编制程序

① 、将索要拼接字符串的值,用2个@指引的变量名代替。

二 、使用SqlParameter类型将参数变量与值绑定在一块儿。

叁 、将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());
    }
}

调用存款和储蓄进度

创造2个囤积进程,添加钦赐数量,并回到新增的id。

use hzsweb
go

create proc InsertArticle
    @title varchar(250),
    @content varchar(250),
    @createTime datetime,
    @lastId int output
as
begin
    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);
    }
}

事务

作业是一组必须全方位成功或任何退步的操作,事务的对象是保证数据总能处于有效一致的事态。

工作有五个被称为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 catch

exec 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; //起始1
colId.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等封装成2个类,调用方法时只要求传入一些参数和数据库连接字符串就足以访问数据库了。

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);

//DataReader
string readerSql = "select id,title,createTime from article";
using (var rd = SQLHelper.ExecuteReader(readerSql))
{
    while (rd.Read())
    {
        ////
    }
}

//DataTable
string dtSql = "select id,title,createTime from article";
var dt = SQLHelper.getDataTable(dtSql);
foreach (DataRow row in dt.Rows)
{
    ///
}