| 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. NOTE 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.
WARNING 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: -
In the Solution Explorer window, select Global.asax. -
Right-click and select View Code from the context menu. -
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. | 
