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:

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:

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:

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.