Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

dataset refresh after update to mssql db(Sql2008R2)

Posted on 2012-12-31
6
306 Views
Last Modified: 2013-01-02
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.
0
Comment
Question by:venkataramanaiahsr
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38732509
you should consider moving to Entity Framework.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38733563
> 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.
0
 
LVL 40
ID: 38733650
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:venkataramanaiahsr
ID: 38734164
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.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 38734417
you can update the datasets read from your data retrieval manually instead of requiring everything.
0
 

Author Closing Comment

by:venkataramanaiahsr
ID: 38736172
Thanks emoreau. Manual update for only status flags in the grid did the trick.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vb.net class 3 21
VB.net Open video relating to control 2 20
Negative isnull? 3 15
SQL Server Shrink hurting performance? 4 21
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question