priyachaudhary
New member
Can anyone explain the DataReader and data set with examples
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
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.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
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
DataTable
to a DataGridView
then it might look like this:StockItemGrid.DataSource = table
DataSet
, it might look like this:StockItemGrid.DataMember = "StockItem"
StockItemGrid.DataSource = data
StockItemGrid.DataSource = data.Tables("StockItem")