Starr Duskk
asked on
Closing a reader attached to a grid
I have a grid which I populate by using a sqldatareader.
My GetSource consists of this, which returns a SqlDataReader
Okay, so I set my reader to CloseConnection when the reader is closed. But when is the reader closed?
I am populating my grid with it using the grid.DataSource, but when is the reader closed? Is it automatically closed when the user leaves the page? Or do I need to specifically close that reader somewhere? If so, where and how?
thanks!
grdSelector.DataSource = GetSource(blah)
My GetSource consists of this, which returns a SqlDataReader
Dim dr As SqlDataReader = Nothing
Dim sql As String = GetQuery(Blah)
Dim con As New SqlConnection(Name.GetConnectionString)
Dim cmd As New SqlCommand(sql.ToString(), con)
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 600
con.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' close the connection when the reader is closed
Return dr
Okay, so I set my reader to CloseConnection when the reader is closed. But when is the reader closed?
I am populating my grid with it using the grid.DataSource, but when is the reader closed? Is it automatically closed when the user leaves the page? Or do I need to specifically close that reader somewhere? If so, where and how?
thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I know this is unrelated to your question but would mention this in case you are not aware. Data reader is usually used when you want to either load a single record or loop through records to do some processing. For grids, using a table adapter to populate a datatable is usually a preferred method as it downloads all data simultaneously as opposed to data reader which only downloads one row at a time.
ASKER
Thank you CodeCruiser, I will change that. I assume that applies to populating comboboxes as well.
ASKER
Also:
I don't define my datareader like this:
I call a method that returns a data reader like this:
Here is the gist of the method:
If I somehow use a using block around that, will it close the datareader or just the connection?
I believe I remember trying to put a using block around it before and when it loaded into my grid it had no values in it and was empty. I'd have to try again to see what happened, but pretty sure.
Any ideas for me rather than totally redoing all my grids? I guess I can, but would like to see if there's something I can do to salvage what I have. I already have to change to datatables.
thanks!
I don't define my datareader like this:
Dim dr As SqlDataReader = GetSource(blah)
grdSelector.DataSource = dr
dr.Close()
I call a method that returns a data reader like this:
Dim daOrm As DataAccess.CustomDepartment = New DataAccess.CustomDepartment()
grdSelector.DataSource = daOrm.GetDepartmentByClientIdSqlCommandReader(ClientId)
Here is the gist of the method:
Dim dr As SqlDataReader = Nothing
Dim con As New SqlConnection(Name.GetConnectionString)
Dim cmd As New SqlCommand(sql.ToString(), con)
cmd.CommandType = CommandType.Text
con.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' close the connection when the reader is closed
Return dr
If I somehow use a using block around that, will it close the datareader or just the connection?
I believe I remember trying to put a using block around it before and when it loaded into my grid it had no values in it and was empty. I'd have to try again to see what happened, but pretty sure.
Any ideas for me rather than totally redoing all my grids? I guess I can, but would like to see if there's something I can do to salvage what I have. I already have to change to datatables.
thanks!
Even dislike passing datareaders around even more as it creates risk of open connections and readers over time. If you do not have a lot of code at this stage, I would suggest change it.
ASKER
I saw this example which fills the DataTable and closes the connection. Is it necessary to close the datatable of the grid? or just the connection?
Public Function GetDataTable() As DataTable
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode FROM Customers"
Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
Dim conn As New SqlConnection(ConnString)
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand(query, conn)
Dim myDataTable As New DataTable()
conn.Open()
Try
adapter.Fill(myDataTable)
Finally
conn.Close()
End Try
Return myDataTable
End Function
ASKER
I have an additional question and started it here:
https://www.experts-exchange.com/questions/28377259/Populating-a-DataTable-best-method.html
https://www.experts-exchange.com/questions/28377259/Populating-a-DataTable-best-method.html
Hi BobCSD;
To your question, "I saw this example which fills the DataTable and closes the connection. Is it necessary to close the datatable of the grid? or just the connection?", no you just close the reader, there is nothing to close on the DataGrid or DataTable.
To your question, "I saw this example which fills the DataTable and closes the connection. Is it necessary to close the datatable of the grid? or just the connection?", no you just close the reader, there is nothing to close on the DataGrid or DataTable.
ASKER
>>no you just close the reader
We are talking about my new scenario. I have been told not to use a reader and to use a DataTable instead. Plus, my scenario doesn't create the DataTable (or Reader) in the codebehind, it creates it in another library and passes it in. So I came up with a new change.
So looking at the suggested change from my last post, what do I need to make sure I do besides closing the connection? I do not close the DataTable anywhere. Nor do I close the Adapter.
ID: 39895363
thanks!
We are talking about my new scenario. I have been told not to use a reader and to use a DataTable instead. Plus, my scenario doesn't create the DataTable (or Reader) in the codebehind, it creates it in another library and passes it in. So I came up with a new change.
So looking at the suggested change from my last post, what do I need to make sure I do besides closing the connection? I do not close the DataTable anywhere. Nor do I close the Adapter.
ID: 39895363
thanks!
In connection to this post, "ID: 39895363", the way you are doing it there is correct.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>DataTable is disconnected so there is no need to "close" it.
Conversely, is a SQLDataReader not disconnected, so there is a need to close it?
Conversely, is a SQLDataReader not disconnected, so there is a need to close it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks both of you! And thanks CC for the "not related to question" because evidentally it is, since a DataTable doesn't need closing and that's what I'm looking for. Something I don't have to close.
thanks!
thanks!