It seems so easy to say the data in dataset can be edited and updated to the database. But in reality there are abundant problems surrounding this single concept. This is because there are numerous different situations pertaining to this simple concept. Some are, what if the underlying table has no primary key? what if the underlying table is a result of join on 2 different tables? what if the underlying table does have a primary key? Will the dataset created in a Button_Click be carried over to the other events of the gridview/ any databound control for that matter? What about AcceptChanges(), BeginEdit()?
We will analyze every question one by one here.
what if the underlying table has no primary key?
if the dataTable of Dataset has no PK , manually set a PK on any column of the dataTable.
dset.Tables[0].PrimaryKey = new System.Data.DataColumn[] { dset.Tables[0].Columns[1] };
To have more than 1 column as a PK, i.e, composite PK, use :
dset.Tables[0].PrimaryKey = new System.Data.DataColumn[] { dset.Tables[0].Columns[1] , dset.Tables[0].Columns[2]};
Remember, PrimaryKey property returns a DataColumn[]. So you need to set a DataColumn array value to it otherwise it throws an error.
what if the underlying table is a result of join on 2 different tables?
if the Dtbl is a result of join, then the DataTable will not have any PK on it. In this case, you have to manually set it.
what if the underlying table does have a primary key?
This usually happens if the DataTable is a result of some SELECT statement and the SELECT statement gives a column set as PK. So in this case, we have to make sure the CONSTRAINTS info is updated in the dataset. This is because when you call a SELECT statement, the schema info is not added to the dataset. It should be done manually thus:
da.FillSchema(dset, SchemaType.Source, “Authors”);
if you didn’t use the above statement, you can also use the below one:
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
If you manually set the PK on the underlying DaTbl, donot use the above code. If you use both, it will throw exception.
Will the dataset created in a Button_Click be carried over to the other events of the gridview/ any databound control for that matter?
No. the dset will not be carried over. So you have to have a method to fill the dataset and call that method in your other DataBound events.
What about AcceptChanges(), Begin/EndEdit()?
To make changes in any DataRow, I need to create a new DataRow. But I cannot use DataRow() because this cannot be used. You need to use
DataRow dr = dt.NewRow();
and assign the dr with the required row,
dr = dt.Rows.Find(“Aria Cruz”);
Following the above,
dr.BeginEdit();
dr["EMPLOYEE_NAME"] = “Aria Tamil”; //changed the original name to Aria Tamil
dr.EndEdit();
dset.AcceptChanges(); //committing the dataset with the changes
da.Update(dset); //pass the changed dataset to d/b
Tags: AcceptChanges, BeginEdit, datarow, dataset, edit, update
