How to make sure no other table writes happen between 2 SQL statements ?

Ok, here is my situation....

When someone navigates to a user's profile page on my site, I present them with a slideshow of the user's photos using the AJAX slideshow extender. I obtain the querystring value in the URL (to determine which user's page I'm on) and feed that into a webservice via a context value where an array of photos is created for the slideshow. Now, in order to create the array's size, I do a COUNT of all of that specific user's photos. Then, I run another SQL statement to obtain the path of those photos in the file system. However, during the time of that first SQL query's execution (the COUNT statement) to the time of the second SQL query (getting the paths of the photos), the owner of that profile may upload or delete a photo from his profile. I understand this would be a very rare occurrence since SQL statements 1 and 2 will be executed within milliseconds of each other, but it is still possible I suppose. When this happens, when I try to populate the array, either the array will be too small or too large. I'm using SqlDataReader for this as it seems to be less memory and resource intensive than datasets, but I could be wrong since I'm a relative beginner and newbie. This is what I have in my vb file for the webservice....

PublicFunction GetSlides(ByVal contextKeyAsString)As AjaxControlToolkit.Slide()
Dim dbConnectionAsNew SqlConnection("string for the data source, etc.")

Try
dbConnection.Open()

Dim memberId =CInt(contextKey)
Dim photoCountLookupCmdAsNew SqlCommand _
("SELECT COUNT(*) FROM Photo WHERE memberId = " & memberId, dbConnection)
Dim thisReaderAs SqlDataReader = photoCountLookupCmd.ExecuteReader()

Dim photoCountAsInteger
While (thisReader.Read())
photoCount = thisReader.GetInt32(0)
EndWhile
thisReader.Close()

Dim MySlides(photoCount - 1)As AjaxControlToolkit.Slide

Dim photoLookupCmdAsNew SqlCommand _
("SELECT fullPath FROM Photo WHERE memberId = " & memberId, dbConnection)
thisReader = photoLookupCmd.ExecuteReader()

Dim iAsInteger
For i = 0To 2
thisReader.Read()
Dim photoUrlAsString = thisReader.GetString(0)
MySlides(i) =New AjaxControlToolkit.Slide(photoUrl,"","")
Next i
thisReader.Close()

Return MySlides

Catch exAs SqlException

Finally
dbConnection.Close()

EndTry

EndFunction

I'm trying to use the most efficient method to interact with the database since I don't have unlimited hardware and there may be moderate traffic on the site. Is SqlDataReader the way to go or do I use something else? If I do use SqlDataReader, can someone show me how I can run those 2 SQL statements in best practice? Would I have to somehow lock writing to that table when I start the first SQL statement, then release the lock after I execute the second SQL statement? What's the best practice in this kind of scenario.

Thanks in advance.

Hello S2KDriver,

Why don't you use a SqlDataAdapter and fill a DataTable with your second SELECT statement (e.g. the photoLookupCommand). You can use the rowcount property to get the number of rows retrieved.

In this scenario you still have the chance that when Slides are added to the AjaxControlToolkit, the photo is deleted and not available.

|||

Hi jeroenm,

Yes, now that I think about it, you're right. Since the photo's filepath is contained in the database and not the photo itself, there can be more or less photos by the time the aspx page receives the array of photo filepaths.

Maybe this is an ignorant and a beginner question, but if the dataset doesn't solve this problem of making sure things are in sync, what is its benefit over the datareader? The only time I've used the dataset is when implementing custom paging with datalists (since you can't page forwards AND backwards with a datareader). In all other instances, I've used the datareader and then I'd always make sure to close the reader right after each reading. I'm trying to be as stingy as possible in terms of resource utilization... is my approach wrong?

And also, to the issue at hand, I guess the only way to keep things in sync, is to somehow lock writing to the Photo table right before I execute my first SQL statement (to prevent the owner of the profile from uploading or deleting his photos during those few milliseconds), then release the write lock after I return the array of photo filepaths back to the requesting aspx page. Am I correct? And if so, how would I do this?

Thanks.

|||

The dataset was my suggestion to get ride of the first select statement. In your situation a datareader gives probably less resource utilization.

Maybe you can use the following scenario:

- open a transaction with the appropriate isolation level.

- update photo records with the matching memberid. (locking the set of records, you want to read locked).

- you will get the number of records updated by the command in return (given you the count).

- select the rows with the datareader and process the result set.

- rollback the transaction (releasing the lock).

I don't know when the AjaxControlToolkit reads the photo's. But if they are read just before showed to the user, you still have a synchronization issue.