function to return reader question

[Guest]

I've written functinos to return readers and want to know if i'm using them
correctly and if there is a "more better" way.
Here is a simple example

Dim thsusr As New UsrSrv <--- component containing the function to get 1
user
Dim usrResult As SqlClient.SqlDataReader =
thsusr.fn_Isvalid(Me.txtUserID.Text, Me.txtPassword.Text)
If usrResult.HasRows Then
usrResult.Read()
......
......

thanks
kes

 

[W.G. Ryan MVP]

I can't really tell from this. YOu can't remote readers so if you need
remoting, then you're definitely barking up the wrong tree. You'll want to
make sure you close the reader and connection (or use the CommandBehavior to
close the connection) in a finally or using block.. Also, by calling Read
once, you may have additional values in there unless the query expressly
prohibts that from happening. Unless your 100% sure you only have 1 row,
then you should use the While(reader.Read()) and if you only have 1 row,
then you probably should opt for another approach.

If you're looking for a function to validate a user for instance, and you
are just looking for a count > 0 with Username and Password matching, I'd
use an Output parameter...

 

[Marina]

Make sure the function that is creating the datareader is passing in
CommandBehavior.CloseConnection into ExecuteReader, and that the method
using the reader is closing it.

 

[Guest]

Thanks for responding,

That really was my question, "What other way wuold be better?"
Given that there will only ever be one row, due to db constraints, what
would you do?
1. reader is closed (commandbehavoir...)
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

I can't really tell from this. YOu can't remote readers so if you need
remoting, then you're definitely barking up the wrong tree. You'll want to
make sure you close the reader and connection (or use the CommandBehavior to
close the connection) in a finally or using block.. Also, by calling Read
once, you may have additional values in there unless the query expressly
prohibts that from happening. Unless your 100% sure you only have 1 row,
then you should use the While(reader.Read()) and if you only have 1 row,
then you probably should opt for another approach.

If you're looking for a function to validate a user for instance, and you
are just looking for a count > 0 with Username and Password matching, I'd
use an Output parameter...

 

[Guest]

THanks you for responding!

the function is closing it.
this is the whole thing:
Dim cmd As SqlCommand = Me.SqlConnection1.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_getUser"
cmd.Parameters.Add(New SqlParameter("@thsUserID", SqlDbType.VarChar))
cmd.Parameters.Add(New SqlParameter("@thsPassword",
SqlDbType.VarChar))
cmd.Parameters(0).Value = thsUserID
cmd.Parameters(1).Value = thsPassword
Me.SqlConnection1.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

do i need to close the reader object that is being assigned the return from
the function? I didn't think that was necessary?

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

Make sure the function that is creating the datareader is passing in
CommandBehavior.CloseConnection into ExecuteReader, and that the method
using the reader is closing it.

 

[Marina]

If you don't close the reader once the client function gets it, that
connection will stay open, and you will end up with a connection leak.

THanks you for responding!

the function is closing it.
this is the whole thing:
Dim cmd As SqlCommand = Me.SqlConnection1.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_getUser"
cmd.Parameters.Add(New SqlParameter("@thsUserID",
SqlDbType.VarChar))
cmd.Parameters.Add(New SqlParameter("@thsPassword",
SqlDbType.VarChar))
cmd.Parameters(0).Value = thsUserID
cmd.Parameters(1).Value = thsPassword
Me.SqlConnection1.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

do i need to close the reader object that is being assigned the return
from
the function? I didn't think that was necessary?

 

[Guest]

Thanks for responding,
I've changed the code a little
1. The function returns a reader.
2. the function is using commandbehavior.closeconnection on executereader
3. from the calling procedure (or sub)
here i added two close calls to the reader object u that is assigned the
reader returned from the function. THey are noted below... Are they really
necessary?
thank!!
Try
Dim thsusr As New UsrSrv
Dim u As SqlClient.SqlDataReader =
thsusr.fn_getUser(Me.tbUserID.Text, Me.tbPassword.Text)
If u.HasRows Then
u.Read()
Me.lblmsg.Text = "ok " + u(1) + "is logged in."
u.Close() '<---- new close added is it needed?
FormsAuthentication.RedirectFromLoginPage(Me.tbUserID.Text,
False)
' FormsAuthentication.SetAuthCookie(me.txtUserID,False)
Response.Redirect("main.aspx", 0)
Else
u.Close() ' <---- new close added, is it needed?
Me.lblmsg.Text = "Invalid userid or password"

End If
Catch ex As Exception
Dim msg As String = String.Format("<B>MSG:</B> {0} <BR> <B>STACK
TRACE:</B> {1}", _
ex.Message, ex.StackTrace)
Me.lblmsg.Text = "<B>ERROR:</B> " & ex.GetType.ToString + "<br>"
+ msg
End Try

 

[W.G. Ryan MVP]

'Better' depends on perspective. I'd personally use OutPut parameters b/c
they express my intent a little better and there's less room for screw ups
later on - but even that could be argued - changing the proc could easily
screw up my method too.

Thanks for responding,

That really was my question, "What other way wuold be better?"
Given that there will only ever be one row, due to db constraints, what
would you do?
1. reader is closed (commandbehavoir...)

 

[Guest]

thanks
kes