Updating data in data set and data table.

[C# Programming]

I am using a data grid view which loads data from the database and displays. Moreover, on my form are the buttons as INSERT(i.e for inserting records in database and grid), DELETE_SELECTED_ROWS(i.e for deleting records from database and grid). Now as you know that grids are editable so if any changes are made inside the grid then how to update.


Talking all in all I need to know how to update a dataset/datatable



private void ManageCustomer_Load(object sender, EventArgs e)
{
LoadCustomers();
}

private void btnInsertInDataBase_Click(object sender, EventArgs e)
{
// Insert Customer In Database

DALHelper helper = new DALHelper();
helper.InsertCustomer(textBox1.Text);
LoadCustomers();

}

private void LoadCustomers()
{
// Load Cashiers from Database
DALHelper dal = new DALHelper();
dataGridView1.DataSource = dal.GetCustomers();
dataGridView1.*******();
}


ListcustomerIDsToDelete = new List();

private void btnDELETE_Click(object sender, EventArgs e)
{
// Iterate all the Rows in DataGridView Rows Collection
foreach (DataGridViewRow row in dataGridView1.Rows)
{
// Get the status of CheckBox in the current Row
bool deleteStatus = Convert.ToBoolean(row.Cells[0].Value);

// Get the CashierID of the Cashier in the current row
int customerID = Convert.ToInt32(row.Cells[1].Value);

if (deleteStatus)
{
row.Selected = true;
customerIDsToDelete.Add(customerID);
}
}

// Remove From DataGridView

foreach (DataGridViewRow selectedRow in dataGridView1.SelectedRows)
{
dataGridView1.Rows.Remove(selectedRow);
}


// Remove From Database

DALHelper dal = new DALHelper();
dal.DeleteCustomers(customerIDsToDelete);

customerIDsToDelete.Clear();

// Load Cashiers from Database

LoadCustomers();

}

}
}

// THE FOLLOWING IS THE CODE I AM USING IN MY DAL(Data Access Layer) CLASS
public DataTable GetCustomers()
{
string query = "SELECT * FROM Customer_2";
SqlDataAdapter da = new SqlDataAdapter(query, constr);
DataTable table = new DataTable();
da.Fill(table);
return table;

}

// For Inserting Customers
public void InsertCustomer(string customerName)
{
string query = "INSERT INTO Customer_2 (CustomerName) VALUES (@CustomerName)";
SqlConnection con = new SqlConnection(constr);
SqlCommand com = new SqlCommand(query, con);
com.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = customerName;
con.Open();
com.ExecuteNonQuery();
con.Close();
}


// For Deleting Customers

public void DeleteCustomers(List<int> customerIDsToDelete)
{
string query = "DELETE FROM Customer_2 WHERE CustomerID = @CustomerID";
SqlConnection con = new SqlConnection(constr);
SqlCommand com = new SqlCommand(query, con);
SqlTransaction tr = null;

try
{
con.Open();
tr = con.BeginTransaction();
com.Transaction = tr;
com.Parameters.Add("@CustomerID", SqlDbType.Int);

foreach (int item in customerIDsToDelete)
{
com.Parameters["@CustomerID"].Value = item;
com.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
throw ex;
}
finally
{
con.Close();
}
}