ADO.NET in ASP.NET With Microsoft Access: Introduction

	Introduction to ADO.NET

Fundamentals of ADO.NET and Microsoft Access

Introduction

A database is a list or many lists of values. You can create such a database using Microsoft Access. You can then make that database available to your web site.

A web-based application is a web site that allows the visitors to create and submit values. To make this possible, a web page on the web site is equipped with graphical objects called web controls.

After creating the values, the visitor can click a button. The values are then sent to the database server.

Getting the Database Ready

Obviously, in order to use a database on your web site, you must first create it (the database), which you do in Microsoft Access. Then, if you are planning to create your ASP.NET application using either Microsoft Visual Studio or Microsoft Visual Web Developer, start your programming environment and create the project.

After creating the database, you can either put it in a folder where your application can easily find it, or import it. If you are working in Microsoft Visual Studio or Microsoft Visual Web Developer, when you start an ASP.NET project, the studio automatically creates a sub-folder named App_Data. Use a file utility, such as Windows Explorer, to copy the database. Then paste it in the App_Data sub-folder of your project.

If you are creating your ASP.NET application using Microsoft Visual Studio or Microsoft Visual Web Developer, you can first create a data source. To do this, display the form that will host it. From the Data section of the Toolbox, drag AccessDataSource and drop it on the form. After doing this, you must configure the data source. To do this, on the form, click the newly added data source, click its button, and click Configure Data Source... The Configure Data Source dialog box would come up:

Configure Data Source

You would click the Browse button. In the Select Microsoft Access Database, in the Project Folders list view, click the folder where you had stored your Microsoft Access database. In the Contents of Folder list, click the database file:

Select Microsoft Access Database

After selecting the database, click OK. Back in the Configure Data Source dialog box, click Next. In the next wizard page, you would have to make some sections:

Configure Data Source

In other lessons, we will explain what all those options are. After making the selections, you can click Next, then click Finish.

Referencing the OLEDB Namespace

ADO.NET is a group of libraries used to create powerful databases using various sources that such as Microsoft Access. ADO.NET relies on the .NET Framework's various classes to process requests and perform the transition between a database system and the user. The operations are typically handled through the DataSet class.

The classes used in ADO.NET are defined in the System.Data namespace and are stored in the System.Data.dll library.

If you are creating the application from scratch, in order to use ADO.NET, you must remember to reference the System.Data.dll library and add the System.Data namespace in your file(s). To do this:

If you are working in Microsoft Visual Studio or Microsoft Visual Web Developer, in the top section of the file, you can type Imports System.Data
```
Imports System.Data.OleDb

Partial Class Customers

    Inherits System.Web.UI.Page

    End Sub

End Class
```

If you are using a text editor, in the top section of the file, type
<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<html>
<head>

<title>Exercise</title>

</head>
<body>

</body>
</html>

Establishing a Connection to a Microsoft Access Database

Creating a Connection Object

In order for a visitor to communicate with your database, you must establish a connection from a form on your web page to your database. To support a connection to a database server, the .NET Framework provides the OleDbConnection class that is defined in the System.Data.OleDb namespace. Before using this class, we have seen that you can first include this namespace in your file.

To connect to a database, you can first declare a variable of type OleDbConnection using one of its two constructors. The default constructor allows you to declare the variable without specifying how the connection would be carried. The second constructor takes as argument a string value. Its syntax is:

Public Sub New(connectionString As String)

You can create the necessary (but appropriate) string in this constructor when declaring the variable. This would be done as follows:

Imports System.Data.OleDb

Partial Class Customers
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim connection As OleDbConnection = New OleDbConnection("Something")
    End Sub
    End Sub
End Class

If you want, you can first create the string that would be used to handle the connection, then pass that string to this construction. This would be done as follows:

Imports System.Data.OleDb

Partial Class Customers
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim strConnection As String = "Something"
    Dim connection As OleDbConnection = New OleDbConnection(strConnection)
    End Sub
End Class

To support the connection as an object, the OleDbConnection class is equipped with a property called ConnectionString that is a String. If you use the default constructor to prepare the connection, you can first define a String value, then assign it to this property. This would be done as follows:

Imports System.Data.OleDb

Partial Class Customers
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim strConnection As String = "Something"
    Dim connection As OleDbConnection = New OleDbConnection

    connection.ConnectionString = strConnection
    End Sub
End Class

Introduction to the Attributes of a Connection String

To use a OleDbConnection object, you must provide various pieces of information, packaged as one and made them available to the variable. These pieces are joined into a string but are separated from each other with a semi-colon ";". Each piece appears as a Key=Value format. In our lesson, we will refer to each of these pieces (Key=Value) as an attribute of the connection string. When joined, these attributes appear as follows:

Key1=Value1;Key2=Value2;Key_n=Value_n

