Connect SqlDataAdapter to SqlConnection outside of constructor

Question

Teaching myself ado.net coming from dao vb background.

Searching assorted code examples I was able to write something to extract some data from the pubs database in sql. Now I am trying to modify it to fit my needs. Because I will be accessing the database quite often I want to avoid recreating my objects over and over (sqlconnection, sqldataadapter, dataset) and just create them once globally. But they seem designed to use their constructors to build them up in succession when it is time to use them. I was able to find a way around most of it but I am stuck on a couple spots.

How can I connect the SqlDataAdapter to the SqlConnection outside of it's constructor?
What is the syntax to reference a specific 'record' in the results without iterating through the results with a FOR EACH.

Here is my code (This code is just to learn how to use it. It is not intended to be a practical application)

Imports System.Data.SqlClient

Public Class SqlLink

Dim conn As New SqlConnection
Dim da As New SqlDataAdapter
Dim ds As New DataSet

Public Function Connect(ByVal sConnString As String) As Integer
    'attempts to connect to database, returns error code
    Try

        conn.ConnectionString = sConnString
        conn.Open()
        Return 0

    Catch ex As Exception
        MsgBox(Err.Description)
        Return Err.Number

    End Try

End Function


Public Sub exampleroutine()

    Dim TempRow As DataRow

    'DO NOT WANT Dim da as new sqlDataAdapter("some query", conn)
    'I DO NOT WANT TO DIM THE da HERE. I WANT IT DIM'D ONCE GLOBALLY
    'HOW CAN I TIE THE da TO THE conn HERE OUTSIDE OF IT'S CONSTRUCTOR

    da.SelectCommand.CommandText = "SELECT au_fname FROM authors where au_fname like '%ann%'"
    da.Fill(ds)

    For Each TempRow In ds.Tables(0).Rows
        MsgBox(TempRow("au_fname"))
    Next

    da.SelectCommand.CommandText = "SELECT au_fname FROM authors where au_fname like '%reg%'"
    da.Fill(ds)

    'HOW TO GET A SPECIFIC 'RECORD' WITHOUT ITERATING THROUGH WITH A FOR EACH
    'If ds.Tables(0).Rows.Count > 0 Then ???

End Sub

End Class

======================================================

I worked out a solution but I don't know if anyone wants to, or should, follow it in light of the comments but.. just for closure, this is what I did to resolve this.

In the Connect function, right after conn.Open I added
da = New SqlDataAdapter(False, conn)
This created a reusable global instance of the data adapter.

Do not work against the intended flow of ADO.NET. You'll find yourself in more trouble than you expect — Steve
– Steve, Commented Apr 30, 2013 at 16:00
Generally, "globals" should be avoided unless they are providing some particular advantage. And unlike in the old COM environment, in .NET there is no particular advantage or efficiency to avoiding the creation of new objects. So, other than the connection object (which has real practical overhead), you shouldn't be going out of your way to "cache" your Ado.net objects. (this is distinct from caching data). — RBarryYoung
– RBarryYoung, Commented Apr 30, 2013 at 16:25
Steve, kind of like do not taunt happy fun ball eh? ;) I get what you're saying and I have a feeling the more I build up my application the more true that is going to ring. — Robb
– Robb, Commented Apr 30, 2013 at 19:57
Barry, I just assumed there was always considerably more overhead creating objects and then using them versus reusing. I ran a test retrieving a dataset 50k times using both create method and 'cached' method. There was a difference but only 1.6 seconds. I don't think I'll be accessing the database 50,000 times in succession hehe... so it looks like recreating them all for every access will be okay. — Robb
– Robb, Commented Apr 30, 2013 at 20:07

Paul Anderson · Accepted Answer · 2013-04-30 16:09:40Z

2

You can set the connection of the SelectCommand:

da.SelectCommand.Connection = conn

to get the first row in the table:

ds.Tables(0).Rows(0)

You can further filter a DataSet using a DataView. Set the conditions in the rowFilter property of the DefaultView. The filter rows are then in DefaultView.

    ds.Tables(0).DefaultView.RowFilter = "<your conditions here>"

answered Apr 30, 2013 at 16:09

Paul Anderson

1,1803 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tshepang · Accepted Answer · 2013-11-15 23:30:52Z

2

I hope this is what you were looking for :)

I hate typing when I don't have to either:

Imports System.Data.SqlClient

Module Module1

Dim con As New SqlConnection
Dim myConString As String = getSQLString()
Dim objcommand As SqlCommand = New SqlCommand
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet

Public Function getSQLString()
    Dim thisstring As String = "Server.... ur server"
    Return thisString
End Function


Public Function GetData(ByVal sqlstringtext As String)

    'Dim cmdText As String = "SELECT EquipList from SiteAuditor where client='" & GLClient & "' and market='" & GLMarket & "' and project='" & GLProject & "'"
    Dim conn As New SqlConnection(getSQLString())
    Dim strQ As String = String.Empty
    strQ = sqlstringtext
    cmd = New SqlCommand(strQ, conn)
    da = New SqlDataAdapter(cmd)
    ds = New DataSet
    da.Fill(ds)

    'we want to do this on close of program 
    'da = Nothing
    'ds = Nothing
    Return Nothing
End Function


'calling function you can do this whenever because we don't have to re-dim anything there being overridden
Sub whatever()

    GetData("Select thisItem from thisTable where thiscondition='thiscondition")
    'first table'firstrow'firstrecord
    Dim specificRecord As String = ds.Tables(0).Rows(0).Item(0).ToString

End Sub

Sub formclosing()
    'we want to do this on close of program  ( the actual syntax for that would be .... 
    '
    'Private Sub Main_Closing(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
    'da = Nothing
    'ds = Nothing
    'Application.Exit()
    'End Sub
    '
End Sub

End Module

EDIT: if you have to change the myconstring you can do so by setting it public btw or creating its own subroutine changing / redimming it

edited Nov 15, 2013 at 23:30

tshepang

12.5k25 gold badges98 silver badges140 bronze badges

answered Apr 30, 2013 at 16:17

Pakk

8592 gold badges19 silver badges39 bronze badges

2 Comments

Robb Over a year ago

Thanks user2236967.. may I call you 2? I appreciate your reply and the work you put into it. I was just looking for a way to declare the dataadapter globally. Turns out it was a simple da = New SqlDataAdapter(False, conn) command right after opening the connection. Then just set the da commandtext and call fill whereever I wanted to access data.

Pakk Over a year ago

Sure, don't forget to vote up helpful answers and mark a comment/answer as correct! Happy Coding.

Collectives™ on Stack Overflow

Connect SqlDataAdapter to SqlConnection outside of constructor

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related