Introduction to ADO.NET

A database is a list-based application as a collection of information destined to make that information easy to view and exploit. The word database primarily suggests a fancy and powerful programming environment used to create such an application. With today's requirements, the expectation is certainly justified. Consequently, there are many software packages used to create database applications. On this site, we will use MSDE.

ADO.NET is a group of libraries used to create powerful databases using various sources that include Microsoft SQL Server, MSDE, Microsoft Access, Oracle, 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, as we have introduced it so far, serves as an intermediary between the database engine and the user interface. Still, remember that a DataSet object is used to manage lists, any lists, not just those created using database environments such as Microsoft SQL Server or Microsoft Access.

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

To be as flexible as possible, ADO.NET allows you to work with various types of databases. This also means that when you create an application, you must certainly first specify the type of database system you would be using. Before performing any database operation, you must first connect to the database server.

To support a connection to a database, the .NET Framework provides 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:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()

    End Sub

End Module

To connect to a database, you can first declare a SqlConnection variable 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(ByVal connectionString As String)

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

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()
        Dim strConnection As String = ""
        Dim conSQL As SqlConnection = New SqlConnection(strConnection)
    End Sub

End Module

You can also directly create the necessary (but appropriate) string in the second constructor when declaring the variable:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()
        Dim conSQL As SqlConnection = New SqlConnection("")
    End Sub

End Module

If you prefer to use the default constructor, you can first define a string value. To pass it to the default constructor, the SqlConnection class is equipped with a property called ConnectionString that is of type string. You would use it as follows:

Imports System
Imports System.Data.SqlClient

Module Exercise

    Public Sub Main()
        Dim strConnection As String = ""
        Dim conServer As SqlConnection = New SqlConnection

        conServer.ConnectionString = strConnection
    End Sub

End Module

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=Value1Key2=Value2Key_n=Value_n

Anything that is part of this string is not case-sensitive. Remember that 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 the 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 or MSDE installed. When establishing a a connection 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.

If you are creating an application used to connect to a computer on which the application resides, the computer can be identified as (local). Here is an example:

Dim conDatabase As SqlConnection = New SqlConnection("Server=(local) ")

If you know the name of the computer, you can assign it to the computer attribute. In the same way, if you are connecting to a specific computer, you must provide its name. Here is an example:

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 an application that would be used on a standalone computer, you must take care of this issue. The security referred to in this attribute has to do with the connection, not how to protect your database.

To support security, 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 doesn't 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. For example, if the application is being opened on Windows XP Home Edition (that, by default, doesn't require authentication), Windows 2000 Professional that has a default user name and password, or Windows XP Professional that has a default user name and password, the application would be opened fine without checking security.

To apply authentication, 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. For example, the following code will produce an error if you execute it:

So, if you set the security attribute to false or no, then you must provide login credentials. Notice that in some cases, you can provide empty credentials or a blank password.

To specify the user name used in a connection, 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:

The Password

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

In some circumstances, you can use an empty password in which case you would assign an empty string to the password attribute.

Microsoft SQL Server (including MSDE) ships with various ready-made databases you can work with. To let you specify the database, the connection string includes an attribute named Database. The Database attribute allows you to specify the name of the database you are connecting to, if any. The Database keyword can also be substituted for the Initial Catalog. 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, 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.

Operations on a Database Connection

Opening a Connection

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

Public Overridable Sub Open() Implements IDbConnection.Open

As you can see, this method doesn't 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 connection string doesn't 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, you can call the SqlConnection.Close() method. Its syntax is:

Public Overridable Sub Close() Implements IDbConnection.Close

This method is simply called to close the current connection. 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.