2

In our ASP.NET 4.6 webforms application using SQL Server, we want a page to use a #temptable, but we cannot get it to work. Page1 creates the #temptable in SQL Server, and then launches Page2 that wants to put the #temptable into a GridView to be edited within the grid.

It appears that the scope of the #temptable does not extend beyond Page1 on the server when the Page2 is loaded and becomes visible to the browser.

We are considering using a table in the DB itself, not #temptable. Also, other users may be able to be editing the same table but with different "data". We thought that #temptable would belong to a specific user (from the browser).

Any guidance will be appreciated. Thanks...John.

ANSWER : Based on @Faruq's explanation, we will use a normal DB table for each user.

John D
  • 517
  • 7
  • 22
  • 1
    Possible duplicate of [Difference between #temptable and ##TempTable?](https://stackoverflow.com/questions/21011276/difference-between-temptable-and-temptable) – hardkoded Mar 23 '18 at 14:22
  • @hardkoded ##TempTable is not a solution in this case. – Faruq Mar 23 '18 at 15:23
  • The two will have nothing to do with eachother. The temp table exists within the scope of the thing that created it, such as a stored procedure. It won't have anything to do with the page. You'll want to research a different mechanism to do this, but also look at why you need to move information around like this if it's temporary. Perhaps page 1 and page 2 can be combined into the same page but with different regions that use the same data in viewstate to finish building it out before sending it to the database. – Mark Fitzpatrick Mar 23 '18 at 15:38

1 Answers1

5

I am afraid neither #tempTable nor ##tempTable is an option for you. Let me explain a bit.

Local Temporary Table:
When you use #someName in SQL server, it creates a temporary table with a scope of current database connection. In your case, the #tempTable is created in Page1 and dropped as soon as the database connection is closed in Page1. So, that table doesn't exists anymore when you are opening a connection from Page2.

Global Temporary Table:
When you use ##someName(notice the double hash), it creates a global temporary table which is accessible from multiple connection. In a way this works like a normal database table but lives inside the tempdb and usually slower than a normal table. In your case, you need a table per user; so global temp table doesn't fit that criteria.

Session Storage:
If the data is small, you can consider to store in a session variable. Also, there is an option to configure Asp.Net session to use SQL database. I never did this before, but this is something you can look into. However, be very careful while storing objects in session variable as all referenced objects (and their referenced objects and so on) will be serialized and stored in session as well.

Database Table
In my opinion, your best bet is to use a normal database table to store all users' data and use a column with per-user-key(like SessionId) to keep the data separated. You also have to implement a cleanup technique to get rid of old data.

Faruq
  • 1,361
  • 1
  • 11
  • 23
  • Thank you very much @Faruq. This is the answer. Your explanation is clear and I understand each section. So our team was thinking correctly (see original question) , when we thought about a normal DB table. We name the table by the user's unique login-name. And save all changes to this temp-table. User on Page2 can then "save all" or "cancel all" changes (Inserts, Updates and Deletes). Thank you for an excellent reply. I need to give you points!!!! – John D Mar 23 '18 at 16:01