2012年2月15日星期三

SqlDataReader，SqlDataAdapter與SqlCommand的一點總結.

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, " %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, " %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, " %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;
  }

}
source:http://www.cnblogs.com/liuzhendong/archive/2012/01/28/2330689.html

發佈者： 於

沒有留言:

發佈留言

訂閱： 發佈留言 (Atom)