关闭

SqlDataReader,SqlDataAdapter与SqlCommand的一点总结.

779人阅读 评论(1) 收藏 举报
分类:

转自:http://www.cnblogs.com/liuzhendong/archive/2012/01/28/2330689.html

1.SqlDataReader,在线应用,需要conn.open(),使用完之后要关闭.

SqlConnection conn = new SqlConnection(connStr);
 //conn.Open();
SqlCommand cmd = new SqlCommand("select top 10 * from tuser", conn);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
    Console.WriteLine(reader.GetValue(2));
}
这段代码报错:ExecuteReader requires an open and available Connection. The connection's current state is closed.
应该将conn.Open()打开.


2.SqlDataAdapter,离线应用,不需要用conn.open(), 它把这部分功能给封装到自己内部了,不需要你来显式的去调用, 它直接将数据fill到dataset中.

SqlCommand与ADO时代的Command一样,SqlDataAdapter则是ADO.NET中的新事物,它配合DataSet来使用。其实,DataSet就像是驻留在内存中的小数据库,在DataSet中可以有多张DataTable,这些DataTable之间可以相互关联,就像在数据库中表关联一样!SqlDataAdapter的作用就是将数据从数据库中提取出来,放在DataSet中,当DataSet中的数据发生变化时,SqlDataAdapter再将数据库中的数据更新,以保证数据库中的数据和DataSet中的数据是一致的! 
用微软顾问的话讲:DataAdapter就像是一把铁锹,它负责把数据从数据库“铲”到DataSet中,或者将数据从DataSet“铲”到数据库中!

调用DataAdapter的Fill方法时, 它会打开到数据库的SqlConnection, 再通过创建一个SqlCommand和调用ExecuteReader的方式来执行命令, 然后, 通过一个隐式士创建的SqlDataReader, 从数据库读取数据, 结束行的读取之后, SqlDataReader和SqlConnection会被关闭.

DefaultView是DataTable类的一个属性。可用DataView类为每个DataTable定义多个视图。


用Reflacter察看一下SqlDataAdapter及其父类的原代码,其方法调用脉络如下:

Fill(DataSet dataSet)->

Fill(dataSet, 0, 0, "Table", selectCommand, fillCommandBehavior)->

FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior)->

Fill(dataset, srcTable, reader, startRecord, maxRecords)->

Fill(dataset, srcTable, reader, startRecord, maxRecords)->

FillFromReader(dataSet, null, srcTable, container, startRecord, maxRecords, null, null)->

FillLoadDataRow(mapping)->

while (dataReader.Read())

因此个人认为最精髓的一句总结就是:SqlDataAdapter内部获取数据是通过调用SqlDataReader来实现的,而两者都需要使用SqlConnection和SqlCommand。

 

具体Reflacter察看一下SqlDataAdapter及其父类的原代码如下:

2.1 SqlDataAdapter是DbDataAdapter的子类.

public sealed class SqlDataAdapter : DbDataAdapter, IDbDataAdapter, IDataAdapter, ICloneable

2.2 DbDataAdapter是一个抽象类,里面包含了Fill的具体实现.

public abstract class DbDataAdapter : DataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
{
  public override int Fill(DataSet dataSet)
  {
    int num;
    IntPtr ptr;
    Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet\n", base.ObjectID);
    try
    {
        IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
        CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
        num = this.Fill(dataSet, 0, 0, "Table", selectCommand, fillCommandBehavior);
    }
    finally
    {
        Bid.ScopeLeave(ref ptr);
    }
    return num;
  }

  protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
  {
    int num;
    IntPtr ptr;
    Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, startRecord, maxRecords, srcTable, command, behavior=%d{ds.CommandBehavior}\n", base.ObjectID, (int) behavior);
    try
    {
        if (dataSet == null)
        {
            throw ADP.FillRequires("dataSet");
        }
        if (startRecord < 0)
        {
            throw ADP.InvalidStartRecord("startRecord", startRecord);
        }
        if (maxRecords < 0)
        {
            throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
        }
        if (ADP.IsEmpty(srcTable))
        {
            throw ADP.FillRequiresSourceTableName("srcTable");
        }
        if (command == null)
        {
            throw ADP.MissingSelectCommand("Fill");
        }
        num = this.FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior);
    }
    finally
    {
        Bid.ScopeLeave(ref ptr);
    }
    return num;
  }

  private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
  {
    bool flag = null == command.Connection;
    try
    {
        IDbConnection connection = GetConnection3(this, command, "Fill");
        ConnectionState open = ConnectionState.Open;
        if (MissingSchemaAction.AddWithKey == base.MissingSchemaAction)
        {
            behavior |= CommandBehavior.KeyInfo;
        }
        try
        {
            QuietOpen(connection, out open);
            behavior |= CommandBehavior.SequentialAccess;
            using (IDataReader reader = null)
            {
                reader = command.ExecuteReader(behavior);
                if (datatables != null)
                {
                    return this.Fill(datatables, reader, startRecord, maxRecords);
                }
                return this.Fill(dataset, srcTable, reader, startRecord, maxRecords);
            }
        }
        finally
        {
            QuietClose(connection, open);
        }
    }
    finally
    {
        if (flag)
        {
            command.Transaction = null;
            command.Connection = null;
        }
    }

  protected virtual int Fill(DataSet dataSet, string srcTable, IDataReader dataReader, int startRecord, int maxRecords)
  {
    int num;
    IntPtr ptr;
    Bid.ScopeEnter(out ptr, "<comm.DataAdapter.Fill|API> %d#, dataSet, srcTable, dataReader, startRecord, maxRecords\n", this.ObjectID);
    try
    {
        if (dataSet == null)
        {
            throw ADP.FillRequires("dataSet");
        }
        if (ADP.IsEmpty(srcTable))
        {
            throw ADP.FillRequiresSourceTableName("srcTable");
        }
        if (dataReader == null)
        {
            throw ADP.FillRequires("dataReader");
        }
        if (startRecord < 0)
        {
            throw ADP.InvalidStartRecord("startRecord", startRecord);
        }
        if (maxRecords < 0)
        {
            throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
        }
        if (dataReader.IsClosed)
        {
            return 0;
        }
        DataReaderContainer container = DataReaderContainer.Create(dataReader, this.ReturnProviderSpecificTypes);
        num = this.FillFromReader(dataSet, null, srcTable, container, startRecord, maxRecords, null, null);
    }
    finally
    {
        Bid.ScopeLeave(ref ptr);
    }
    return num;
  }

  internal int FillFromReader(DataSet dataset, DataTable datatable, string srcTable, DataReaderContainer dataReader, int startRecord, int maxRecords, DataColumn parentChapterColumn, object   parentChapterValue)
  {
    int num2 = 0;
    int schemaCount = 0;
    do
    {
        if (0 < dataReader.FieldCount)
        {
            SchemaMapping mapping = this.FillMapping(dataset, datatable, srcTable, dataReader, schemaCount, parentChapterColumn, parentChapterValue);
            schemaCount++;
            if (((mapping != null) && (mapping.DataValues != null)) && (mapping.DataTable != null))
            {
                mapping.DataTable.BeginLoadData();
                try
                {
                    if ((1 == schemaCount) && ((0 < startRecord) || (0 < maxRecords)))
                    {
                        num2 = this.FillLoadDataRowChunk(mapping, startRecord, maxRecords);
                    }
                    else
                    {
                        int num3 = this.FillLoadDataRow(mapping);
                        if (1 == schemaCount)
                        {
                            num2 = num3;
                        }
                    }
                }
                finally
                {
                    mapping.DataTable.EndLoadData();
                }
                if (datatable != null)
                {
                    return num2;
                }
            }
        }
    }
    while (this.FillNextResult(dataReader));
    return num2;
  }

  private int FillLoadDataRow(SchemaMapping mapping)
  {
    int num = 0;
    DataReaderContainer dataReader = mapping.DataReader;
    if (!this._hasFillErrorHandler)
    {
        while (dataReader.Read())
        {
            mapping.LoadDataRow();
            num++;
        }
        return num;
    }
    while (dataReader.Read())
    {
        try
        {
            mapping.LoadDataRowWithClear();
            num++;
            continue;
        }
        catch (Exception exception)
        {
            if (!ADP.IsCatchableExceptionType(exception))
            {
                throw;
            }
            ADP.TraceExceptionForCapture(exception);
            this.OnFillErrorHandler(exception, mapping.DataTable, mapping.DataValues);
            continue;
        }
    }
    return num;
  }

}

