Why DataAdapter.Update() does not update the database

Question

There is a DataGridView in my form, and I have a save button. Both the DataAdapter and the DataSet are automatically generated. I want to use DataAdapter.Update() to update my database, but it seems nothing changed after I updated the DataGridView when I open the table in .mdf or generate the solution again.

I knew this was asked and read some posts, trying to find the solutions but it doesn't work.

I have set the .mdf file property 'Copy to output directory' to 'Copy if newer'
BindingSource and BindingNavigator work successfully.

Code Sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.myTableTableAdapter.Fill(this.myDatabaseDataSet.myTable);
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(myTableTableAdapter.Adapter);
            myTableTableAdapter.Adapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
            myTableTableAdapter.Adapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
            myTableTableAdapter.Adapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();

        }

        private void SaveSToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                bindingSource1.EndEdit();
                myTableTableAdapter.Adapter.Update(myDatabaseDataSet.myTable);
                MessageBox.Show("Succeed");
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "Failed");
            }

        }
    }
}

Jacey · Answer 1 · 2020-04-15T02:10:39.783

Just solved this problem. Someone with the same problem may refer to this. Why saving changes to a database fails? Steve's answer is quite helpful to understand this problem.

I did modify my database, but when I connect the datasource, it just copy again, so it seems the update fails.

You can write the connection manually, changing the |Directory| to where your database actually is. Or do something like this:

string dataDir = AppDomain.CurrentDomain.BaseDirectory;
            if (dataDir.EndsWith(@"\bin\Debug\")
            || dataDir.EndsWith(@"\bin\Release\"))
            {
                dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
            }

But I don't recommend this because it may modify your original database when you're debugging. It should be only modifying my database in the bin folder while keeping the original database intact.

What you have to do is just setting your .mdf file Copy to directory property to Copy if newer or Copy Never (In this case, add a script to copy the file to the bin\debug folder only if it doesn't exist). And do not focus too much on your original .mdf in your Resource Manager.

And other notes:

Lines like myTableTableAdapter.Adapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand(); are not necessary.

When associated with a DataAdapter, the DbCommandBuilder automatically generates the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter if they are null references. If a Command already exists for a property, the existing Command is used. MSDN
I need to check before calling BindingSource.EndEdit()

this.Validate();

Why DataAdapter.Update() does not update the database

1 Answers1