Introduction to ADO.NET - II

DataReader Object: Provides a forward-only, read-only stream of data from a data source.

Sometimes the application needs to only read the data and not update or write data. Especially in cases where such applications require large amounts of data, then it is wiser to do away with the DataSet because of memory overheads. The DataReader requires very little memory because it just reads data as its name suggests. Also, it reads only one record at a time. For applications using huge read-only data, DataReader is an excellent alternative because it is a read-only and forward-only stream that cuts down significantly on memory requirements.

After creating an instance of the Command object, a DataReader can be created by calling Command.ExecuteReader to retrieve rows from a data source. Assuming a connection as in the previous example is set up; the following code illustrates how to loop through a DataReader. It loops through the publishers’ table in the pubs database and displays pub_id for all the publishers.

Dim dReader As OleDbDataReader 
Set dReader = Nothing
dReader = cmd.ExecuteReader()
Do While dReader.Read
MsgBox(myReader.GetString(1))
End While 

Read method of the DataReader object is used to obtain a row from the results of the query. Columns can be accessed by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32 and so on).

DataSet Component: This component provides in-memory access to relational data. It is a disconnected object, so that once it is filled with data, it will work independently of any other objects and needs no connection to the data source.

The DataSet is independent of any specific data source and therefore can be populated from multiple and differing data sources, including relational databases and XML, or can be populated with data local to the application. Data is stored in a collection of one or more tables. It can be accessed non-sequentially and without limits to availability, unlike ADO in which data must be accessed a single row at a time. A DataSet can contain relationships between tables, similar to the ADO Recordset in which a single result set is created from a JOIN. A DataSet can also contain unique, primary key and foreign key constraints on its tables.

DataSet is similar to a Recordset with CursorLocation = adUseClient, CursorType = adOpenStatic and LockType = adLockOptimistic. However, the DataSet has extended capabilities over the Recordset for managing application data.

Let us look at how to create a new DataSet:

Dim myDataSet As DataSet = New DataSet
Or
Dim myDataSet As DataSet = New DataSet("MyCustomerDataSet")

We can create a new instance of a DataSet just by calling the ‘New’ key word on our DataSet object. You can pass the name of the DataSet as a parameter if you want. If you don't, the default name of New DataSet will be used.

DataAdapter Object: This object populates a DataSet with data from a relational database and resolves changes in the DataSet back to the data source.

The DataAdapter enables you to explicitly specify behavior that the Recordset performs implicitly.

DataAdapter provides the bridge between the DataSet and data source. You control the behavior for populating the DataSet and resolving inserts, updates and deletes in the DataSet back to the data source by defining explicit commands that the DataAdapter will use.

The DataAdapter command properties are SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. Each command corresponds directly to a SELECT, INSERT, UPDATE and DELETE action at the data source. Additionally, these actions can be optimized as a stored procedure call.

Once the DataAdapter commands have been defined, you can pass a DataSet to the Fill method of a DataAdapter to fill a DataSet with the results returned by the SelectCommand, or pass a DataSet to the Update method of a DataAdapter to propagate changes in the DataSet back to the data source. The InsertCommand will process rows that have been added to the DataSet. The UpdateCommand will process existing rows that have been modified in the DataSet. The DeleteCommand will process existing rows that have been deleted from the DataSet.

ADO.NET has two primary data adapters for use with databases:

· OleDbDataAdapter: This object is suitable for use with any data source exposed by an OLEDB provider; and
· SqlDataAdapter: object is specific to SQL Server. As it does not have to go through an OLEDB layer, it is faster than OleDbDataAdapter. However, it can be used only with SQL Server 7.0 or later.

Normally, one DataAdapter is created for one table or data set. If the DataSet consists of more than one table, usually separate DataAdapters are created for each table.

DataTable Object: A DataSet contains a collection of tables as a DataTableCollection. The DataSet stores a reference to this object in its Tables property. Each individual memory resident table is referred to as a DataTable and there could be zero or more tables in the collection. DataTables contain rows of data and each row is made up of columns.

You can create a new DataTable object as follows:

Dim myCustomerTable As DataTable = New DataTable
Or by specifying the name for the DataTable
Dim myCustomerTable As DataTable = New DataTable("CustomersTable")

