By: John Kilgo
' By: John Kilgo
' Date: November 28, 2002
' CodeBehind: ConnString.aspx.vb
' Purpose: Demonstrates using Web.Config to hold a connection string
' and reading that string from a program
%>
<%@ Page Inherits="ConnString" Src="ConnString.aspx.vb" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>ConnString</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<BODY MS_POSITIONING="GridLayout">
<form id="Form1" runat="server">
<asp:DataGrid id="dataGrid"
AutoGenerateColumns="False"
runat="server">
<Columns>
<asp:BoundColumn HeaderText="Company Name" DataField="CompanyName" />
<asp:BoundColumn HeaderText="Contact Name" DataField="ContactName" />
<asp:BoundColumn HeaderText="Contact Title" DataField="ContactTitle" />
</Columns>
</asp:DataGrid>
</form>
</BODY>
</HTML>
Web.Config (shown after the .vb code) contains a key-value pair which specifies a key of "ConnectionString" with a value which is the connection string. We set a string variable ("strConnection") equal to ConfigurationSettings.AppSettings("ConnectionString"). We have now captured the connection string for use in our program.
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class ConnString : Inherits Page
Protected dataGrid As DataGrid
Protected Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConnection As String
Try
'Get connection string from Web.Config
strConnection = ConfigurationSettings.AppSettings("ConnectionString")
sqlConn = New SqlConnection(strConnection)
sqlCmd = New SqlCommand("SELECT * FROM Customers WHERE " _
& "(CompanyName LIKE 'A%') OR (CompanyName LIKE 'B%')", sqlConn)
sqlConn.Open()
dataGrid.DataSource = sqlCmd.ExecuteReader()
dataGrid.DataBind()
Catch ex As Exception
Response.Write(ex.ToString & "<br>")
Finally
sqlConn.Close()
End Try
End Sub
End Class
<!-- Web.Config Configuration File -->
<configuration>
<appSettings>
<add key="ConnectionString"
value="server=localhost;database=Northwind;uid=sa;password=secret;" />
</appSettings>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>