update a dataset corresponding to a database from it's datagridview

Question

there is a datagridview corresponding to a table of some database. this database has a identity (auto-incrementing) column named "id".this column is primary key,also. user just see a blanked and empty datgridview.he/she can modify this table(datagridview) for example add new row,remove it,edit(update)one cell and can do every possible modification (i have tried sqlcommandBuilder but no result is obtained) how can i do this? I use sql server and c# cheers

Answer 1

While there are lots of data binding methods out there (SQLcommandbuilder, objectcommandbuilder, etc) I have always had the best luck when I control more of the process. I have used this since the old DAO and ADO days. Call it old fashioned, but you will quickly develop a code base that is tested and easy to modify.

My current method is adapted for the ASP.NET gridview, but the principals are the same.

1. Write data binding code that fills a dataset (ds.Tables(0))
2. Build a grid with BoundColumns - don't let it "autogenerate columns".
3. Add button columns for Edit, Update and Cancel. Show/hide columns as appropriate. One row in edit mode at a time.
4. Bind the dataset to the grid's datasource. This isn't really creating a link back to the dataset, just populating a static grid from the dataset.
5. Handle the Edit, Update and Cancel events. That means writing routine code that creates parameters and calls ExecuteDataSet or ExecuteNonQuery and then requeries and rebinds the grid after each update.