The difference between SqlDataReader, SqlDataAdapter and SqlCommand

1.SqlDataReader, online application, need Conn.Open (), close after use.

SqlConnection conn = new SqlConnection (CONNSTR);
Conn. Open ();
SqlCommand cmd = new SqlCommand ("SELECT Top ten * from Tuser", conn);
SqlDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);
while (reader. Read ())
{
Console.WriteLine (reader. GetValue (2));
}
This code error: ExecuteReader requires an open and available Connection. The connection ' s is closed.
should be conn. Open ().

2.SqlDataAdapter, off-line application, do not need to use Conn.Open (), it put this part of the function is encapsulated in its own internal, do not need you to explicitly call, it directly fill the data into the dataset.

SqlCommand and the command of the ADO era, SqlDataAdapter is a new thing in ADO, which works with datasets. In fact, a dataset is like a small database that resides in memory, and there can be more than one DataTable in a dataset that can be associated with one another, just like a table in a database! SqlDataAdapter's role is to extract data from the database and put it in a dataset, and when the data in the dataset changes, SqlDataAdapter updates the data in the database. To ensure that the data in the database is consistent with the data in the DataSet!
In the words of Microsoft Consultant: DataAdapter is like a spade, it is responsible for the data from the database "shovel" into the dataset, or the data from the dataset "shovel" into the database!

When you call DataAdapter's Fill method, it opens the SqlConnection to the database and executes the command by creating a SqlCommand and calling ExecuteReader, and then With a SqlDataReader created by an implicit, the data is read from the database, and the SqlDataReader and SqlConnection are closed after the read of the row is completed.

DefaultView is a property of the DataTable class. You can use the DataView class to define multiple views for each DataTable.

Use Reflacter to look at the original code of SqlDataAdapter and its parent class, and its method invocation context is as follows:

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

So personally think the most essence of a summary is: SqlDataAdapter internal access to data is by calling SqlDataReader to achieve, and both need to use SqlConnection and SqlCommand.

Specific Reflacter look at the original code of SqlDataAdapter and its parent class as follows:

2.1 SqlDataAdapter is a subclass of DbDataAdapter.

Public sealed class Sqldataadapter:dbdataadapter, IDbDataAdapter, IDataAdapter, ICloneable

2.2 DbDataAdapter is an abstract class that contains the concrete implementation of 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, COMMANDB Ehavior 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, idbcom Mand 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 St Artrecord, 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;
}

}

Download from: Sqldatareader,sqldataadapter and SqlCommand a little summary. Http://www.cnblogs.com/liuzhendong/archive/2012/01/28/2330689.html

The difference between SqlDataReader, SqlDataAdapter and SqlCommand