Architectural, design issue, please

[Radu]

Hi. I have the following problem:

The user has to be able to upload lists of PINS (in an Excel/CSV
format). Upon receiving them, the aspnet app must run many rather
complicated queries using the uploaded PIN list and the 'static' files
already in the database. To simplify my coding life, I have imagined
this solution:

a. each user, on login, is assigned a 'Session ID' number, composed of
the PIN of the user and Seconds(now), assuming that no user will login
that many times at the same time... (I should mayube use some other
scheme here, but this, I think, solves MOST of the concurrency
problems). Since Session("Sessio nID") might be lost if the server gets
low on resources, I save this number in a file on the disk and re-
populate Session ID if necessary (I will save it in the db on the
server, or maybe in cookies, if I can be sure that cookies will be
allowed by the users).

I now have a big problem - the queries I have to run are 1)
complicated 2) MIGHT involve many records and 3) will work with a
different dataset (different input PIN list) for each user logged in.

b. I order to avoid concurrency problems, on each upload, I insert the
contents of the file uploaded by the user (Excel/CSV) into a TEMP
table named ImportedPINS, together with the SessionID number which
will act as a selector. So if 5 users will be online at the same time,
each uploading, say, a list of 200 PINS, I will have in table
"ImportedPI NS" a number of 1000 PINS, in 5 sets of 200 each,
differentiated mainly by the SessionID.

>From now on, I will run all my queries with clauses like "WHERE

SessionID = "...", thus separating the datasets.

Because the 'ImportedPINS' table contains an ID column (identity),
which I would like to reset from time to time, and also I don't want
to let 'ImportedPINS' (and similiar tables) grow to much overtime, I:

c. I clean (i.e. recreate) these TEMP tables on "Application_En d" in
Global.ASAX with code like this:

Sub Application_End (ByVal sender As Object, ByVal e As EventArgs)

Dim cnSQL As New SqlConnection
Dim cmd As SqlCommand

Try
Context.Cache.R emove("Template s")
'The session variables simply expire on session end...

'Clean up/recreate the temp tables ImportedFiles, DisplayFiles, etc:
cnSQL = New
SqlConnection(S ystem.Configura tion.Configurat ionManager.AppS ettings("Connec tionString"))
cnSQL.Open()

cmd = New SqlCommand("[CreateTableImpo rtedPins]", cnSQL)
cmd.CommandType = CommandType.Sto redProcedure
cmd.ExecuteNonQ uery()

Catch ex As Exception

Finally
Try
'Explicitly close - don't wait on garbage collection.
cmd = Nothing
cnSQL.Close()
Catch ex As Exception

End Try
End Try
End Sub

Questions:

1. Is this architecture safe ? Does it make sense ? Is there any
other, more intelligent and hopefully not much more laborious,
solution ?

2. I have a problem - the code above, in Global.ASAX, in
Application_END , NEVER EXECUTES !!!! I wonder what's the problem...
Did I make a mistake ?

Thank you very much.
Alex.