A Question an Class Design with ADO.NET

[melegant]

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..

 

   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

[quwiltw]

Without knowing exactly what you're trying to do, it's hard to provide any meaningful feedback for you. From what I can tell though, I'm wondering if you've taken a look at the SQLHelper code you can download from Microsoft? It wraps a lot of the housekeeping type stuff for you if that's what you're after -- though I don't use it.

[Nerseus]

Also, if you'd 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. You'll 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

[melegant]

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.

 

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

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

[Nerseus]

It looks fine, but hard to say what's best from an outsider's 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