Reputation: 37
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.
SqlDataAdapter
to the SqlConnection
outside of it's constructor?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
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
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