All You Need to Know about ADO.NET: Part 2/2
 
Published: 08 Oct 2003
Unedited - Community Contributed
Abstract
In this, the final part of the two part series on ADO.NET, we will talk about the purpose of ADO.NET, scalability, XML, the ADOCommand and SQLCommand objects and more. If you're just new to .NET then the ADO.NET tutorial series will have you up and running with databases in a short time.
by Devarticles.com
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 90603/ 162

Introduction

In this, the final part of the two part series on ADO.NET, we will talk about the purpose of ADO.NET, scalability, XML, the ADOCommand and SQLCommand objects and more. To read the first part of this article, please click here. If you're just new to .NET then the ADO.NET tutorial series will have you up and running with databases in a short time.
Command Object

A command object is used for executing a query against the database. ADO command objects are equivalent to DataAdapters that hold the actual query. It needs an established connection, it's command type and commandtext properties to be set before calling its Execute method.

The SQLCommand object is analogous to the command object in ADO. We also need a connection for execution. The DataAdapter supports read, add, update and delete operations and for these it supports following properties:
  1. SelectCommand
  2. UpdateCommand
  3. InsertCommand
  4. DeleteCommand

Actually, these properties are objects. To be mores specific, these are instances of the SQLCommand object. Unless otherwise specified, the appropriate objects are generated at runtime automatically. The SelectCommand can be edited at design time while configuring the DataAdapter. The command objects can be executed independently by using their execute methods. Important properties of the object are CommandType and CommandText. If the CommandType property is set to StoredProcedure, then CommandText property should be set to the name of the stored procedure, otherwise the CommandText property contains the actual query.

To see a command working, let's use following piece of code:

Dim sqlSttg As String = "SELECT * FROM Publishers"
Dim cmd As New OleDBCommand (cmd.CommandText = sqlSttg
cmd.ActiveConnection = Conn

[Note] This code assumes that you have a connection to data (Conn open as discussed in the previous example). [End Note]

ADO.NET's Connected Architecture

ADO.NET has been designed fundamentally to be a disconnected and distributed data access technology based on XML, so ADO is still a better approach if you need a continuous connection to the underlying database, although it does requires COM Interop. The DataReader object of ADO.NET comes closest to the RecordSet object of ADO in that it also depends upon a connection to read the data.
The DataReader Object

Sometimes an application is only required to read data and not to update or write the data. Especially if such applications need large amount of data, then it is wiser to do away with the DataSet object because of its memory overheads. The DataReader requires very little memory because it just reads the data as its name suggests. It reads only one record at a time, and for applications using huge read only data, the 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:

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

The code above loops through the publishers table in the pubs database and displays the pub_id value for all of the publishers.

The 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).

Performance Considerations

The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory. There's only row row of data in memory at any time, which thereby reduces the memory requirements. It is obvious that if the application requires sequential, read only data, then the DataReader object will be the best choice from a performance point of view.

We should keep in mind that while the DataReader is open, the connection is used exclusively by it. This may hamper the performance of your applications. To avoid this, the DataReader object should be closed explicitly by using its close method as soon as its job is done.
Comparison to the Fire-Hose Cursor

A cursor is a database element used by ADO to control the record navigation, ability to update data and visibility of changes made to the data by other users. ADO.NET does not have an inherent cursor object, but has data classes that include traditional functionality of the cursors. A forward only and read only cursor is commonly known as a Fire-hose cursor, and the DataReader object provides the functionality of the forward only and read only cursors.
ADO.NET's Disconnected Architecture

We have been continuously stressing that ADO.NET uses a disconnected architecture. With the ever-increasing demand on the Web, data requirements are changing. Now, scalability has become a major issue thanks to the ever growing population of the web. Another aspect is availability of an open connection for the data all of the time. Both these issues require that applications should connect to the data source, read required data and disconnect the connection, process the data, reconnect and save the changes. Using disconnected RecordSets is tedious with ADO due to marshalling issues. All this was leading to disconnected architecture for quite some time now.

