SQL Queries and Stored Procedures

Hi All,

I would like to use the datareader and the sqlconnection to access records in my db. I am brand new to .net and have an application to convert.

What is the most efficient data access method??

Could someone give me an example of a complete SUB where a connection is made and then a dataset/ datareader is populated from a stored proc with parameters.

Sorry I don't have my code in front of me.

What is the deal with tableadapters? Are they  efficient? I am using a direct connection to the DB by the way.

Thanks
LVL 1
nzfireAsked:
Who is Participating?
 
wtconwayConnect With a Mentor Commented:
Here are two functions I wrote a while back. They should be helpful, hopefully ;)

Imports System.Data.OleDb
Imports System.Data

    Overloads Function GetDataSet(ByVal sql As String, ByVal Source As String) As DataSet
        Dim conConnect As OleDbConnection = New OleDbConnection(Source)
        Dim dapAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
        Catch ex As OleDbException
             'process error like so:
             MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return dstDataSet
    End Function

    Overloads Function GetDataTable(ByVal sql As String, ByVal Source As String) As DataTable
        Dim conConnect As OleDbConnection = New OleDbConnection(Source)
        Dim dapAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conConnect)
        Dim dstDataSet As New DataSet
        Dim tblDataTable As DataTable
        Try
            conConnect.Open()
            dapAdapter.Fill(dstDataSet)
            tblDataTable = dstDataSet.Tables(0)
            dstDataSet.Tables.Clear()
        Catch ex As OleDbException
             'process error like so:
             MsgBox(ex.Message)
        Finally
            conConnect.Close()
        End Try
        Return tblDataTable
    End Function
0
 
Fahad MukhtarDistinguished EngineerCommented:
I think you better use datareader which is faster than dataset
here is an article with source code that explains scenario in which a connection is made and then a dataset/ datareader is populated
http://www.startvbdotnet.com/ado/sqlserver.aspx
http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=1

To read difference in dataset and datareader check:
http://www.sitepoint.com/article/dataset-datareader
http://www.eggheadcafe.com/forums/ForumPost.asp?id=67487&ThreadID=67487&INTID=14
0
 
Fahad MukhtarDistinguished EngineerCommented:
tableadapters(use to fill a dataset) are efficient but datareaders are more
0
 
LordWabbitCommented:
But if you open a datareader the connection used is not re-usable by aything untill you close the datareader.  Not particularly fond of datareaders myself.
0
 
Bob LearnedCommented:
Yeah, but DataReaders are very light-weight, if you only need read-only data.

Bob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.