Solved

dataset refresh after update to mssql db(Sql2008R2)

Posted on 2012-12-31
6
296 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 69

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 69

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now