making datareaders

[dbahooker]

team

i'm having a tough time getting these data readers to work correctly

I'd just like to be able to centralize my GetDataReader functions; so i
can pass a simple SQL statement and be passed back a valid DataReader
object.

I've been slaving with this all day long. I've been trying 'new' and
not new and just slaving over this; im calling it an early day..

I'm pretty darn sure i've got my connection working; i know my SQL is
working correctly.
I can setup a command and it works just fine (executeNonQuery).

I just can't figure out what i'm doing wrong on this side.

It's like-- it keeps on complaining that i've already got a dataset by
the same name; shouldn't MARS make this easier??

-------------------------------------------------------------

System.Transactions Critical: 0 : <TraceRecord
xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord"
Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled
exception</Description><AppDomain>FileMon.vshost.exe</AppDomain><Exception><ExceptionType>System.NullReferenceException,
mscorlib, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Object
reference not set to an instance of an object.</Message><StackTrace>
at FileMon.basCatalog.Catalog() in C:\Documents and Settings\aarkem\My
Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\basCatalog.vb:line 28
at FileMon.frmMain.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\aarkem\My Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\frmMain.vb:line 19
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m,
MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp;
m)
at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp;
m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at
System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp;
msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at FileMon.frmMain.Main() in C:\Documents and Settings\aarkem\My
Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\frmMain.Designer.vb:line 2
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at
System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.NullReferenceException:
Object reference not set to an instance of an object.
at FileMon.basCatalog.Catalog() in C:\Documents and
Settings\aarkem\My Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\basCatalog.vb:line 28
at FileMon.frmMain.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\aarkem\My Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\frmMain.vb:line 19
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m,
MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp;
m)
at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp;
m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at
System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp;
msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at FileMon.frmMain.Main() in C:\Documents and Settings\aarkem\My
Documents\Visual Studio
2005\Projects\FileMon8_20060501\FileMon_9\frmMain.Designer.vb:line 2
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at
System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>



-------------------------------
Public Sub Catalog()

TOTAL_FILEKEEP = 0
ROOTFOLDER_DINCLAUSE = DinClause("ROOTFOLDERID", "ROOTFOLDERS",
"ACTIVE=1")

Dim rootFolderID As Integer
Dim RootFolder As String

Dim DR As New System.Data.SqlClient.SqlDataReader
DR = Nothing
SetDataReader(DR, "EXEC spRootFoldersToCatalog")

While DR.Read
RootFolder = DR.GetValue(1).ToString
rootFolderID = CInt(DR.GetValue(0).ToString)
Call RootFolder_Catalog(RootFolder, rootFolderID)
End While


Files_Catalog()

End Sub
--------------------------------------------------------------------------------
Public Sub SetDataReader(ByRef thisDR As
System.Data.SqlClient.SqlDataReader, ByVal strSql As String)
frmMain.WriteStatus(strSql)

Dim cmd As New System.Data.SqlClient.SqlCommand

cmd.CommandText = strSql
cmd.Connection = cnnX
Try
thisDR = cmd.ExecuteReader
Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub
-----------------------------------------------
Public Sub RunSql(ByVal strSql As String)

Dim cmd As New System.Data.SqlClient.SqlCommand

frmMain.WriteStatus(strSql)
cmd.CommandText.Equals(strSql)

Try
cmd.ExecuteNonQuery()
Catch ex As Exception
frmMain.WriteStatus("SQL FAILED: " & strSql)
Stop
End Try

End Sub
---------------------------------------------

 

[Guest]

(e-mail address removed) wrote in @y43g2000cwc.googlegroups.com:

I'd just like to be able to centralize my GetDataReader functions; so i
can pass a simple SQL statement and be passed back a valid DataReader
object.

Take a look at Microsoft Data Application Blocks ... does exactly what you
want : )

 

[Chris Dunaway]

Dim DR As New System.Data.SqlClient.SqlDataReader
DR = Nothing

First thing, you declare a new instance of a DataReader but then in
the next line you throw it away! The New keyword is not needed here
and you can delete the DR = Nothing line.

SetDataReader(DR, "EXEC spRootFoldersToCatalog")

While DR.Read

Assuming your stored procedure is executing, then you need to check if
DR is Nothing before you try to use it. I believe that is the problem.
Your stored procedure is not returning anything. You should use SQL
Profiler to help determine why you are not getting any data back.

 

[aaron.kempf]

