question

PederThrnstrm-2402 avatar image
1 Vote"
PederThrnstrm-2402 asked PederThrnstrm-2402 commented

AppConfig and .NET5 database connections

Hi everyone,
I do development with C# and done so using .NET4.

Best practice, I've been told, and for many reasons is to have your connection string in AppConfig. Comming over to .NET5 AppConfig doesn't even exists. So, where should I keep the connection string now?

Would appreciate your help - just a link to some documentation will do.

Many thanks.

dotnet-csharpdotnet-runtime
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered PederThrnstrm-2402 commented

As @TimonYang-MSFT indicated you can still use an App.config file but the favored way under .NET Core is to read connection strings and other settings in a file named appsettings.json.

If you were using ASP.NET Core appsettings.json is easy to implement for reading connection strings while those writing desktop applications require to write code.

I created a simple GitHub repository as a starter for getting a connection string from appsettings.json where in this case there are three connection strings, one for development, one for test and one for production. If you have only one environment then as coded set the development connection.

Example appsettings.json

 {
   "ConnectionStrings": {
     "DevelopmentConnection": "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2020;Integrated Security=True",
     "ProductionConnection": "Data Source=ProductionServer;Initial Catalog=NorthWind2020;Integrated Security=True",
     "TestConnection": "Data Source=TestServer;Initial Catalog=NorthWind2020;Integrated Security=True",
     "Environment": 2
   }
 }

Example usage to read from a SQL-Server database

 using System.Data.SqlClient;
 using SqlServerConnectionLibrary;
    
 namespace AppSettingsCoreUnitTestProject.Classes
 {
     public class SqlOperations
     {
         public static string ConnectionString = "";
    
         public static CustomerRelation GetCustomers()
         {
    
             InitializeConnection();
                
             CustomerRelation customer = new();
    
             var selectStatement = "TODO";
    
             using var cn = new SqlConnection() { ConnectionString = ConnectionString };
             using var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement };
    
             cn.Open();
    
             var reader = cmd.ExecuteReader();
    
             if (reader.HasRows)
             {
                 reader.Read();
                 customer.CustomerIdentifier = reader.GetInt32(0);
                 customer.CompanyName = reader.GetString(1);
                 customer.City = reader.GetString(2);
                 customer.PostalCode = reader.GetString(3);
                 customer.ContactId = reader.GetInt32(4);
                 customer.CountryIdentifier = reader.GetInt32(5);
                 customer.Country = reader.GetString(6);
                 customer.Phone = reader.GetString(7);
                 customer.PhoneTypeIdentifier = reader.GetInt32(8);
                 customer.ContactPhoneNumber = reader.GetString(9);
                 customer.ModifiedDate = reader.GetDateTime(10);
                 customer.FirstName = reader.GetString(11);
                 customer.LastName = reader.GetString(12);
             }
    
             return customer;
    
         }
    
         private static void InitializeConnection()
         {
             if (!string.IsNullOrWhiteSpace(ConnectionString)) return;
             Helper.Initializer();
             ConnectionString = Helper.ConnectionString;
         }
     }
 }

We can have other properties stored in appsettings.json e.g.

 {
   "GeneralSettings": {
     "LogExceptions": true,
     "DatabaseSettings": {
       "DatabaseServer": ".\\SQLEXPRESS",
       "Catalog": "School",
       "IntegratedSecurity": true,
       "UsingLogging": true
     },
     "EmailSettings": {
       "Host": "smtp.gmail.com",
       "Port": 587,
       "EnableSsl": true,
       "DefaultCredentials": false,
       "PickupDirectoryLocation": "MailDrop"
     }
   }
 }


Not included but wanted to show other configuration possibilities

121135-config1.png

And last note, with not much effort we can programmatically change settings in the appsettings.json file. With that, if you can a setting while the app is running the change will be recognized.

GitHub solution

121156-solution.png



config1.png (9.7 KiB)
solution.png (36.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Wow, thank you so much. I didn't expect so thorough answer so fast. Now I con try these suggestions out.

I want to avoid having connection strings, and such, hard coded in my source code as see this as very ugly way to do coding.

Using NUGET is one solution using appsettings.json. I have a feel it is the json way I should look at but I will look at both solutions as I can see both solution come in handy.

Once again, many thanks.
No I can move forward.

Peder Thornstrom

0 Votes 0 ·

When you have time check out my Microsoft article .NET Core desktop application configurations (C#)


1 Vote 1 ·
TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered

One way is to add a nuget package: System.Configuration.ConfigurationManager, and then manually add an App.config file, just like using it in the .Net Framework:

             string connectionString = ConfigurationManager.ConnectionStrings["Model1"].ConnectionString;
             Console.WriteLine(connectionString);

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.