Using Data Readers, SQL Server

http://www.startvbdotnet.com/ado/sqlserver.aspx

http://www.java2s.com/Code/VB/Database-ADO.net/SqlDataReader.htm

Need to find out:
- what ORM is
- difference between SqlDataReader & SqlDataAdapter in VB.NET

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

Database Interaction In ADO.NET

http://www.programmersheaven.com/2/Les_VBNET_13_p1

http://www.programmersheaven.com/2/Les_VBNET_13_p2


SQL Command Execution Methods:

ExecuteNonQuery	Executes an SQL statement on the connected data source. You can use it for DDL statements, action queries (e.g., INSERT, UPDATE, and DELETE operations), and ad hoc queries. This method returns the number of rows affected but doesn't return output parameters or result sets.
ExecuteReader	Executes an SQL SELECT statement on the data source and returns a fast forward-only result.
ExecuteScalar	Executes a stored procedure or an SQL statement that returns a single scalar value. It returns the first row of the result set's first column to the calling application and ignores any other returned values.
ExecuteXMLReader	Executes a FOR XML SELECT statement that returns an XML data stream from the data source. The ExecuteXMLReader method is compatible only with SQL Server 2000 and later releases.

Source: MSDN