Commit is not working while saving a record in database

Question

I'm having difficulty inserting a simple record to the database. I'm not getting any errors while saving it. Also, I can verify that it got inserted if I try to retrieve it while program is running. But as soon as I close the program and refresh the database, it doesn't appear in the database. I know this is a commit problem but not sure what exactly I'm missing here.

private void saveEmpBtn_Click(object sender, EventArgs e)
{
    string aSQL = "INSERT INTO Employee(Id, Name, Type, Email, UTAId, Dept) VALUES (@Id, @Name, @Type, @Email, @UTAId, @Dept) ";

    using (conn = new SqlConnection(connectionString))
    {
        conn.Open();

        using (var tx = conn.BeginTransaction())
        using (SqlCommand command = new SqlCommand(aSQL, conn))
        {
            command.Connection = conn;
            command.Transaction = tx;

            command.Parameters.AddWithValue("@Id",  3);
            command.Parameters.AddWithValue("@Type", empTypCmbBx.SelectedItem.ToString());
            command.Parameters.AddWithValue("@Name", nmTxtBx.Text);
            command.Parameters.AddWithValue("@UTAId", utaIdTxtBx.Text);
            command.Parameters.AddWithValue("@Email", emailTxtBx.Text);
            command.Parameters.AddWithValue("@Dept", deptTxtBx.Text);

            try
            {
                command.ExecuteNonQuery();
                tx.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        MessageBox.Show("Employee Saved Successfully");
    }
}

Included few images of database script of the table. Schema definition and ID properties as well. Some questions: the example I followed had employee.mdf and had only one table. In my case database name is RoomAllocationSystemDatabase and table name is Employee. Do I have to include schema as dot notation to access the table in the insert statement? I tried this. When I do, it says invalid object. Not sure if I need to include database name somewhere else.

CREATE TABLE [dbo].[Employee] 
(
    [Id]       INT          NOT NULL,
    [Name]     VARCHAR (50) NULL,
    [Type]     VARCHAR (50) NULL,
    [Email]    VARCHAR (50) NOT NULL,
    [UTAId]    VARCHAR (50) NULL,
    [Dept]     VARCHAR (50) NULL,
    [Password] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Connection string logic while loading the form

connectionString = ConfigurationManager.ConnectionStrings["RoomAllocationSystem.Properties.Settings.RoomAllocationSystemDatabaseConnectionString"].ConnectionString;

Connection string in app.config:

<connectionStrings>
    <add name="RoomAllocationSystem.Properties.Settings.RoomAllocationSystemDatabaseConnectionString"
         connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\RoomAllocationSystemDatabase.mdf;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Answer 1

This is the answer. It was posted here. It is a common scenario. You have a connection string that uses the substitution string |DataDirectory|. In a desktop application this directory is usually the same directory where your program runs. Inside Visual Studio, your program run in the BIN\DEBUG (or x86 variant) directory. Thus the Visual Studio copies your MDF file from the project directory to the BIN\DEBUG folder. You add records to this copy, not to the one in the project folder. However, the Server Explorer window has a connection specific to the Project Folder database that, of course, remains empty.

You could add another connection to the Server Explorer pointing to the folder BIN\DEBUG and check that your database has been updated or not.

To complicate the matter, there is the property Copy to the Output Directory associated with the MDF file. If this property is set to Copy Always everytime you start a new debug session with Visual Studio, the file is copied again from the project folder to the output directory (BIN\DEBUG) overwriting the copy already there with a new empty one. So the first run succeds, the second one fails. And the symptoms that you observed are a clear sign of this situation.

Simply change the property Copy to the Output directory to Copy if newer, the code works well. (Peraphs it is too early, but remember to change your query to a parameterized query. As is, you could break your code simply inserting a single quote in the txtName textbox like O'Malley, not to mention the Sql Injection hack)

Answer 2

Try using transaction statement inside curly braces

            using (var tx = conn.BeginTransaction())
            {
                using (SqlCommand command = new SqlCommand(aSQL, conn))
                {
                    command.Connection = conn;
                    command.Transaction = tx;
                    command.Parameters.AddWithValue("@Id", 3);

                    try
                    {
                        command.ExecuteNonQuery();
                        tx.Commit();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                    }
                }
            }