It was but natural that Microsoft would introduce this architecture in the latest version of ADO, which is ADO.NET. We have just seen how to use a Connection object, a Command object and a DataReader to perform database operations. This is good enough when you want to process only one row at a time. In practice however, one needs to process or display multiple rows at a time. In such cases, it is difficult to use the DataReader. Therefore, ADO.NET provides other objects for such operations. These objects are mostly use in a disconnected environment.
The DataAdapter Object

Data adapters are an integral part of the ADO.NET managed providers. They are the set of objects used to communicate between a data source and a DataSet. Adapters are used to exchange data between a data source and a DataSet. Mostly, this would mean reading data from a database into a DataSet, and then writing changed data from the DataSet back to the database. The DataAdapter, however, is more capable. It can move data between any source and a DataSet. For example, there could be an adapter that moves data between a Microsoft Exchange server and a DataSet.

Generally, we can configure the DataAdapter so that we can specify what data to move into and out of the DataSet. Often this takes the form of references to SQL statements or stored procedures that are invoked to read or write to a database. ADO.NET has two primary data adapters for use with databases:

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

This means that vendors can create vendor specific DataAdapters that can be faster or more efficient for different types of data sources. Normally, one DataAdapter is created for one table or data set. If the DataSet consists of more than one table, usually a separate DataAdapter is created for each table. For populating a DataSet, a DataAdapter's Fill method is called. For writing the updated data back to the table, the DataAdapter's update method is called. A DataAdapter is created by the DataAdapter configuration wizard or by using the server explorer. A DataAdapter can also be configured manually by using the property window.

The DataSet Object

The DataSet object can be thought of as the heart of ADO.NET's disconnected architecture. The DataSet object is an in memory copy of the data stored by a DataAdapter. The structure of the DataSet is like a relational database. It exposes a hierarchical model of tables, rows and columns. It also contains constraints and relations just like a database. When working with a set of related tables, the DataSet becomes a handy object.

Another important feature of the DataSet is that it uses XML to represent the data.

The structure of the DataSet, its tables, rows, columns, and everything else can be defined as an XML schema. A DataSet can read and write XML schemas using its ReadXmlSchema and WriteXmlSchema methods. XML is used to store and transmit data of all kinds. As XML is an almost universally accepted format, the data can be transported to or received from any platform using a DataSet.

The DataSet is populated with:

  1. A DataAdapter's Fill method
  2. Using the ReadXml method
  3. Manually

The DataSet is not connected to a data source after it is populated. This feature differentiates it from ADO objects. An ADO RecordSet has record pointers while all the records are current in a DataSet. That's why it does not support record navigation methods. Instead of using a record, the DataSet object uses the Rows collection. It can still be manipulated like any collection, however. AS the DataSet is an in-memory database, ADO.NET provides us with methods to build the schema for the DataSet easily.

Building An In-Memory Schema

A DataSet is not directly tied to a data source. It's a temporary storage area for data coming from multiple data sources. Yet, we still can work with it using a consistent object model, regardless of its original source.

Data is Persisted as XML

Data needs to be moved from the data source to the DataSet and from there to various components. In ADO.NET, the format for transferring data is XML. Similarly, if data needs to be persisted (for example, into a file), it is stored as XML. If you have an XML file, you can use it like any data source and create a DataSet out of it.

In ADO.NET, XML is a fundamental format for data. The ADO.NET data API's automatically creates XML files from information in the DataSet and sends them to another component. The second component can invoke similar API's to read the XML back into a DataSet. Basing data protocols around XML offers a number of advantages as we have already seen. For most scenarios, we do not need to know XML in order to use data in ADO.NET. ADO.NET automatically converts data into and out of XML as needed; you interact with the data using ordinary programming methods.
Schemas Define Data Structures

Although most of the time we don't need to know about XML or how to read or write the XML data, there are some times when that is precisely what we look for. These are the situations when we have to design data while the application is running. To put it another way, in ADO.NET we can use XML directly when we are working with metadata.

Datasets are represented as XML. The structure of the DataSet, the definition of tables, columns, data types, constraints, and so on are in the DataSet which is defined using an XML schema based on the XML schema definition language (XSD). Just as data contained by a DataSet can be loaded from and serialized as XML, the structure of the DataSet can be loaded from and serialized as XML Schema.

Schema

