Techie Pie

One of the projects I’m working on is a rewrite of web portal for users to access some data metrics. A requirement for this web portal is that users must authenticate against a database running on SQL 2005 that contains usernames and passwords fed to it from a 3rd party, non-MS email system. This means, simply, that I cannot use SQL logins or Windows authentication as a means to get users into the web portal.

Now this project is the sort of thing that doesn’t have deadlines attached to it, and is not a high priority. Because of this I’m using it as my learning tool for VB.net and ASP.net. So the first thing I had to figure out was how to transform the original ASP login page into a secure, VB.net login page that would query the database for user authentication.

It took a while. Searches through Google yielded a variety of “how-to” articles on designing VB.net login pages, but they all assumed that I was going to use an ASP.net security setup, or some other Windows-based authentication. I could not, for the life of me, find an example of VB.net code to do what I wanted.

Lucky for you, my pain is your gain. Here is the code behind for a login page in VB.net.

———————————————————————–

Imports System.Data.SqlClient

———————————————————————–
Partial Class _Default
Inherits System.Web.UI.Page

———————————————————————–
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub
———————————————————————–
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
‘ variables for username and password
Dim strUser, strPassword, strPWResult
Dim intBldg, blnAllSchools, intEmployeeNo
‘ set form values into variables
strUser = txtUser.Text
strPassword = txtPassword.Text

‘ echo input for testing
‘Response.Write(strUser & “</br>”)
‘Response.Write(strPassword & “</br>”)

‘ the connection string is defined in the web.config file

Dim MySqlConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(”cnMyConn”))
Dim dr As SqlDataReader

Try
MySqlConn.Open()
Dim SQL As String
SQL = “SELECT * FROM [table] WHERE userid = ‘” & strUser & “‘”

Dim mySQLCmd As New SqlCommand(SQL, MySqlConn)

dr = mySQLCmd.ExecuteReader
While dr.Read
‘Response.Write(dr(0))
strPWResult = dr(0).ToString()
intBldg = dr(1).ToString()
blnAllSchools = dr(2).ToString()
intEmployeeNo = dr(3).ToString()

‘Response.Write(strPWResult)
If LCase(strPWResult) = LCase(strPassword) Then
‘Response.Write(”User valid!”)
Session(”UserID”) = strUser
Session(”Bldg”) = intBldg
Session(”AllSchools”) = blnAllSchools
Response.Redirect(”my_webpage.aspx”)

‘ for debugging

‘Response.Write(intBldg & “</br>”)
‘Response.Write(blnAllSchools & “</br>”)
‘Response.Write(intEmployeeNo & “</br>”)

Else
Response.Write(”Wrong username or password!”)
Session.Abandon()

End If

End While
dr.Close()

Finally
MySqlConn.Close()
End Try

End Sub
End Class

For all its improvements, SQL 2005 still doesn’t provide native support for epoch date/time. So I had to write some functions to do the conversion for me, then configure computed columns to use the functions. Here’s how I did it:

1. Create a function to convert a date to epoch:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[date2timestamp](
@dateStr datetime
)
returns bigint
as begin

/** Now, find out how many seconds have existed between now and the epoch **/

return convert(bigint,
datediff(ss, ‘01-01-1970 00:00:00′, @dateStr))
end

2. Create a function that will convert epoch back to date:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[timestamp2date](
@numSeconds bigint
)
returns varchar(20)
as begin
/** Now, find out how many seconds have existed between @numSeconds and the epoch. **/

/** If you need to modify according to your TZ **/

/** set @numSeconds = @numSeconds + (60 * 60 * -6) — subtract CST **/

return dateadd(ss, @numSeconds, ‘01-01-1970 00:00:00′)
end

3. Since my task needed to calculate the difference between the to values, I had to create a third function:

USE [tablename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[epoch_diff] ( @epoch_start bigint, @epoch_end bigint )

RETURNS bigint
AS

BEGIN
RETURN (@epoch_end - @epoch_start)
END

4. To configure the computed column in SQL 2005, you need to right-click on the table and choose Design from the pop-up menu. Add your new column (if not already created), and under Column Properties locate the property Computed Column Specification (Formula).

For this example, it assumes that there is another column in the table with the actual date value that you want to convert to its epoch equivalent. So in the (Formula) field insert the function:

([dbo].[date2timestamp]([fieldname]))

I left the Is Persisted value set to no.

SQL 2005 will go through the existing table data and add the new field value when you save the changes, and new row inserts will have the field value automatically inserted.

In your query code, you can access the difference between the epoch dates like this:

USE tablename

/** gets the number of seconds and divides by 60 for minutes. rounds to nearest whole number. Values less than 1 minute show up as zero. **/

SELECT (dbo.epoch_diff(epoch_start, epoch_end)/60) AS diff
FROM tablename