A database is one or more lists of items. You can create a
database in a normal computer file or you can use a computer environment. For
the Microsoft Windows family of operating systems, there are various
applications used to create databases. These include Microsoft Access, Microsoft
SQL Server, Oracle, MySQL, and many others. Some of these tools don't provide
the ability to create graphical applications. One way you can solve this problem
is to use other means. For example, you can create a web-based application.

A web-based application can be an interactive web page or
web site that allows a visitor to submit values to a web server. The web page is
typically equipped with graphical elements. To
create an application made of graphical user interface (GUI) objects, you can
use a separate environment such as Microsoft Visual Web Developer or Microsoft Visual Studio.
You would then create a project that communicates with the web server, and that
web server would have a database application. Microsoft Visual Studio and
Microsoft Visual Web Developer make it easy to create an ASP.NET application
that uses a Microsoft SQL Server database.

Introduction to ADO.NET

ADO.NET is a group of libraries used to create powerful
databases using various sources that include Microsoft SQL Server, Microsoft
Access, Oracle, Paradox, XML, etc. 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.

While
ADO.NET is the concept of creating and managing database systems, the DataSet
class serves as an intermediary between the database
engine and the user interface, namely the web controls that a visitor uses to
interact with the web page.

Besides using features of a database in an ADO.NET
application, you can also fully take advantage of XML as it is completely and
inherently supported by the DataSet class. To fully support XML in your
application, the .NET Framework is equipped with
the System.Xml.dll library.

Getting Access to ADO.NET Libraries

The classes used to create ADO.NET databases 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).

Connection to a Microsoft SQL Server

Working from the Server

After installing Microsoft SQL Server, you can use it.
To perform an operation on a database server, you must
establish a connection to it. You have various options.
For example, you can start Microsoft SQL Server Management Studio and a Connect To
Server dialog box would come up. Here is an example:

Alternatively, you can use an existing SQL Server account.
To do this, select SQL Server Authentication from the Authentication combo box.
Enter a username in the Login combo box and enter a password in the
corresponding text box:

Once you are ready, click Connect.

Working from Microsoft Visual Studio

ADO.NET allows you to work
with various types of databases. If you are creating an ASP.NET application using
Microsoft Visual Studio, you do not have to first explicitly log on to a
Microsoft SQL Server. You can establish a connection from Microsoft Visual
Studio. To do this, first display the Server Explorer window (on the main menu, you can click View -> Server
Explorer).

To create a
new connection, you can right-click the Data Connections node and click Add
Connection...

The Add Connection dialog box would display. You would have
to make some choice before clicking Continue:

Characteristics of a Database Connection

Introduction

To support a connection to a database server, the .NET
Framework provides some connection classes. If you are going to use a Microsoft
SQL Server database, you can use the SqlConnection class that is defined in the System.Data.SqlClient
namespace. Before using this class, you can first include this namespace in your
file:

To connect to a database, you can first declare a variable
of type
SqlConnection 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 SqlConnection
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 SqlConnection object, you must provide
various pieces of information, packaged as one and made 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:

How you create these attributes depends on the type of
computer you are connecting to, whether you are connecting to a database, what
level of security you would use (or need), etc. There are various of these
attributes, some of them are always required, some of them are usually optional,
and some others depend on the circumstances.

The Source of Data

To establish a connection, you must specify the
computer you are connecting to, that has Microsoft SQL Server installed.
We saw that you could specify this from the Connect to Server dialog box
where you would select the machine object from the Server Name combo box:

If you are programmatically connecting to a computer using the SqlConnection
class, the connection string includes an
attribute named Server, or Data Source, or Address,
or Addr, or Network Address. For the rest of our lessons,
this attribute will be referred to as the computer attribute.

If you are creating your application on the same
computer on which Microsoft SQL Server is installed, the computer attribute can
be identified as (local). Here is an example:

As an option, you can include the name of the computer in
single-quotes.

If you are working from the Add Connection dialog box, you
can type the name of the server without clicking the arrow of the combobox
and press Enter.

Remember that the computer attribute is a requirement
regardless of the (type of) application, even if it is local.

Security

An important aspect of establishing a connection to a
computer is security. Even if you are developing for an intranet, you must take care of this issue.

If you are using the Connect To Server dialog box, after
specifying the server, you can use the Windows Authentication or you can use SQL
Server Authentication which you would select from the Authentication combo box:

To
support security, if you are programmatically establishing the connection, the
connection string of the SqlConnection class includes an attribute
called Trusted_Connection or Integrated Security that can have a value of true, false, yes, no,
or SSPI with the SSPI having the same indication as true.

