DataReader ExecuteReader Sql Connection problem

[aspnetvbnetnerd]

Is it possible for me to execute multiple stored procedure and returning SqlDataReader without having to open a connection to the database.

What I understand I have to open a connection to the database every time I want to executure a stored procedure. I'm I correct with this?

Private mDataAdapter As Class1
Private mSqlDrCustomerAssignment As SqlDataReader
Private mSqlDr As SqlDataReader
        
mDataAdapter = New Class1
Call mDataAdapter.Init()

mSqlDr = mDataAdapter.GetAllCustomer()
While mSqlDr.Read()

    mSqlDrCustomerAssignment = mDataAdapter.GetCustomerAssignment(CType(mSqlDr.Item("CustomerID"), Integer))
    While mSqlDrCustomerAssignment.Read()
    End While

End While

Class1

Private mDbConn As SqlConnection
Private mSqlCmd As SqlCommand
Private mSqlDr As SqlDataReader

Public Overridable Sub Init()
    mDbConn = New SqlConnection(ConnectionString)
    mDbConn.Open()
End Sub

Public ReadOnly Property ConnectionString() As String
    Get
        Return "Data Source=(local);Initial Catalog=DAarchiving;User Id=sa;Password=ett2tre;"
    End Get
End Property

Public Function GetAllCustomer() As System.Data.SqlClient.SqlDataReader

    mSqlCmd = New SqlCommand("usp_GetAllCustomer", mDbConn)
    mSqlCmd.CommandType = Data.CommandType.StoredProcedure

    mSqlDr = mSqlCmd.ExecuteReader()

    Return mSqlDr

End Function

Public Function GetCustomerAssignment(ByRef CustomerID As Integer) As System.Data.SqlClient.SqlDataReader

    Dim mSqlCustomerAssignment As SqlDataReader

    mSqlCmd = New SqlCommand("usp_GetCustomerAssignment", mDbConn)
    mSqlCmd.CommandType = Data.CommandType.StoredProcedure

    mSqlCmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int, 5)
    mSqlCmd.Parameters("@CustomerID").Value = CustomerID

    mSqlCustomerAssignment = mSqlCmd.ExecuteReader() [COLOR=red]<-- There is already an open DataReader associated with this Command which must be closed first.[/color]

    Return mSqlCustomerAssignment

End Function

 

[chrissie1]

Apart form the bad desgin (do not return datareaders but data) a connection can only have one datareader open at a time. So you either make multiple connection for each datareader or you close the datareader and then request a new one from the same connection.

Christiaan Baes
Belgium

My Blog

 

[aspnetvbnetnerd]

What is wrong with this design?
Christiaan, please let me know what is wrong so I will learn.

How would you do it?

 

[chrissie1]

You should return a list of objects (best case scenario) or a datatable/dataset. not a datareader. A datareader should read the data and put them in objects. Returning a datareader is not needed.

something like this

Public Function GetAllCustomer() As Ilist<Customer>
...
end function




Christiaan Baes
Belgium

My Blog

 

[aspnetvbnetnerd]

I use the datareader to create a treeview menu.
This is the code that I am using.

Is this better? or could I have done this better?
Is it okay to do like this?

'This refresh/gets the customer treeview.
'Should be called when the treeview is changed

' Builds the menu for the treeview
Dim NodeParent As TreeNode
Dim NodeChild As TreeNode
Dim NodeChildU As TreeNode

mDataAdapter = New DataAdapter
'Initiate the connection
Call mDataAdapter.Init(ConnectionString)

' TreeView
With TrViewCustomer
    ' Suppress repainting the TreeView until all the objects have been created.
    .BeginUpdate()
    .Nodes.Clear()                  ' Clear all nodes

    'Mainmenu
    NodeParent = .Nodes.Add(FormLogIn.htbLang.Item(10001).ToString)     'Kunder

    '2007-06-01 : Call Sp (usp_getAllCustomer)
    mSqlDr = mDataAdapter.GetAllCustomer()
    While mSqlDr.Read()

        NodeChild = NodeParent.Nodes.Add(mSqlDr.Item("CustomerID").ToString, mSqlDr.Item("CustomerName").ToString)
        .Tag = mSqlDr.Item("CustomerID")
        NodeChild.Tag = mSqlDr.Item("CustomerID")
        'Get all assignment from a customer
        mSqlDrCustomerAssignment = mDataAdapter.GetCustomerAssignment(CType(mSqlDr.Item("CustomerID"), Integer))
        While mSqlDrCustomerAssignment.Read()
            NodeChildU = NodeChild.Nodes.Add(mSqlDrCustomerAssignment.Item("Rubric").ToString.PadRight(6, CType(" ", Char)) + mSqlDrCustomerAssignment.Item("DocumentTypeID").ToString.PadLeft(2, CType("0", Char)))
            .Tag = mSqlDrCustomerAssignment.Item("AssignmentID")
            NodeChildU.Tag = .Tag
        End While

        'Cleaning up
        mSqlDrCustomerAssignment.Close()
        mSqlDrCustomerAssignment = Nothing

    End While

    .EndUpdate()                    ' Begin repainting the TreeView.
    .Sort()                         ' Sorting all the nodes

End With

'Clear everything
mSqlDr.Close()
mSqlDr = Nothing
mDataAdapter.Dispose()
mDataAdapter = Nothing

 

[chrissie1]

YOu could have done better.

Your view (treeview, form, anything else in system.windows.forms) should not have a reference to system.data in any for or size.

So you either fill a datatable with the datareader or you make a customer object put that in a list and give that to the view and loop through that list.

Remove any imports to system.data out of your forms and see how you can get it to work without it then you will be on the right track.

I'm willing to rewrite that piece you have there but just not at the moment. But you can try and do what I said to see if you can get it working.



Christiaan Baes
Belgium

My Blog