Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dataset refresh after update to mssql db(Sql2008R2)

Posted on 2012-12-31
6
Medium Priority
?
312 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
[X]
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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