i am getting data back

i will delete the new keyword; it was choking during compilation saying
i needed to do something with it before i get values from it or
something along those lines..

can i do

Dim DR as System.Data.SqlClient.SqlDataReader = GetDataReader("EXEC
spRootFoldersToCatalog")

??

 

[Chris Dunaway]

You'd have to write your GetDataReader function to return a DataReader,
but yes, that is probably the preferred method.

 

[aaron.kempf]

ok i am looking through the factory methods; i've been looking into
that for a while now

but i just swear; it is something much much simpler

I rewrote it so that i dont have to deal with the 'getDataReader'
procedure
but still i get the same error:
----------------------------------

There is already an open DataReader associated with this command which
must be closed first.
----------------------------------

This is my first ADO.net conversion and i'm just kinda banging my head
against a wall.

From what i understand; this is like a 2003 bug and it's fixed by

MARS-- multiple active result sets.

Right??

I know that the connection works; i've tested it with commands and it
works like a charm.


Dim cmd As System.Data.SqlClient.SqlCommand = cnnX.CreateCommand()
cmd.CommandText = "EXEC spRootFoldersToCatalog"
Dim DR As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader

While DR.Read
RootFolder = DR.GetValue(1).ToString
rootFolderID = CInt(DR.GetValue(0).ToString)
Call RootFolder_Catalog(RootFolder, rootFolderID)
End While

 

[Chris Dunaway]

A DataReader requires a constant connection to the database and only
one DataReader can be open on a connection at once. If the Sql query
returns more than one result set, you can advance to the next one by
calling the DataReader.NextResult method.

 

[aaron.kempf]

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

 

[Chris Dunaway]

Here's an example of what we do.

We execute the sql query to get a datareader back then pass that
datareader into a method to fill and object (or it could be an array)
and return that object:

Private Function FillUserObect(rdr As SqlDataReader) As User
Dim u As New User()

u.Id = rdr.GetInt32("Id")
u.Name = rdr.GetString("Name")

'More lines for the rest of the fields here

Return u
End Function


You can also use the data reader's GetValues method to fill an object
array with the values.

 

[aaron.kempf]

but the getValues only handles one record at a time right?

i'm so in over my head lol.

kneedeep in dotnet and loving it; im a VB6 / VBA convert.

-Aaron

 

[aaron.kempf]

and i still dont understand why MARS isn't working.

and what's an object array?

i just want a string array?

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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]

UNLESS IM USING MARS.

RIGHT?

 

[aaron.kempf]

i mean doesn't my example say this should work? i just dont understand

i guess that the bottom line is that i figured out the root of my
performance problem; a simple little cartesian.

THANK GOD.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Yes, if the database supports MARS. Judging by the error message it doesn't.

You should close the datareaders anyway.

 

[aaron.kempf]

well i'm on SQL 2005 Standard on Windows 2000 professional.
I do have a 4gb limit on db size; so maybe SQL thinks that i'm in
personal edition or something.

I'd love to get around the 4gb limit on my laptop; it drives me crazy!!

Is there anywhere that says which OS support MARS?

-Aaron

 

[aaron.kempf]

I kinda feel like I'm stuck in the (bad) old days of DAO where you had
to close all these objects for stability sake.

If I Dim a SqlDataReader why should i need to close it when it's gone
out of scope?

-Aaron

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Because if you don't, it will remain open (and keep the database object
alive) until the garbage collector wants to collect it but finds it
unclosed, so it calls it's finalizer to dispose of it. As you can't
control (or rather shouldn't interfer with) when carbage collections
occur, you don't know how long the datareader and connection will remain
in memory.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

SQL 2005 should support MARS.

Have you actually specified in the connection string that it should use
MARS?

 

[aaron.kempf]

the MS documentation right here says that it's on by default.

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


where can i learn about 10,000 times more about garbage collection and
GAC?
I've been banging my head against the wall on the tiniest performance
optimizations; i'd love to get to know the big picture.

like for starters-- this ETL job im running.. if i run it twice; it
usually goes a lot faster the 2nd time than the first time.
and i'd like to find out more about that effect.

i dont think that it's as simple as database caching or anything; i hit
the db pretty damn hard; the same tables over and over and over again.

any good references / websites?

non-MS websites? i dont trust MS anymore; years and years of betrayals.
those bunch of drunk idiots across the pond (lake washington lol) can't
even fix sql authentication.

-Aaron