MyGeekProject

In my recent post Asynchronous DataBinding with ASP.NET I eluded to the fact of connecting to Sql Server with .NET.  Since sometimes I have spent time looking for the simplest things when working on a project I figured I would take a moment and walk you through setting up Sql Server Connection strings in ASP.NET.

Step 1:  Define your connection string in your web.config file.

1. Open web.config in your web project
2. Locate the following section <appSettings/>
3. REPLACE <appSettings/> with the following

<appSettings>

<add key=”connString” value=”server=YOURSERVER\YOURSQLSERVERINSTANCE;uid=SQLSERVERUSERID;pwd=SQLSERVERPASSWORD;database=DATABASENAME”/>

</appSettings>

Ok, so now you have a setting in your web.config file with the name of “connString” that you can access from anywhere in your application and if it changes you just change it one time and it is a global change.  You can also define multiple database connection strings here so you can reference multiple db’s.

Step 2: Reference the Connection String from your code behind file

1. You need to import the System.Configuration.ConfigurationManager library, do that as follows

VB.NET

Imports System.Configuration.ConfigurationManager
Imports System.Data
Imports System.Data.SqlClient

C#.NET

using System.Configuration.ConfigurationManager;
using System.Data;
using System.Data.SqlClient;

Step 3: Establish as Sql Connection {so we are just going to declare a new SqlConnection that can be used by a DataAdapter, DataReader, etc.}

VB.NET

Dim con as New SqlConnection(AppSettings(“connString”))

C#.NET

SqlConnection con = new SqlConnection(AppSettings(“connString”));

Ok great, now we can use this Sql Connection throughout our application to interact with our sql server.

I will do another post that shows the advantages of connecting through TCP/IP instead of named pipes and the slight differences that we use in order to make that connection string work.