[RESOLVED] Recordset equivalent in VB.NET

[Dave Sell]

In VB6 there was a thing called a Recordset Object. I could start with a stored procedure which returned some columns and rows from a SELECT statement, and then get the .Items(X) from the Recordset in code.

I am trying the same thing in .NET but cant find a Recordset object or anything like it. I have seen alot of references to the Dataset, but nearly all the examples are binding it to GUI objects. Thats not what I want.

I have stored procs that return "recordsets". How can I get at them from VB.NET?

Here is how I am currently getting return values from SP to .NET:

Dim myGetInfoConnection As New SqlConnection(System.Environment.GetEnvironmentVariable("RhinoConnectionString"))
            Dim myGetInfoCommand As New SqlCommand("spIAS_GetImageFromOperation", myGetInfoConnection)
            '
            ' Mark the Command as a SPROC
            myGetInfoCommand.CommandType = CommandType.StoredProcedure
            '
            ' Add Parameters to SPROC
            prmRETURN_VALUE = myGetInfoCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int)
            prmRETURN_VALUE.Direction = ParameterDirection.ReturnValue
            '
            Dim prmOperationID As New SqlParameter("@operationID", SqlDbType.Int)
            prmOperationID.Value = m_OperationID
            myGetInfoCommand.Parameters.Add(prmOperationID)
            '
            Try
                '
                myGetInfoConnection.Open()
                myGetInfoCommand.ExecuteNonQuery()
                m_PictureID = myGetInfoCommand.Parameters(0).Value
                '
                myGetInfoConnection.Close()
                '
            Catch SQLexc As SqlException
                Response.Write("SP Failed. Error Details are: " & SQLexc.ToString())
            End Try

Thanks!

Dave

[iainpb]

THe .net equivalent is the dataset and you use a data adapter between your database and your dataset. Heres a quick tutorial

http://www.homeandlearn.co.uk/NET/nets12p4.html

[szlamany]

We have this CLASS:

Option Strict On
Option Explicit On 
 
Imports System.Data
Imports System.Data.SqlClient
 
Public Class Database
 
    Private Connection As SqlClient.SqlConnection
    Private Command As SqlClient.SqlCommand
    Private Parameters As SqlClient.SqlParameterCollection
    Private strServerSelected As String
    Private strDatabaseSelected As String
    Private strConnectionString As String
 
    Public Function EstablishConnect(ByVal strServer As String, ByVal strDatabase As String, ByVal booLeaveOpen As Boolean) As Boolean
 
        strConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=" & strDatabase _
                        & ";server=" & strServer & ";Connect Timeout=30"
 
        Connection = New SqlClient.SqlConnection
        Connection.ConnectionString = strConnectionString
 
        Call OpenConnection()
 
        If Not booLeaveOpen Then Call CloseConnection()
 
    End Function
 
    Public Function OpenConnection() As Boolean
        Connection.Open()
    End Function
 
    Public Function CloseConnection() As Boolean
        Connection.Close()
    End Function
 
    Public Function SetSProc(ByVal strSP As String) As Boolean
        Command = New SqlClient.SqlCommand
        Try
            Command.Connection = Connection
            Command.CommandType = CommandType.StoredProcedure
            Command.CommandText = strSP
        Catch Exp As SqlException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
 
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
 
        End Try
 
    End Function
 
    Public Function SetParam(ByVal strParam As String, ByVal lngLen As Integer, ByVal strValue As String) As Boolean
        Dim parameter As New SqlParameter
        parameter = Command.Parameters.Add(strParam, Data.SqlDbType.VarChar, lngLen)
        parameter.Value = strValue
    End Function
 
    Public Function ExecuteSProc() As SqlDataReader
 
        ExecuteSProc = Nothing
 
        Try
            ExecuteSProc = Command.ExecuteReader
        Catch Exp As SqlException
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
 
        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
 
        End Try
 
    End Function
End Class

and use it this way...

db = New Database
        Dim dr As SqlClient.SqlDataReader
        'Was me.txtServer.text
        db.EstablishConnect(Me.txtServer.Text, ADC.strDB, True)
 
        db.SetSProc("GetADCReportNames_P")
        dr = db.ExecuteSProc()
 
        cmbReports.SelectedIndex = -1
        cmbReports.Items.Clear()
 
        Do While dr.Read()
            cmbReports.Items.Add(dr.Item(1))
            cmbReports.SelectedIndex = 0
        Loop
 
        db.CloseConnection()

or this way

db.OpenConnection()
            db.SetSProc("GetADCReportSpecs_P")
            db.SetParam("@SP", 50, cmbReports.SelectedItem)
 
            dr = db.ExecuteSProc()
 
            Do While dr.Read
                RO.CreateObject(dr.Item(1), dr.Item(2), dr.Item(3) _
                        , dr.Item(4), dr.Item(5), dr.Item(6) _
                        , dr.Item(7), dr.Item(8) & "")
            Loop

I'm still new to .net - but this works for me...

[Shaggy Hiker]

Also, there is the datareader, which is a forward only recordset. This will be faster than a dataset if you only need to read through the set one time.

[szlamany]

As you can see I use the datareader - we like forward-only, read-only type recordsets...

[Dave Sell]

Thanks all for this. sz, i dont get this loop:

Do While dr.Read
                RO.CreateObject(dr.Item(1), dr.Item(2), dr.Item(3) _
                        , dr.Item(4), dr.Item(5), dr.Item(6) _
                        , dr.Item(7), dr.Item(8) & "")
            Loop

What is the RO? What is the deal using CreateObject?

[szlamany]

RO is just another CLASS object that I am loading the data into - doesn't have anything to do with the DATABASE object or my example - just what I am doing with my recordset data...

[JAKSupport]

Couple of issues I see here:

1) Your use of variable names is awkward, some are keywords
2) Your parameters object only takes varchar
3) Your connection remains open across the entire time or lifetime of the project...it might be best to close the connection after executing a command object. (I know you have a function to close the connection, but you should not rely on programmers to use that class if they have to call that function many time, I guess it really depends on the complexity of the program (maybe your app was small and an open connection wasn't a bad idea ??))
4) You should not store your string file in the actual class, wouldn't the application configuration file be a better choice ?
5) Ugh @ dynamically changing the connection string via variables
6) Did you know datareaders maintain the connection to the db? Performance loss
7) I do not see any finally statements to release the command object / the connection object or your reader out of memory, big time memory leak (the GC will collect the memory only when it deems necessary
8) Some of your functions could be GET / SET properties rather than actual function definitions.

[szlamany]

Thanks for the info! This was kind of a learning experience for us in the whole .Net and OO world - thanks for the constructive criticism. Always appreciated!