Avatar of venkataramanaiahsr
venkataramanaiahsr
 asked on

dataset refresh after update to mssql db(Sql2008R2)

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.
Visual Basic.NETMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
venkataramanaiahsr

8/22/2022 - Mon
Éric Moreau

you should consider moving to Entity Framework.
Nasir Razzaq

> 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.
Jacques Bourgeois (James Burger)

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
venkataramanaiahsr

ASKER
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.
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
venkataramanaiahsr

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