G
Guest
Hi i am creating a login routine that i have overloaded and wondered how i
could return some data back to my program or even if i am going about the
right way of achieving what i want to do.
The function takes some parameters the server name, the database, the
username and the stored procedure name the other overloaded function takes
the same parameters plus a password and two optional parameters for the
username and password input parameters for the stored proc defaulted at
@username and @password.
Then the function opens a data reader using cmd.executereader if the
datareader has rows then it returns true if not it returns false. It is
meant to be a generic login dll for use in my apps either windows or asp.
Sometimes i may need to return the full name of the user or one or two other
parameters how could i do this i was thinking about having some public
property's but i am unsure of how to go about setting them. I keep getting
an error invalid attempt to read when no data is present. When i call
dr.hasrows does this then empty my datareader i remember from my course
manual that a datareader is readonly forward only?
Is this a good way of doing things or have i got the idea mixed up
The code is below, by the way i am coming from a predominately ASP
background into .net so i know little of it really
Public Overloads Function LoginCheck(ByVal Username As String, ByVal
Password As String, _
ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
String, ByVal numReturns As Integer, Optional _
ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
"@Password") As Boolean
Dim conn As SqlConnection = Nothing
Dim dr As SqlDataReader = Nothing
Dim cmd As New SqlCommand
Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
Try
conn = New SqlConnection("data source=" & Server & "; integrated
security=true; initial catalog = " & DBName)
cmd.CommandText = (StoredProc)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn
'set up the paramaters
P1.Value = Username
P1.Direction = ParameterDirection.Input
P2.Value = Password
P2.Direction = ParameterDirection.Input
cmd.Parameters.Add(P1)
cmd.Parameters.Add(P2)
conn.Open()
dr = cmd.ExecuteReader
If dr.HasRows Then
Select Case numReturns
Case 0
'do nothing
Case 1
Return1 = dr("Fullname").ToString
Case 2
End Select
Return True
Else
Return False
End If
Catch ex As Exception
Console.WriteLine(ex.Message)
If Not dr Is Nothing Then
dr.Close()
End If
If Not conn Is Nothing Then
conn.Close()
End If
Finally
If Not dr Is Nothing Then
dr.Close()
End If
If Not conn Is Nothing Then
conn.Close()
End If
End Try
End Function
Dim m_Return1 As String
Public Property Return1() As String
Get
Return m_Return1
End Get
Set(ByVal Value As String)
m_Return1 = Value
End Set
End Property
could return some data back to my program or even if i am going about the
right way of achieving what i want to do.
The function takes some parameters the server name, the database, the
username and the stored procedure name the other overloaded function takes
the same parameters plus a password and two optional parameters for the
username and password input parameters for the stored proc defaulted at
@username and @password.
Then the function opens a data reader using cmd.executereader if the
datareader has rows then it returns true if not it returns false. It is
meant to be a generic login dll for use in my apps either windows or asp.
Sometimes i may need to return the full name of the user or one or two other
parameters how could i do this i was thinking about having some public
property's but i am unsure of how to go about setting them. I keep getting
an error invalid attempt to read when no data is present. When i call
dr.hasrows does this then empty my datareader i remember from my course
manual that a datareader is readonly forward only?
Is this a good way of doing things or have i got the idea mixed up
The code is below, by the way i am coming from a predominately ASP
background into .net so i know little of it really
Public Overloads Function LoginCheck(ByVal Username As String, ByVal
Password As String, _
ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
String, ByVal numReturns As Integer, Optional _
ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
"@Password") As Boolean
Dim conn As SqlConnection = Nothing
Dim dr As SqlDataReader = Nothing
Dim cmd As New SqlCommand
Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
Try
conn = New SqlConnection("data source=" & Server & "; integrated
security=true; initial catalog = " & DBName)
cmd.CommandText = (StoredProc)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn
'set up the paramaters
P1.Value = Username
P1.Direction = ParameterDirection.Input
P2.Value = Password
P2.Direction = ParameterDirection.Input
cmd.Parameters.Add(P1)
cmd.Parameters.Add(P2)
conn.Open()
dr = cmd.ExecuteReader
If dr.HasRows Then
Select Case numReturns
Case 0
'do nothing
Case 1
Return1 = dr("Fullname").ToString
Case 2
End Select
Return True
Else
Return False
End If
Catch ex As Exception
Console.WriteLine(ex.Message)
If Not dr Is Nothing Then
dr.Close()
End If
If Not conn Is Nothing Then
conn.Close()
End If
Finally
If Not dr Is Nothing Then
dr.Close()
End If
If Not conn Is Nothing Then
conn.Close()
End If
End Try
End Function
Dim m_Return1 As String
Public Property Return1() As String
Get
Return m_Return1
End Get
Set(ByVal Value As String)
m_Return1 = Value
End Set
End Property