Building Connection Strings in .Net

.Net developers frequently need to build connection strings, especially when connecting to Access or Excel files using OleDB.
Code like the following has been written countless times:

//Bad code! Do not use!
string conn = "Data Source=" + openFileDialog1.FileName + "; "
            + "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Extended Properties=\"Excel 8.0\"";

This code looks innocuous at first glance, but will not work for all filenames.  If the filename contains characters like ', ", ;, or =, this code will create an invalid connection string and throw an exception.

The correct way to build connection strings is to use one of the DbConnectionStringBuilder classes.  This class implements a dictionary of key-value pairs in the connection string.  It has a ConnectionString property which assembles the instance’s contents into a usable connection string.  Unlike the string concatenation shown above, this property will correctly escape all values.

In addition, each of the database clients included with the .Net framework (SQL, OleDB, ODBC, Oracle, and Entity Framework) have their own inherited ConnectionStringBuilder classes in their respective namespaces.  These classes add type-safe properties for the the keys supported by their databases, and handle any special cases when generating the connection string.

Thus, the correct way to write the above code is:

var connBuilder = new OleDbConnectionStringBuilder {
    DataSource = openFileDialog1.FileName,
    Provider = "Microsoft.Jet.OLEDB.4.0"
};
connBuilder["Extended Properties"] = "Excel 12.0 Macro";
As an added bonus, these classes implement ICustomTypeDescriptor, so they can be bound to a PropertyGrid to allow the end-user to edit the connection string.  This can be seen in certain places in Visual Studio.

11 comments:

It looks pretty good. Cara

I think that you should definitely check this out before writing your research essay. It will help you to get a high grade

We strive to provide a lot of useful information about mail order brides and international online dating to slovenian women increase your chances of success in mail order brides search. Joining our site is easy and free - so simple that in a matter of minutes we can open up exciting opportunities to meet the desired brides by mail.

I don't even know what this code will do Anyway, I am also a student. I only got a coding assignment once which I found very difficult. It was difficult to complete myself. Completed with the help of Buy Assignment Service. But thank you very much for telling me how to write the code correctly.

"This is so informative thank you for sharing this I have an assignment due tomorrow on this very topic and I was thinking of getting help therefore I have been searching for the best assignment writing website but this has made everything so easy for me I do not think
I need any external help now. Thank you!"

I didn’t understand everything, not only in my studies, but also in life, now I can buy an essay online, I do any written work to order, and the price is low, and the work is of high quality, it helps those who find it difficult.

Providing services to students for completing assignments of various types since 2007, qualified authors from the project https://essaykeeper.com/term-papers-for-sale have proven themselves on the positive side. You will make the right choice by entrusting the solution to the problem of writing student work to authors who have many years of experience in writing student work. We help students who really need prompt and high-quality assistance in getting an education

This comment has been removed by the author.

The consumption of vegetable crops that require potash fertilisers to maintain their potassium content,the potash fertilisers market is predicted to grow. Brazil is expected to be the largest consumer and producer of potash fertiliser over the projection period, owing to rising demand for greater agricultural yields and productivity.

Also Read: microtome market
canine arthritis treatment market

Post a Comment