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:

<connectionStrings>
<add name="YourConnectionString" connectionString="Data Source=YourSQLServer;Initial Catalog=YourDatabase;User ID=YourUserID;Password=YourPassword" providerName="System.Data.SqlClient" />
</connectionStrings>

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;
    }
}

Tim

A .NET, PHP, Marketing Guru authority, at least I hope I am. Reach me at tboland@gmail.com

12 thoughts on “Web.Config Connection String Settings in .NET 2.0

  1. Antonio says:

    Thanx,this was very helpfull.

  2. waltman says:

    Thanks — you guys rule!!!

  3. jamil says:

    your sample is good but leaves out few stuff.

    Go to the MyProject page and select References. Click “Add” and go to the .NET tab and select System.Configuration.

    Also, class has following imports
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration

  4. jamil says:

    I am sorry. I could not get your code to work. Keep getting connection could be opened or something like that.

  5. Cybertech says:

    sample of different connection strings

  6. D says:

    There are couple of problems i think with this apporahc
    1. userid and password int config files.

    2. Moving from Development to Qa to prouction someone needs to manually change the values for production system this would be a problem.

    And usually the development group do have the passwords for production databases… is there way to a create a data source and externally and refer to the datasource by name in the config file?

  7. Oliver Kiss says:

    So there were 3 tomatoes OK, a papa tomato, a mama tomato and a baby tomato. One day the 3 tomatoes were walking down the street and baby tomato kept falling behind. After a couple of times, the mama tomato gets upset and smashes the baby tomato and says “Catch Up”.

    Get it?

  8. Jithin Shyam says:

    Thank u ……it is usefull 4 me…..

    I’ll b back again… for new things…

  9. Amala says:

    Its very useful for me thanks

  10. Hi the post is good but please increase the font size.I hope you will.

    Visit my blog also.
    http://dotnetpgm.blogspot.com

  11. Hi its helpful. thanks!
    But, Code text too small.


    http://shashi-bhushan-mca.blogspot.com/

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>