Solved

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

Posted on 2011-09-09
9
2,046 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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 Detach & Attach 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.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 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