'C# DbContext Connection Type and Server

I have a WinForm application developed on one laptop connected to an SQL server on the same laptop.

I have a new laptop and have created a docker setup for an SQL server. I am looking to change the code base to use the new SQL server.

The new server is using SQL Server auth with username and password on the new laptop. The old laptop is using windows authentication on a windows installed setup. I have migrated a copy of the entire DB into my dockerised instance of the sql server.

The application has the connection settings in the app config and naturally this is for windows authentication.

My app.config is comitted to my github repository. I do not want to store the sql user/password in the app.settings, but instead I would like to get these from env variables I set on the machine.

I would also like to know how to change the format of the connection string in app.config so it works with sql server authentication.

Or maybe now I have explained what I am trying to do, there might be a better way?

My current connection strings are

<connectionStrings>
    <add name="Blah.Properties.Settings.BlahConnectionString" 
        connectionString="Data Source=W.....R....;
        Initial Catalog=Blah;
        Integrated Security=True;
        Connect Timeout=30;Encrypt=False;
        TrustServerCertificate=False" 
        providerName="System.Data.SqlClient"/>
</connectionStrings>


[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="Blah")]
public partial class BlahDBDataContext : System.Data.Linq.DataContext

I searched all code for 'AddDbContextFactory' and 'GetConnectionString'

    public BlahDBDataContext() :
 base(global::Blah.Properties.Settings.Default.BlahConnectionString, mappingSource) 
{ 
    OnCreated(); 
}


[global::System.Configuration.DefaultSettingValueAttribute("Data Source=PCNAME;Initial Catalog=Blah;Integrated Security=True" + ";Connect Timeout=30;Encrypt=False;TrustServerCertificate=False")] 
public string BlahConnectionString 
{ 
    get { return ((string)(this["BlahConnectionString"])); 
}
c++sharpsql-server


Solution 1:[1]

The database context class is generated code inheriting as far as I can tell on System.Data.Linq.DataContext, as shown by this code from my application.

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="Blah")]
public partial class BlahDBDataContext : System.Data.Linq.DataContext
{

The BlahDBDataContext class provides a number of constructors. Please check for yourself for a fuller list.

For my purposes the constructor I needed was

public BlahDBDataContext(System.Data.IDbConnection connection) : 
    base(connection, mappingSource)
{
    OnCreated();
}

This requires the construction of a System.Data.IDbConnection object to hold/manage the connection details.

Therefore in my case all I needed to do was construct the connection string, for example.

string userName = EnvironmentVariables.GetValue("BLAH_USERNAME");
string password = EnvironmentVariables.GetValue("BLAH_PASSWORD");
string server = EnvironmentVariables.GetValue("BLAH_SERVER");
string database = EnvironmentVariables.GetValue("BLAH_DATABASE");
connectionString = $@"Data Source={server};Initial Catalog={database};Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;User ID={userName};Password={password};";

and then all thats needed is

new BlahDBDataContext( new SqlConnection( Program.GetDatabaseConnectionString()))

Where Program.GetDatabaseConnectionString() is.

    public static string GetDatabaseConnectionString()
    {
        if (connectionString is null)
        {
            string userName = EnvironmentVariables.GetValue("BLAH_USERNAME");
            string password = EnvironmentVariables.GetValue("BLAH_PASSWORD");
            string server = EnvironmentVariables.GetValue("BLAH_SERVER");
            string database = EnvironmentVariables.GetValue("BLAH_DATABASE");
            connectionString = $@"Data Source={server};Initial Catalog={database};Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;User ID={userName};Password={password};";
        }
        return connectionString;
    }

From a SOLID principles and clean coding perspective this is requires refactoring, and will be cleaned up now I have working code.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yogibear

Related Questions