For most of the work done with data in ADO.NET, you don't have to delve deeply into schemas. When you use the tools to create a DataSet representing tables in your database, Visual Studio .NET generates an XML Schema describing the structure of the DataSet. The XML Schema is then used to generate a typed DataSet, in which data elements (tables, columns, and so on) are available as first-class members.

However, there are times when we have to create or edit schemas ourselves. A typical example is developing a schema in conjunction with a partner or client. One will often have to map the schema elements to the structure of one's own database. VB provides us with visual tools for working with XML files/schemas/ADO.NET Datasets. It's called the XML Designer. The XML Designer is used to create and edit XML Schema files, ADO.NET Datasets, XML data files, and XML source code. The XML Designer has three views.
DataTable Objects

We use RecordSet objects as tables with ADO. Each RecordSet object corresponds to a database table. With Datasets, a similar function is handled by the DataTable object. A DataTable object is like a database table. It belongs to the datatables collection of the DataSet. If the DataSet contains data from multiple tables, one DataTable corresponds to one table or view. This way, the DataSet can mimic the structure of its underlying database. When we say that the DataSet is a container for data, we must keep in mind that the data is actually contained in the DataTable objects. In other words, a DataTable is an in memory cache of the data from one table. A DataTable can be instantiated just like any other object in VB.NET with the "New" keyword.
DataRelation Object

Many a time an application has to work with related data. A DataSet does not have a database's inherent capacity to maintain relations, so we have to create a DataRelation object that can maintain these relations.

If we continue with the Northwind example, a DataSet might have some data from the Customers table and the Orders table. Even if the tables contain a key in common (CustomerID), the DataSet itself does not keep track of the records in one table that relate to those in another. You can create a DataRelation object that references the parent and child tables (and their keys), and then use the object to work with the related tables.

The DataRelation object can perform two functions:

  1. It can provide either with the child record or with the parent record depending upon what we are working with.
  2. It can enforce constraints for referential integrity, such as deleting orphaned records.
It is important to understand the difference between a true join and the function of a DataRelation object. In a true join, records are taken from parent and child tables and put into a single, flat recordset. A DataRelation object maintains the synchronization between the related tables and no new recordset is created. Instead, the relation tracks the relationship between tables and keeps parent and child records in synch.
Accessing Related Records

If tables in a DataSet have a logical relationship, a DataRelation object can make the related records in another table available to us. We can create a DataRelation object whose properties are set to reflect the common keys. We can then use the DataRelation object to get related records. Instead of joining the tables, we call the GetChildRows method of a data row in the parent table, passing the DataRelation object that defines the parent/child relationship to it. The method returns an array of related child records.

The following code illustrates a simple example of getting related records. In this example, the array DRelnArray is set to the child records of the first row in the Customers table. This assumes that the relation CustomesrOrders is added to the DataSet:

Dim RowCtr As Integer
Dim DRelnArray() As DataRow

RowCtr = 0
DRelnArray = _ dsCustomersOrders1.Customers(RowCtr).GetChildRows("CustomersOrders")

DataRelation Objects and Constraings

A DataRelation object is also used to create and enforce the following constraints:

  1. A unique constraint, which guarantees that a column in the table contains no duplicates.
  2. A foreign-key constraint, which can be used to maintain referential integrity.
A unique constraint is implemented either by simply setting the Unique property of a data column to true, or by adding an instance of the UniqueConstraint class to the DataRelation object's ParentKeyConstraint. As part of the foreign-key constraint, you can specify referential integrity rules that are applied at three points:

  1. when a parent record is updated
  2. when a parent record is deleted
  3. when a change is accepted or rejected
Handling Updates

Unless the data used by the application is read only, we typically perform some modifications to the data or add to the data before sending it back to the data source or to another process or application. In windows Forms, the data binding architecture takes care of sending changes in the data bound controls to the DataSet. But, when the controls are not data bound, we have to send the changes in the records to the DataSet manually. Each record in a DataSet is represented by a DataRow object. The DataRows collection can be used for updating DataSet. If we update, add or delete existing rows, we will be updating the DataSet.

