C# DataGridViewRow - is new or updated row

Question

I have DataGridView where I am showing data read from database:

DataSet ds = new DataSet();
sqlDa.Fill(ds);
dgView.DataSource = ds.Tables[0]

After adding all of the rows in the UI, I need to to SQL UPDATE of rows that previously read from database, and do INSERT for new rows by clicking Save button (I don't save rows one by one when adding, just all of them when I click the Save button):

foreach (DataGridViewRow dgvRow in dgView.Rows)
{
  // do insert for new rows, and update for existing ones from database
}

How can I know what rows are newly added and what are not? Can I add some type of attribute to every row that is read from database so that can I know that they need to be updated?

Answer 1

How can I know what rows are newly added and what are not?

You don't need to; the datatable the DGV is showing is already tracking this. If you make a SqlDataAdapter and plug a SqlCommandBuilder into it _{see the example code in the docs} so that it gains queries in its InsertCommand/UpdateCommand/DeleteCommand properties (or you can put these commands in yourself, but there isn't much point given that a command builder can make them automatically) then you just say:

theDataAdapter.Update(theDataTable);

If you didn't save it anywhere else you can get it from the DataSource of the DGV:

theDataAdapter.Update(dgView.DataSource as DataTable);

Ny the way, the word "Update" here is nothing to do with an update query; Microsoft should have called it SaveChanges. It runs all kinds of modification query (I/U/D) not just UPDATE

---

If you really want to know, and have a burning desire to reinvent this wheel, you can check a DataRow's RowState property, and it will tell you if it's Added, Modified or Deleted, so you can fire the appropriate query (but genuinely you'd be reimplementing functionality that a SqlDataAdapter already has built in)

---

All this said, you might not be aware that you can make your life massively easier by:

• Add a new DataSet type of file to your project (like you would add a class). Open it
• Right-click in the surface of it, choose add TableAdapter
• Design your connection string in (once)
• Enter your query as a "select that produces rows" like SELECT * FROM SomeTable WHERE ID = Id (it's advisable to use a where clause that selects on the ID; you can add more queries later to do other things, like SELECT * FROM SomeTable WHERE SomeColumn LIKE @someValue but for now selecting on ID gives you a base query to use that is handy for loading related data). You can also use existing or new stored procs if you want
• Give it a sensible name pair like FillById, GetDataById - FillBy fills an existing table, Get gets a new one
• Finish

You'll now have objects available in your code that are wrappers data adapters and datatables - same functionality but more nicely strongly typed

e.g. you can fill your grid with:

var ta = new SomeTableAdapter();
dgView.DataSource = ta.GetDataByFirstName("John%"); //does select * from table where firstname like 'john%' into a datatable

The datatables are strongly typed, so you don't access them like this:

//no
foreach(DataRow row in someTable.Rows){
  if((row["someColumn"] as string) == "hello" && row.IsNull("otherColumn"))
    row["otherColumn"] = "goodbye";
}

You have named properties:

//yes
foreach(var row in someTable){
  if((row.SomeColumn == "hello" && row.IsOtherColumnNull())
    row.OtherColumn = "goodbye";
}

Much nicer. LINQ works on them too, without AsEnumerable or Cast and endless casting the values.

It's not magic; VS writes boatloads of code behind the scenes for you - check in the YourDataSet.Designer.cs file - hundreds of SqlCommands, fully parameterized, for all the table operations (Select/Insert/Update/Delete), all base don typing a SELECT command into a tool pane. It's quite nice to use really, even all these years later.

Oh, but the designer doesn't work very nicely in net core. They're really lagging behind on fixing up the bugs that netcore brings (other priorities)