Hi, all,
Long time no see. I am working on an app. VB 2005. The log on screen authenticates to Active Directory. When the end user logs in, if their full name is not in MSSQL, it adds the name. If the end users name is in the table, the app is not slow as hell with loading. If the app inserts their name, the app slows down to a crawl.
I am using a stored procedure for the check and insert. Please let me know what I need to do, to make this faster.
User Authentication
Stored ProcedureCode:Public Function GetFullName(ByVal UName As String) As String
Using Searcher As New DirectorySearcher(New DirectoryEntry())
With Searcher
.PageSize = 1000
.SearchScope = SearchScope.Subtree
.Filter = "(SAMAccountName=" & UName & ")"
End With
Using Results As SearchResultCollection = Searcher.FindAll
If Results Is Nothing OrElse Results.Count <> 1 Then
Throw New ArgumentException("Invalid number of results returned - either no users were found or more than one user account was found.")
End If
Using Entry As DirectoryEntry = Results(0).GetDirectoryEntry
Return CStr(Entry.Properties("GivenName").Value) & " " & CStr(Entry.Properties("sn").Value)
End Using
End Using
End Using
End Function
Login method for authenticationCode:ALTER PROCEDURE dbo.CheckTicketAuthorExists(@User VARCHAR(MAX))
AS
BEGIN
IF (SELECT COUNT(*) FROM TicketAuthors WHERE TicketAuthors = @User) = 0
BEGIN
INSERT INTO TicketAuthors
VALUES(@User)
END
END
Code:Private Sub Login_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Login.Click
Dim User As String = Username.Text.ToString.Trim
Dim Pass As String = Password.Text.ToString.Trim
If ValidateUser(User, Pass) Then
With DisplayMessage
.Text = GetFullName(User) & " Authenticated! Logging In..."
.ForeColor = Color.DarkGreen
Dim fDashboard As New DashBoard
With fDashboard
.DisplayUserFullName.Text = "Welcome " & GetFullName(User) & "!"
.MdiParent = Me.MdiParent
.Dock = DockStyle.Fill
.Show()
End With
MDIContainer.TasksMenu.Enabled = True
Using SetDatabaseConnection As New SqlConnection(MSSQLConnection)
Dim CheckUser As SqlCommand = New SqlCommand
SetDatabaseConnection.Open()
With CheckUser
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "CheckTicketAuthorExists"
.Parameters.AddWithValue("@User", GetFullName(User))
.ExecuteNonQuery()
End With
SetDatabaseConnection.Close()
End Using
With Me
.Close()
.Dispose()
End With
End With
Else
With DisplayMessage
.Text = "Login Mismatch! Please try again!"
.ForeColor = Color.Red
Password.Focus()
End With
End If
End Sub
End Class
