A Question an Class Design with ADO.NET

M

melegant

Well-known member
Joined
Feb 2, 2003
Messages
52
Location
NY
I am working on creating a bunch of classes to deal with a SQL Database..my question is what is the best way to design these in terms of using Variables and Properties? For Example..

Code: 
    Private conString As String = "Database=Contracts; data source=DATA\MASTER;Integrated Security=SSPI"
    Private SQLcn As New SqlClient.SqlConnection(conString)
    Private SQLda As SqlClient.SqlDataAdapter
    Private SQLdt As DataTable
    Private SQLds As DataSet
    Private SQLdr As SqlClient.SqlDataReader
    Private SQLdrw As DataRow
    Private myDataSet As DataSet

    Property SQLCmdText() As String
        Get
            Return mySQLCmd
        End Get
        Set(ByVal Value As String)
            mySQLCmd = Value
        End Set
    End Property

    Property MySQLdt() As DataTable
        Get
            Return SQLdt
        End Get
        Set(ByVal Value As DataTable)

        End Set
    End Property

methods etc...

I am wondering, am I on the road to bad practices?

Thanks
 
Without knowing exactly what youre trying to do, its hard to provide any meaningful feedback for you. From what I can tell though, Im wondering if youve taken a look at the SQLHelper code you can download from Microsoft? It wraps a lot of the housekeeping type stuff for you if thats what youre after -- though I dont use it.
 
Also, if youd like a simple class that mimics the design of your Database, simple create a new DataSet object through the .NET wizard. When the blank yellowy screen comes up, drag on a table from your favorite database. Youll get a table-like view in the DataSet designer (the file has an XSD extension).

Now, what you ALSO get is a handy-dandy class with all of the fields in the table (or proc, or whatever you used) exposed as properties. To see the auto-generated code, click on the "Show All Files" toolbar button in your Solution Explorer (the yellow toolbar button). Then expand the XSD file and you should see a .CS file (or .VB). In there is the code for a working class that mimics your database table. You can learn a lot from looking in there, as a starting point.

If you make any changes to the code be aware that it will get overwritten if you make any changes to the XSD. When changes occur to the XSD, the .CS file is rebuilt to make sure it contains properties that match the XSD.

Have fun!

-Nerseus
 
Thanks for the advice...let me show you my updated code.

What I am after is this. I am trying to build a base Data Class that I can inherit from for various data stuff in a program such as filling text boxes, filling grids, updating customer info etc..

Now, I am trying to write the code as tight as possible and as flexible as possible and most importantly as right as possible (:

I will first post the base class, then an example of an inherited class.

Code: 
Public Class iDataManip
    SQL
    Private conString As String = "Database=Contracts; data source=DATA;Integrated Security=SSPI"
    Private SQLdr As SqlClient.SqlDataReader
    Private SQLda As New SqlClient.SqlDataAdapter(m_SQLCmd, m_SQLcn)

    Private m_SQLCmd As String
    Private m_SQLdt As New DataTable()
    Private m_SQLcn As New SqlClient.SqlConnection(conString)

    Property SQLCmdText() As String
        Get
            Return m_SQLCmd
        End Get
        Set(ByVal Value As String)
            m_SQLCmd = Value
        End Set
    End Property

    Property SQLTable() As DataTable
        Get
            Return m_SQLdt
        End Get
        Set(ByVal Value As DataTable)
            m_SQLdt = Value
        End Set
    End Property

    ReadOnly Property SQLConnection() As SqlClient.SqlConnection
        Get
            Return m_SQLcn
        End Get
    End Property

End Class

dervided class next, for grid manipulation
Code: 
Public Class iGridManip
    Inherits iDataManip

    Public Sub FillGrid()
        Dim SQLAdapter As New SqlClient.SqlDataAdapter(SQLCmdText, SQLConnection)
        SQLConnection.Open()
        SQLAdapter.Fill(SQLTable)
        SQLConnection.Close()
        SQLAdapter = Nothing
    End Sub

    Public Sub RowLockAll(ByVal num As Integer)
        Do Until num = 0
            SQLTable.Columns(0).ReadOnly = True
            num = num - 1
        Loop
    End Sub

    Public Sub RowLock(ByVal num As Integer)
        SQLTable.Columns(num).ReadOnly = True
    End Sub


End Class

again, am I on the right track? thanks
 
It looks fine, but hard to say whats best from an outsiders point of view :)

If you are allowing anything to go through the properties SQLCmdText and SQLTable, you might as well declare them as protected and then your inherited class can see them, but outside classes cannot.

-ners
 
Back
Top