ConnectionString with external file for SqlConnection

Question

C# on VisualStudio 2017. Windows Forms Application.

Hi all. I've read on the web that is not possible to use an .udl file in which write a ConnectionString for a SqlConnection. Is that true at today? And, if yes, there is an alternative way to use an external file for a ConnectionString in SqlConnetion?

I have to run project in 5 PCs that have different connection strings, for example:

PC1) Data Source=PCNAME\SQLEXPRESS;Initial Catalog=DBNEW;User ID=sa;Password=123;

PC2) Data Source=SERVER\SQLEXPRESS;Initial Catalog=DB;User ID=sa;Password=999;

[...]

Currently I use a string inside the project

string connSQL = "Data Source=.\\SQLEXPRESS;Initial Catalog=DBNEW;Persist Security Info=True;User ID=sa;Password=123;";

that I have to change five times for the five PCs' different connection.

I've tried anyway to connect with an .udl file

string connSQL = "Data Source=.\\SQLEXPRESS;AttachDbFile=C:\\connstring.udl";

that contains this

[oledb]
; Everything after this line is an OLE DB initstring
Data Source=PCNAME\SQLEXPRESS;Initial Catalog=DBNEW;Persist Security Info=True;User ID=sa;Password=123;

but of course it doesn't works.

Any ideas for an alternative solution?

Answer 1

Finally I found a solution. Thanks also to MethodMan's comment I later realized that you can use an external file to compile the connectionString, that is MyProjectName.exe.config, which is saved in the same directory as the software exe, and which has the same functions and settings of the App.config.

So what I did is create a new FormConn where you manually enter data for the connectionString, overwrite them in MyProjectName.exe.config and link this file to Form1 for SQL database management. Below the code.

In the Form1.cs:

using System.Configuration;

public Form1()
{
    //Check if a connectionString already exists
    //To the first slot there is a system configuration so 1 = no custom connectionString
    if (ConfigurationManager.ConnectionStrings.Count == 1)
        {
            FormConn frmConn = new FormConn();
            frmConn.ShowDialog();
            try
            {
                //Restart Form1 in the case connectionString has changed
                Application.Restart();
            }
            catch(Exception ex)
            {
                 MessageBox.Show(ex.Message);
            }
        }
        //Associates the custom connectionString to the string I will use in the code for operations that are connected to the DB.
        StringSQL = ConfigurationManager.ConnectionStrings[1].ConnectionString;
}

In the FormConn.cs:

using System.Configuration;
using System.Reflections;

string appPath = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        string appName = Environment.GetCommandLineArgs()[0];
        string configFile = System.IO.Path.Combine(appPath, appName + ".config");
        ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap();
        configFileMap.ExeConfigFilename = configFile;
        System.Configuration.Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
        var sezione = (ConnectionStringsSection)config.GetSection("connectionStrings");<br>sezione.ConnectionStrings["MyProjectName.Properties.Settings.MyDataSetConnectionString"].ConnectionString = "Data Source=" + txtDataSource.Text + ";Initial Catalog=" + txtInitialCatalog.Text + ";Persist Security Info=True;User ID=" + txtUserID.Text + ";Password=" + txtPassword.Text + ";";
        config.Save();
        ConfigurationManager.RefreshSection("connectionStrings");
        this.Close();

And that's what I have inside my MyProjectName.exe.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
<connectionStrings>
    <add name="MyProjectName.Properties.Settings.MyDataSetConnectionString"
        connectionString="Data Source=MyPcName\SQLEXPRESS;Initial Catalog=DB-1;Persist Security Info=True;User ID=sa;Password=123psw321"
        providerName="System.Data.SqlClient" />
</connectionStrings>
<startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
</configuration>

This worked for me!