作者:BobLiu 
邮箱:lzd_ren@hotmail.com
出处:http://www.cnblogs.com/liuzhendong
本文版权归作者所有,欢迎转载,未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

0
0
查看评论

Asp.net中SqlDataAdapter和SqlCommand对比分析

   一、SqlDataAdapter和DateSet原理:DateSet是数据的内存驻留表示形式,它提供了独立于数据源的一致关系编程模型;从某种程度上说DateSet就是一个不可视的数据库。但真正与数据源打交道的是SqlDataAdapter,包括从数据源填充数据集和从数据集更...
  • zhangyj_315
  • zhangyj_315
  • 2008-03-30 15:09
  • 682

sqlconnection,sqlcommand,sqldataadapter,sqldatareader,dataset

1 上帝说,要连接数据库,于是就有了sqlconnection (数据库连接,配置连接字符串等,用户名密码之类) 2 上帝说,要执行sql语句。于是就有了sqlcommand, 直接翻译成sql命令。每个sqlcommand都有commandtext跟parameters 文本跟参数。填写好这个命...
  • zhu6006
  • zhu6006
  • 2013-07-09 08:42
  • 584

c#之SqlDataAdapter和SqlDataReader

System.Data.SqlClient.SqlDataReader   System.Data.SqlClient.SqlDataAdapter 从机制上区分: SqlDataReader 查询数据始终是在数据库中查询,在使用该对象进行查询时connection始终...
  • XiaoqiangNan
  • XiaoqiangNan
  • 2017-02-06 17:36
  • 705

DataSet,SqlDataAdapter,SqlCommand,SqlDataReader

因朋友强烈要求。总结DataSet,SqlDataAdapter,SqlCommand,SqlDataReader直接的关系。。。反正没事。就给他总结下。。。    DataSet和SqlDataAdapter在一起用,就没SqlCommand什么事了,通常作用是把某张...
  • franco_zhan
  • franco_zhan
  • 2010-03-13 22:53
  • 708

DataSet、SqlDataAdapter、SqlCommand、ExecuteNonQuery、SqlDataReader

DataSet和SqlDataAdapter在一起用,就没SqlCommand什么事了,通常作用是把某张表的信息显示出来,比如显示在GridView上之类的,事例代码如下:         SqlConnection conn ...
  • zzz5323381
  • zzz5323381
  • 2012-03-17 13:56
  • 370

SqlDataAdapter和SqlDataReader

1.SqlDataAdapter SqlDataAdapter 是 DataSet 和 SQL Server 之间的桥接器,用于检索和保存数据。 命名空间:  System.Data.SqlClient 程序集:  ...
  • nkcxr
  • nkcxr
  • 2013-02-26 10:50
  • 1512

使用SqlDataAdapter批量更新数据

应用说明         数据适配器有SelectCommand、InsertCommand、DeleteCommand、UpdateCommand四种命令对象。分别给每种命令对象赋予相应的命令,就可以用数据适配器对数据集进行更新操作了。   &...
  • zlwzlwzlw
  • zlwzlwzlw
  • 2013-04-12 22:00
  • 1330

c#学习笔记(数据库连接以及SqlDataReader、SqlCommand的使用)

利用SqlDataReader检索数据:private void Search()  {      StrConn="Initial Catalog=study;Data Source=(local);user id=s...
  • yugang1219
  • yugang1219
  • 2006-07-03 15:32
  • 1571

SqlCommand 类读取SqlDataReader数据动态创建DataTable

今天学习“SqlCommand ”类:         SqlConnection objConn = GetConnection(ConnStr);       ...
  • HDOJ_lin
  • HDOJ_lin
  • 2017-05-11 23:58
  • 362

SqlConnection,SqlDataAdapter,SqlCommand,SqlParameter

这次做vb.net版机房收费系统中常常使用这样一些类——SqlConnection,SqlDataAdapter,SqlCommand,SqlParameter,这些类都是SqlClient类,SqlClient类位于System.Data命名空间中,此命名空间在ADO.NET中也可以算的上是核心部...
  • cjr15233661143
  • cjr15233661143
  • 2013-04-29 16:30
  • 1280
    个人资料
    • 访问:1900475次
    • 积分:18502
    • 等级:
    • 排名:第601名
    • 原创:160篇
    • 转载:874篇
    • 译文:0篇
    • 评论:104条
    最新评论