• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
nzfire
Asked:
nzfire
1 Solution
 
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
 
wtconwayCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now