Anything that is part of this string is not case-sensitive. This whole ensemble is either passed as a string to the second constructor:

Imports System.Data.OleDb

Partial Class Customers
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim connection As OleDbConnection = _
        New OleDbConnection("Key1=Value1;Key2=Value2;Key_n=Value_n")
    End Sub
End Class

or assigned as a string to the OleDbConnection.ConnectionString property:

Imports System.Data.OleDb

Partial Class Customers
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim strConnection As String = "Key1=Value1;Key2=Value2;Key_n=Value_n"
    Dim connection As OleDbConnection = New OleDbConnection

    connection.ConnectionString = strConnection
    End Sub
End Class

Some of the attributes are always required, some of them are usually optional, and some others depend on the circumstances.

The Provider of a Connection

One of the expressions you can specify in the connection string is the name of the provider. To support this, the OleDbConnection class is equipped with a read-only property named Provider. To specify the provider, in the connection string, type Provider= followed by the provider you are using. For most databases we will create or use here, the provider will be Microsoft.JET.OLEDB.4.0. This means that our connection string can start with:

<%@ Import Namespace="System.Data.OleDb" %>

<html>
<head>

<script language="vb" type="text/vbsscript" runat="server">

Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; ..."
        Dim connection As OleDbConnection = New OleDbConnection

        connection.ConnectionString = strConnection
End Sub

</script>
<title>Exercise</title>

</head>
<body>

</body>
</html>

You can also include the value of the provider in single-quotes to delimit it.

If a connection exists already to a database, to know its provider, get the value of the OleDbConnection.Provider property.

The Data Source of a Connection

The second part of the connection string specifies the data source. To support it, the OleDbConnection class is equipped with a read-only property named DataSource. To specify the data source, in the connection, you would type Data Source followed by = and followed the name or path of the database. Because a path to a server can be subjective, relative, and even unpredictable, to assist you with this, the Server class is equipped with the MapPath() method. This method takes one argument as the path to the database. Using the Server.MapPath() method, here is an example of specifying the data source of a connection:

<%@ Import Namespace="System.Data.OleDb" %>

<html>
<head>

<script language="vb" type="text/vbsscript" runat="server">

Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim strConnection As String
    Dim connection As OleDbConnection = New OleDbConnection

    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Server.MapPath("exercise.mdb")

    connection.ConnectionString = strConnection
End Sub

</script>
<title>Exercise</title>

</head>
<body>

</body>
</html>

Opening and Closing a Connection

Opening a Connection

After programmatically creating a connection string, to apply it and actually establish the connection, you must call the Open() method of the OleDbConnection class. Its syntax is:

Public Sub Open

Here is an example of calling it:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise2.mdb")

        connection.ConnectionString = strConnection

        connection.Open()
    End Sub
End Class

As you can see, this method does not take any argument. The OleDbConnection object that calls it is responsible to get the connection string ready:

If the connection fails, the compiler would throw a OleDbException exception
If the connection string does not contain the computer attribute or the connection is already opened, the compiler would throw an InvalidOperationException exception.

Closing a Connection

After using a connection and getting the necessary information from it, you should terminate it.

To close a connection an OleDbConnection connection, you can call its Close() method. Its syntax is:

Public Sub Close

This method is simply called to close the current connection. Here is an example of calling it:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise2.mdb")

        connection.ConnectionString = strConnection

        connection.Open()

        ' Blah Blah Blah

        connection.Close()
    End Sub
End Class

While you should avoid calling the Open() method more than once if a connection is already opened, you still can.

You should always remember to close the connection so that the resources that the database application was using can be made available to other applications. Fortunately, the Visual Basic language provides an alternative that can close the connection for it. To assist you with this, you can use the Using operator as follows:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Using connection As OleDbConnection = _
            New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & Server.MapPath("App_Data/exercise2.mdb"))

            connection.Open()

            ' Blah Blah Blah

        End Using
    End Sub
End Class

When this code executes, it opens the connection. Inside of the curly brackets, you can do whatever you want. When the compiler reaches the closing curly bracket, it calls the OleDbConnection.Close() method, which means you do not need to remember to close it.

Commanding a Database

Introduction

After establishing a connection to a database, if you are successful, the database system becomes available to you and you can take actions. An action you perform on the database server or on a database is called a command.

To support the various commands you can perform on a Microsoft Access database, the System.Data.OleDb namespace provides the OleDbCommand class. To use it, you can declare a variable of type OleDbCommand using one of its constructors. Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load

        Dim CommandToExecute As OleDbCommand = New OleDbCommand
        
    End Sub
End Class

The Text to Command

The OleDbCommand class is equipped with four constructors. The default constructor allows you to initiate a command without specifying what action would be taken. The action to perform is created as a string. This action is represented by the CommandText property of the OleDbCommand class, which is of type String.

