DataReader's Connection

  • Thread starter Thread starter A.M
  • Start date Start date
A

A.M

Hi,

Is there any way to have a refrence to connection object associted to a
DataReader ?
I have a member function that returns a DataReader, When i finished my work,
I need to close the both DataReader and it's connection. How can i do that ?

Thanks,
Ali
 
You can either call close() method on the reader and the
connection object or you can do something like this:

SqlDataReader myReader = myCommand.ExecuteReader
(CommandBehavior.CloseConnection);

this will close the connection when you call close() on
the datareader.
 
A DataReader is created using a command object's ExecuteReader method. A
command is associated with a particular connection, therefore a DataReader
is also associated with that connection.

When done with a DataReader, you can call its "close()" method and when done
with a connection, you do the same.
 
Based on what you are saying, my class library user has to maintain a
refrence to the connction, and that is what i am trying to avoid.
I am looking for some way to retrive connection from DataReader itself
rather than maintaining a refrence to it, so my class library method returns
just a refrence to datareader (instead of returning both connection and
datareader)

Thanks,
Ali
 
Thanyou Dan, That helped me.

Dan Normington said:
You can either call close() method on the reader and the
connection object or you can do something like this:

SqlDataReader myReader = myCommand.ExecuteReader
(CommandBehavior.CloseConnection);

this will close the connection when you call close() on
the datareader.
Click to expand...
 
In order for a datareader to do anything, it needs to be connected to the
database, there's no way around that. A datareader without a connection is
worthless. In the purest sense of what you are asking, you could have your
method return a DataReader and dispose of it's connection in the Method.
However, this would not accomplish anything productive other than passing
back a DataReader for its own sake.

Depending on what the ultimate goal is, you can use a datareader to populate
some datastructure and pass thatb back. Your method could just return your
myObject and never worry about the reader or connection again.

But if I'm understanding you correctly, you don't want tied to the
connection, and if that's the case, returning a datareader probably isn't
going to get you there..

HTH,

Bill
 
Please see Dan Normington's reply.
Based on his reply i made following function and all the programmer has to
do is just close the datareader after he is done with it:


Public Shared Function ADOExecuteReader(ByVal strSql, ByVal strConnection)
As System.Data.SqlClient.SqlDataReader

Dim sqlReader As System.Data.SqlClient.SqlDataReader
Dim myConnection As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection(strConnection)
Dim myCommand As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand
Try
myConnection.Open()
myCommand.Connection = myConnection
myCommand.CommandText = strSql
sqlReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Catch e1 As Exception
ErrorHandler(e1)
Finally
'We don't need to call myConnection.Close()
CommandBehavior.CloseConnection will do that
End Try
ADOExecuteReader = sqlReader
End Function
 
Am I correct in assuming that this function never deallocates the
SQLDataReader?


'return a SQLDataReader result set given a SQL query
Protected Function GetReaderResultSet(ByVal strSQL As String)
Dim connectionString As String =
ConfigurationSettings.AppSettings("connString")
Dim conLU As New SqlConnection(connectionString)
Dim cmdGetData As New SqlCommand(strSQL, conLU)
Dim drdGetData As SqlDataReader
conLU.Open()
GetReaderResultSet =
cmdGetData.ExecuteReader(CommandBehavior.CloseConnection)
End Function


So what is the best way to do this? Should I use a dataset/datatable
instead?
 
Yes, the main idea is returning DaraReader to caller and caller will close
it.
When there is no refrence to the returned DaraReader then GC will take care
of deallocation
 
Just make SURE that the user (of the DataReader) closes the DataReader and
you use CommandBehavior.CloseConnection. If the user does not close the
DataReader the connection is orphaned and the pool will leak a connection.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
CommandBehavior.CloseConnection is already inside the function and Closing
the datareader is small thing that developer have to take care of.
 
I use datareader. I would like to know after clsoing why I should run
executeNonQuery to get the result.
code follows:
Dim strSQL As String = "usp_CheckProgramAssociation"
Dim objCommand As New SqlCommand(strSQL, cn)
Dim EditDataReader As SqlDataReader
objCommand.CommandType = CommandType.StoredProcedure
Dim strprogCat1 As String = Session("ProgCatID")
objParam = objCommand.Parameters.Add("@ProgCatId",
SqlDbType.Int)
objCommand.Parameters.Item("@ProgCatId").Value = strprogCat1
objParam.Direction = ParameterDirection.Input
EditDataReader = objCommand.ExecuteReader

objParam = objCommand.Parameters.Add("ReturnValue",
SqlDbType.Int)
objParam.Direction = ParameterDirection.ReturnValue
'EditDataReader.Close()
objCommand.ExecuteNonQuery() 'test remove it

If objCommand.Parameters("ReturnValue").Value > 0 Then
If Session("UpdateClicked") = "Y" Then
ValidProgCatAssociation = "Y"
'txtProgCatDescription.Text =
Session("ProgCatDescription")
Session("UpdateClicked") = "N"
Else
lblProgCatMessage.Text = " This category is
associated with the program.<br> If you want to delete the program
category <br> click Confirm Delete button."
txtProgCatDescription.Text =
Session("ProgCatDescription")
End If

End If
 
Hello,

You can read from the datareader, and close the connection after that
(instead of before it).
Check the following code:
reader=command2.ExecuteReader();
DataTable schemaTable=reader.GetSchemaTable();
System.Object[] rowdata=new object[schemaTable.Rows.Count];

while(reader.Read())
{
reader.GetValues(rowdata);
}
reader.Close();

Hope this helps you understand how to modify your code to solve your
problem.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
Back
Top