[RESOLVED] Using variable for connection strings

Hello, 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:

C# Code:

_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:

C# Code:

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?

Well, since you didn't post the error, we're relegated to guessing as to what the error is. My guess is that the error is something along the lines of the database is not found. That's because you're installing the database into the app folder... which is generally discouraged. It should be deployed to the Datadirectory for the app (the installer takes care of that) ... Again, this is all guess work here, but I'd go back to the installer you're using and tell it to stuff the database into the DataDirectory rather than in the app path.

-tg

Thank you for your reply. I'm getting error

Code:

---

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

---

for line sqlDataAdapter.Fill(dataTable)

I tried adding

C# Code:

AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));

but am still getting error.

Why would setting the data directory to your Documents folder be useful when you say that explicitly setting the data source in the connection string to your Desktop folder works? How EXACTLY are you deploying your application? Where EXACTLY is your MDF file in relation to your EXE?

I don't know how to answer, couldn't understand your first question, sorry for my English. I tried Release type and running it by f5. I was also struggling with prerequisties, sorry for the late reply. I'm sure I'll have to compile with framework 3.5 prerequisties.

It's the best for my mdf to be in same folder of exe. Thank you for your reply.

Thanks for your replies. I got help from another site and fixed the problem using stringbuilder. Here's my final code:

AppDomain.CurrentDomain.SetData("DataDirectory", @AppDomain.CurrentDomain.BaseDirectory + "database");

C# Code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); 
builder["Data Source"] = @"(LocalDB)\MSSQLLocalDB"; 
builder["AttachDbFilename"] = @"|DataDirectory|\gazi_db.mdf"; 
builder["Integrated Security"] = true; 
builder["Connect Timeout"] = 30; 
 
sqlConnection = new SqlConnection(builder.ConnectionString);

Your code is not wrong but it is suboptimal. It should be like this:

csharp Code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
 
builder.DataSource = @"(LocalDB)\MSSQLLocalDB";
builder.AttachDBFilename = @"|DataDirectory|\database\gazi_db.mdf";
builder.IntegratedSecurity = true;
builder.ConnectTimeout = 30;
 
sqlConnection = new SqlConnection(builder.ConnectionString);

There's no need to call SetData on the current AppDomain because you can include that subfolder in the file path. You could even collapse that code down to this:

csharp Code:

sqlConnection = new SqlConnection(new SqlConnectionStringBuilder
                                    {
                                        DataSource = @"(LocalDB)\MSSQLLocalDB",
                                        AttachDBFilename = @"|DataDirectory|\database\gazi_db.mdf",
                                        IntegratedSecurity = true,
                                        ConnectTimeout = 30
                                    }.ConnectionString);