Store user-specific connection strings for later use in ASP.NET Web API

user_12243870 0
Viewed 157

I am working on complicated project which allows every client to set their connection string information to connect to their own database but after that I didn't now what to do or how to let the client reach his own connection string (I don't have a table to save the connection string I want to be dynamic).

Is there any way to store the connection string in config page for every client and read those page in my web config.

asp.net-web-api c#
2 Answers

You wont be able overwrite or add something to the Web.config during runtime, only read from it.The web.config file is only read once during startup and wont be used afterwards.

A suggestiong to overcome this is either store the connectionstring for your customers in your own database or store them somewhere else. I prefer using Azure KeyVault for these things, as it allows you to let the user change the data using a specific key.

Also this is one of those scenarios where security is a big factor, so you must be very carefull when giving your customers these kinds of actions.

user_30465610

If number of client is managable, then you store it in web config file and you need seperate dbContext implentation if everyones database is different. If same db design is same for all, you can store credentials in dictionary and dynamically prepare connection string run time and connect.

Here is an example for MSSQL (I assume you are not using EF) Not need to store connectionstring in web config. Prepare connection string. You can store user wise parameters in DB as well.

public string BuildConnectionString(string dataSource, string dbName, string userId, string password,
        string persistSecurityInfo, string encrypt, string trustServerCertificate, string applicationIntent, string multiSubnetFailover)
        {
            return $"Data Source = {dataSource}; Initial Catalog = {dbName}; User ID = {userId}; Password = {password}; " +
                $"Persist Security Info = {persistSecurityInfo}; Encrypt = {encrypt}; TrustServerCertificate = {trustServerCertificate};" +
                $" ApplicationIntent = {applicationIntent}; MultiSubnetFailover ={multiSubnetFailover}";
        }

DB Connection example:

void CheckDbConnection()
{
    string connetionString = BuildConnectionString("WIN-50GP30FGO75", // or IP
         "Demodb", "YourUserID", "YourPassword", "True", "False", "False", 
         "ReadWrite", "False");
    string sql = "Select TutorialID,TutorialName from table1";

    using (SqlConnection cnn = new SqlConnection(connetionString))
    {
         using (SqlCommand command = new SqlCommand(sql, cnn))
         {
            cnn.Open();
            dataReader = sqlquery.ExecuteReader();

            while (dataReader.Read())
            {
                 Output = Output + dataReader.GetValue(0) + "-" + 
                  dataReader.GetValue(1) + "</br>";
             }

             Console.Write(Output);

             cnn.Close();
          }
    }
}
user_82443530
Related