2010-04-01
We frequently get to fetch data from the database, update them and then write them back to the database.
Most of the time we only have to write them back as individual records.
How about updating a whole database table in the memory and having to synchronize all changes to the actual data table?
My initial thought was this would be full of complex coding. However, thanks to Microsoft, there’s nothing much to be done at all.
So how are we gonna do this is…
We will use a data adaptor to fill our data table as usual.
The only new thing is the use of a Command Binder.
Command Binder: A command binder is capable to detect changes that have occurred to a table (in the memory) and then automatically generate appropriate SQL statements to save those changes into the actual data table (in the database).
Following is a simple example: Scenario: In my application I had to take a database table name and present data in a data grid and then save all changes made by the user.
What we need: a data table an adaptor a connection a command builder I have defined them at the form level so I can use them across the form from different events.
private DataTable _tbl; private SqlDataAdapter _adptr; private SqlConnection _conn; private SqlCommandBuilder _cbldr;
Step1: Initialize connection and retrieve data from the database
_tbl = new DataTable();
_conn = FetchData.GetOpenConnection();
_adptr = new SqlDataAdapter("Select * from " + DatabaseTableName, _conn);
_cbldr = new SqlCommandBuilder(_adptr);
_adptr.Fill(_tbl);
Step2: Let the user to change data (in here simply bind the table to a grid)
dgMainGrid.DataSource = _tbl;
Step3: Save (synchronize) changes to the actual database table Even though this is a complex process and undoubtedly would take a lot of effort to do manually, Thanks to .net framework all we need is a line of code.
_adptr.Update(_tbl);
Once you call the adaptor to update the table, it will use the command builder attached to it to generate all necessary SQL command building. The database table is now up-to-date!
Was this post helpful to you? How can I improve? – Your comment is highly appreciated!
Cassian Menol Razeek
I Learnt Today 