none
How to build connection strings for different providers RRS feed

  • Question

  • We have three connections strings pointing to the same database using different providers.

    1>For Entity Framework,

    2>For Altova which uses SQL Native Client provider

    3>For classic ADO.NET SqlConnection class  which uses System.Data.SqlClient provider.

    Requirement is NOT to have three connection strings in the configuration file but instead have partial connection string which has Server Name, Security Info etc and then build the connection string  based on the context.

    .Net has connection string builder class which builds connection string but I could not find connection string builder for SQL native client and Entity Framework.

    What is the best & secure way to do this

     

    Here are my three connection string

     

    <add name="Connection1" connectionString="metadata=res://*/Forms.csdl|res://*/Forms.ssdl|res://*/Forms.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />    
        <add name="Connection2" connectionString="Provider=SQLNCLI10.1; Data Source=.\sqlexpress; Initial Catalog=MyDatabase;Integrated Security=SSPI;"/>
        <add name="Connection3" connectionString="Data Source=.\sqlexpress; Initial Catalog=MyDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
    Monday, September 26, 2011 4:47 PM

Answers

  • You can instantiate and use a generic DbConnectionStringBuilder object. Note the optional boolean argument, used to specify whether or not to use "ODBC rules for quoting values".

    It looks like you're using the built-in System.Configuration.ConnectionStringSettings API to store your connection strings. For security, see the Encrypting Configuration File Sections Using Protected Configuration section in Connection Strings and Configuration Files (ADO.NET).

    As for actually storing the connection strings, do as you describe literally: Have one common (partial) connection string with parts that all versions will share, the one each (partial) connection string for the three specific providers, containing their respective provider-specific arguments. Remember a ConnectionStringBuilder is really just a Dictionary<String,String> with a few extra methods and properties, so you can assign and retrieve arbitrary arguments using the .Item property or direct indexer bracket notation.

    So let's say here are your four example connection strings in the config file:

        <add name="CommonPieces"

             connectionString="Data Source=.\sqlexpress;Initial Catalog=MyDatabase;Integrated Security=SSPI;Pooling=False;MultipleActiveResultSets=True" />

        <add name="EntityFrameworkPieces"       

     connectionString="metadata=res://*/Forms.csdl|res://*/Forms.ssdl|res://*/Forms.msl;provider=System.Data.SqlClient;"

             providerName="System.Data.EntityClient" />

        <add name="AltovaPieces"

             connectionString="Provider=SQLNCLI10.1;"/>

        <add name="SqlConnectionPieces"

             connectionString=""

             providerName="System.Data.SqlClient"/>

     Here's how you could construct the full connection strings at runtime. I've defined an enum to store the three options.

        public enum ConnectionProviders {
            EntityFramework,
            Altova,
            SqlClient 
        }
    
    


    And a function to construct the full connection string for a given provider. This could be made smaller, but I wanted to err on the side of clarity:

        public static string BuildConnectionString(ConnectionProviders provider)
        {
            // Get common parts
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
            DbConnectionStringBuilder commonBldr = new DbConnectionStringBuilder();
            DbConnectionStringBuilder itemBldr = new DbConnectionStringBuilder();
            commonBldr.ConnectionString = settings["CommonPieces"].ConnectionString;
    
            // Get specific parts
            switch (provider)
            {
                case ConnectionProviders.EntityFramework :
                    itemBldr = new DbConnectionStringBuilder(true);
                    itemBldr.ConnectionString = settings["EntityFrameworkPieces"].ConnectionString;
                    itemBldr.Add("provider connection string", commonBldr.ConnectionString);
                    break;
                case ConnectionProviders.Altova :
                    itemBldr.ConnectionString = settings["AltovaPieces"].ConnectionString;
                    itemBldr.ConnectionString += commonBldr.ConnectionString;
                    break;
                case ConnectionProviders.SqlClient :
                    itemBldr.ConnectionString = settings["SqlConnectionPieces"].ConnectionString;
                    itemBldr.ConnectionString += commonBldr.ConnectionString;
                    break;
            }
    
            return itemBldr.ConnectionString;
        }
    
    

     


    jmh
    • Marked as answer by lax4u Tuesday, September 27, 2011 1:53 PM
    Monday, September 26, 2011 6:07 PM

