CODEWITHSUNDEEP
sqlsql-servervb.netado.net
Robb
Robb

Reputation: 37

Connect SqlDataAdapter to SqlConnection outside of constructor

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.

  1. How can I connect the SqlDataAdapter to the SqlConnection outside of it's constructor?
  2. 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.

Upvotes: 0

Views: 3587

Answers (2)

Pakk
Pakk

Reputation: 1339

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

Upvotes: 2

Paul Anderson
Paul Anderson

Reputation: 1180

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>"

Upvotes: 2

Related Questions