C# save changes in datagridview

Question

I have DataGridView when I press to modify button then make same changes and Save it after refresh there are values same like before changes.

button 1 - insert new record button 2 - refresh DataGridView button 3 - allow to update records in DataGridView button 4 - save changes button 5 - cancel changes

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;
using System.Configuration;

namespace IS
{
public partial class Form2 : Form
{
    public Form2()
    {
        InitializeComponent();
        LoadTable();

        label1.Text = "User: " + User.name + " " + User.surename;
    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void LoadTable()
    {
        using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\***\Documents\Visual Studio 2015\Projects\***\Database1.mdf;Integrated Security=True;Connect Timeout=30"))
        using (SqlCommand query = new SqlCommand("Select FirstName, LastName, Login, Email from Users", connection))
        {
            try
            {
                SqlDataAdapter Adapter = new SqlDataAdapter();
                Adapter.SelectCommand = query;

                DataTable UsersTable = new DataTable();
                Adapter.Fill(UsersTable);
                BindingSource UserTableSource = new BindingSource();

                UserTableSource.DataSource = UsersTable;
                dataGridView1.DataSource = UserTableSource;
                Adapter.Update(UsersTable);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
    private void label1_Click_1(object sender, EventArgs e)
    {

    }

    private void UserManageToolStripMenuItem_Click(object sender, EventArgs e)
    {
        panel2.Visible = true;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        Form3 form3 = new Form3();
        form3.FormClosing += new FormClosingEventHandler(this.Form3_FormClosing);
        form3.Show();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        LoadTable();
    }

    private void Form3_FormClosing(object sender, FormClosingEventArgs e)
    {
        LoadTable();
    }

    private void button3_Click(object sender, EventArgs e)
    {
        dataGridView1.ReadOnly = false;
        button4.Visible = true;
        button5.Visible = true;
    }

    private void button4_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlCommandBuilder scb = new SqlCommandBuilder();
            DataTable dt = new DataTable();

            scb = new SqlCommandBuilder(sda);
            sda.Update(dt);

            dataGridView1.ReadOnly = true;
            button4.Visible = false;
            button5.Visible = false;
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void button5_Click(object sender, EventArgs e)
    {
        LoadTable();
        button4.Visible = false;
        button5.Visible = false;
    }
}
}

Answer 1

This should do what you want.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        SqlCommand sCommand;
        SqlDataAdapter sAdapter;
        SqlCommandBuilder sBuilder;
        DataSet sDs;
        DataTable sTable;        

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connectionString = "Data Source=.;Initial Catalog=pubs;Integrated Security=True";
            string sql = "SELECT * FROM Stores";
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            sCommand = new SqlCommand(sql, connection);
            sAdapter = new SqlDataAdapter(sCommand);
            sBuilder = new SqlCommandBuilder(sAdapter);
            sDs = new DataSet();
            sAdapter.Fill(sDs, "Stores");
            sTable = sDs.Tables["Stores"];
            connection.Close();
            dataGridView1.DataSource = sDs.Tables["Stores"];
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }

        private void new_btn_Click(object sender, EventArgs e)
        {
            dataGridView1.ReadOnly = false;
            save_btn.Enabled = true;
            new_btn.Enabled = false;
            delete_btn.Enabled = false;
        }

        private void delete_btn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                sAdapter.Update(sTable);
            }
        }

        private void save_btn_Click(object sender, EventArgs e)
        {
            sAdapter.Update(sTable);
            dataGridView1.ReadOnly = true;
            save_btn.Enabled = false;
            new_btn.Enabled = true;
            delete_btn.Enabled = true;
        }
    }
}

Answer 2

So in my case I wanted to do the same so that the user could change the status of employees and save that into the datasource! I combined some of my previous knowledge and trials with Brad Larson's answer and I ended up doing this, which worked like a charm for me!

    SqlCommand sCommand;
    SqlDataAdapter sAdapter;
    SqlCommandBuilder sBuilder;
    DataSet sDs;
    DataTable sTable;
    private void StatusEmployeeForm_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'nestorConsultingDBDataSet.Employee' table. You can move, or remove it, as needed.
        string connectionString = your connection string
        string sql = "SELECT * FROM Employee";
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        sCommand = new SqlCommand(sql, connection);
        sAdapter = new SqlDataAdapter(sCommand);
        sBuilder = new SqlCommandBuilder(sAdapter);
        sDs = new DataSet();
        sAdapter.Fill(sDs, "Employee");
        sTable = sDs.Tables["Employee"];
        connection.Close();
        employeeData.DataSource = sDs.Tables["Employee"];
    }

    private void saveBtn_Click(object sender, EventArgs e)
    {
        this.employeeBindingSource.EndEdit();
        this.sAdapter.Update(sTable);
        MessageBox.Show("Changes saved!");
    }

Note you will also need to insert the "using.System.Data.SqlClient;" at the top of your code and of course change the connectionString to the right connection String!