How to pass ConnectionString parameters with variables?

Question

I have a ConnectionString and I want to pass it values (DataSource, Database, User ID, Password) with values from a .txt file and I need to read them, then pass them to the connectionString but I'm very confused how should I do this.

In my program I have a Helper Class to return the connectionString

public static class Helper
{
    public static string ConnectionString(string name)
    {
        return ConfigurationManager.ConnectionStrings[name].ConnectionString;
    }
}

This is how I call the connectionString so I can access the database data

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnectionString("Hotel")))
{
     connection.Execute($"INSERT INTO dbo.Registos_Cancelados(Nome, Telemovel, Data) VALUES(@Nome, @Telemovel, @Data)", new { Nome = nome, Telemovel = telemovel, Data = data });
}

I have a text file with the values

"DataSourceName"
"DataBaseName"
"User IDName"
"PasswordName"

And I want them in the connection string.

<connectionStrings>
    <add name="Hotel" connectionString="DataSource="DataSourceName";Database="DatabaseName";User Id="UserIdName";Password="PasswordName""
        providerName="System.Data.SqlClient" />
</connectionStrings>

So is your question how to read values from a text file, or how to build a connection string? — Johnathan Barclay
– Johnathan Barclay, Commented May 28, 2021 at 10:19
build a connection string, i know how to read the values but I don't know how to pass them to the connection string — romanCastro
– romanCastro, Commented May 28, 2021 at 10:21

Marc Gravell · Accepted Answer · 2021-05-28 10:26:28Z

6

You're using SqlClient, so: your best bet here is SqlConnectionStringBuilder:

var cb = new SqlConnectionStringBuilder(theBaseString);
cb.DataSource = dataSourceName;
cb.InitialCatalog = dataBaseName;
cb.UserID = userId;
cb.Password = password;
var connectionString = cb.ConnectionString;

If you don't have a template string (theBaseString), just use new SqlConnectionStringBuilder() instead.

The advantage of using SqlConnectionStringBuilder here is that it knows all about the escaping rules for non-trivial values, reserved characters, etc.

answered May 28, 2021 at 10:26

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Engincan Veske · Accepted Answer · 2021-05-28 10:25:14Z

You can format your connection string like below to pass the necessary values like dbname later.

<connectionStrings>
    <add name="Hotel" connectionString="DataSource={0};Database={1};User Id={2};Password={3}"
        providerName="System.Data.SqlClient" />
</connectionStrings>

After that in your Helper class, return the formatted connection string with the values that you read from txt file.

public static string ConnectionString(string name)
    {
        var dataSourceName = "...";
        var dbName = "...";
        var userId = "...";
        var password = "...";

        var connectionString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
        return string.Format(connectionString, dataSourceName, dbName, userId, password);
    }

Collectives™ on Stack Overflow

How to pass ConnectionString parameters with variables?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related