asp:Feature

LANGUAGES: C#

ASP.NET VERSIONS: 2.0

Build Connection Strings Seamlessly in .NET 2.0

Ensure Your Database Connection Strings Are Safe and Secure

By Joydip Kanjilal

The basic requirement for connecting to a database involves the use of connection strings. A connection string is comprised of the database server to connect to, the database name, the user s credentials, the authentication mode that should be used, etc. In the earlier version of .NET (.NET 1.x), we had to manually merge the various parameters to build the connection string and use it. With the advent of .NET 2.0, however, things have changed. You have the option of using the connection string builder classes available to build safe and secure connection strings seamlessly. This article presents how we can make use of these classes to build our database connection strings in .NET 2.0 (with code examples where appropriate).

The Connection String Builder Classes in .NET 2.0

Whether you store your connection strings in the configuration file or you hard code them in your application, you no longer need to dynamically concatenate the necessary parameters to build your database connection strings. With .NET 2.0 you have the connection string builder classes that are designed to eliminate syntax errors and potential security threats from SQL injection attacks in your database connection strings. For more on SQL injection attacks see my article Prevent SQL Injection Attacks.

We have four such classes that correspond to each type of data provider being used. You have the following connection string builders from which to choose:

• SqlConnectionStringBuilder
• OracleConnectionStringBuilder
• OleDbConnectionStringBuilder
• OdbcConnectionStringBuilder

Needless to say, the names of these classes relate to the type of the respective data providers. The DbConnectionStringBuilder class in the System.Data namespace serves as the base for all these strongly typed connection string builder classes. The following code snippet illustrates how we can build our database connection string using the SqlConnectionStringBuilder class and specifying the required parameters through the properties of this class:

SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder(); //Create an instance of the SqlConnectionStringBuilder class

sqlConnectionStringBuilder.UserID = // The database user's id

sqlConnectionStringBuilder.Password = //Password for the database user

sqlConnectionStringBuilder.DataSource = // The name of the database server to connect to

sqlConnectionStringBuilder.PacketSize = // Size of the data packet

sqlConnectionStringBuilder.InitialCatalog = // The name of the database to connect to

sqlConnectionStringBuilder.ConnectTimeout = // The connection timeout value in seconds

As an example, you can build your database connection string for the database test that resides in your local system, as shown in the code snippet below:

SqlConnectionStringBuilder sqlConnectionStringbuilder = new SqlConnectionStringBuilder();

sqlConnectionStringbuilder.DataSource = "(local)";

sqlConnectionStringbuilder.InitialCatalog = "Test";

sqlConnectionStringbuilder.IntegratedSecurity = true;

Once you are done with specifying the different parameters, you can retrieve the connection string using the ConnectionString property of the SqlConnectionStringBuilder class, as shown below:

String connectionString = sqlConnectionStringBuilder.ConnectionString;

Now you can create a connection using the connection string built earlier (in either of the following two ways):

SqlConnection connection = new SqlConnection(sqlConnectionStringbuilder.ToString());

or

SqlConnection connection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString);

Conclusion

.NET 2.0 allows you to dynamically build database connection strings that are safe and error free. You no longer need to append the required parameters to build your database connection strings. This article has taken a brief look at the connection string classes in .NET 2.0 and illustrated how we can use them programmatically.

Working extensively in Microsoft technologies for more than 10 years, Joydip Kanjilal is a Senior Technical Leader in the Design and Architecture team for a reputed company in a Hyderabad, India. His programming skills include C, C++, Java, C#, VB, VC++, ASP.NET, XML, and UML. He has worked with .NET and C# for more than five years. Reach Joydip at mailto:joydipkanjilal@yahoo.com or at his blog at http://aspadvice.com/blogs/joydip/.