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.

18 comments:

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

I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favorites blog site list and will be checking back soon. Basically I offer 3d logo design services in logoistic. Please check out my site as well and let me know what you think.

If you want to play at the casino, you must choose the best. You can see this one.

embroidery digitizing said...
Thanks for this informative building connection article this is very interesting and very helpful for me thanks for posting this and keep it up.

Your blogs are really good and interesting. It is very great and informative bankruptcy lawyers virginia beach. Entity Framework) have their own inherited Connection String Builder 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. I got a lots of useful information in your blog. Keeps sharing more useful blogs..

This comment has been removed by the author.

Unlock seamless compatibility with our cross platform app development in the UK. We craft versatile apps that effortlessly run on multiple devices and operating systems, ensuring wider reach and consistent user experiences.

Your blogs are really good and interesting. It is very great and informative. I got a lots of useful information in your blog. These classes add type-safe properties for the the keys supported by their databases, and handle any special cases when generating the connection string virginia bankruptcy means test. Keeps sharing more useful blogs..

Post a Comment