Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function
Best way to return SqlDataReader from function Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Go Back  Xtreme Visual Basic Talk > > > Best way to return SqlDataReader from function


Reply
 
Thread Tools Display Modes
  #1  
Old 04-15-2013, 08:28 AM
mrkamran's Avatar
mrkamran mrkamran is offline
Centurion
 
Join Date: Jun 2003
Posts: 188
Default Best way to return SqlDataReader from function


Hi,

I have written below function in a module to get sqldatareader in any form. Is it correct way? there is one question in my mind that after return statement I have two line CN.Close() and sqlCmd.Dispose(), it will not be run after return statement then this connection and command remains open in memory? and for every call it will make garbage in memory? or please tell me the best and efficient way..


Public Function GetData(ByVal sSQL As String) As SqlDataReader

Dim CN As SqlConnection
Dim sqlCmd As SqlCommand = New SqlCommand(sSQL)
Dim myData As SqlDataReader

CN = New SqlConnection(ConStr)

Try
CN.Open()
sqlCmd.Connection = CN
myData = sqlCmd.ExecuteReader

Return myData

CN.Close()
sqlCmd.Dispose()
Catch ex As Exception

End Try
End Function


Thanks

Kamran
__________________
Thoughts determine what you want...Action determines what you get!
Reply With Quote
  #2  
Old 04-15-2013, 08:37 AM
DrPunk's Avatar
DrPunkBest way to return SqlDataReader from function DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

The code after your return won't get run.

The best is to utilise the Finally part of the Try Catch block. A Finally part will get run regardless of whether the Try succeeds or fails.

Consider the following Function to test the return...
Code:
Private Function TestReturn As Boolean
    return True

    messagebox.show("Do you see this message")
End Function
Running the function confirms that no messagebox is shown.

Adding in a Try with a Finally gets around that.
Code:
Private Function TestReturn As Boolean
    try
        return True
    catch ex as exception
        retun False
    finally
        messagebox.show("Do you see this message")
    end try
end Function
Running that function will confirm you get the message.

That makes it a great place to close database connections because you don't have to worry about if the actions on the database connection worked or not, it will be closed regardless.
Code:
Try
    cn.open

    ' do something with the connection

    ' return something
Catch ex as exception
    ' warn the user it went wrong
    ' or maybe raise the error again for the caller to catch
    Throw
Finally
    ' close the connection regardless of working or not
    cn.close
    cn.dispose
End Try
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 04-15-2013, 09:26 AM
mrkamran's Avatar
mrkamran mrkamran is offline
Centurion
 
Join Date: Jun 2003
Posts: 188
Default

Dear DrPunk,

I tried it, the finally statements execute but due to that reason I got error in my form that reader is close now. The sqlreader in form which is receiving sqlreader from function also close.

I use that statements in form:

Dim reader as SqlDataReader
reader = getdata("Select EmpID, EmpName from Employees")
txtEmpID.Text = reader.Item("EmpID").ToString

Due to running statements after Finally I got error of no reader.

Or can I use dataset in replacement of sqldatareader? records in dataset remain available if i close connection, command and reader?

Thanks

Kamran
__________________
Thoughts determine what you want...Action determines what you get!
Reply With Quote
  #4  
Old 04-15-2013, 09:54 AM
PlausiblyDamp's Avatar
PlausiblyDampBest way to return SqlDataReader from function PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

A SqlDataReader requires an open connection to work, if you close the connection then the SqlDataReader cannot function.

If you need to use a SqlDataReader then you could have the function return one but no close the connection down, the code that uses the SqlDataReader however could wrap it in a Using block to make sure it is closed when no longer needed.

If you use a DataSet then you will not be required to maintain an open connection, however if you are on a more recent version for .Net (3 or later) you might want to investigate Linq2Sql or the entity framework as alternate data access methods.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #5  
Old 04-26-2013, 05:45 AM
DrPunk's Avatar
DrPunkBest way to return SqlDataReader from function DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by mrkamran View Post
Or can I use dataset in replacement of sqldatareader? records in dataset remain available if i close connection, command and reader?
In my opinion that would be a better design choice.

GetData would return a DataSet (or DataTable), i.e. the Data.

That way you can do all your opening connection, getting data, closing connection, and disposing of objects used all in one routine (and one try catch finally) and not have to worry about leaving stuff open and making sure it gets closed.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
 
Best way to return SqlDataReader from function
Best way to return SqlDataReader from function
 
-->