How can I use variable in connection string?

Question

I'm having trouble with variables for connection strings. In my app.config database path seems fine but on form, I'm getting error for that connection string. When I try to add:

_connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; " +
    "AttachDbFilename = \"|DataDirectory|\\gazi_db.mdf\"; " +
    "Integrated Security = True; Connect Timeout = 30";

the database won't work correctly; it can't save data on exit. However this Works fine:

connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; " +
        "AttachDbFilename = \"C:\\Users\\Can\\Desktop\\c_sharp_gazi_installer" +
        "\\Gazi Installer\\gazi_installer\\gazi_db.mdf\"; " +
        "Integrated Security = True; Connect Timeout = 30";

How can I fix this?

EDIT: I hoped this would work

string DataDirectory = "";
string folder = Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().CodeBase);
AppDomain.CurrentDomain.SetData("DataDirectory", folder);

_connectionString = _connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; " +
        "AttachDbFilename = \"" + DataDirectory + "\\gazi_db.mdf\"; " +
        "Integrated Security = True; Connect Timeout = 30";

But it's still giving me sqlclient sqlexception error.

EDIT2: This error indicates connection open. I don't understand why this doesn't work either:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder["Data Source"] = "(LocalDB)\\MSSQLLocalDB";
builder["AttachDbFilename"] = @"C:\Users\Can\\Desktop\c_sharp_gazi_installer\Gazi Installer\gazi_installer\gazi_db.mdf";
builder["Integrated Security"] = true;
builder["Connect Timeout"] = 30;

Please someone help. I'm going to go mad.

"I'm getting error". If only there was a way for us to know what the error message was. — jmcilhinney
– jmcilhinney, Commented Mar 7, 2017 at 3:20
If you create database from sqlserver management tool, instead of from VS, or attach the mdf file first, there will no longer be such problem. — Lei Yang
– Lei Yang, Commented Mar 7, 2017 at 3:29

Luca Ghersi · Accepted Answer · 2017-03-07 08:33:00Z

2

Firstly, you can use the @ "symbol" to avoid using so many escape characters. Then, where did you define the "DataDirectory" variable?

edited Mar 7, 2017 at 8:33

Luca Ghersi

3,34123 silver badges34 bronze badges

answered Mar 7, 2017 at 3:30

Ragnarokkr Xia

3031 silver badge11 bronze badges

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

3 Comments

onurcano Over a year ago

I don't have it for now. I remember that I tried it as well. Just copied the string from app config.

onurcano Over a year ago

shouldn't mdf placed in the same path with exe?

onurcano Over a year ago

I tried this gist.github.com/anonymous/45f5fe567aefded419da2ccc98ebfec8 but am still getting sqlexception error.

WereGoingOcean · Accepted Answer · 2017-03-07 04:38:50Z

1

.NET has a connection string builder class. Using this you can set each value i.e. Data Source, Integrated Security separately to variables and then use the .ConnectionString property to get the connection string.

edited Mar 7, 2017 at 4:38

answered Mar 7, 2017 at 3:20

WereGoingOcean

1081 silver badge10 bronze badges

3 Comments

Ňɏssa Pøngjǣrdenlarp Over a year ago

That isnt limited to c# - its a NET thing

WereGoingOcean Over a year ago

Good call. Edited the original answer.

onurcano Over a year ago

Thanks for all helps. Here's my final code: gist.github.com/nikel/1335c9573b1406cffc201d1c2e99746e

Collectives™ on Stack Overflow

How can I use variable in connection string?

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related