If you are establishing a
trusted or simple connection that does not need to be verified, you can assign a value of true or SSPI.
Here is an example:

When you use the true or SSPI values, the user name (if any)
and the password (if any) of the person opening your application would be
applied. If you are programmatically establishing the connection, you can assign False or No to the security
attribute you selected. If you do this,
then you must (this becomes a requirement) specify the user name and the password.

The Username

If you are using the Connect to Server dialog box and you want to apply authentication, after
selecting SQL Server Authentication, you must enter a username. To specify the user name, after assigning
False or No to the
security attribute, you must use the User ID attribute and assign it a
valid username. Here is an example:

If you are "physically" connecting to the server
using the Connect to Server dialog box, after selecting SQL Server
Authentication, besides the username, you must provide a
password to complete the authentication:

If you are programmatically establishing the connection,
besides the username, to create a secured connection, you
must also provide a password. To specify the password, you can use either the PASSWORD
or the PWD (remember that the attributes are not case-sensitive (but the
value of the password is))
attribute and assign it the exact password associated with the User ID
attribute of the same connection string. Here is an example:

To let you specify the
database, the connection string includes an attribute named Database. The Database keyword can also be
substituted for the Initial Catalog value. If you are connecting to an existing
database, assign its name to this attribute. If you are not connecting to a
database, you can omit this attribute. Alternatively, you can assign nothing to
this attribute. Here is an example:

As mentioned above, the Database attribute is
optional, especially if you are only connecting to the computer and not to a
specific database.

Additional Attributes

There are various other attributes used in the connection
string. They include Network Library (also called Net), Application
Name, Workstation ID, Encrypt, Connection Timeout, Data
Source, Packet Size, AttachDBFilename, Current Language,
Persist Security Info.

After creating the connection string, when the application
executes, the compiler would "scan" the string to validate each key=value
section. If it finds an unknown Key, an unknown value, or an invalid combination
of key=value, it would throw an ArgumentException exception and
the connection cannot be established.

Opening and Closing a Connection

Using an Existing Connection

When working in Microsoft Visual Studio, if you create a
connection using the Server Explorer, a link to the database would display as a
sub-node of the Data Connection link. You can re-use one of these connections
when necessary.

Opening a Connection

If you are "physically" connecting to a server or
a database using the Microsoft SQL Server Management Studio,
the steps we have described so far allow you to open the connection.

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

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

If the
connection fails, the compiler would throw a SqlException 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. If you are working in Microsoft SQL Server Management Studio, to close the
connection, you can simply close the window as an application.

If you are working in Microsoft Visual Studio, to
close a connection, you can right-click it in the Server Explorer and click Close
Connection.

If you are working from a SqlConnection object, to
close a connection, you can call the Close() method of the SqlConnection
class. Its
syntax is:

Public Sub Close

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

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

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 SqlConnection.Close() method, which
means you do not need to remember to close it.

Commanding a Database

Introduction

We have learned different ways of
connecting to a server. After establishing a connection, if you are successful, the
database system becomes available to you and you can take actions, such as
creating a database and/or manipulating data. 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 SQL
Server database, the System.Data.SqlClient namespace provides the SqlCommand class. To use
it, you can declare a variable of type SqlCommand using one of its
constructors.

The Text to Command

The SqlCommand 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 statement. This action is represented by the CommandText
property of the SqlCommand
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 SqlConnection
object. To provide it to the command, the SqlCommand class is equipped
with a property named Connection that is of type SqlConnection. After
creating a SqlConnection object, to provide it to the command, you can assign it
to the SqlCommand.Connection property. This would be done as follows:

Instead of declaring a SqlCommand variable and the
command text separately, as an alternative, you can define the command text when
declaring the SqlCommand variable. To do this, you can use the
second constructor of the SqlCommand 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 SqlConnection
object to the Connection property of your SqlCommand. Here is an
example:

Instead of assigning a SqlConnection object to the SqlCommand.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 SqlCommand
variable, you can use the third constructor of this class. Its syntax is:

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

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 SqlCommand class, you can assign a SqlConnection
object to the Connection property of the SqlCommand class.

Command Execution

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

Public Overrides Function ExecuteNonQuery As Integer

This method does not take any argument. The SqlCommand
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 SqlCommand.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 SqlCommand variable.

The Type of Command

To allow you to specify the type of command
you want to perform, the SqlCommand 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 SqlCommand 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 SqlDataReader. To get a data reader, you can declare a
variable of type SqlDataReader.
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 SqlCommand 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 SqlCommand.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.