Add row while assigning datasource for datagridview

Question

I have a datagridview assigned a datasource to it. now how to add a new row to that grid and remove a row from it?

Answer 1

One way to do this is as follows:

Step #1 Setup the Data Adapter, Data Grid etc:

// the data grid
DataGridView dataGrid;

// create a new data table
DataTable table = new DataTable();

// create the data adapter
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strDSN);

// populate the table using the SQL adapter
dataAdapter.Fill(table);

// bind the table to a data source
BindingSource dbSource = new BindingSource();
dbSource.DataSource = table;

// finally bind the data source to the grid
dataGrid.DataSource = dbSource;

Step #2 Setup the Data Adapter SQL Commands:

These SQL commands define how to move the data between the grid and the database via the adapter.

dataAdapter.DeleteCommand = new SqlCommand(...);

dataAdapter.InsertCommand = new SqlCommand(...);

dataAdapter.UpdateCommand = new SqlCommand(...);

Step #3 Code to Remove Select lines from the Data Grid:

public int DeleteSelectedItems()
{
    int itemsDeleted = 0;

    int count = dataGrid.RowCount;

    for (int i = count - 1; i >=0; --i)
    {
        DataGridViewRow row = dataGrid.Rows[i];

        if (row.Selected == true)
        {
            dataGrid.Rows.Remove(row);

            // count the item deleted
            ++itemsDeleted;
        }
    }

    // commit the deletes made
    if (itemsDeleted > 0) Commit();
}

Step #4 Handling Row Inserts and Row Changes:

These types of changes are relatively easy to implement as you can let the grid manage the cell changes and new row inserts.

The only thing you will have to decide is when do you commit these changes.

I would recomment putting the commit in the RowValidated event handler of the DataGridView as at that point you should have a full row of data.

Step #5 Commit Method to Save the Changes back to the Database:

This function will handle all the pending updates, insert and deletes and move these changes from the grid back into the database.

public void Commit()
{
    SqlConnection cn = new SqlConnection();

    cn.ConnectionString = "Do the connection using a DSN";

    // open the connection
    cn.Open();

    // commit any data changes
    dataAdapter.DeleteCommand.Connection = cn;
    dataAdapter.InsertCommand.Connection = cn;
    dataAdapter.UpdateCommand.Connection = cn;
    dataAdapter.Update(table);
    dataAdapter.DeleteCommand.Connection = null;
    dataAdapter.InsertCommand.Connection = null;
    dataAdapter.UpdateCommand.Connection = null;

    // clean up
    cn.Close();
}