BaseDataAccess.vb and effeciency

[r3plica]

Hi all,

This isnt a problem, I am just trying to make my coding better.
So a quick description.

I have a base data access layer that all my data access classes inherit.
It has some cool features that basically removes redundant code, because it's methods can be applied to all my data classes.
It has grown over the years and been modified, but one things that is missing is using a SqlDataReader.

So just looking at this class, can you tell me what you would do to change it and why?

Thanks in advance

Imports System.Data
Imports System.Data.SqlClient

Public Class BaseDataAccess
    Private Shared mstrConnectionString As String
    Private Shared mConn As SqlConnection

    Public Shared WriteOnly Property SetConnectionString() As String
        Set(ByVal value As String)
            mstrConnectionString = value
        End Set
    End Property

    Shared Function FillDataSet(ByVal pSQLCmd As SqlCommand) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            da.SelectCommand = pSQLCmd
            da.Fill(ds)
            da.Dispose()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
        Return ds
    End Function

    Shared Sub ExecuteNonSelect(ByVal pSQLCmd As SqlCommand)
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            pSQLCmd.ExecuteNonQuery()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
    End Sub

    Private Shared Sub OpenConnection()
        mConn = New SqlConnection
        mConn.ConnectionString = mstrConnectionString
        mConn.Open()
    End Sub

    Private Shared Sub CloseConnection()
        If mConn.State = ConnectionState.Open Then
            mConn.Close()
            mConn.Dispose()
        End If
    End Sub

End Class

 

[r3plica]

no one? It can't be that effecient, surely

 

[cjard]

It's not, and for sure it would break down in a heap if used in a multithreaded environment.. It also encourages button handlers full of SQL (ugh)

Microsoft put a good few man-years worth of development into facilities within Visual Studio that generate high quality, robust, secure, fast data access layers that are highly functional and easily accessible. Possibly the reason nobody has a comment on your class, is because they use Microsoft's provision instead (though granted some people aren't with the 20th century yet (as is evidenced regularly in the database forums).. )

If you want to generate a DAL generator to rival Microsoft's offering, you will be at it for a loooong time - probably the predominant reason why people don't