Managed Connections | Programming Data-Driven Web Applications with ASP.NET

only for RuBoard

Much like classic ADO, the OleDb and SQL Managed Connection objects ( OleDbConnection and SqlConnection ) provide a set of properties that might 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 might 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; Data Source=C:  \Program Files\MicrosoftOffice\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\MicrosoftOffice\Office\Samples\Northwind.mdb;"); 07:   myConnection.Open(); 08:   ConnectionState.Text = myConnection.State.ToString(); 09:   myConnection.Close(); 10:  } 11: </script> [VB & C#] 12: <html> 13: <body> 14: <form runat="server" method="post"> 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 2. On line 5, you declare a variable for the OleDbConnection class and on line 6, 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 7 you open the connection with the Open() method of the OleDbConnection class. On line 8 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 might see an error indicating that Access can not find the installable 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> [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> [VB & C#] 12: <html> 13: <body> 14: <form runat="server" method="post"> 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 2 and 6. On line 2 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 6 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.

only for RuBoard