Introduction to ADO.NET

• Data is stored in Recordset - a data structure that is stored in memory and needs a connection to the database throughout the lifetime of the Recordset. This exhausts connections on the database. Though this problem is solved using disconnected recordsets and RDS (Remote Data Services), it involves extra programming.

• Establishing relationship between multiple tables is difficult. Each table should have its own Recordset and after taking a row from master table (first recordset), we have to search for its child records in child table (second recordset). Otherwise we have to join both the tables and put the joined data into the recordset. The first approach involves more programming effort and later approach needs more space in memory as master record is stored along with each child record.

• Doesn’t support XML (Extensible Markup Language) - the new way of storing data. Instead programmers have to use DOM(Document Object Model) to access and manipulate the data. That means one has to learn the API of DOM separately.

• Data is transmitted across machines using COM marshalling. This supports the data types that are defined in COM standard and in some cases it requires that existing data to be converted to the standard data type. This will also have problem in Internet, as Firewalls may not permit low-level data transfer.

Let us now see what is new in ADO.NET. I will just give you some idea about new features now, only to give a feel of what is to come. I will get into all the details in this article and in the remaining articles.

Scalability

That means the connection time to database is kept to absolute minimum. This is an important development since connections to the database is a bottleneck. This process allows more number of clients to connect to database thus increasing scalability (the ability to scale application up/down) of the application. That also means though more number of clients are accessing the database, the number of connections at any given time will not significantly increase and thus enabling system provide more scalability.

Performance

ADO.NET supports Dataset, which is a mini database in memory. It supports constraints and relationship between tables in memory. This enables master-detail relationship between tables in memory.

XML support is built-in

Right now only two managed providers are available.

SQL Server .NET Data Provider – this is used to access SQL Server. The classes related to this provider are provides through SYSTEM.DATA.SQLCLIENT namespace.

OLE DB .NET Data Provider – is to internally access OLE DB provider. We have to use OLE DB.NET Data Provider until we get a provider that directly accesses the database. This is used in cases where a true provider is not available. This is similar to the concept of OLE DB provider for ODBC drivers. This is provided through SYSTEM.DATA.OLEDB namespace.

All the programs are written as console programs in VB.NET. I am using .NET Beta 2. If you like, you can make use of any text editor such as Notepad or you can even use Visual Studio.NET for the same.

The following is a simple program to access MS Access database – PHONES.MDB.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module OLEDBConExample
    
    Public Sub Main()
        
       dim  constr as string = "provider=Microsoft.jet.oledb.4.0;Data source=c:\srikanth\phones.mdb"

       dim con  as New OLEDBConnection(constr)

        
        Try
             

            con.Open()

            console.WriteLine("Connection is established")
            
        Catch ex As Exception

            console.WriteLine(ex.Message)

        Finally

            ' close connection if it is open
            If con.State <> ConnectionState.Closed Then
                console.WriteLine("Closing the connection")
                con.Close()
            End If
            
        End Try
        
        
    End Sub
    
End Module

c:\srikanth>vbc /r:system.dll /r:system.data.dll  /r:system.xml.dll oledbconexample.vb

In the above example we are using OLE DB .NET Data Provider to access MS Access database. The actual data access is done through OLE DB provider for MS Access.

The following are the important steps in the above example.

1. First we imported the required namespaces. If you don’t import namespaces then you have to explicitly qualify each class of the namespace. For example, without importing namespace SYSTEM you can still use Console class as System.Console. As it turns out to be length, it is better you import the namespaces.

   
   ' import required namespaces
   
   Imports system
   Imports System.data
   Imports system.data.oledb
   
   

2. Then we created an object of OLEDBConnection class. We passed the connection string as parameter to its constructor. If you decide to use SQL Server then you must be SQLConnection class. I will show you how to access SQL Server in the next example.

   
          dim  constr as string = "provider=Microsoft.jet.oledb.4.0;Data source=c:\srikanth\phones.mdb"
   
          dim con  as New OLEDBConnection(constr)
   
   

3. Open method of the OLEDBConnection class is used to open connection. If this results in error then catch will catch the exception. Catch block is designed to catch all exception. Catch block displays the error message using Message property of Exception class.

4. If connection is established then we display a message and close the connection using Close method of OLEDBConnection class.

c:\srikanth>OLEDBCONExample
Connection is established
Closing the connection

C:\srikanth>OLEDBCONExample
Could not find file 'c:\srikanth\phones.md'.

' import required namespaces

Imports system
Imports system.data.SQLClient   ' SQL managed provider


Module SQLConExample
    
    Public Sub Main()
        
        Dim con As New SQLConnection()
        
        Try
            
            con.ConnectionString = "server=localhost;uid=sa;pwd=;database=pubs"
            con.Open()
            console.WriteLine("Connection is established")
            con.Close()

        Catch ex As Exception

            console.WriteLine(ex.Message)

        End Try
        
        
    End Sub
    
End Module

It uses SYSTEM.DATA.SQLCLIENT namespace, which contains SQL Server .NET Data Provider. It used SQLConnection class is used instead of OLEDBConnection class. ConnectionString is different as we now access SQL Server.

Method	Meaning
BeginTransaction()	Begins a database transaction and returns an object of OLEDBTransaction class
Close()	Closes the connection to the database.
Open()	Opens a database connection.
CreateCommand()	Creates an object of OLEDBCommand class and returns it
ChangeDatabase(string)	Allows the current database to be changed for the current connection.

Note: Please consult .NET documentation for SQLConnection and OLEDBConnection to get comprehensive documentation on these classes.

That's all for now. In the next articles I will discuss more about how to retrieve data using ADO.NET.

P.Srikanth.