Solved

Closing a reader attached to a grid

Posted on 2014-02-27
14
364 Views
Last Modified: 2014-02-28
I have a grid which I populate by using a sqldatareader.

            grdSelector.DataSource = GetSource(blah)

Open in new window


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

Open in new window


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!
0
Comment
Question by:Starr Duskk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 167 total points
ID: 39893349
Close it immediately after loading the grid.

Dim dr As SqlDataReader = GetSource(blah)
grdSelector.DataSource = dr
dr.Close()

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39894506
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.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895232
Thank you CodeCruiser, I will change that. I assume that applies to populating comboboxes as well.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895253
Also:

I don't define my datareader like this:
Dim dr As SqlDataReader = GetSource(blah)
grdSelector.DataSource = dr
dr.Close() 

Open in new window


I call a method that returns a data reader like this:
 Dim daOrm As DataAccess.CustomDepartment = New DataAccess.CustomDepartment()
            grdSelector.DataSource = daOrm.GetDepartmentByClientIdSqlCommandReader(ClientId)

Open in new window



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

Open in new window


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!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39895333
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.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895363
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

Open in new window

0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895478
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39895550
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.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895601
>>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!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39895619
In connection to this post, "ID: 39895363", the way you are doing it there is correct.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 333 total points
ID: 39895631
DataTable is disconnected so there is no need to "close" it.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895872
>>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?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 333 total points
ID: 39895928
Yes SQLDataReader keeps the connection to database open and occupied until it is closed. It will become apparent if you try to execute another command using the same connection (you will get an error that an open reader is already attached to this connection).
0
 
LVL 2

Author Closing Comment

by:Starr Duskk
ID: 39895947
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!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question