If you want to use the default constructor, you can then create a string that would carry the action to perform. Once the string is ready, you can assign it the CommandText property. This would be done as follows:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim CommandToExecute As OleDbCommand = New OleDbCommand
        Dim strCommandToExecute As String = "Blah Blah Blah"

        CommandToExecute.CommandText = strCommandToExecute
    End Sub
End Class

After creating the action that would be performed, you must specify what connection would carry it. To do this, you can first create a OleDbConnection object. To provide it to the command, the OleDbCommand class is equipped with a property named Connection that is of type OleDbConnection. After creating a OleDbConnection object, to provide it to the command, you can assign it to the OleDbCommand .Connection property. This would be done as follows:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = New OleDbCommand()
        Dim strCommandToExecute As String = "Blah Blah Blah"

        connection.Open()
        CommandToExecute.Connection = connection
        CommandToExecute.CommandText = strCommandToExecute

        connection.Open()

        ' Blah Blah Blah

        connection.Close()
    End Sub
End Class

Instead of declaring a OleDbCommand variable and the command text separately, as an alternative, you can define the command text when declaring the OleDbCommand variable. To do this, you can use the second constructor of the OleDbCommand class. The syntax of this constructor is:

Public Sub New(cmdText As String)

Once again, after using this constructor, you must specify what connection would carry the action. To do this, you can assign a OleDbConnection object to the Connection property of your OleDbCommand . Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = New OleDbCommand("Blah Blah Blah")

        connection.Open()
        CommandToExecute.Connection = connection
        connection.Close()
    End Sub
End Class

Instead of assigning a OleDbConnection object to the OleDbCommand .Connection property, you can specify what connection would carry the action at the same time you are creating the command. To specify the connection when declaring the OleDbCommand variable, you can use the third constructor of this class. Its syntax is:

Public Sub New(cmdText As String, connection As OleDbConnection)

The second argument to this constructor is an established connection you would have defined. Here is an example:

Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String
        Dim connection As OleDbConnection = New OleDbConnection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & Server.MapPath("App_Data/exercise.mdb")
        connection.ConnectionString = strConnection

        Dim CommandToExecute As OleDbCommand = _
            New OleDbCommand("Blah Blah Blah", connection)

        connection.Open()
        CommandToExecute.Connection = connection
        connection.Close()
    End Sub
End Class

If you had initiated the action using the default constructor of the OleDbCommand class, you can assign a OleDbConnection object to the Connection property of the OleDbCommand class.

Command Execution

After establishing a connection and specifying what command needs to be carried, you can execute it. To support this, the OleDbCommand class is equipped with the ExecuteNonQuery() method. Its syntax is:

Public Overrides Function ExecuteNonQuery As Integer

This method does not take any argument. The OleDbCommand object that calls it must have prepared a valid command.

Well, the Command Timed Out

In some cases, some actions take longer than others to execute. For this type of command, the compiler would keep trying to execute a command until successful. If there is a problem, this operation can take long or too long. You can specify how long the compiler should wait to try executing the command, again.

The OleDbCommand .CommandTimeOut property allows you to specify the time to wait before trying to execute a command. The default value of this property is 30 (seconds). If you want a different value, assign it to your OleDbCommand variable.

The Type of Command

To allow you to specify the type of command you want to perform, the OleDbCommand class is equipped with the CommandType property, which is based on the CommandType enumeration.

The CommandType enumeration has three members: StoredProcedure, TableDirect, and Text. For a OleDbCommand object, the default value is Text.

Reading Data

Introduction

A data command is used to initiate an action to perform on a database. To read data of a database, one of the objects you can use is called a data reader. With a data reader, the compiler reads the first value, then moves to the second value, then moves to the third value, and so on. One of the particularities of a data reader is that, once it visits a value, reads it, and moves to the next value, the compiler cannot refer to the previous value. This can be illustrated as follows:

Data Reader

The SQL Data Reader

To support data readers, the .NET Framework provides, for a Microsoft SQL Server database, a class named OleDbDataReader. To get a data reader, you can declare a variable of type OleDbDataReader. This class does not have a constructor. This means that, to use it, you must (directly) specify where it would read its data.

To provide data to the reader, the OleDbCommand class is equipped with the ExecuteReader() method that is overloaded with two versions. The simplest version of this method uses the following syntax:

Public Function ExecuteReader As SqlDataReader

Before using a data reader, you should first create a command that would specify how data would be acquired. Once the data is read, you can pass it to the data reader by assigning the result of a call to a OleDbCommand .ExecuteReader() method to a SqlDataReader object.

Using a SQL Data Reader

Once data is supplied to the reader, you can access it, one value at a time, from top to bottom. To access data that the reader acquired, you can call its Read() method whose syntax is:

Public Overrides Function Read As Boolean

As mentioned already, the Read() method simply reads a value and moves on. When reading the values, the data reader reads one value at a time and moves to the next.