Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

ConnectionStringBuilders in .NET 2.0

By Vasant Raj,

Introduction

A connection string is a basic requirement for any application to perform database operations. It contains the details about the server and the database to connect to, including the authentication mode to be used and various other parameters.

The on-going practice for creating connection strings is to store individual elements [like Server and database name, authentication details, timeout parameters, etc] in the configuration file. By string concatenation, the complete connection string is obtained. Also, storing the entire connection string in the configuration file is followed. Both these methods are being used successfully by most of the application.

While using the first method, you are supposed to manually merge the strings to get the connection string. .NET 2.0 provides various in-built classes which give the correct connection string, if valid inputs are provided.

.NET 2.0 Classes

Classes provided by .NET 2.0 [with their namespace]:
  • SqlConnectionStringBuilder [System.Data. SqlClient]
  • OracleConnectionStringBuilder [System.Data.OracleClient]
  • OdbcConnectionStringBuilder [System.Data.Odbc]
  • OleDbConnectionStringBuilder [System.Data.OleDb]
All thess four classes inherit from the DbConnectionStringBuilder [in System.Data.Common namespace] class, which can also be used to create your connection strings. This class performs no checks for valid connection string elements, so there are chances to generate invalid connection strings.
The SqlConnectionStringBuilder supports elements supported by SQL Server and OracleConnectionStringBuilder supports elements supported by Oracle only.These classes are used to create and parse correct connection strings programmatically. In this text, SqlConnectionStringBuilder class has been used.

Example

//include namespace
using System.Data.SqlClient;

//declaration
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();

OR

SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(connectionString);
If connection string is available, it can directly be used to initialize the object. The connection string specified should be valid. There are various properties available to specify/retrieve the details from the connection string.
  • connStringBuilder.DataSource [Name of the SERVER to connect]
  • connStringBuilder.InitialCatalog [Name of the DATABASE to connect]
  • connStringBuilder.IntegratedSecurity [For using windows authentication]
  • connStringBuilder.PacketSize [SIZE of packets in bytes]
  • connStringBuilder.ConnectTimeout [TIME in seconds for connecting to Server]
  • connStringBuilder.UserID [USER NAME for connecting]
  • connStringBuilder.Password [PASSWORD for the specified user]
After specifying the above details, you can find out the connection string using:
connStringBuilder.ConnectionString;
This can be directly used to create the connection object:
SqlConnection  sqlConn = new SqlConnection(connStringBuilder.ConnectionString);

Conclusion

Using these classes, the need to perform string concatenation for preparing connection strings can be avoided. Most of the properties for the ConnectionStringBuilder classes are similar, so a generic design can also be created for various data providers.
Total article views: 9289 | Views in the last 30 days: 4
 
Related Articles
FORUM

WMI Query Data Reader connection string

dynamically set the connection string

ARTICLE

Connection Strings 101

This article introduces connection strings and suggests using MDAC to easily write efficient connect...

FORUM

Error on Connection String

Error on Connection String

FORUM

Dynamic Connection string

Setting Dynamic Connection string Of Flat File

Tags
.net    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones