none
SQL SERVER CONNECTION STRING RELATED

    Question

  • I want to know that I am using SQL Server 2008 and Visual Studio 2010 using C# for development of a project. I passed the connection string to connect to the database of my computer as founded, but my problem is that when I deliver this project to client and he is going to use that project than the string should be replaced as per its system sql installation. I want to know that what is the process to create the connection string dynamically as when i install the project on client's system it connected to the database without the changing in the connection string
    Sunday, October 06, 2013 1:01 PM

Answers

  • I want to know that what is the process to create the connection string dynamically as when i install the project on client's system it connected to the database without the changing in the connection string

    The most common way to implement a configurable connection string in .NET applications is with an application configuration file.  The app code can then retrieve the configured value at run time.  The steps needed to do this are:

    1) add an application configuration file to your project named "App.config" (if you don't already have one)

    2) edit the "App.config" file to add a connection string section and connection string:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <connectionStrings>
        <add name="MyDatabase" connectionString="Data Source=MyDatabaseServer;Initial Catalog=MyDatabase;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
      </connectionStrings>
    </configuration>

    3) add a System.Configuration reference to your project 

    4) in the app code, you can get the named connection string using a ConfigurationManager:

    String myDatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
     

    At build time, the "App.config" file will be renamed to your assembly name with the file extention ".config".  Your installer can replace the connection string with the one needed for the target system.  Sample code snippet:

                System.Configuration.Configuration config =
                    System.Configuration.ConfigurationManager.OpenExeConfiguration("AssemblyName.exe");
    
                var connectionStringsSection = config.ConnectionStrings;
                connectionStringsSection.ConnectionStrings.Remove("MyDatabase");
                var connectionStringSetting = 
                    new System.Configuration.ConnectionStringSettings("MyDatabase", newConnectionString, "System.Data.SqlClient");
                connectionStringsSection.ConnectionStrings.Add(connectionStringSetting);
                config.Save(System.Configuration.ConfigurationSaveMode.Modified);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, October 06, 2013 2:20 PM
    Moderator