Simon:
There's nothing wrong with what you are doing with one exception and you can
use a one lined approach to get it to work.
You'll want to avoid Dynamic Concatenated sql like the plague. If you must
use it, make sure you use parameters b/c otherwise you are creating a
security vulnerability, hindering performance and making code much more
difficult to read. As far as using an aggregate, a DataReader or an output
parameter, in general an Output parameter is the best bet. Bill Vaughn has
a great article on the subject at
www.betav.com -> Articles ->
MSDN ->Retrieving the Gozoutas. Using Count(*) like you are, you will ensure
that if you use ExecuteScalar you won't get a null value (it will be 0 if
nothign is returned) and this will minimize the possibilty of an exception
or save you from coding around the null value issue. However, you are still
invoking an aggregate unnecessarily which performance wise, is probably
something you want to avoid where possible. Not a big deal in general, but
done by enough users and all it may be and if nothing else, it's a waste of
resources.
Anyway, for a log on like this, I personally use an Output parameter.
Also, as a rule, use Stored Procedures as much as possible (always if
possible) b/c you will get better performance, can make modifications
without having to redeploy your application or interrupt users and you exert
greater control over security. You can give a user an account w/ least
privilege that has execute permissions on the proc and give the proc select
permissions (or whatever on the respective db objects) but not give the user
account privileges to hit that table/object directly. Anyway, enough Stored
procedure advocacy. Here's an example of how to invoke and use a Stored
Proc w/ output params - in the proc definition, you simply mark the
paramater as Out but since you mention it I assume you konw how to do it.
Here's the proc I used. I have some other stuff like the decryption routine
and all that can be ignored
Public Function UserOk(ByVal User_ID As String, ByVal pw As String) As
Boolean
Dim c As Crypto = New Crypto
Dim _Password As String = pw
Try
_cn = New SqlConnection(mData.ConnectionBase)
User_ID = c.Decrypt(User_ID, "***********")
pw = c.Decrypt(pw, "1(((0_*32_^%$_abx_)31+=xcnn")
_cmd = New SqlCommand("usp_LoginSP", _cn)
_cmd.CommandType = CommandType.StoredProcedure
With _cmd.Parameters
.Clear()
.Add("
@Password", SqlDbType.VarChar, 50).Value = _Password
.Add("@LoginID", SqlDbType.Int, 4)
.Add("
@username", SqlDbType.VarChar, 50).Value = User_ID
End With
_cmd.Parameters("
@Password").Direction = ParameterDirection.Input
_cmd.Parameters("@LoginID").Direction = ParameterDirection.Output
_cmd.Parameters("
@username").Direction = ParameterDirection.Input
If _cn.State <> ConnectionState.Open Then _cn.Open()
_cmd.ExecuteNonQuery()
If _cn.State <> ConnectionState.Closed Then _cn.Close()
'I"m closing it here so I can get the values back but I have Close in a
finally just in case it
' were to blow up. I know it looks redundant but I can't wait for
finally if I want to get my values
Dim IDValue As Integer
Try
IDValue = CType(_cmd.Parameters("@LoginID").Value, Integer)
Catch ex As Exception
Dim s As String
End Try
Return IDValue > 0
Catch sq As SqlClient.SqlException
If sq.ToString.IndexOf("Login failed for user") = 1 Then
Throw New UserDoesNotExistException
errMessage = "This username and password do not exist in the
database, please retry logging in with a valid username and password."
Return False
Else
errMessage = "Non-specified log-in error, please try again."
End If
Catch exc As System.Exception
errMessage = exc.ToString
Return False
Finally
If _cn.State = ConnectionState.Closed Then _cn.Close()
End Try
End Function
--
W.G. Ryan, eMVP
http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp