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
.