dataset refresh after update to mssql db(Sql2008R2)

venkataramanaiahsr
venkataramanaiahsr used Ask the Experts™
on
Step1 - I am retriving db data from 3 stored procedures into 3 tables of a dataset. i have established a relation ship between the three tables.  table1 - linked to - table 2 and table 2  - linked to table 3.  hierarchial structure.

Step 2  - I am binding these tables to a grid in the front end.  when user clicks on any row in grid 1 (table 1) all the relevent rows in grid 2(table 2) is displayed. when user clicks on any row of grid 2 (table2) all relevent rows of grid 3 (table3) is getting displayed.

Step 3 - i edit the data in the grid and submit the data using data table. I am having a insert_update stored procedure wherein it accepts a table variable and using merge statement it either updtates/inserts depending the on the condition of data.
all this logic is created in a DAL layer different from UI. i am passing datatable byval.

Up  to this it is working fine and updates and inserts are happening fine.

Now once the update is done, to refresh the data in the grid i am loading the data entirely
(Step 1).  This is causing some performance issues. is there any way to refresh the dataset moment it  executes the insert_update stored procedure for the updates in the db and reflect these changes back to ui and the grid. I am using  executenonquery of command object in excuting insert_update procedure.

Request some expert to respond at the earliest.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you should consider moving to Entity Framework.
Most Valuable Expert 2012
Top Expert 2014

Commented:
> i edit the data in the grid

Are the changes not coming from the UI? If yes then why you need to refresh? UI is already showing updated data right? Selectively updating the datatable is not practical I think.
Top Expert 2015

Commented:
What do you want in your refresh.

Only the ID (Identity / AutoNumber) generated by Inserts? This can be sent back to your application by the stored procedure by returning @@IDENTITY.

Do you need to see the changes made by other users on the data that you have in your DataSet? If so, when you refresh, do you use a Merge or do you clear the DataSet and rebuild it from scrach?

Something else?

Solutions could be different depending on your meaning of refresh and the exact mechanism that you use to perform it.

You should also test to see if the performance problem comes from your reload of the data or when rebinding.

How many rows / columns do you have? Do you need all of those? DataSets and DataGrids are memory eaters, and I do not have enough fingers and toes to count the number of times where I have encountered programmers that grab 10 times the amount of data needed for a specific job, specially when a user interface is concerned.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Hi

to be precise, the flow is as mentioned below.

Data retrival :  I have 3 stored procedures


usp_GetBydatePatientRegn_WithLabno --   Parent grid (Topmost hierarchy) - single day's trn.  -  columns from multiple tables  

usp_GetBydatePatientRegnLabAdvise_WithLabno -  child grid - 2nd one  -  relevent records for the ones retireved in  usp_GetBydatePatientRegn_WithLabno .

usp_GetBydatePatientRegnLabRep -  grandchild  3rd one -- relevent records for the ones retrieved from usp_GetBydatePatientRegnLabAdvise_WithLabno

data from the above stored proceudres are retrived into single dataset ds with 3 tables.
Data relation is established between the three tables.

Data Update:

I selecting few relevent columns from the grid into a new datatable and passing into
usp_InsertUpdate_PatientRegnLabRep  which accepts table parameter. using merge statement  i am updating the table in db  and also updating few flags in another table.

though the edited data is avialable in ui at the time of submission, I need to have  these flag status  updtated in the grid after successful updation.

I am reexcecuting the data retrieval sps, to get the updated stauts of flags in the grids of UI.  

My question is there any way to get the update stauts of these flags in the relevent tables of dataset  the moment i execute usp_InsertUpdate_PatientRegnLabRep  without reexecuting all the data retrival sps again.
Senior .Net Consultant
Top Expert 2016
Commented:
you can update the datasets read from your data retrieval manually instead of requiring everything.

Author

Commented:
Thanks emoreau. Manual update for only status flags in the grid did the trick.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial