With ADO, ADO.NET’s predecessor, you, as the developer, could create Connectionobjects explicitly or through another object such as a Commandobject. 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 devel- opers 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 Importconstruct 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.
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, OleDband 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,
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 Connectionobjects 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 con- nection.
Opening a Connection
In order to open a connection to a data source, you need to know a little about the data- base. 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 cru- cial information, you will construct a connection string that is effectively a collection of name/value pairs, separated by semicolons, which tell the Connectionobject how to con- nect 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 Providerparameter.
Here is a snippet of code that opens, using the OleDbConnectionobject’s Openmethod, 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”
oConn = New OleDBConnection(sConnString) oConn.Open() oConn.Close() End Sub </SCRIPT> <HTML> <BODY> Opening a Connection! </BODY> </HTML>
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 connec- tion string contains several very important pieces of information including Provider, Data Source, Initial Catalog, User ID, and Password. The Providerspecifies which method you are using to connect to the database, the Data Sourceindicates on which server your database resides, the Initial Catalogrepresents the name of the database you are attempting to access, and the User IDand Passwordare the credentials you need to pass to the RDBMS in order to gain access. The OleDbConnectionobject 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 Note
</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 pro- vides 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 Providerproperty. 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.comor www.4guysfromrolla.com.
However, it’s not quite as simple with ADO.NET. You cannot connect to a database via ODBC directly with the OleDbConnectionor SqlConnectionobjects. As a matter of fact, you can’t use the SqlConnectionto 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.
Suppose you’ve created a DSN to a Music database, named “Bands.” To access the data- base via ODBC, you would use the following code:
Note Note
<%@ Page LANGUAGE=”VB”%>
<%@ Import Namespace=”System.Data” %> <%@ Import Namespace=”System.Data.ODBC” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OdbcConnection
Dim sConnString As String sConnString = “DSN=Bands”
oConn = New OdbcConnection(sConnString) oConn.Open() oConn.Close() End Sub </SCRIPT> <HTML> <BODY>
Opening a Connection with ODBC! </BODY>
</HTML>
You’ll notice that the code is very similar to the previous OLEDB examples. Actually, the only difference is how you construct the connection string. Instead of providing all of the database information, such as location, Data Source, and name, Database, you simply supply a DSN name/value pair. You’ve also already provided the User IDor Passwordwhen you created the DSN.
You’ll notice in all of the examples, we call a Closemethod. This method closes the connection that we have opened. Always close connections!!!!! We can’t emphasize this enough. When you close the connection, it is returned to the connection pool and can be used by another request.
Now that the connection is open, what can we do with it? Well, to be quite honest not too terribly much. With the ADO.NET connection objects you can’t directly execute SQL com- mands against a data source like you could with ADO. In order to create, update, delete, or retrieve data from our data source, you must utilize other ADO.NET objects such as the DataReader and DataAdapter objects, both of which we’ll discuss in Sessions 19 and 20, respectively. The connection does, however, provide several properties and methods to man- age transactions.