Interacting with Databases - Namespaces and Classes

(Page 2 of 4 )

ADO.NET contains sets of classes designed to interact with a specific DBMS. Each set is identified by a namespace. A namespace organizes classes in a hierarchy of classes to prevent naming conflicts. This sounds a little strange, but you won’t give the term namespace a second thought once you begin to use it in your application.

The most important point to understand is that you must import into your application the namespace that corresponds to the DBMS that is accessed by your application. Here are commonly used namespaces:

• System.Data.SqlClient  Used for Microsoft SQL Server version 7.0 or higher
  
• System.Data.OleDb  Used for OLE DB DMBSs such as Microsoft Access
  
• System.Data.Odbc  Used for ODBC driver-based DBMSs. ODBC is used in Windows to connect to many popular DBMSs.
  
• System.Data.OracleClient  Used for the Oracle database server

Throughout this chapter, we’ll be showing examples that use System.Data .SqlClient to interact with the Microsoft SQL Server and System.Data.OleDb used to interact with Microsoft Access. Techniques used in these examples are similar to the way you use the other namespaces to interact with other DBMSs.

Although each namespace refers to different classes, there are similarities among them. For example, SqlConnection is used to open a DBMS connection using the System.Data.SqlClient namespace. OleDbConnection performs the same task when using the System.Data.OleDb namespace.

Likewise, SqlCommand is used to send a query to the DBMS in the System .Data.SqlClient namespace. OleDbCommand does the same using the System.Data .OleDb namespace.

Opening a Connection to a DBMS

Your application must open a connection to the DBMS before sending or requesting data from the DBMS. Think of a DBMS connection as the same as a telephone connection. Before you can talk to your friend, you must dial your friend’s telephone number and wait for her to answer. You talk as long as you want once the connection is made, and you close the connection after you are through, enabling someone else to connect to your friend.

To create a connection to the DBMS, follow these steps:

1. Import the namespace  This identifies the set of classes that you’ll be using within your application to interact with the database. Developers import the namespace so that they don’t have to write the fully qualified class name, which is much longer than if the namespace is imported.
   
2. Create an instance of the connection class Remember from Chapter 2 that a class definition describes a class much as a stencil describes a letter of the alphabet. You create a real object described by the class by creating an instance of the class. This is similar to using the stencil to create a real letter of the alphabet.
   
3. Open the connection  You do this by calling an appropriate instance function of the instance of the class.

Let’s see how this is done by creating a connection to Microsoft SQL Server. The initial step is to import that namespace. The namespace for Microsoft SQL Server is System.Data.SqlClient. We import that namespace by using the following page directive at the beginning of the ASP.NET web page.

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

The next step is to create an instance of the connection class. First, declare a variable that references the instance, and second, create the instance and assign it to the variable as shown here:

Dim conMyDb As SqlConnection
conMyDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=mydatabase")

We need to create an instance of the SqlConnection class and pass the constructor of the SqlConnection class information it needs to link to the DBMS. The constructor creates the instance of a class. There are three pieces of information that you must provide.

The first is the location of the server that contains the DBMS. This is the URL of the server or localhost if the DBMS resides on your computer. For this example, we’re assuming that you have the database on your local computer.

The next two pieces of information are needed to log onto the DBMS. These are user ID (uid) and the password (pwd). These are assigned by directly interacting with the DBMS. In a business environment, the database administrator is the person who assigns logon information to everyone.

The last piece of information is the name of the database. You’ll remember from Chapter 2 that the DBMS maintains many databases, each having its own unique name. You must identify the database that you want to link to by assigning the database name to the database parameter when you create an instance of the SqlConnection class.

The SqlConnection constructor returns a reference to the instance to the variable. You then use the variable to access functions and attributes of the class. One of the first of these is called the Open() function, which opens the database connection as shown here:

conMyDb.Open()

Now let’s put these statements together to create an ASP.NET web page that accesses the customer database. We’ll show two examples. The first is for Microsoft SQL Server, and the second is for Microsoft Access. Notice that the connection to the database is made in the Page_Load subroutine. Each time the page is loaded, the ASP.NET engine establishes a connection with the database.

<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
   Sub Page_Load
      Dim custDb As SqlConnection
      custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
      custDb.Open()
   End Sub
</Script>

Here is the example for linking to Microsoft Access. You’ll notice a few differences between this example and the preceding example. The first is that we’re importing the SystemData.OleDb namespace, which enables us to use the OleDb classes that are needed to interact with Microsoft Access.

Another difference is in the OleDbConnection constructor. Notice that there are two parameters. The first is Provider, which is the name of the DBMS. OleDB is the provider of Microsoft.Jet.OLEDB.4.0, which Microsoft Access is associated with. The second parameter is DataSource, which is the location and name of the database. In this example the database is located on the C: drive and is called cust.mdb.

The other parts of the example are identical to the preceding example.

<%@ Import Namespace="System.Data.OleDb" %>
<Script Runat="Server">
   Sub Page_Load ( s As Object, e As EventArgs )
      Dim custDb as OleDbConnection
      custDb = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:cust.mdb" )
      custDb.Open()
   End Sub
</Script>

Next: Creating a Database and Tables >>
 

More ASP.NET Articles
More By McGraw-Hill/Osborne

Comments On: Interacting with Databases

· This article is an excerpt from the book "ASP.NET 2.0 DeMYSTiFieD," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 10 of ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412). Check it out today at your favorite bookstore. Buy this book now.