All replies

  • You can instantiate and use a generic DbConnectionStringBuilder object. Note the optional boolean argument, used to specify whether or not to use "ODBC rules for quoting values".

    It looks like you're using the built-in System.Configuration.ConnectionStringSettings API to store your connection strings. For security, see the Encrypting Configuration File Sections Using Protected Configuration section in Connection Strings and Configuration Files (ADO.NET).

    As for actually storing the connection strings, do as you describe literally: Have one common (partial) connection string with parts that all versions will share, the one each (partial) connection string for the three specific providers, containing their respective provider-specific arguments. Remember a ConnectionStringBuilder is really just a Dictionary<String,String> with a few extra methods and properties, so you can assign and retrieve arbitrary arguments using the .Item property or direct indexer bracket notation.

    So let's say here are your four example connection strings in the config file:

        <add name="CommonPieces"

             connectionString="Data Source=.\sqlexpress;Initial Catalog=MyDatabase;Integrated Security=SSPI;Pooling=False;MultipleActiveResultSets=True" />

        <add name="EntityFrameworkPieces"       

     connectionString="metadata=res://*/Forms.csdl|res://*/Forms.ssdl|res://*/Forms.msl;provider=System.Data.SqlClient;"

             providerName="System.Data.EntityClient" />

        <add name="AltovaPieces"

             connectionString="Provider=SQLNCLI10.1;"/>

        <add name="SqlConnectionPieces"

             connectionString=""

             providerName="System.Data.SqlClient"/>

     Here's how you could construct the full connection strings at runtime. I've defined an enum to store the three options.

        public enum ConnectionProviders {
            EntityFramework,
            Altova,
            SqlClient 
        }
    
    


    And a function to construct the full connection string for a given provider. This could be made smaller, but I wanted to err on the side of clarity:

        public static string BuildConnectionString(ConnectionProviders provider)
        {
            // Get common parts
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
            DbConnectionStringBuilder commonBldr = new DbConnectionStringBuilder();
            DbConnectionStringBuilder itemBldr = new DbConnectionStringBuilder();
            commonBldr.ConnectionString = settings["CommonPieces"].ConnectionString;
    
            // Get specific parts
            switch (provider)
            {
                case ConnectionProviders.EntityFramework :
                    itemBldr = new DbConnectionStringBuilder(true);
                    itemBldr.ConnectionString = settings["EntityFrameworkPieces"].ConnectionString;
                    itemBldr.Add("provider connection string", commonBldr.ConnectionString);
                    break;
                case ConnectionProviders.Altova :
                    itemBldr.ConnectionString = settings["AltovaPieces"].ConnectionString;
                    itemBldr.ConnectionString += commonBldr.ConnectionString;
                    break;
                case ConnectionProviders.SqlClient :
                    itemBldr.ConnectionString = settings["SqlConnectionPieces"].ConnectionString;
                    itemBldr.ConnectionString += commonBldr.ConnectionString;
                    break;
            }
    
            return itemBldr.ConnectionString;
        }
    
    

     


    jmh
    • Marked as answer by lax4u Tuesday, September 27, 2011 1:53 PM
    Monday, September 26, 2011 6:07 PM
  • Thanks

    The reason i posted the connection strings here is just for clarification but eventually i want only one partial connectionstring in the config file. I got what you are saying though.

    I will use Entity Framework's connection string as a partical connection string (Common) and then manipulate this string based on the ConnectionProviders. The reason i would like to use the entity framewroks connection string so that design time edmx file can use it too.

    However, i would have really like to have only  <add name="CommonPieces" connectionString="Data Source=.\sqlexpress;Initial Catalog=MyDatabase;Integrated Security=SSPI;" /> But design time would not like it

    Monday, September 26, 2011 7:06 PM
  • What do you mean that "design time would not like it?"


    jmh
    Monday, September 26, 2011 7:22 PM
  • when we add edmx file, i beleive it needs the connection string so that in design mode we can add/update entity model

    Monday, September 26, 2011 7:28 PM
  • I see. That makes sense.
    jmh
    Monday, September 26, 2011 7:51 PM