170 |
Saturday Evening |
time-consuming process. Connection pooling is effectively a method of improving performance when a Web server is regularly accessed.
Creating a Connection
With ADO, ADO.NET’s predecessor, you, as the developer, could create Connection objects explicitly or through another object such as a Command object. That is no longer the case. With ADO.NET you must explicitly open your connections using one of its constructors.
Some developers might be taken aback by this feature, however, we recommend that developers create their connection objects explicitly for two reasons: (1) the code is easier to maintain and (2) connection pooling can be utilized.
In order to use the OLEDB Managed Provider objects, such as OleDbConnection, OleDbCommand, and so on, you need to include the OleDb namespace in your ASP.NET page. To do this, you use the Import construct at the top of your ASP.NET page:
<%@ Import Namespace=”System.Data.OleDb” %>
For all you Visual Basic programmers out there, using a namespace is like adding a Reference to your project.
Note
Table 17-1 lists the various namespaces you are likely to use in order to access the ADO.NET objects.
Table 17-1 Namespaces Used to Access ADO.NET Objects
Namespace |
Contains |
System.Data |
ADO.NET base objects |
|
|
System.Data.OleDb |
Managed OLDDB data store objects |
|
|
System.Data.SqlClient |
SQL Server-specific implementation |
|
of the ADO.NET objects |
|
|
System.Data.SqlTypes |
SQL Server data types |
|
|
Now you’re ready to create your connection object. In the following code, we’ve used VB to create a connection object called oConn:
<%@ Import Namespace=”System.Data.OleDb” %> <script language=”VB” runat=”server”>
Dim oConn As New OleDbConnection </script>
The ADO.NET Connection objects, OleDb and Sql, have several constructors. A constructor is essentially the syntax you use to instantiate an object. Any given object can have several constructors, like the Connection objects, or no constructors, like the DataReader objects,
Session 17—Opening a Connection |
171 |
which need to be instantiated or created by another object. In the previous code snippet, we demonstrated the use of the OleDbConnection’s default constructor that does not accept any parameters. The Connection objects have another constructor that accepts a connection string as its only parameter.
If you think back to your telephone analogy, what we have done thus far is pick up the telephone. Now that you have picked up the phone, it’s time to make a call or open a connection.
Opening a Connection
In order to open a connection to a data source, you need to know a little about the database. Kind of like making a phone call, you need to have a phone number. When opening a connection, you need to supply several pieces of information depending on the Relational Database Management System (RDBMS) you are using. Some of these pieces of information could be server name or IP address, database name, user name, and password. With this crucial information, you will construct a connection string that is effectively a collection of name/value pairs, separated by semicolons, which tell the Connection object how to connect to your database. The information that you use to construct your connection string will vary depending on the type of database to which you are trying to connect. Table 17-2 lists some of the most common parameters you will use to build a connection string.
Table 17-2 Parameters Used to Construct a Connection String
Parameter |
Description |
Provider |
The OLEDB provider used to access the database. |
|
|
Data Source |
The IP address or name of the server on which the database resides. |
|
|
Database |
The name of the database to be used once the connection is open. |
|
|
User ID |
The user ID for the account used to access the database. |
|
|
Password |
The password for the account used to access the database. |
|
|
All of the parameters listed in Table 17-2 are optional with the exception of the Provider parameter.
Here is a snippet of code that opens, using the OleDbConnection object’s Open method, a connection to a SQL Server database named “Music” that is located on my local machine:
<%@ Page LANGUAGE=”VB”%>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDB” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OleDBConnection
Dim sConnString As String
sConnString = “Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Music;User ID=music;Password=music”
172 |
Saturday Evening |
oConn = New OleDBConnection(sConnString) oConn.Open()
oConn.Close() End Sub </SCRIPT> <HTML>
<BODY>
Opening a Connection! </BODY>
</HTML>
Note
In the previous code listing, the connection string, sConnString, contains a user ID/password combination. You may need to alter the user ID/password combination to get the code to run successfully on your computer.
We constructed a string, sConnString, that we passed to the connection’s Open() method. The Open() method in turn parses this string and attempts to open a connection to the database. The Open() method is equivalent to an operator in our telephone analogy. The operator takes the phone number you dial and attempts to place the call. The connection string contains several very important pieces of information including Provider, Data Source, Initial Catalog, User ID, and Password. The Provider specifies which method you are using to connect to the database, the Data Source indicates on which server your database resides, the Initial Catalog represents the name of the database you are attempting to access, and the User ID and Password are the credentials you need to pass to the RDBMS in order to gain access. The OleDbConnection object actually has a read-only property for each of these pieces of information. So, you can easily gather a connection’s settings using these properties as shown in Listing 17-1.
Listing 17-1 Inspecting a Connection’s Properties
<%@ Page Language=”VB” debug=”true” %> <%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As New OleDbConnection
Dim sConnString As String
sConnString = “Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=Music;User ID=music;Password=music”
With oConn
.ConnectionString = sConnString
.Open()
Response.Write(.Provider & “<BR>”)
Response.Write(.DataSource & “<BR>”)
.Close()
End With
End Sub
Session 17—Opening a Connection |
173 |
</SCRIPT>
<HTML>
<BODY>
Opening a Connection! </BODY>
</HTML>
Let’s talk about the Provider property for a second. There are actually several ways to connect to a database in the Microsoft world. You can use OLEDB or you can use ODBC. What’s the difference? The major difference, from a developer’s viewpoint, is performance. OLEDB communicates directly with the data source whereas ODBC, in most instances, must go through the OLEDB provider for ODBC in order to access the data source. So, OLEDB provides a little better performance.
In the previous examples, we used OLEDB. When accessing a database via OLEDB, a data source-specific provider needs to be specified via the Provider property. Because, in our examples, we were accessing a SQL Server database, we used the SQLOLEDB provider. To obtain a provider for the data source you need to access, try contacting the product’s vendor or Microsoft. The most common providers, for example Oracle, SQL Server, and Microsoft Access, are readily available.
Connecting to a database via ODBC and ADO is very simple. You simply create a data source name (DSN) on the server from which you are accessing the data source and then add a “DSN=value” name/value pair to your connection string.
Creating a DSN is fairly straightforward and well documented. For details see www.15seconds.com or www.4guysfromrolla.com.
Note
However, it’s not quite as simple with ADO.NET. You cannot connect to a database via ODBC directly with the OleDbConnection or SqlConnection objects. As a matter of fact, you can’t use the SqlConnection to a database via ODBC at all. The .NET SQL Server data provider uses its own protocol to communicate with SQL Server therefore it does not support the use of an ODBC DSN because it does not add an ODBC layer. You can use the .NET OleDb data provider in conjunction with a Universal DataLink (UDL) file to connect to a database via ODBC; however, this is not the approach we recommend. To this point, we have neglected to mention a third .NET data provider, ODBC. To access the ODBC data provider, use the following namespace:
System.Data.ODBC
You may need to download the .NET update that includes the ODBC data provider from www.microsoft.com.
Note
Suppose you’ve created a DSN to a Music database, named “Bands.” To access the database via ODBC, you would use the following code: