Creating the QueryTrackerRemotingObject Project

To create the QueryTrackerRemotingObject project, you need to create the QueryProvider class that defines the remoting object. The QueryProvider.vb file provides the QueryProvider class, which provides methods to retrieve and update query information in the database.

Listing 4-8 shows the code for the QueryProvider.vb file that defines the QueryProvider class:

Listing 4-8: The QueryProvider.vb File

 Imports System Imports System.Data Imports System.Data.SqlClient 'The QueryProvider class that defines the remoting object Public Class QueryProvider Inherits MarshalByRefObject    Dim ds As New DataSet    Dim sqlcon As New SqlClient.SqlConnection    Sub New()       'Establishing a connection with QueryTracker Database       Try          sqlcon.ConnectionString = "SERVER=192.168.0.12;UID=sa;PWD=sa;Initial Catalog=QueryTracker"          sqlcon.Open()          Catch ex As Exception          MsgBox("Connection Error", MsgBoxStyle.OKOnly, "Attempt to connection to data source failed")       End Try    End Sub    'This function returns the queries meant for a particular executive in form of a dataset    Public Function getQuery(ByVal id As Integer) As DataSet       Try          Dim sqlcmd As New SqlClient.SqlCommand("select * from queries where QueryId in _          (select QueryId from execquery where eid=" & id & ") and status='forwarded'", sqlcon)          Dim sqlda As New SqlClient.SqlDataAdapter(sqlcmd)          'Filling the data set          sqlda.Fill(ds)          'returning the data set          Return ds          Catch ex As Exception          'In case any exception occurs then returning a blank data set          Return ds       End Try    End Function    'This function allows the executive to log on and returns the executive id    Public Function login(ByVal strUserName As String, ByVal strpwd As String) As Integer       Try          'Verifying the specified information for Executive Login          Dim sqlcom As New SqlClient.SqlCommand("select * from CustSupportExecutives where FirstName='" _          & strUserName & "' and Pwd='" & strpwd & "'", sqlcon)          Dim sqldr As SqlClient.SqlDataReader          sqldr = sqlcom.ExecuteReader          'To check whether the login attempt is successful          If sqldr.HasRows Then             'return the executive id             sqldr.Read()             Return sqldr.GetInt32(0)             Else             'in case the login is unsuccessful return 0             MsgBox("Login Unsuccessful", MsgBoxStyle.OKOnly, "INVALID LOGIN")             Return 0          End If          Catch ex As Exception          MsgBox("Error in executive login", MsgBoxStyle.OKOnly, "LOGIN ERROR")          Return 0       End Try    End Function    'This function accepts a dataset containing the queries to be marked as     'attended for a particular executive    Public Function updateQuery(ByVal ds As DataSet)       Try          'Updating the dataset ds contents back to database QueryTracker          Dim sqlcmd As New SqlCommand("Update Queries set status='attended' where QueryId=@QueryID", sqlcon)          sqlcmd.Parameters.Add(New SqlParameter("@QueryID", SqlDbType.Int))          Dim rowcount As Integer          If ds.Tables.Count <> 0 Then             If ds.Tables(0).Rows.Count <> 0 Then                For rowcount = 0 To ds.Tables(0).Rows.Count  1                   sqlcmd.Parameters("@QueryID").Value = ds.Tables(0).Rows(rowcount).Item(0)                   sqlcmd.ExecuteNonQuery()                Next             End If          End If          Catch ex As Exception          MsgBox("Unable to update", MsgBoxStyle.OKOnly, "Update Error")       End Try    End Function End Class 

 

Download this Listing .

The above listing defines the following methods:

• getQuery() : Retrieves the queries meant for a specific executive.

• login() : Validates the login information provided by executives.

• updateQuery() : Updates the queries attended to by executives in the database.