DB Wrapper Using DataReader

[rcombs4]

A while back I wanted to create a wrapper for my DB calls. In my applications it is just easier for me to use inline SQL queries. I'm constantly having to change what fields I'm using to pull data and what fields are returned. I started out using a proper DAL but it was just too much to upkeep. So I went with a simple wrapper that in its constructor you passed in a SQL statement. Then you had a function to execute the SQL statement and return a DataSet. It has worked perfectly for what I want.

But now we have grown so much that we are definitely starting to see performance issues with returning a huge DS with a thousand plus records. I'd like to keep my same structure but use a DataReader instead. I've done a good bit of reading but nothing has really pleased me.

So for now I've decided to continue using a DataSet when I'm doing more than just displaying the data to a grid. In those cases I'm almost always just pulling 1 record anyway. But when I'm binding to a grid I've created a new sub that takes a Delegate. This way I can pass in the address of the sub that does my binding to a grid and have my wrapper have total control of the datareader and connection.

Could you guys please review my wrapper class and just tell me your thoughts. Just with playing with it on a few pages it seems that it will do what I want, but I'm not confident at all that it is the "best" way to do it.

*I took Delegate stuff from an article on ASP Alliance. I've never actually used Delegates outside of tutorials before so please let me know if this is improper use of them*

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data

Public Class MyDB

    'Keeps ConnectionType
    Public myCurConnection As ConnectionType = ConnectionType.****
    'Keeps Query/Params
    Public DA As New SqlDataAdapter

    'Delegate
    Public Delegate Sub IDataReaderHandler(ByVal dReader As SqlDataReader)

    'Default Constructor
    Sub New()

    End Sub

    'Constructor to pass in Query and optional DB
    Sub New(ByVal strQuery As String, Optional ByVal curConnection As ConnectionType = ConnectionType.****)
        'Points to correct DB
        myCurConnection = curConnection

        'Creates DA with correct query and connection
        DA = New SqlDataAdapter(strQuery, GetConnection())
    End Sub

    'Function gets the correct SqlConnection
    Private Function GetConnection() As SqlConnection
        If myCurConnection = ConnectionType.**** Then
            Return New SqlConnection(ConfigurationManager.AppSettings("***"))
        Else
            Return New SqlConnection(ConfigurationManager.AppSettings("****"))
        End If
    End Function

    'Enum to flag which DB
    Public Enum ConnectionType
        ***
        ****
    End Enum

    'Execute the query and pass the dReader to the Delegate
    Public Sub Execute(ByVal handler As IDataReaderHandler)
        Using conn As SqlConnection = DA.SelectCommand.Connection

            'Open the Query
            conn.Open()

            'now we want to call the delegate and pass in the reader
            Using dReader As SqlDataReader = DA.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection)
                handler(dReader)
            End Using

            'Auto closes at end of Using dReader...
        End Using
    End Sub


End Class

Usage:

Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim db As New MyDB("SELECT * FROM Test")
        db.Execute(AddressOf LoadData)
    End Sub

 Private Sub LoadData(ByVal dReader As SqlDataReader)
        grvTest.DataSource = dReader
        grvTest.DataBind()
    End Sub

Thanks a bunch guys.

 

[jmcilhinney]

I would suggest doing things the easy way: create a model using the Entity Framework and use LINQ.

 

[rcombs4]

Can I do that in .net 2.0?

 

[jmcilhinney]

No, but that's a good reason to upgrade to VB 2010.