C# Transaction with store procedure

Question

Hello all,         I need help...    I have c# code in which i have transaction  I am adding row to table in transaction and updating same table's column using procedure in same transaction. But i am not getting updated rows when I am retriving from store procedure . Example:   db.Connection.Open(); using (var dbcxtransaction = db.Connection.BeginTransaction()) { while (odr.Read()) { var model = new TEMP_EXCEL(); model.EXID = Convert.ToInt32(odr[0]); model.EXCOL1 = odr[1].ToString().Trim(); model.EXCOL2 = odr[2].ToString().Trim(); db.TEMP_EXCEL.AddObject(model); db.SaveChanges(); } /// comment transaction.commit() I dont want to insert any data permanently in TEMP_EXCEL IList EXCEL = db.PROC_UPLOADMACHINES("Y").ToList(); db.SaveChanges(); }   In above example m adding rows in TEMP_EXCEL which is in transaction but I don't want to commit any data in TEMP_EXCEL   alter PROCEDURE PROC_UPLOADMACHINES @saveFlag varchar(1) AS BEGIN declare @hasError bit update TEMP_EXCEL set EXCOL3 ='test' select * from TEMP_EXCEL END GO   I am not getting updated record in
IList EXCEL = db.PROC_UPLOADMACHINES("Y").ToList();     Plz help

Hemant Panchal · Answer

Hi thanks for solution but I tried this way and it is working   db.Connection.Open(); using (var transaction = db.Connection.BeginTransaction()) { while (odr.Read()) { var model = new TEMP_EXCEL(); model.EXID = Convert.ToInt32(odr[0]); model.EXCOL1 = odr[1].ToString().Trim(); model.EXCOL2 = odr[2].ToString().Trim(); db.TEMP_EXCEL.AddObject(model); db.SaveChanges(); } db.PROC_UPLOADMACHINES("Y"); db.Refresh(System.Data.Objects.RefreshMode.StoreWins, db.TEMP_EXCEL); IList vExcel = db.TEMP_EXCEL.ToList(); }   ///// now vExcel has updated list    :) :)

Manoj Bhoir · Answer

Hi Hemant,   In your code you are using Transactions and you said you don't want to commit your changes becuase you don't want to commit any data in TEMP_EXCEL Table.   So my question is you are not committing any changes in TEMP_EXCEL then how you can expect to get latest changed values from the same table? Don't you think its something which is logically incorrect.   So what i will suggest you just follow these steps :   -Add row in your table -Commit this transactioin -Do whatever you want with that data -and after that if you don't require this data then delete that data from the table or simply truncate the table.