You can manipulate the collection directly to delete records. However, if you want to send changes to the database or to another process, the DataSet has to maintain change information for deleted rows. Data tables therefore provide special methods for deleting records so this information can be retained. If the DataSet includes constraints, you can temporarily turn constraints off before making any updates, and then turn them back on afterwards.
Updating Records

The following example shows how to update the data in the first two columns of the fifth record of the first table in dataset1, accessing the data values with the tables, rows, and items (columns) collection indices:

[Note] The table in the example is Customers from Northwind. Field number 0 is customer ID and field number 4 is Company Name [End Note]

dataset1.Tables(0).Rows(4).Item(0) = "ABCDE"
dataset1.Tables(0).Rows(4).Item(1) = "My Company"

We can also replace the indices with column names if we like. This code makes the necessary changes in the table.

Record Management

Inserting a record

A blank record is inserted by using the NewRow method. The new row inherits all of the columns from the concerned table's DataColumnCollection collection:

Dim anyRow as DataRow = ExistingTable.NewRow

The code shown above is an example of adding a row. A row can be added using the add method of the DataRowCollection. After the new blank record is added, it is treated like an existing record and updated in the same way.

Deleting a record

A record is deleted using the Delete method of the DataRow object:

DsCustomers1.Customers.Rows(0).Delete()

Merging two Datasets

You can merge two Datasets by using Merge method of the DataSet object.

Typed vs. Untyped Datasets

A DataSet can be Typed or Untyped. The difference between the two lies in the fact that a Typed DataSet has a schema and an Untyped DataSet does not have one. It should be noted that the Typed Datasets have more support in Visual studio. A typed dataset gives us easier access to the contents of the table through strongly typed programming that uses information from the underlying data schema. A typed DataSet has a reference to an XML schema file:

Dim s As String
s = dsCustomersOrders1.Customers(0).CustomerID

In contrast, if we are working with an untyped DataSet, the equivalent code looks like this:

Dim s As String
s = CType(dsCustomersOrders1.Tables("Customers").Rows(0).Item("CustomerID"), String)

As the syntax is much simpler and more practical, using typed Datasets is much more handy.

XML Integration

Extensible Markup Language (XML) provides a format for describing data. This facilitates more precise declarations of content and more meaningful search results across multiple platforms. In addition, XML enables the separation of presentation from data, just like in HTML where we use tags to tell the browser how to format the data. In XML, we use tags only to describe the data, such as CustomerID, CompanyName, etc. XML separates the data from the presentation and the process, enabling us to display and process the data as we wish by applying different style sheets and applications to it.

XML is at the core of many features of Visual Studio .NET and the .NET Framework. XML plays a large role in the way data is handled in Visual Studio .NET. XML is used in the following ways:

  1. Datasets can serialize their data as XML. The schema of a DataSet is defined using an XML Schema (.xsd file).
  2. We can infer XML Schemas from existing Datasets. We can also create Datasets from existing XML Schemas.
Visual Studio makes working with XML files, XML Schemas, and ADO.NET Datasets easy by providing us with a visual tool, the XML Designer.
The XML Designer

The XML designer provides a set of visual tools for working with XML Schemas, ADO.NET Datasets, and XML documents. It supports the XML Schema Definition (XSD) language defined by the World Wide Web Consortium (WC3).

To display the designer, add a DataSet, XML Schema, or XML file to your project or open any of the file types listed below.

[Note] You can not use undo while working with XML Designer. [End Note]

  1. Schema: For visually creating and modifying XML Schemas and ADO.NET datasets.
  2. Data: For visually modifying XML data files in a structured data grid.
  3. XML: For editing XML; the source editor provides color-coding and IntelliSense, including Complete Word and List Members.
Designing Datasets

A DataSet is derived from an XML schema. An XML schema can:

  1. Describe 'shape' of the DataSet. In other words, it tells if the data is related or hierarchical.
  2. Validate the data being imported.
  3. Establish the relational structure of database tables.
