Requirements
To proceed with our Web Service authentication, we will add the following requirements:
Members table
For our data table, we will use a Visual FoxPro backend by the use of a free table. We will create Member.dbf which will be used for our members. This table will be defined as follow:
The following provides an overview of some sample data:
Adjusting the Login() method
We will adjust the Login() method to authenticate the user against our Members table. This method is now responsible to locate the user record in the Members table by the use of the username received, verify to make sure the password is valid, create a session, update the session field of the user record and update the last login time stamp field so we know when this user last logged in.
This method as well as other methods will now make use of a data class which eases the data access by the use of setting up properties and calling custom methods. The data class shown used here handles SQL parameters and provides a basic error handling capabilities. Thanks to several Universal Thread members who provided some valuable information for properly setting up this class.
Here is the updated code for our Login() method:
' Do the login for the specific user <WebMethod()> _ Public Function Login(ByVal tcUsername As String, ByVal tcPassword As String) As Boolean Dim lcUsername As String Dim lcPassword As String Dim loDataProvider As Data = New Data Dim lcConnection As String Dim loRow As DataRow Dim lcSession As String lcUsername = UCase(tcUsername).PadRight(10, " ") lcPassword = UCase(tcPassword).PadRight(10, " ") loDataProvider.oParameters.Add(New OleDbParameter("@Username", lcUsername)) lcConnection = "Provider=VFPOLEDB.1;Data Source=" + _ """d:\Visual Studio Projects\Level Extreme .NET Magazine"";"+ _ "Password='';Collating Sequence=MACHINE" ' Get the record of the user If loDataProvider.SQLExec("SELECT PrimaryKey,Password,Session FROM Member "+ _ "WHERE Username=?", lcConnection) = False Then Throw New System.Exception("Problem with retrieving the data: " + _ loDataProvider.cError) End If ' If we didn't find any record If loDataProvider.nCount = 0 Then Throw New System.Exception("This username does not exist.") End If ' Get it into a row loRow = loDataProvider.oDataView(0).Row ' If the password is not valid If UCase(loRow("Password")) <> lcPassword Then Throw New System.Exception("The password is invalid.") Return False End If ' Update the session and the last login time stamp lcSession = Guid.NewGuid.ToString() If loDataProvider.SQLUpdate("UPDATE Member SET Session=""" + lcSession + _ """" + ",LastLogin=" + DateUniversal(Date.Now) + " WHERE PrimaryKey=" + _ loRow("PrimaryKey").ToString, lcConnection) = False Then Throw New System.Exception("Problem with updating the session: " + _ loDataProvider.cError) Return False End If ' Create the cookie System.Web.HttpContext.Current.Response.Cookies("Session").Value = lcSession Return True End Function
Public Class Data Public oDataAdapter As OleDbDataAdapter Public oDataSet As DataSet Public oDataView As DataView Public cSQL As String Public cConnectionString As String Public nCount As Integer Public oParameters As New Collection Public cError As String ' SQL exec ' expC1 SQL command ' expO1 Connection Public Function SQLExec(ByVal tcSQL As String, ByVal tcConnection As String) As Boolean Dim llSuccess As Boolean Dim loCommand As New OleDbCommand Dim loParam As OleDbParameter llSuccess = False cSQL = tcSQL cConnectionString = tcConnection Try loCommand.Connection = New OleDbConnection(tcConnection) loCommand.CommandText = tcSQL ' Only add the parameters if we have some If Not oParameters Is Nothing Then For Each loParam In oParameters loCommand.Parameters.Add(loParam) Next End If oDataAdapter = New OleDbDataAdapter oDataAdapter.SelectCommand = loCommand oDataSet = New DataSet oDataAdapter.Fill(oDataSet, "Temp") ' Record count nCount = oDataSet.Tables("Temp").Rows.Count ' If we have at least one record If nCount > 0 Then oDataView = New DataView oDataView = oDataSet.Tables("Temp").DefaultView End If llSuccess = True Catch loError As Exception cError = loError.Message End Try Return llSuccess End Function ' SQL update ' expC1 SQL command ' expO1 Connection Public Function SQLUpdate(ByVal tcSQL As String, ByVal tcConnection As String) As Boolean Dim llSuccess As Boolean Dim loConnection As OleDbConnection llSuccess = False cSQL = tcSQL cConnectionString = tcConnection Try loConnection = New OleDbConnection(tcConnection) loConnection.Open() oDataAdapter = New OleDbDataAdapter oDataAdapter.SelectCommand = New OleDbCommand(tcSQL, loConnection) oDataSet = New DataSet oDataAdapter.Fill(oDataSet, "Temp") llSuccess = True Catch loError As Exception cError = loError.Message End Try Return llSuccess End Function End Class
' Return a date in a universal format ' expD1 Date Public Function DateUniversal(ByVal tdDate As Date) As String Dim lcDate As String Dim lnDay As Integer Dim lnMonth As Integer Dim lnYear As Integer Dim lnHour As Integer Dim lnMinute As Integer Dim lnSecond As Integer lcDate = "" lnDay = tdDate.Day lnMonth = tdDate.Month lnYear = tdDate.Year lnHour = tdDate.Hour lnMinute = tdDate.Minute lnSecond = tdDate.Second lcDate = lcDate + "{^" lcDate = lcDate + Trim(lnYear.ToString) + "/" + lnMonth.ToString.PadLeft(2, "0") + "/" + _ lnDay.ToString.PadLeft(2, "0") + " " + lnHour.ToString.PadLeft(2, "0") + ":" + _ lnMinute.ToString.PadLeft(2, "0") + ":" + lnSecond.ToString.PadLeft(2, "0") lcDate = lcDate + "}" Return lcDate End Function
' Check for the login Public Function CheckLogin() As Boolean Dim loCookie As System.Web.HttpCookie Dim lcSession As String Dim lcError As String Dim loDataProvider As Data = New Data Dim lcConnection As String loCookie = System.Web.HttpContext.Current.Request.Cookies("Session") lcError = "" lcConnection = "Provider=VFPOLEDB.1;Data Source=" + _ """d:\Visual Studio Projects\Level Extreme .NET Magazine"";"+ _ "Password='';Collating Sequence=MACHINE" ' If the cookie is not found If loCookie Is Nothing Then Throw New System.Exception("You have to login in order to use the Web Service.") End If ' Get the session from the cookie lcSession = loCookie.Value.ToString() ' Get the user record loDataProvider.oParameters.Add(New OleDbParameter("@Session", lcSession)) If loDataProvider.SQLExec("SELECT PrimaryKey FROM Member WHERE Session=?", _ lcConnection) = False Then Throw New System.Exception("Problem with retrieving the data: " + loDataProvider.cError) End If ' If we didn't find any record If loDataProvider.nCount = 0 Then Throw New System.Exception("This session does not exist.") End If End Function
This implementation also provides a detailed error log that you may want to remove if you use that code for an application in production. I usually rely on a framework property to let me know in which mode I am executing the application so I can return a basic error for the users and a more detailed one for me.
This article covered a more detailed overview, in regards to the data access, for our Web Service authentication approach. I hope this has helped you to get more ideas about related implementation for your applications.
Source code
