How to save only the new rows AND modified rows in datagridview?-CodePudding

I'm trying to save my data from datagridview row to my sql database. My problem is with my current code, it reads and updates all the rows regardless if it has any changes or not. I know this is going to be a problem especially if my table has large amounts of data.

These are what I did with my code so far:

//retrieve data from dbase
public void loadToDGV()
   {
       DBConn.DBConnect();
       SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT * from TableName", DBConnection.conn);
       sqlDA.Fill(dataTable);
       gridView.DataSource = dataTable;
   }

Below is what's in my Save button.

foreach (DataGridViewRow row in gridView.Rows)
{ 
     DBConn.DBConnect();
     SqlCommand comm = new SqlCommand();
     comm.Connection = DBConnection.conn;
     comm = new SqlCommand("SPName", DBConnection.conn);
     comm.CommandType = CommandType.StoredProcedure;

     comm.Parameters.AddWithValue("@ID", row.Cells["ID"].Value == DBNull.Value ? "" : row.Cells["ID"].Value);
     comm.ExecuteNonQuery();
}

I have another sample code where I can only save modified rows

changeTable = dataTable.GetChanges(DataRowState.Modified);
foreach (DataRow row in changeTable.Rows)
{ 
     DBConn.DBConnect();
     SqlCommand comm = new SqlCommand();
     comm.Connection = DBConnection.conn;
     comm = new SqlCommand("SPName", DBConnection.conn);
     comm.CommandType = CommandType.StoredProcedure;

     comm.Parameters.AddWithValue("@ID", row["ID"].ToString());
     comm.ExecuteNonQuery();
}

What I wanted to do is save only BOTH the "New Rows" AND "Modified Rows". Is there any way to only get newly added/edited rows? and not include all the rows from the gridview?

My Stored Procedure only checks if the ID is existing or not.

IF NOT EXISTS (SELECT 1 FROM TABLE WHERE ID = @ID) -- INSERT QUERY

ELSE -- UPDATE QUERY

CodePudding user response：

You should not be using a loop at all, nor even referring to the grid. You obviously have a DataTable already. If you're not already doing so, populate it with a data adapter, bind it to a BindingSource and bind that to the grid. When it's time to save, call EndEdit on the BindingSource call Update on the data adapter and pass the DataTable. That's it, that's all. There's no need to call GetChanges.

Obviously you will have to configure the data adapter appropriately, with an InsertCommand to insert new records and an UpdateCommand to save modified records. I won't go into specifics as you haven't gone into specifics but there are plenty of examples around. You can find my own here.

EDIT:

The short answer to your question is that, if you want both modified and added rows, then specify both Modified and Added rows. The DataRowState enumeration has the Flags attribute, which means that you can create compound values. That would mean that this:

changeTable = dataTable.GetChanges(DataRowState.Modified);

would become this:

changeTable = dataTable.GetChanges(DataRowState.ModifiedData Or RowState.Added);

If you were going to then loop through those rows, you wouldn't create a new command object every iteration. You'd create one command and add the parameters once, then simply set the Value of each parameter in the loop. You would also need to call AcceptChanges on the original DataTable afterwards.

You should do any of that though. As I said, you should use the same data adapter as you used to retrieve the data to save the changes. Normally you'd use different SQL for the InsertCommand and UpdateCommand but, in your case, you can use the same. Just create a single command object with the appropriate parameters and assign it to both the InsertCommand and UpdateCommand properties, then call Update.