Differences between DbConnectionStringBuilder and OdbcConnectionStringBuilder Connection Strings

Question

I am trying to make my application database agnostic and in doing so I have elected to use the DBConnection Class instead of the SqlConnection, OracleConnection, etc that currently litter my application. So I am having an issue with the DBConnectionStringBulder adding double quotes in my connection string. When I use the OdbcConnectionStringBuilder the extra double quotes are omitted. So now I need to replace the double quotes after my connection string is built to avoid any issues.

OdbcConnectionStringBulder Connection String:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Temp\;Extensions=asc,csv,tab,txt;Persist Security Info=False

DbConnectionStringBulder Connection String:

Driver="{Microsoft Text Driver (*.txt; *.csv)}";Dbq=C:\Temp\;Extensions=asc,csv,tab,txt;Persist Security Info=False

So I am guessing that this is a bug in the DbConnectionStringBuilder but I wanted to see if I was doing something wrong or not. Here is the applicable code with my workaround:

        OdbcConnectionStringBuilder odbcConnectionString = new OdbcConnectionStringBuilder();
        odbcConnectionString.Add("Driver", "{Microsoft Text Driver (*.txt; *.csv)}");
        odbcConnectionString.Add("Dbq", @"C:\Temp\");
        odbcConnectionString.Add("Extensions", "asc,csv,tab,txt");
        odbcConnectionString.Add("Persist Security Info", "False");
        Console.WriteLine(odbcConnectionString.ConnectionString);

        DbConnectionStringBuilder dbConnectionString = new DbConnectionStringBuilder();
        dbConnectionString.Add("Driver", "{Microsoft Text Driver (*.txt; *.csv)}");
        dbConnectionString.Add("Dbq", @"C:\Temp\");
        dbConnectionString.Add("Extensions", "asc,csv,tab,txt");
        dbConnectionString.Add("Persist Security Info", "False");
        Console.WriteLine(dbConnectionString.ConnectionString.Replace("\"", string.Empty)); //<-- My Workaround.

Here is a link to .Net Fiddle: https://dotnetfiddle.net/3qhB5S

score 0 · Answer 1 · answered Aug 06 '18 at 06:52

0

I tried the same a few years ago, I ended up making an abstraction around the DBConnection class for the UI where the end user the must select and connect to the databse server. Then I implemented the rules for sqlserver and mysql so that the end result would adjust according to the rules that the implementing databaseservice could handle.

For the query execution I created a wrapper based upon the DbConnection and DbCommand class from ado.net.

answered Aug 06 '18 at 06:52

Samyne

308
1
13

Thanks I was trying to avoid having to do something like that but that seems to be what I need to do. – Mark Kram Aug 06 '18 at 11:59
1

Each database server has it's own specific set of options that make it hard to unify them in a single path for configuration. I tried and failed myself. I did not walked away from the ADO.net interfaces. Making a unified wrapper no matter we use mysql, sql server, postgres, sqlite, was in the end what we really needed. – Samyne Aug 06 '18 at 12:40

Differences between DbConnectionStringBuilder and OdbcConnectionStringBuilder Connection Strings

1 Answers1