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;
}
}
Subscribe for mcitp training and learn about different ccie tools and tips for web configuration using ccnp programming tutorials.
Written by Tim on February 13th, 2006 with
6 comments.
Read more articles on asp.net.
- [+] Digg: Feature this article
- [+] Del.icio.us: Bookmark this article
- [+] Furl: Bookmark this article
#1. March 28th, 2006, at 11:25 PM.
poker casino poker 813