How to INSERT added rows and UPDATE edited ones in a DataGridView?

Question

I have a Form that shows information about a purchase and within this form I have a DataGridView whose columns are dynamically generated based on a table on the database. This DGV contains information about the items in the selected purchase.

Here's an example of how I generate the columns in runtime in an existing DataGridView:

            foreach (ColumnDGV columnDGV in columnsDGV)
            {
                DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
                col.Name = columnDGV.A02_CAMPO;
                col.HeaderText = columnDGV.A02_TITULO;
                col.DataPropertyName = columnDGV.A02_CAMPO;
                this.dataGridView.Columns.Add(col);
            }

The object ColumnDGV contains information about the column to be created.

Then I select the columns' names from the columns list and perform the sql query based on those columns:

    private void CarregarConteudoDGV()
    {
        SolicitCompraBLL solicitCompraBLL = new SolicitCompraBLL();

        // Gets the columns' names to be used in the SELECT statement.
        List<string> columnsNames = this.CamposDGV.Select(c => c.A02_CAMPO).ToList();

        // Returns a DataTable containing the items of the selected purchase.
        this.dataGridView.DataSource = solicitCompraBLL.ObterItensSolicitacao(columnsNames, this.B11_NUM);
    }

When the user clicks in the save button I just iterate through all rows in the DGV and add its values to a nested List where each item is a list of string[2], where the position 0 contains the column name and position 1 its value. Then I pass this List to a method that creates the sql command to update the records.

I can already save the changes of each item in the database, but I also have to insert the added items. How do I know which items should be inserted and which ones should be updated?

this.dataGridView.DataSource will build DataTable with columns so you don't have to add the columns in code. So get rid of the foreach loop. Use a DataAdapter (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter(v=vs.110).aspx) which in the command keeps track of new and added items in the datatable. — jdweng, Oct 16 '16 at 00:07
@jdweng That would be difficult because unlike the example I provided some columns are using a custom DataGridView column type that will show a user control when in edit mode. — hideki.eduardo, Oct 16 '16 at 00:26
[CRUD Operations using DataGridView, DataTable and TableAdapter](http://stackoverflow.com/a/36274706/3110834) — Reza Aghaei, Oct 16 '16 at 05:19
You could create a class that inherits the IDataAdapter. See msdn : https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx — jdweng, Oct 16 '16 at 07:42
@jdwent just another question: I've noticed that the DataGridView automatically increments the column that corresponds to the primary key when a new row is added. The corresponding field in the database is defined as auto_increment. Can't this cause any problems if two users add an item to the same purchase, or the ID generated by my application is ignored when a new record is inserted? — hideki.eduardo, Oct 16 '16 at 13:10

score 1 · Accepted Answer · answered Oct 16 '16 at 00:12

When you insert an object in DataBase, generally your object don't have yet an Id. The object to be update must have their Id's existing yet. Now you should see if the object has an Id or Not. If the Id is Zero (or does not exist) then you insert it, otherwise you update it.

How to INSERT added rows and UPDATE edited ones in a DataGridView?

1 Answers1