Providing ADO.NET Connection Information


ASP.NET Developer's JumpStart
By Paul D. Sheriff, Ken Getz
Table of Contents
Chapter 13.  ADO.NET Connection and Command Objects

In order to create a connection to a data source, you'll need to supply a string containing a series of name/value pairs, providing the information that ADO.NET requires in order to locate and use the data. For example, if you want to connect to a SQL Server database using the System.Data.OleDb namespace, you might use a connection string like this:

 Provider=sqloledb;Data Source=(local); Initial Catalog=Northwind;User ID=sa;Password=; 

Alternatively, to connect to a Microsoft Access (Jet) database, you might use a connection string like this:

 Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Northwind.mdb 

If you're using SQL Server with integrated security, you won't want to pass in the user ID and password in the connection string. Instead, you can modify the connection string so that it indicates to SQL Server that you want to use its integrated security:

 Provider=sqloledb;Data Source=(local); Initial Catalog=Northwind;Integrated Security=SSPI; 

Connection strings you might use when accessing SQL Server directly, using the System.Data.SqlClient namespace, are slightly different. See the online help for the SqlConnection object for more information on creating the appropriate connection string.

In an earlier chapter, you created a class named DataHandler that included a ConnectStringBuilder procedure, shown in Listing 13.1.

Listing 13.1 You'll Use This Procedure in Many Examples, Creating a Connection String Based on the Supplied User ID and Password
 Public Shared Function ConnectStringBuild( _  ByVal LoginID As String, _  ByVal Password As String) As String   Dim strConn As String   If LoginID = String.Empty Then     LoginID = "sa"   End If   strConn = String.Format("Provider=sqloledb;" & _    "Data Source=(local);Initial Catalog=Northwind;" & _    "User ID={0};Password={1}", LoginID, Password)   Return strConn End Function 

This procedure accepts as parameters a login ID and a password, and it generates a valid string that you can use with an OleDbConnection object in order to retrieve data from the Northwind sample database in SQL Server.

You could call the ConnectStringBuild procedure from your code every time you need a connection to the data source, but there's no need most likely, the connection information won't change within a given session. You might find it more useful to create the connection string as each session starts and store it in a Session variable from then on. In this sample application, you'll place the connection information into a Session variable named ConnectString.


In Chapter 11, "Data Binding on Web Forms," you used the user-interface data tools provided by Visual Studio .NET in order to bind a DataGrid control to a SQL Server table. Although you can use these tools, we think it's important to work carefully through the steps involved in writing all this code manually. This chapter and future chapters all use code to bind data to controls rather than using the components provided by Visual Studio .NET.


Be careful using Session variables to store important information such as the connection string. Unless you understand the limitations of storing stateful information this way, you may be limiting your application's scalability. You'll find coverage of state management in Chapter 23, "State Management in ASP.NET." Make sure you investigate that information, as well, before using a Session variable in a real application. There are many options for managing state, and one of them will certainly fit your needs.

To modify your application and add support for the session-wide connection information, follow these steps:

  1. In the Solution Explorer window, select Global.asax.

  2. Right-click and select View Code from the context menu.

  3. In the code-behind file, locate the Session_Start procedure. Modify the procedure so that it looks like Listing 13.2.

Listing 13.2 Session_Start Runs Each Time a User Begins a Session
 Sub Session_Start( _  ByVal sender As Object, ByVal e As EventArgs)   Session("LoginID") = String.Empty   Session("Password") = String.Empty   Session("ConnectString") = _    DataHandler.ConnectStringBuild("sa", "") End Sub 

This procedure calls the ConnectStringBuild procedure that you created in an earlier chapter, passing in hard-coded values for the user ID and password. Later chapters will show how you can use a login page to gather this information and validate users before allowing them into your application.


    ASP. NET Developer's JumpStart
    ASP.NET Developers JumpStart
    ISBN: 0672323575
    EAN: 2147483647
    Year: 2002
    Pages: 234 © 2008-2017.
    If you may any questions please contact us: