Web.Config Connection String Settings in .NET 2.0

We have been doing a lot of asp.net 2.0 development lately. Here is how we are setting up the web.config file to hold our connection settings. First, set your web.config file Connection String settings as follows:

In your data access layer classes set the sqlconnection object to the web.config connection string thru the constructor. For VB.NET that is the class’s New() function. In C# it is the function named the same as the class. Below are both examples or download the C# version or VB.NET version directly.

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI

Public Class DataAccess

Dim conn As New SqlConnection
Dim sql As String
Dim objSql As SqlCommand
Dim MyDataReader As SqlDataReader

Public Sub New()
If ConfigurationManager.ConnectionStrings("YourConnectionString") Is Nothing OrElse _
ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString.Trim() = "" Then
Throw New Exception("Connection Error")
Else
conn.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString
End If

End Sub

Public Function GetMSRP(ByVal productid As Int32) As String

Dim strUnitPrice As String = Nothing
sql = "SELECT UnitPrice FROM Products WHERE productid = @productid"
‘Set SQL OBJECT
objSql = New SqlCommand(sql, conn)
‘Add Parameters
objSql.Parameters.AddWithValue("@productid", productid)

Try
‘Open Connection
conn.Open()
‘Execute DataReader
MyDataReader = objSql.ExecuteReader
‘Store Values in String Variables
While (MyDataReader.Read())
strUnitPrice = MyDataReader.Item("UnitPrice")
End While
‘Close Connection
conn.Close()
Catch ex As Exception
‘TODO HANDLE EX
End Try
Return strUnitPrice
End Function

End Class

################### c# version

using Microsoft.VisualBasic;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
public class DataAccess {

    private SqlConnection conn = new SqlConnection();

    private string sql;

    private SqlCommand objSql;

    private SqlDataReader MyDataReader;

    public DataAccess() {
        if (((ConfigurationManager.ConnectionStrings("YourConnectionString") == null)
                    || _)) {
            ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString.Trim() = "";
            throw new Exception("Connection Error");
        }
        else {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString").ConnectionString;
        }
    }

    public string GetMSRP(Int32 productid) {
        string strUnitPrice = null;
        sql = "SELECT UnitPrice FROM Products WHERE productid = @productid";
        objSql = new SqlCommand(sql, conn);
        // Add Parameters
        objSql.Parameters.AddWithValue("@productid", productid);
        try {
            // Open Connection
            conn.Open();
            // Execute DataReader
            MyDataReader = objSql.ExecuteReader;
            // Store Values in String Variables
            while (MyDataReader.Read()) {
                strUnitPrice = MyDataReader.Item["UnitPrice"];
            }
            // Close Connection
            conn.Close();
        }
        catch (Exception ex) {
            // TODO HANDLE EX
        }
        return strUnitPrice;
    }
}