Re: making datareaders

I don't see any code for closing any datareader. You have to close the datareader when you are done with it, or the connection won't be usable again.

aaron.kempf@xxxxxxxxx wrote:
only one can be open on a connection at once.

i swear; i've seen documentation in several places that SQL 2005
against VB 2005 supports MARS -- multiple active result sets.

i guess i'm just mistaken.

So. How can i get the results of a datareader into an array??

This example is from the 101 Samples, VB 2005, Data from
microsoft.com/visualbasic or whatever the url is.
version 2005.


''' <summary>
''' This method fills employee and address information using
Multiple Active Result Sets
''' This is set in the connection string by setting the
MultipleActiveResultSets to true
''' by default it is set to true if ommitted.
''' By setting Multiple Active Results Sets, multiple data readers
can be opened
''' by using the same connection without any blocking from
occurring.
''' </summary>
Private Sub FillUserAddressesWithMARS()

Dim myEmployeeID As Integer = 0
Dim myAddressReader As SqlDataReader
Dim myWatch As Stopwatch = New Stopwatch()
Dim myFinalString As String = ""
Dim myConnectionCount As Integer = 0

Try

'' explicitly set the connection string to support Multiple
Active Result Sets.
Dim connectionString As String =
ConfigurationManager.AppSettings("myMarsConnectionString")


'' Set the query strings
Dim myEmployeeQuery As String = "SELECT * FROM Employees
ORDER BY LastName"
Dim myAddressQuery As String = "SELECT * FROM Addresses
WHERE EmployeeID = @EmployeeID"

'' Use the new StopWatch class to time the retrieval and
display of data
myWatch.Start()

'' Encapsulate the retrieval with this one connection
Using myConnection As SqlConnection = New
SqlConnection(connectionString)

'' Open the connection and incrase the count
myConnection.Open()
myConnectionCount = myConnectionCount + 1

'' We create both SqlCommand objects using the same
connection
Dim myEmployeeCommand As SqlCommand = New
SqlCommand(myEmployeeQuery, myConnection)
Dim myAddressCommand As SqlCommand = New
SqlCommand(myAddressQuery, myConnection)

'' Add the parameter to the address command, this will
allow us
'' to get all the addresses for each employee id
myAddressCommand.Parameters.AddWithValue("@EmployeeID",
SqlDbType.Int)

'' We are going to go through all the Employee records
Using myEmployeeReader As SqlDataReader =
myEmployeeCommand.ExecuteReader()

While myEmployeeReader.Read()

'' We want to display the name of the employee
in the final text box
Dim myName As String =
myEmployeeReader("FirstName").ToString() & " " &
myEmployeeReader("LastName").ToString()
myFinalString = myFinalString & myName & vbCrLf

'' Retrieve the EmployeeID for getting all the
addresses
myEmployeeID =
Convert.ToInt32(myEmployeeReader("EmployeeID"))


myAddressCommand.Parameters("@EmployeeID").Value = myEmployeeID


'' get the address information
myAddressReader =
myAddressCommand.ExecuteReader()
Using myAddressReader
If myAddressReader.HasRows Then

'' retrieve all the addresses in the
DataReader object
While myAddressReader.Read()

Dim myAddress As String =
myAddressReader("Address").ToString()
Dim myCity As String =
myAddressReader("City").ToString()
Dim myState As String =
myAddressReader("State").ToString()
Dim myZipCode As String =
myAddressReader("ZipCode").ToString()
Dim myAddressType As String =
myAddressReader("AddressType").ToString()

myFinalString = myFinalString &
myAddress + vbCrLf _
& myCity & ", " & myState & " " &
myZipCode & vbCrLf _
& "Address Type: " & myAddressType
& vbCrLf & vbCrLf
End While

Else
myFinalString = myFinalString & "No
Address " & vbCrLf & vbCrLf
End If
End Using
End While
End Using
'' Close the connection
myConnection.Close()
End Using
'' Stop the StopWatch so that we can display the time
myWatch.Stop()
'' Display the time elapsed
elapsedTimeLabel.Text =
myWatch.ElapsedMilliseconds.ToString() & " ms"
'' Display how many connections were created
connectionNumberLabel.Text = myConnectionCount.ToString()
'' set some properties for the RichTextBox
displayedDataRichTextBox.ScrollBars =
RichTextBoxScrollBars.Vertical
displayedDataRichTextBox.WordWrap = True
'' Assign the final data
displayedDataRichTextBox.Text = myFinalString

Catch ex As Exception
MessageBox.Show("There was an error retrieving data using
MARS", "Alert")
End Try

End Sub

.

Relevant Pages

  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: making datareaders
    ... ''' This is set in the connection string by setting the ... Dim myAddressReader As SqlDataReader ... Dim myFinalString As String = "" ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ADO connections question
    ... I have a module which creates the ADO connection object to validates the ... Function dbLogin(txtUser As String, txtPword As String, txtServer As String, ... I have a specific login form that calls the dbLogin function as shown below: ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)