The first line creates a new table without any table name parameter, so the table name will remain blank. The second line creates a new table and specifies the table name as CustomersTable.

You can add a new table to a DataSet by the Add method of DataSet's Tables property:

Dim myDataSet As DataSet = New DataSet("MyDataSet")
Dim myCustomerTable As DataTable = myDataSet.Tables.Add("CustomersTable")

You can add an existing table to a DataSet by passing a DataTable object:

‘Create a new DataSet called MyDataSet.
Dim myDataSet As DataSet = New DataSet("MyDataSet")

’Create a new table called MyDataTable.
Dim myDataTable As DataSet = New DataTable("CustomersTable")

’Add the existing table to the DataSet.
Dim myCustomerTable As DataTable = myDataSet.Tables.Add(myDataTable)

To remove a table from a DataSet:

myDataSet.Tables.Remove("CustomersTable")

DataColumn Object: 
Before you can add row/s to a table, you must define its schema. This is typically generated when you create a new DataSet by right clicking on the DataAdapter control. The schema defines the structure of the table and, when first created, a table does not have any schema associated with it until you add columns to the table. A column is represented by a DataColumn object.

DataColumn is the key to creating a schema of a DataTable; by adding columns to a DataColumn collection we build a schema. Within our table, we have a collection of columns called DataColumnCollection. Each collection item will refer to each column name in our table. We can access these column names by referring to the Columns property of the DataTable.

To reference a column by name, use the DataTable’s Columns property specifying the column name:

Dim myColumn As DataColumn
myColumn = myCustomerTable.Columns("Address")

Or by index:

Dim myColumn As DataColumn
myColumn = myCustomerTable.Columns(O)

To view the entire column names in the DataColumnCollection, iterate through the collection as you would any other collection:

Dim myDataColumn As DataColumn
For Each myDataColumn in myCustomerTable.Columns
Console.WriteLine(myDataColumn.ColumnName)
Next

An example:
Dim myDataSet As DataSet = New DataSet()

‘Add one new table
myDataSet.Tables.Add("CustomersTable") 

' Reference the new table 
Dim myCustomerTable as DataTable = MyDataSet.Tables(“customersTable”)

‘Add an address column to the new table.
myCustomerTable.Columns.Add("Address", Type.GetType("System.String"))

Primary Keys
You can add a primary key column by setting the AllowDBNull property of the DataColumn to False and Unique property to ‘True’. E.g.:

Dim myColumn As DataColumn = inyCustomerTable.Columns.Add("Address",
Type.GetType(“System.String"))
myColumn.AllowDBNull = False
myColumn.Unique = True

Or you can specify the PrimaryKey property of the table object, which accepts an array of one or more DataColumn objects.

Dim myColumn(l) As DataColumn
myColumn(O) = myCustomerTable.Columns("CustomerID")
myCustomerTable.PrimaryKey = myColumn

DataRow Object:

DataRow and DataColumn objects make up the DataTable. Using these objects' properties and methods, you can view, update, insert and delete information from the tables. DataRow represents the actual data in the table and is contained within a DataRowCollection collection object. Like the DataColumnCollection, we can access multiple items within the collection using standard collection methods.

Rows are represented by a collection within the table called Rows. So, if we want to access the first row of data in a table, we could do so as shown below, using a zero-based index:

myCustomerTable.Rows(O)

To view all the items in DataRow collection, iterate through the collection as follows:

CONST ADDRESS_COLUMN As Integer = 2
Dim myDataRow As DataRow
For Each myDataRow in myCustomerTable.Rows
Console.WriteLine(myDataRow(ADDRESS_COLUM).ToString())
‘alternative method for the above line of code
Console.WriteLine(myDataRow("Address").ToString())
Next 

To add a new row to the collection:

‘create a new row
Dim myRow As DataRow
myRow = myCustomerTable.NewRow()

‘Set the field values of the created row
myRow("Address") = "100 Elm Street"
myRow("City") = "Sacramento"
myRow("PostalCode") = “95825”

‘Add the new row to the collection.

myCustomerTable.Rows.Add(myRow)

To access an individual column, use the Item property of DataRow. We can use an index value, column name (within double quotes) or column object to access the column as shown:

Dim myRowNumber As Integer = 0
Dim myColumnNumber As Integer = 2
myCustomerTable.Rows(myRowNumber).item(myColumnNumber)
myCustomerTable.Rows(myRowNumber).Item("Address")

Or

Dim AddressColumn As DataColumn
AddressColumn = myCustomerTable.Columns(“Address")myCustomerTable.Rows(myRowNumber).Item(AddressColumn)

We can also access the column by using the column name as a parameter of the DataRow we are looking at. This is the preferred method of referencing a row:

Dim myRow as DataRow = myCustomerTable.Rows(0)
MessageBox.Show(myRow("Address”))

Row States
Whenever you perform any type of action like addition, modification, deletion, etc. the corresponding row’s state is changed accordingly. Before updating large sets of records without updating all the rows, you can simply mark out the rows whose state has changed and then update only those rows. Thus, you can save time and resource also.

Typically, the corresponding row is flagged as modified, allowing you to review changes before actually committing them by calling the table's AcceptChanges method. This method is available for DataSet, DataTable and DataRow objects and simply places each altered object's RowState back to the normal unedited value. This will commit all the changes you have made since the last AcceptChanges method was called for the table you are working with. We can also call this method on an individual row as well as an entire table or DataSet.

Dim myRow As DataRow = myCustomerTable.Rows(0)
myRow(“Address”) = “200 RSCM Street”
myCustomerTable.Acceptchanges()

Or

‘save changes for the row individually
myRow.Acceptchanges()
‘save all the changes to the table
myCustomerTable.Acceptchanges()
‘save all changes to the tables in the dataset
myDataSet.Acceptchanges()

Table 1 provides a list of the available RowState values.

Table 1

DataRelation Object:

You can relate one column to another column in a separate table through what is known as DataRelation. All relations within a DataSet are kept in a DataRelationCollection, which maintains all child and parent relations.

If we had two tables, a customer table and an order table, and both contain a CustomerID column, we could relate the two to show the customer's orders along with their other details. The Customer table would be the parent and the Orders table would be the child.

Below, we reference two columns: the first column is the CustomerID field in the Customer table, the second ‘Const’ is also called CustomerID but this is in the Orders table. We assign these to a DataColumn object since that is what a DataRelation object needs to create a relation. The field names do not have to be the same, just as long as the data types are of the same type. Next, we create a DataRelation object by passing in the parent and child DataColumn object as properties. We give this DataRelation the name Customer0rders and we finally call ‘Add’ to add the relationship to the relationship collection of the DataSet.

Dim parentColumn As DataColumn 
Dim childColumn As DataColumn

‘Get Datacolumn objects
parentColumn = MyDataSet.Tables("Customers").Columns("CustomerID")
childColumn = myDataSet.Tables("Orders").Columns(“CustomerID”)

‘Create the DataRelation.
Dim relCustomer0rders As DataRelation
relCustomerorders = New DataRelation(“CustomersOrders”, parentColumn, childColumn)

‘Add the relation to the DataSet
MyDataSet.Relations.Add(relCustomerorders)

Constraints:

Constraints help to enforce data integrity rules and specify what action to take when records are updated or deleted. A table has what is called a ConstraintCollection that can hold two types of constraints, UniqueConstraints and ForeignKeyConstraints.

A customer table usually has a value, the primary key, uniquely identifying each customer. To ensure this stays unique, you assign the field a UniqueConstraint ensuring that any value given for the column is unique. You can turn these constraints on or off by changing the EnforceConstraints property to True or False.

Here is an example of setting up a ForeignKeyConstraint:

Dim myFkey As ForeignKeyConstraint

Dim parentColumn As DataColumn
Dim childColumn As DataColumn

‘Set parent and child column variables.
parentColumn = myDataSet.Tables("Customers").Columns("CustomerID")
childColumn = MyDataSet.Tables("Orders").Columns("CustomerID")

‘Create a new foreign constraint.
myFkey = New ForeignKeyConstraint("CustomerFKConstraint", parentColumn, childColumn)

‘Set Null values when a value is deleted.
myFkey.DeleteRule = Rule.Cascade
myFkey.UpdateRule = Rule.Cascade
myFkey.AcceptRejectRule = AcceptRejectRule.Cascade

‘Add the constraint, and set EnforceConstraints to true. 
myDataSet.Tables("Customers").Constraints.Add(myFkey)
myDataSet.EnforceConstraints = True

The rules available for DeleteRule and UpdateRule properties of a ForeignKeyConstraint are listed in table 2.

Table 2

We can also add a UniqueConstraint to the ConstraintCollection to ensure that the primary key of a column is unique. This process is very similar to setting the primary key, shown earlier.

‘Declare a ConstraintCollection.
Dim myCKey As ConstraintCollection
Dim myColumn As DataColumn

‘Get the column we want to place a unique constraint on.
myColumn = myDataSet.Tables("Customers").Columns("CustomerID")

‘Add the constraint to the constraint collection.
myCKey.Add("MyConstraint", myColumn, True)

‘Add the constraint collection to the table's constraint collection.
myDataSet.Tables("Customers").Constraints.Add(myCKey)

Connection Object:

This opens a connection to the data source. You can either use the OleDbConnection or SqlConnection object. OleDbConnection is used for managed provider for data sources other than SQL Server. SqlConnection is used for data sources belonging to SQL Server.

For the OLE DB provider, you use a Provider, Data Source, User ID and Password in the connection string. For SQL provider, you need the same arguments as for the OLE DB provider, but omit the Provider type (which is always SQLOLEDB). If the connection string for a SQL provider does specify a Provider parameter, an exception is generated.

The connection strings properties are discussed in table 3.

Table 3

To connect to Access Database:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Samples\Northwind.mdb;User ID=;Password=;"

To connect to an Oracle Database:

"Provider=MSDA0RA;Data Source=My0rac1eDB;User ID=myID; Password=myPWD;"

To connect to a SQLServer Database:

"Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Pubs;Password=;User ID=;"

You can set the time before a connection fails to open by modifying your connection string to include a timeout value:
Dim MyConnString As String = "Data Source=localhost; Initial Catalog=NorthwindSQL;User ID=sa;Connect Timeout=30;"

To use a connection, try the following code.
Dim conString As String 
Dim conn As OleDBConnection
ConString “Provider= SQLOLEDB.1;Data Source=xxxx;” & _
“uid=abcd;pwd=abcd;Initial Catalog=pubs;”
Set conn = New OleDbConnection()
Conn.ConnectionString = conString
Conn.Open()

Note: You will have to check the local server, user id and password to execute this.

Updating the Database
Once changes are made to a DataSet, how do we update those changes back to the database? Well, here goes the solution.

Earlier, I had shown how calling the AcceptChanges method updates only local records and not the database. Assume you haven't called AcceptChanges on any of your modified records before you post changes to the server. We have a couple of methods for updating our database. To update a DataSet, call Update method of the DataAdapter. This can take a DataSet, DataTables or an array of DataRow objects, and examines the RowState property to determine which rows have changed. Then Insert, Update or Delete is executed, depending on the state of the changed row.

We can use GetChanges method of a DataSet or DataTable to control which updates we want to occur first. GetChanges returns a DataSet containing changes that match the RowState parameter you specify, thus allowing us to retrieve only records marked as modified, deleted or inserted, as the code below shows:
Dim MyDataSetChanges As New DataSet ()

‘Get all changes
MyDataSetChanges = myCustomerDataSet.GetChanges()

‘Get records that have been modified only
myDataSetChanges = myCustomerDataSet.GetChanges (DataRowState.Modified)
or
‘Get records that have been deleted only
myDataSetChanges = myCustomerDataSet.GetChanges (DataRowState.Deleted)
or
‘Get records that have been added only
myDataSetChanges = myCustomerDataSet.GetChanges (DataRowState.Inserted)

‘Update changes hack to actual database
myDataAdapter.Update (myDataSetChanges)

Well readers, if you have gone through the article, I believe you would have understood the basics of ADO.NET and how it works behind the scenes. However, I recommend that before going any further, you should clear the basic concepts because that will help you later on.

If you have any questions regarding ADO.NET, you can contact me at kunal_mukherjii@yahoo.co.in or kunal_programmer@rediffmail.com. If anyone of you wants a demo sample project, then mail me and I will send the demo to you.



Language: .NET 
Platform: Windows