save changes to database C# ADO.NET

Question

    public EmployeeForm()
    {
        this.bindingSource = new BindingSource();

        this.Load += new EventHandler(EmployeeForm_Load);
    }

    private void EmployeeForm_FormClosing(object sender, FormClosingEventArgs e)
    {
        this.connection.Close();
        this.connection.Dispose();
    }     
    private void RetrieveDataFromTheDatabase()
    {
        
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"Employee.mdb\"";
        this.connection = new OleDbConnection(connectionString);

        this.connection.Open();

        OleDbCommand command = connection.CreateCommand();
        command.CommandText = "Select * From Employee";

        this.adapter = new OleDbDataAdapter();
        this.adapter.SelectCommand = command;

        this.dataset = new DataSet();
        this.adapter.Fill(dataset, "Employee");  
        
        OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(this.adapter);

        commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;

        commandBuilder.GetInsertCommand();
        commandBuilder.GetDeleteCommand();
        commandBuilder.GetUpdateCommand();

    }        

    private void BindControls()
    {
        this.bindingSource.DataSource = this.dataset.Tables[0];
        this.dgvEmployee.DataSource = this.bindingSource;
    }

    void FileSave_Click(object sender, EventArgs e)
    {
        this.adapter.Update(this.dataset, "Employee");
        
        this.dgvEmployee.EndEdit();
        this.bindingSource.EndEdit();
    }

I want to save all the data changes into the database when the save button is clicked. the code that I have doesn't make a change to the database. I want to also disable the save button after a successful save.

Answer 1

You can get visual studio to write all this code for you, including the save stuff:

• ensure you're working in a .net framework project - this process in core+ has bugs and parts of VS are deliberately disabled by Microsoft
• add a DataSet type of file to your project
• open it, right click the surface, add.. table adapter
• Go through the wizard to connect your access database. When presented with a large messagebox containing the text "press f1 for information on controlling this behavior" read and understand it in full. What it is saying is "if you copy the db into your project it will be copied again into the debug folder when you run. The app will alter the database in the bin/debug folder, and the db will be overwritten with a fresh copy by the build process next time. This can make it look like your data isn't saving; that is not true"
• Choose "select that returns rows"
• Enter SELECT * FROM Employee WHERE id = @id
• Choose a name of FillById/GetDataById
• Finish the wizard
• Right click the tableadapter, choose Add Query, add another query of SELECT * FROM Employee WHERE Name LIKE @name or similar - it is a good idea to add queries that let you select just certain employees, and do the kind of searching you will want to do in the app. "Search by name" is a reasonable requirement I think. When asked for a name for this query enter FillByName/GetDataByName
• Save the dataset, switch to the form designer
• Open the data sources window (on the View menu.. Other Windows) and drag the node representing the Employee table onto the form
• several things appear; a nav bar, with a box for entering the id, a dataset, a datagrid, a binding source etc.. and it's all plumbed together without you writing a single line of code. All the code is in the Form1.Designer.cs and Form1.cs (or whatever you called your form) and is similar to what you wrote
• Run the app, enter a known employee id in the box and click fillbyid. The employee appears in the grid. Edit their name, click save in the nav bar. Open the database file inside the bin/debug folder and see the changed name
• go to the code view of Form1 and change the FillById call to FillByName and remove the conversion to integer of the contents of the TextBox. Now you can search employees by name, or even search for a name of % to list all employees because it is a LIKE operation

Look at how the code is wired up; it's all very simple. You can add more tables to your dataset and even relationships, you can display related data by dragging child nodes(not top level nodes) out of Data Sources

If you get bored of the build process overwriting your debug db all the time, click on it in the solution explorer and change the Copy option away from "Copy Always" and to "Copy if newer". Now it will only be copied if you make a schema change to the db in the project folder (add a table etc)