Question DataSet into DataReader

[marjune]

hi!!! i have a function which returns a DataSet! then how can i convert into DataReader?? thanksss

 

[jmcilhinney]

Instead of creating a DataAdapter and calling Fill, create a Command and call ExecuteReader. Just be aware that you will have to Close the DataReader after you've used it.

 

[marjune]

thanks!! but i'm confused of what exactly to do!!

-- --my code----

sConnectionString = my Connection String
sSqlStatement = my sql query
iErrorNumber = turn the value to 1 if there something wrong in the conection
sErrorDescription = this is the error exeption


then in my other function i will call the ExecuteCommand(parameters here) which is it returns a DataSet then how to perform ExecuteReader command here????

Public Function ExecuteCommand(ByVal sConnectionString As String, _
ByVal sSqlStatement As String, _
ByRef iErrorNumber As Long, _
ByRef sErrorDescription As String) As DataSet
Dim iErrorValue As Integer
Dim sErrorValue As String = ""

Dim oConnection As SqlConnection
oConnection = New SqlConnection
oConnection = Connection(sConnectionString, iErrorValue, sErrorValue)

If Not iErrorValue = 0 Then
iErrorNumber = iErrorValue
sErrorDescription = sErrorValue

ExecuteCommand = Nothing
Else
Dim da As SqlDataAdapter
Dim ds As DataSet

Try

da = New SqlDataAdapter(sSqlStatement, oConnection)
ds = New DataSet
da.Fill(ds, "Person")

If ds.Tables(0).Rows.Count() > 0 Then
ExecuteCommand = ds
Else
ExecuteCommand = Nothing
End If

Catch ex As Exception

ExecuteCommand = Nothing


End Try

ds = Nothing
da = Nothing

End If

iErrorValue = Nothing
sErrorValue = Nothing

oConnection = Nothing

End Function

 

[jmcilhinney]

The first thing to note is that your code is very convoluted and redundant. It can be simplified considerably:

Public Function ExecuteCommand(ByVal sConnectionString As String, _
                               ByVal sSqlStatement As String, _
                               ByRef iErrorNumber As Long, _
                               ByRef sErrorDescription As String) As DataSet
    Dim result As DataSet = Nothing
    Dim iErrorValue As Integer
    Dim sErrorValue As String = ""
    Dim oConnection As SqlConnection = Connection(sConnectionString, iErrorValue, sErrorValue)

    If iErrorValue = 0 Then
        Dim da As New SqlDataAdapter(sSqlStatement, oConnection)
        Dim ds As New DataSet

        Try
            da.Fill(ds, "Person")

            If ds.Tables(0).Rows.Count() > 0 Then
                result = ds
            End If
        Catch ex As Exception
            '...
        End Try
    Else
        iErrorNumber = iErrorValue
        sErrorDescription = sErrorValue
    End If

    Return result
End Function

As for the changes you need to make, as I said in my previous post, create a Command instead of a DataReader and call ExecuteReader instead of Fill:

Public Function ExecuteCommand(ByVal sConnectionString As String, _
                               ByVal sSqlStatement As String, _
                               ByRef iErrorNumber As Long, _
                               ByRef sErrorDescription As String) As SqlDataReader
    Dim result As SqlDataReader = Nothing
    Dim iErrorValue As Integer
    Dim sErrorValue As String = ""
    Dim oConnection As SqlConnection = Connection(sConnectionString, iErrorValue, sErrorValue)

    If iErrorValue = 0 Then
        Dim cmd As New SqlCommand(sSqlStatement, oConnection)

        oConnection.Open()

        Try
            Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            If rdr.HasRows Then
                result = rdr
            End If
        Catch ex As Exception
            '...
        End Try
    Else
        iErrorNumber = iErrorValue
        sErrorDescription = sErrorValue
    End If

    Return result
End Function

 

[cjard]

hi!!! i have a function which returns a DataSet! then how can i convert into DataReader?? thanksss

Why do you want to do this?