The Difference Between Data Adapters, Data Sets And Data Readers, In Plain English

Recently posed on Yahoo! Answers:

Data reader or Data adapter?

in database programming what is the difference between these?
please tell me completely.(I am begginer)

A data adapter is a thing you use to connect and send commands to a database. A data reader is a thing you use to output records one at a time, and only going foward (from record 1 to the last record).

And the thing he didn’t ask about — the item you use to hold the records you get from a data adapter, so you can work with them — is a data set.

So, in order: You first create a data adapter to connect to a database, send your SQL to it and get a response. You then populate either a data set or a data reader from the response the data adapter gets. If you put the records in a data set, you can work with them; if you put them in a data reader, you can only output them.

Let’s look at a couple typical ASP.NET / VB.NET implementations of these classes, to populate a GridView we’ll call gvMain. First, we’ll consider the use of a DataAdapter and DataSet. Please note that in the code before, SqlDataAdapter is the same as DataAdapter.

Sub gvMain_dataBind(ByVal connectionString As String)

' Create a SqlConnection to the Northwind database.
Dim objConn As New SqlConnection(connectionString)

' Create a SqlDataAdapter for the Suppliers table.
Dim suppliersAdapter As New SqlDataAdapter()

' A table mapping names the DataTable.
suppliersAdapter.TableMappings.Add("Table", "Suppliers")

' Open the connection.
connection.Open()

' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As New SqlCommand("SELECT SupplierID, CompanyName FROM Suppliers;", objConn)
suppliersCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand

' Fill the DataSet.
Dim dataSet As DataSet = New DataSet("Suppliers")
suppliersAdapter.Fill(dataSet)

gvMain.DataSource = dataset.Tables("Suppliers").DefaultView
gvMain.DataBind()

' Close the connection.
objConn.Close()
End Sub

The first part of our subroutine, of course, opens the connection to the SQL database for us.

' Create a SqlConnection to the Northwind database.
Dim objConn As New SqlConnection(connectionString)

Next, we create a SqlDataAdapter that will interpret the SELECT statement for us, get a response from the database, then prepare the response for our use, specifically by creating a table — just like a database table — and naming it “Suppliers”:

' Create a SqlDataAdapter for the Suppliers table.
Dim suppliersAdapter As New SqlDataAdapter()

' A table mapping names the DataTable.
suppliersAdapter.TableMappings.Add("Table", "Suppliers")

Now, we go ahead and provide the DataAdapter with the SQL statement we are going to use, tell it the type of command we are sending, and tell the DataAdapter to go ahead and prepare our statement as a database command:

' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As New SqlCommand("SELECT SupplierID, CompanyName FROM Suppliers;", objConn)
suppliersCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand</pre>
Now that the DataAdapter is ready to send commands to the database on our behalf, we need to have a place for it to put the results. For that, we create a DataSet -- which is basically an in-memory database we create on the server. Once created, we tell the DataAdapter to go ahead, execute the command, and once it gets a result, to "fill" our DataSet with that result:
<pre lang="vb">' Fill the DataSet.
Dim dataSet As New DataSet()
suppliersAdapter.Fill(dataSet)

Some automatic stuff has happened for us, thanks to the DataAdapter and DataSet classes as made by Microsoft. The classes have figured out for us that since the DataAdapter has a table named Suppliers, we should also have a table named Suppliers in the DataSet; so, the DataSet automatically creates a DataTable within itself, named Suppliers.

Additionally, since the DataTable is being populated by the response from the DataAdapter, the DataSet automatically creates columns that are of the same type and have the same properties as the columns in the response from the database. For example, given this SQL:

SELECT SupplierID, CompanyName FROM Suppliers

If SupplierID is an integer with a length of 10, the DataTable will have a column named SupplierID that is an integer column with a length of 10; if CompanyName is a varchar with a length of 50, the DataTable will have a 50-character varchar column named CompanyName.

(Note that not all properties, such as primary keys and autonumber / identity fields, are created in a DataTable by this method. Whether that happens depends on the DataAdapter itself. But that’s an entry for another time.)

Finally, in the example above, since we have the explicit table “Suppliers” in the DataSet, we can now bind our GridView to that table’s default view:

gvMain.DataSource = dataset.Tables("Suppliers").DefaultView
gvMain.DataBind()

' Close the connection.
objConn.Close()
End Sub

Notice that while a lot of things are figured out for us by these classes, it’s still a lot of code to accomplish a simple thing. A DataReader allows us to cut out a lot of middlemen. Consider this code:

Sub gvMain_dataBind(ByVal connectionString As String)
Dim objConn As New SqlConnection(connectionString)
Dim objCmd As New SqlCommand("SELECT SupplierID, CompanyName FROM Suppliers", objConn
objCmd.CommandType = CommandType.Text

objConn.Open()
Dim objReader As SqlDataReader = objCmd.ExecuteReader()
gvMain.DataSource = objReader
gvMain.DataBind()
objConn.Close()
End Sub

As you can see, this is a lot more efficient, because a lot more abstraction is taking place. “Abstraction” is a way to use a given class to invoke one or more additional classes, to combine the properties and methods of several classes into a single class, etc.

This code uses abstraction quite a bit to accomplish its task.

Once again, we open the connection:

Dim objConn As New SqlConnection(connectionString)

But this time, we use a SqlCommand object, which employs the DataAdapter class to prepare our command for the database:

Dim objCmd As New SqlCommand("SELECT SupplierID, CompanyName FROM Suppliers", objConn
objCmd.CommandType = CommandType.Text

Now, we’ll go ahead and open the database connection, then use the ExecuteReader method of the SqlCommand object to populate a DataReader:

objConn.Open()
Dim objReader As SqlDataReader = objCmd.ExecuteReader()

The ExecuteReader() method handles all the activity needed to get the response from the database server and package the records so that we can move through them one by one.

Since by definition, a DataReader is a displayable list of records, we can simply tie that to the GridView control as its data source, then close the connection:

gvMain.DataSource = objReader
gvMain.DataBind()
objConn.Close()

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!