Friday, May 25, 2007

VB.NET: Web Methods, Sessions, Database Operations

Some code I wrote that I want to remember:

<webmethod(enablesession:=true,Description="Post feedback on session id, start time, end time")> _
Public Function PostFeedback() As String
Dim sID As String = HttpContext.Current.Session.SessionID
Dim connString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings.Item(connStringID).ConnectionString
Dim myConn As SqlConnection = New SqlConnection(connString)
Dim insertCommand, selectCommand, updateCommand As SqlCommand
Dim myReader As SqlDataReader
Dim end_time As DateTime
Const delay As Single = 10
Dim msg As String = "none"

myConn.Open()

'check whether there are any existing session ids in feedback table that matches current session id
selectCommand = New SqlCommand("select feedback_session_id, feedback_end_time from feedback where feedback_session_id = '" & sID & "'", myConn)
myReader = selectCommand.ExecuteReader()

If myReader.HasRows = True Then

myReader.Read()

If myReader("feedback_end_time").Equals(DBNull.Value) Then

myReader.Close()
msg = "1"
'insert end time into feedback_end_time where the feedback_end_time field is empty
updateCommand = New SqlCommand("update feedback set feedback_end_time = " & Now() & "where feedback_session_id = '" & sID & "')", myConn)
updateCommand.ExecuteNonQuery()

Else

msg = "2"
end_time = myReader("feedback_end_time")
myReader.Close()

If (DateTime.Now - end_time).TotalMinutes < delay Then

'if difference between start time & end time less than 10 mins then update feedback_end_time
updateCommand = New SqlCommand("update feedback set feedback_end_time = " & Now() & "where feedback_session_id = '" & sID & "')", myConn)
updateCommand.ExecuteNonQuery()

Else

'if more than 10 mins, insert new row into feedback table with session id & start time
insertCommand = New SqlCommand("insert into feedback(feedback_session_id, feedback_start_time) values('" & sID & "', '" & Now() & "')", myConn)
insertCommand.ExecuteNonQuery()

End If

End If

Else

msg = "3"
myReader.Close()
'insert new row into feedback table with session id & start time
insertCommand = New SqlCommand("insert into feedback(feedback_session_id, feedback_start_time) values('" & sID & "','" & Now() & "')", myConn)
insertCommand.ExecuteNonQuery()

End If

myConn.Close()

Return msg '"Inserted " & sID.ToString

End Function

No comments: