Solved

How to use temp table in Stored Procedure with LINQ to SQL

Posted on 2011-09-09
9
2,029 Views
Last Modified: 2013-11-11
I am selecting certain records from a table randomly and saving them in a temp table. I have created a stored procedure to select into this table and need to pull these randomly generated records in different parts of my c# code . Another stored procedure to pull the values from this sp later on. While trying to run the first stored procedure i am getting this error  

"The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window."

Any ideas how to fix this error or a better approach to handle this situation?

I have also tried creating a table variable as mentioned on this site http://odetocode.com/code/365.aspx  but I can't access this Table variable in second stored procedures in parts of code


0
Comment
Question by:mmalik15
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36516184
You're getting that error because the table does not exist when LINQ tries to determine what gets returned.  It can be avoided by not using an actual temporary table but a real one instead.  If you call it something like TmpTable or Temp.Table (using s special "temporary" schema), you would avoid that error and your DB would still remain fairly clean.

Another option is editing the DMBL as can be read here: http://riteshkk2000.blogspot.com/2010/08/error-unknown-return-type-return-types.html
But I'm not familiar with that myself so can't say more about it...

Hope that helps you further a bit?
0
 

Author Comment

by:mmalik15
ID: 36516255
thanks for the comment valentionV.

How can I use a real temp table schema. Can you give me an example of it? All I need is temp table for storing certain questions and keep the data until the the user clicks on finish button. And there can be concurrent users too for this application, each having their own set of questions.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518905
Sorry if I wasn't clear.  What I meant is that you can create a new schema called Temp for instance.  In that schema you would create a table that has the same layout as your current temporary table.  This Temp.SomeTable would then be used as a "temporary" table, in the meaning that the data that it contains is temporary, but the table itself is not.

As for concurrent users, you could create an additional column in your "temporary" table to indicate what user the record belongs to.

Note that you'll need some sort of cleanup process as well.  When would records become obsolete?
0
 

Author Comment

by:mmalik15
ID: 36518925
What you're saying is to create a temp table in the database just like any other table and insert randomly picked questions in it along with the userinfo? And delete the records from that table when the user clicks finish test button from that table?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518936
That's right, but it's not a "temp table", it's a table just like any other.  Based on the info you've given, it seems like what you're needing to deal with is some sort of session state.  What happens if the user taking the test closes the application by accident, or is faced with a crash?  I think it would be beneficial if that user would be able to continue the test from the point where it ended.  And that should be possible using the table as I've described it.  On the other hand, it would be a problem with the temporary table that you were thinking to use.
0
 

Author Comment

by:mmalik15
ID: 36518943
it sounds like a good idea but yes the problem can arise when the user has accidently closed the browser without clicking the finish button when we clear records from this table
0
 

Author Comment

by:mmalik15
ID: 36518957
I dont want the user to resume quiz if he/she has closed the browser rather the user has to start the test again. And I only want to save the results when the user has clicked on finish button
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 36519189
If you keep a timestamp in the table as well, that should help you to clean out the outdated records.  You could run a nightly job that deletes every record older than 24 hours for instance, thereby deleting the "orphaned" records.
0
 

Author Closing Comment

by:mmalik15
ID: 36596210
sorry for late reply. thanks
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore 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.
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

23 Experts available now in Live!

Get 1:1 Help Now