Hello all,
I am having a unique problem.
I am caching a DB table (since I have to use it very frequently at a different place) and I update it now and then.
I want to save the changes immediately to both Cache + Database. Everything I try - AcceptChanges works only for SELECT, UPDATE, DELETE commands if given for that table but actually works only on directly database; Merge Adds another few records to DB; Update() updates only the cache- has some limitation.
So Problem is how do i get to where I want -
1) if there is a change in original database then reflect that in cache ; and
2) if there is change in Cache - reflect in Original
I am attaching the code that I am using and maybe you will have a better idea then. Same Code runs twice one after another and actually gives different output because the updated original table in SQL is not updated in cache section.
Please tell me how do I solve this problem What would be the right way out
-------------------------------------------------------------------------------
string command = "Select * from TradesLog"; SqlDataAdapter adapter = new SqlDataAdapter(command, con.Con_eFOCUSys); DataSet set = new DataSet();adapter.Fill(set);
DataTable table = set.Tables[0]; foreach (DataRow row in table.Rows){
Console.WriteLine(row[0] + " :: " + row[1]);}
adapter.UpdateCommand =
new SqlCommand("Update tradeslog set Lot = 1", con.Con_eFOCUSys); Console.WriteLine("----------");table.Rows[0][0] = 7;
DataTable t = table.GetChanges(DataRowState.Modified); foreach (DataRow row in t.Rows)
{
Console.WriteLine(row[0] + " :: " + row[1]);}
adapter.Update(t);
table.AcceptChanges();
Console.WriteLine("------------"); foreach (DataRow row in table.Rows){
Console.WriteLine(row[0] + " :: " + row[1]);}
----------------------------------------
First Output
-----
5 :: 5/12/2006 10:23:05 PM
5 :: 6/12/2006 11:41:31 PM
----------
7 :: 5/12/2006 10:23:05 PM
------------
7 :: 5/12/2006 10:23:05 PM
5 :: 6/12/2006 11:41:31 PM
_______________________
Second Output - Problem is the change is being shown now as 99 whereas i wanted to see it in first output but in the third section - just after AcceptChanges() - if possible
----
99 :: 5/12/2006 10:23:05 PM
99 :: 6/12/2006 11:41:31 PM
----------
7 :: 5/12/2006 10:23:05 PM
------------
7 :: 5/12/2006 10:23:05 PM
99 :: 6/12/2006 11:41:31 PM
Simultaneous Update of Cached + Original DB
GHtokyo
lowe0
And in you update command you are only updating one field and it is hardcoded to. You should use parameters here.
Conradical
Also, whenever you are using SqlCommandBuilder class make sure you have a Primary Key in the Table!
That's gonna work fine then.