Let's create a sample DataSet with the XML designer. We will assume that we are creating a DataSet for a new table called MyTable:

  1. Open a windows project.
  2. Select Add New Item from File menu.
  3. Select a DataSet and give it some name and then click open.
  4. Select the XML tools tab and drag an element on the designer.
  5. In place of 'Element1' type the name of the table (MyTable).
  6. Go to the next row in the element. Click the dropdown in the 1st column and select 'Attribute'.
  7. In the second column type the name of the attribute and select attribute type from the third column.
  8. You can add all the columns you need. You can also drag 'Key' from the toolbox and define a key for your table.
  9. Save the schema.
  10. Now, if you want another table, just follow the same steps over.
Examining the XSD

As we have seen, the DataSet generated is saved as an .xsd file. If we open the xsd file in a text editor, we see the following HTML like tags, Though we need not know the XML in depth, the marked portion can easily tell us the details about our table name, column names and Key:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="DsXML" targetNamespace="http://tempuri.org/DsXML.xsd" elementFormDefault="qualified" attributeFormDefault="qualified" xmlns="http://tempuri.org/DsXML.xsd" xmlns:mstns="http://tempuri.org/DsXML.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="DsXML" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="MyTable">
<xs:complexType>
<xs:sequence></xs:sequence>
<xs:attribute name="LastName" type="xs:string" />
<xs:attribute name="FirstName" type="xs:string" />
<xs:attribute name="MyID" type="xs:long" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="DsXMLKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:MyTable" />
<xs:field xpath="@mstns:MyID" />
</xs:key>
</xs:element>
</xs:schema>
</td>
Passing Datasets

AS ADO.NET uses XML as its default format, passing the DataSet between two applications or two components becomes very easy. To transmit an ADO RecordSet, COM marshalling has to be used while ADO.NET uses an XML stream. In general, we can say that the following are the benefits of using XML as a native data format over COM marshalling.
XML as the Native Language

COM marshalling supports only a limited set of data types whereas there's no such restriction in XML format.

AS XML does not require data type conversion, it gives a distinct performance advantage over ADO, which requires data type conversion.

Firewalls are created to let HTML text pass, but to stop any system level requests from accessing the server. As XML is a text based protocol, it is able to pass through firewalls.
Conclusion

In this article we've learnt that ADO.NET is the latest data access technology that uses disconnected paradigm and XML, we've learnt about various features of data access in VB.NET, how to create a data form with the help of a wizard and looking at bound control, that the dataSet is at the heart of the ADO.NET and there are many ways to generate it, that data is stored in DataTable objects of a DataSet and much more.

Hopefully you now understand everything there is to know about ADO.NET and you can begin adding database functionality to your .NET applications!


User Comments

Title: disconnected architecture   
Name: kinjalpadashala
Date: 2012-02-14 3:19:39 AM
Comment:
p/z help me how to select the data in grid and bind of textbox.
Title: thank you   
Name: kartik
Date: 2011-03-24 1:04:35 AM
Comment:
is it posibile to insert the record enter at the 1st row of database every time?
Title: Ado.net Connected Architecture   
Name: Vidhya Patel, India(mota)
Date: 2011-03-08 11:27:53 PM
Comment:
I use this content in my Presentation....it's very useful for me....... so thanks a lot
Title: ado.net   
Name: Manimegalai
Date: 2010-09-15 3:31:43 AM
Comment:
That is great collection
thank u;;;;;;;;;;;;;;
Title: Thanks   
Name: Amin Anwar
Date: 2009-05-14 5:19:09 AM
Comment:
That isgr8 collection;;;
thanks
Title: Ado.net   
Name: Saurabh Shrivastava
Date: 2007-02-20 11:46:46 AM
Comment:
Brilliant Work,It could be more useful by giving some code in it.
Title: Thank you   
Name: Jayalakshmi baskaran
Date: 2006-05-30 5:39:23 AM
Comment:
I want to create the sqldataAdapter and the Dataset in Code
Can you please help it out
Title: Ado.Net   
Name: Rohan Ragoo
Date: 2006-01-09 12:37:59 PM
Comment:
Excellent Overview. All topics are dicussed in an effective timeline.
Title: XML   
Name: Rajesh Medackel,India
Date: 2005-10-01 5:26:30 AM
Comment:
Good write up .... Thanx
Title: ADO.Net   
Name: k
Date: 2005-05-03 11:31:30 AM
Comment:
to the authors thanks a lot for the unselfish information

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 5:16:16 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search