This chapter is from the book
This chapter is from the book
This chapter is from the book
Managed Connections
Much like classic ADO, the OleDb and SQL Managed Connection objects provide a set of properties that may be familiar to you. These are listed in Table 3.1. Properties that apply to only one of the Managed Providers are indicated.
Table 3.1 Managed Connection Properties
|
Property |
Description |
|
ConnectionString |
Gets or sets the string used to open a data store. |
|
ConnectionTimeout |
Gets or sets the time to wait while establishing a connection before terminating the attempt and generating an error. |
|
Container |
Returns the IContainer that contains the object. |
|
Database |
Gets or sets the name of the current database or the database to be used once a connection is open. |
|
DataSource |
Gets or sets the name of the database to connect to. |
|
PacketSize (SqlConnection only) |
Gets the size of the packets the data is transferred in. |
|
Provider (OleDbConnection only) |
Gets or sets the name of the OLEDB provider. |
|
ServerVersion (SqlConnection only) |
Gets a string containing the version of the connected SQL Server. |
|
Site |
Gets or sets the site of the component. |
|
State |
Gets the current state of the connection. |
OleDbConnection
The OleDb Managed Provider uses a ConnectionString property format identical to that of a classic ADO connection object. Listing 3.1 shows how to connect to an Access 2000 database using the OleDbConnection object.
Warning
In the following code listing the OleDb Managed Connection object is pointing to an Access 2000 database file using the path, C:\Program Files\ Microsoft Office\Office\Samples\Northwind.mdb. This is the default path to the Northwind sample database that is installed when Access 2000 is installed. The path on your machine may vary. Alter the code as necessary.
Listing 3.1 Connecting to an Access 2000 Database with the OleDbConnection
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data.OleDb" %>
03: <script runat="server">
04: Sub Page_Load(Sender As Object, E As EventArgs)
05: Dim myConnection As OleDbConnection
06: myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
[ic:cccc] Data Source=C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;")
07: myConnection.Open()
08: ConnectionState.Text = myConnection.State.ToString()
09: myConnection.Close()
10: End Sub
11: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data.OleDb" %>
03: <script runat="server">
04: void Page_Load(Object sender, EventArgs e){
05: OleDbConnection myConnection;
06: myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\\Program Files\\Microsoft
Office\\Office\\Samples\\Northwind.mdb;");
07: myConnection.Open();
08: ConnectionState.Text = myConnection.State.ToString();
09: myConnection.Close();
10: }
11: </script>
[VB & C#]
12: <html>
13: <form runat="server" method="post">
14: <body>
15: Connection State: <asp:Label runat="server" id="ConnectionState" />
16: </form>
17: </body>
18: </html>
In Listing 3.1, you import the System.Data.OleDb namespace on line 02. On line 05, you declare a variable for the OleDbConnection class and on line 06, you instantiate the OleDbConnection class, passing in the ConnectionString as the connection's only parameter. The ConnectionString property specifies that the OLEDB Provider is Microsoft. Jet.OLEDB.4.0, the provider necessary to connect to an Access 2000 database.
On line 07 you open the connection with the Open() method of the OleDbConnection class. On line 08 you set the Text property of an ASP.NET Label to the string representation of the State property of the OleDbConnection class.
Warning
In the C# example in Listing 3.1 you will notice that the ConnectionString property of the Managed Connection object uses a double slash (\\) between the tree hierarchy of the path to the Northwind sample database file. This is because C# treats the slash (\) as an escape character in a string. Using a double slash (\\) lets the compiler know that you really want to use a slash character in that spot.
Note
If you are using Access 2000 with user name and password security, you may see an error indicating that Access can not find the instalable ISAM. This error is related to your Access 2000 installation, and not the .NET Framework. For more information, see http://support.microsoft.com/ support/kb/articles/Q209/8/05.ASP
SqlConnection
The SQL Managed Provider uses a ConnectionString property format that's similar to that of a classic ADO connection object. Since you know what the database application is from using the SQL Managed Provider, the Provider property isn't required (it isn't even allowed, for that matter). Listing 3.2 shows sample code for connecting to a Microsoft SQL Server database using the SqlConnection object.
Listing 3.2 Connecting to a SQL Server Database with the SqlConnection
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: Sub Page_Load(Sender As Object, E As EventArgs)
05: Dim myConnection As SqlConnection
06: myConnection = New SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;")
07: myConnection.Open()
08: ConnectionState.Text = myConnection.State.ToString()
09: myConnection.Close()
10: End Sub
11: </script>
12: <html>
13: <form runat="server" method="post">
14: <body>
15: Connection State: <asp:Label runat="server" id="ConnectionState" />
16: </form>
17: </body>
18: </html>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: void Page_Load(Object sender, EventArgs e){
05: SqlConnection myConnection;
06: myConnection = new SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;");
07: myConnection.Open();
08: ConnectionState.Text = myConnection.State.ToString();
09: myConnection.Close();
10: }
11: </script>
12: <html>
13: <form runat="server" method="post">
14: <body>
15: Connection State: <asp:Label runat="server" id="ConnectionState" />
16: </form>
17: </body>
18: </html>
In Listing 3.2 you create a connection to a SQL Server database. The code in Listing 3.2 is nearly identical to that of Listing 3.1. The only two differences are on lines 02 and 06. On line 02 you import the System.Data.SqlClient namespace rather than the System.Data.OleDb namespace. This allows you access to the SQL Managed Provider classes, like the SqlConnection class. On line 06 you create an instance of the SqlConnection class and pass in the ConnectionString property as the only parameter. In the ConnectionString property you do not specify a provider since the SqlConnection is designed to connect only to a Microsoft SQL Server database.