When I started my search on the .NET Platform from Microsoft a few months ago I was pretty confused about the whole idea of .NET, what exactly it is. For any Visual Basic programmer the most basic thing and for most of us the most important thing to run any of our applications is Database Access and yes it means a lot to an experienced VB programmer.

When I use the word confused the very idea of using it is that within a short span of 3-4 years Visual Basic has seen itself go so fast that we don't even remember when we started using DAO's, RDO's, ADO's and Microsoft is back again with another dish in the bowl which is served with the name ADO.NET.

Coming back to the first data access model that was called Data Access Objects (DAO) created for local databases like MS-Access with the built in Jet Engine. Needless to mention you could have used it to access any Server Databases but the functionality and the performance were issues.

The next in the line was the Remote Data Objects (RDO) and then DO which were both designed for Client Server and Distributed Architecture but it was not too late when ADO completely took over and RDO was not seen for a long time.

And now for the .NET framework ADO has been redesigned and enhanced for use on the Internet and distributed architectures. The question every developer will have at this point will be "hey come on isn't ADO sufficient to give me quite a lot?" On this question, an experienced VB or ASP developer might answer "it is a good architecture, but has issues when you work on distributed architectures and that too of the Internet" e.g. one major problem with ADO is that a recordset that is a COM object cannot be easily passed around the network, they cannot penetrate firewalls.

I will be writing a series of articles on ADO.NET and how we can leverage the power of this little complex but a wonderful technology so as to make our applications more scalable, robust and of course you can never forget the performance of the applications. The.NET era of application development ADO.NET is catered with large number of objects to carry very specific functional tasks and they solve the problems that were present with ADO.

ADO.NET can be used for accessing many kinds of data sources. In ADO data was accessed with OLEDB providers, which can be written for any kinds of data, stores such as flat files and of course the RDBMS. We are aware of how XML makes things easy and ADO.NET leverages the power of XML by converting all the data into XML and then using it for all Database related operations.

ADO.NET provides two kinds of Objects or if I am not wrong we can call them providers. One of which is OLEDB and the other is the SQL server. If SQL server is your database then ADO.Net provides you with the set of objects that bypass the OLEDB provider and directly access the SQL server tabular data stream. This direct access to SQL server's data stream increases the applications performance. If you are using the MS Access, Oracle, FoxPro etc you have with you completely a different set of classes that encompass the same functionality as SQL server optimized objects but through an OLEDB provider.

To access the ADO.NET classes you have to import some namespaces that hold the objects, which are required to carry out our database level operations.

The namespaces that are required are

System.Data

This namespace holds all the objects that are required to access and store data in any kind of a RDBMS. Dataset, DataReader and DataRelation are a named few that are used to create the relation data stores in the memory

In the code window they will be imported as

Imports System.Data

• DataSet
• DataTable
• DataRow
• DataRelation

Replacing Recordsets

Microsoft in ADO.NET no longer supports the good old recordset class. No worry, they still exist for backward compatibility but their use for future developments is discouraged if for few server side operations which will be still superior with recordset class. Recordset has been replaced with what we call DataSets, which is a collection of mini recordsets and relations between them. Datasets are just local copies of the relational data that lies on the server and is used for local processing, whether on a client, Web server or a remote PC. All operations are carried on the local copy of the data with virtually no connection to the database at all.

This reminds us the advantage of disconnected recordsets. When all the operations get completed on the local data the whole bunch is submitted to the original database for a commit.

In addition to Datasets there is another class that is available in ADO.NET, this is called the DataReader and seen as read only recordset with a forward only cursor.

Imports System.Data.SQLClient

Any kind of manipulation with our favorite database the SQL server use the set of objects in this class

• SQLConnection - Represents the connection to the SQL Server Database for DataSet object
• SQLCommand - Used to execute the T-SQL commands such as stored procedures, functions etc
• SqlCommandBuilder - Automatically generates the commands in SQL Adapter
• SQLDataSet
• SQLDataReader - Returns read only, forward only cursor
• SQLDataAdapter - Used to map data from SQL server to a dataset for DML operations
• SqlParameter - Similar to parameter objects in ADO.Represents a parameter to SqlCommand

Imports System.Data.OleDb

Contains the objects that will help us accessing the data through OLEDB provider. These objets have the same properties and methods as the SQLClient space

• OleDbConnection
• OleDbCommand
• OleDbDataSet
• OleDbDataReader
• OleDbDataAdapter

OleDb and the SQLClient namespaces are built almost alike as you can just see from the names the commonality between them.

I will give below a very simple example of using a managed Provider (oledb and SQL Providers are both known as the managed providers in the ADO.NET architecture)

Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Sub Main()

        Dim connSql As SqlConnection
        Dim cmSql As SqlCommand

        Dim dremp As SqlDataReader
        connSql = New SqlConnection(
            "server=webserver;uid=sa;pwd=;database=license")
        cmSql = New SqlCommand("procename", connSql)
        cmSql.commandtype = CommandType.StoredProcedure
        Try
            connSql.Open()
            dremp = cmSql.ExecuteReader()
            Do While dremp.Read()
                Console.WriteLine(dremp("ename").ToString())
            Loop
        Catch err As SqlException
            Console.WriteLine(err.ToString())
        Finally
            connSql.Close()
        End Try

    End Sub

End Module

I hope the above piece of code works fine with your machine if you have a license database on the SQL server called Web server and create a procedure called procname that selects just names of the employees from the emp table.

In my next article I will be covering how to read your data into a VB.Net page using a Data grid and some more features of the ADO.NET and how we can pull the data into an ASP.NET page with almost no changes to our code in VB.NET.