C# Winform SQL Server Connection String

Question

Working on a WinForm project making use of SQL Server.

Currently my MusicPlayerDB.mdf has its Copy to Output Directory property set to Copy if newer.

After I run my InsertIntoDB, I close the Winform and proceed to check the table over in Server Explorer. But is seems as my table wasn't updated. But if I go to check Bin/Debug and check MusicPlayerDB.mdf, the data is there.

What would be the best way to fix this? I've seen other comments saying to use the absolute path of the .mdf (or something along those lines), but I would like to avoid that if possible.

Here is my connection string,

private const String CONNECTION_STRING = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\MusicPlayerDB.mdf;Integrated Security=True";

And here is my insert code:

private static void InsertIntoDB(List<string> userAccout) 
{
    String sqlQuery = "INSERT INTO dbo.UserAccount (UserName, UserPassword, PasswordQuestion, PasswordHint, PasswordKey) "
                      + "VALUES (@UserName, @UserPassword, @PasswordQuestion, @PasswordHint, @PasswordKey);";

    using(SqlConnection connection = new SqlConnection(CONNECTION_STRING)) 
    {
        connection.Open();          //open connection

        using(SqlCommand command = new SqlCommand(sqlQuery, connection)) 
        {     
            // set up command
            using(SqlTransaction trans = connection.BeginTransaction()) 
            {
                try 
                {
                    command.Connection = connection;
                    command.Transaction = trans;

                    command.Parameters.AddWithValue("@UserName", userAccout[0]);
                    command.Parameters.AddWithValue("@UserPassword", userAccout[1]);
                    command.Parameters.AddWithValue("@PasswordQuestion", userAccout[2]);
                    command.Parameters.AddWithValue("@PasswordHint", userAccout[3]);
                    command.Parameters.AddWithValue("@PasswordKey", Convert.ToInt32(userAccout[4]));

                    int r = command.ExecuteNonQuery();  //execute the command
                    trans.Commit();
                } 
                catch(Exception ex) 
                {
                    MessageBox.Show(ex.Message);    //couldn't execute command
                }
            }
        }
    }
} //end of InsertIntoDB

Is there a way to just make it go to one location every time? I'm kinda annoyed the data isn't persistent. — kenny10009, Sep 29 '19 at 22:02
And as always - don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Sep 30 '19 at 00:01

score 1 · Accepted Answer · answered Sep 29 '19 at 22:02

This is how it is expected to work. |DataDirectory| in a desktop app point to where your executable runs. This means bin\debug or bin\release (the working folders) when you run the app inside VS but the installation folder when you run the app outside VS.

This arrangement allows you to keep your empty MDF in the project folder while a working copy stays in your output folders. When you need to change something in the schema of your database you use Server Explorer to change the copy in your project folder. So a new copy of the file will be copied in the output folders at the start of the next VS session. When you need to distribute your app you distribute the MDF file in your project folder.

Of course, if you need to check what happens in the working copy of your db then you can create a new connection inside Server Explorer that points to the MDF file in the working folders.

If you need more control then you can change where the substitution string |DataDirectory| points. See, for example, this Question and my answer there

C# Winform SQL Server Connection String

1 Answers1