Fundamental objects in ADO.NET

[priyachaudhary]

Can anyone explain the DataReader and data set with examples

 

[jmcilhinney]

ADO.NET follows good practice as far as following the Single Responsibility Principle (SRP). That means that each class is intended to do one thing only, so an application-level operation will generally involve multiple ADO.NET objects, each doing its specific job. You'll generally start with a connection object, e.g. a SqlClient.SqlConnection for SQL Server, which will actually make the connection to the database. Next you'll have a command object, e.g. a SqlClient.SqlCommand, that represents a SQL command to execute against the database. The command object is associated with the connection object via its Connection property, so the command is executed over that connection. If you call ExecuteReader on a command object, it will return a data reader object, e.g. a SqlClient.SqlDataReader. As the name suggests, that object exists to read the data from the result set of the query in the command. You call the Read method of the data reader to read the next record in the result set and you can then access that record. A data reader is read-only, which mean that it cannot be used to write data to a database. It is also forward-only, which means that once you advance to a particular record in the result set, you no longer have access to any previous records. You also cannot advance to any particular record without reading all previous records. E.g.

Dim sqlQuery = "SELECT GivenName, FamilyName
                FROM Person
                ORDER BY FamilyName, GivenName"

Using connection As New SqlConnection(connectionString),
      command As New SqlCommand(sqlQuery, connection)
    connection.Open()
   
    Using reader = command.ExecuteReader()
        Do While reader.Read()
            Console.WriteLine($"{GivenName} {FamilyName}")
        Loop
    End Using
End Using

A DataSet is basically an in-memory representation of a database. Just as a database contains tables and relations between then, so a DataSet contains DataTable objects in its Tables collection property and DataRelation objects between them in its Relations collection property. If you only want one table of data, which includes the result set from a single query regardless of how many tables it includes, then you should generally use a DataTable on its own. Only use a DataSet if you need to for some reason.

Whether you use a lone DataTable or a DataSet, you will generally use them in conjunction with a data adapter, e.g. a SqlClient.SqlDataAdapter. A data adapter brings up to four command objects together to perform CRUD operations. CRUD stands for create, read, update & delete, which are the four basic operations you can perform on a database. A data adapter has SelectCommand, InsertCommand, UpdateCommand and DeleteCommand properties that each refer to a command object that contain a SQL SELECT, INSERT, UPDATE and DELETE statement respectively. When you call Fill on the data adapter, it will internally call ExecuteReader on the SelectCommand and then read the result set of the query into the specified DataTable. When you call Update on the data adapter, it will check the RowStateICODE] of each [ICODE]DataRow in the Rows collection property of the specified DataTable and call ExecuteNonQuery on the InsertCommand for each Added row, on the UpdateCommand for each Modified row and on the DeleteCommand for each Deleted row. When you call Fill or Update, you can pass a DataTable or pass a DataSet and the name of a table in its Tables collection. E.g.

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem",
                                      connection)
Private table As New DataTable
 
Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", connection)
 
    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
 
    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    adapter.DeleteCommand = delete
    adapter.InsertCommand = insert
    adapter.UpdateCommand = update
 
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
 
Private Sub GetData()
    'Retrieve the data.
    adapter.Fill(table)
 
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub
 
Private Sub SaveData()
    'Save the changes.
    adapter.Update(table)
End Sub

That example uses a DataTable directly but, if you wanted to use a DataSet, you might change it to this:

Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem",
                                      connection)
Private data As New DataSet
 
Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", connection)
 
    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
 
    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
    adapter.DeleteCommand = delete
    adapter.InsertCommand = insert
    adapter.UpdateCommand = update
 
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
 
Private Sub GetData()
    'Retrieve the data.
    adapter.Fill(data, "StockItem")
 
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub
 
Private Sub SaveData()
    'Save the changes.
    adapter.Update(data, "StockItem")
End Sub

If you want to bind the DataTable to a DataGridView then it might look like this:

StockItemGrid.DataSource = table

while, with a DataSet, it might look like this:

StockItemGrid.DataMember = "StockItem"
StockItemGrid.DataSource = data

or this:

StockItemGrid.DataSource = data.Tables("StockItem")