We help IT Professionals succeed at work.

Creating classes in ASP.net that reference SQL server tables and return values

dearness
dearness asked
on
Medium Priority
303 Views
Last Modified: 2012-05-12
I am fairly new to ASP.net and want some expert help on best practice code design; I am designing in Visual Studio in express.  I am giving an example of a part of my system design and would appreciate some help on a few design concepts.  I have two power metering database tables set up, tblMETER which holds meter information and tblPOWER which holds 15 minute power meter readings. In my ASP.net program I want to use classes and objects to reference the many power meters.
This is what I think I should do, create a class called PowerMeter, set properties for NUMBER, DESCRIPTION, and DIVIDER. Now when I create an instance of the class for example PM06 (power meter 06), Firstly what is the best way to assign the property values to the new instance, Like this?
DIM PM06 as new PowerMeter
PM06.Number = “06”
PM06.Description = read the database referencing meter number 06
PM06.Divider = read the database referencing meter number 06
Secondly I want to display the total P_KW readings for the meter on a web page.
Do I create another property for the class and assign it the value such as
PM06.Reading = return calculated reading or is there some way of using a method within the class?
Providing some good points on getting a working system using best practices.
 Database Tables
Comment
Watch Question

GlobaLevelProgrammer
Commented:



this link will help you create classes:
http://msdn.microsoft.com/en-us/library/ms973814.aspx


DIM PM06 as new PowerMeter
PM06.Number = “06”
PM06.Description = read the database referencing meter number 06
PM06.Divider = read the database referencing meter number 06

>>>>yes


display values to a web page....


add a label to your web form via design mode>drop a label on from toolbox on left had tab....

go back to source and enter this into the <script> tags

public sub page_load (object as sender, eventargs as e)

label1.text = PM06.Number
end sub

Author

Commented:
GlobalLevel,
Thanks for the reply. I have been doing some reading and come up with this. Use a constructor routine and pass the meter number on creation of the meter. Use that meter number to get the description and divider with a function inside the class code from the database.
I this something that would normally be done?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Create the new power meter with its number
        Dim PM06 As New PowerMeter("06")
    End Sub

Imports System.Data.SqlClient
Public Class PowerMeter
    Private mNumber As String
    Private mDescription As String
    Private mDivider As Integer
    Private mDbConnection

    Public Sub New(ByVal Value As String)
        mNumber = Value

    End Sub

    ReadOnly Property number() As String
        Get
            Return mNumber
        End Get

    End Property
    Property Description() As String
        Get
            Return mDescription
        End Get
        Set(ByVal value As String)
            mDescription = value
        End Set
    End Property

    Property Divider() As String
        Get
            Return mDivider
        End Get
        Set(ByVal value As String)
            mDivider = value
        End Set
    End Property

End Class

Open in new window

GlobaLevelProgrammer

Commented:
looks good...r u using inline code or code behind?

for inline:

1)Page directive....
2) <@ Import xxxx

3) <scriptxxx>
methods

</script>

Commented:
Its a tricky question. It really depends on what level of abstraction you want to use and what type of objects you would return from that abstraction. Or maybe you're more interested in proper object creation than abstracting layers of communcication.

Anyway here's how I might go about this:
        Dim pm1 = New PowerMeter()
        Dim pm2 = New PowerMeter("2")
<see code>

Here's a code model you might be interrested in:
http://msdn.microsoft.com/en-us/library/aa581769.aspx

Its a pretty common sample from msdn. Instead of creating specific objects it prefers to abstract full data objects based off the db schema.
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data

Public Class PowerMeter

    Private _number As String
    Private _desc As String
    Private _divider As String
    Private _Readings As New List(Of PowerReadings)

    Property Number() As String
        Get
            Return _number
        End Get
        Set(ByVal value As String)
            _number = value
        End Set
    End Property

    Property Description() As String
        Get
            Return _desc
        End Get
        Set(ByVal value As String)
            _desc = value
        End Set
    End Property

    Property Divider() As String
        Get
            Return _divider
        End Get
        Set(ByVal value As String)
            _divider = value
        End Set
    End Property

    ReadOnly Property Readings() As List(Of PowerReadings)
        Get
            Return _Readings
        End Get
    End Property

    'Default Constructor
    Public Sub New()

    End Sub

    ''' <summary>
    ''' Gets PowerMeter by number Id
    ''' </summary>
    ''' <param name="num"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal num As String)
        GetMeterById(num)
    End Sub


    Public Function GetMeterById(ByVal num As String) As PowerMeter

        For Each dRow As DataRow In PretendCallToDataAbstractionLayer(num).Rows
            Me.Number = dRow("Number")
            Me.Description = dRow("Description")
            Me.Divider = dRow("Divider")
            Me._Readings.Add(New PowerReadings(dRow("KDate"), dRow("KW")))
        Next

        Return Me
    End Function


    Private Function PretendCallToDataAbstractionLayer(ByVal num As String) As DataTable
        Dim dTable As New DataTable("Meter")
        dTable.Columns.Add("Number")
        dTable.Columns.Add("Description")
        dTable.Columns.Add("Divider")
        dTable.Columns.Add("KDate", GetType(Date))
        dTable.Columns.Add("KW")
        dTable.Rows.Add("1", "SomeDesc", "SomeDivider", Date.Now, "150")
        dTable.Rows.Add("1", "SomeDesc2", "SomeDivider2", Date.Now.AddDays(1), "200")
        Return dTable
    End Function

    Class PowerReadings
        Private _readingDate As Date
        Private _kw As String

        Property ReadingDate() As Date
            Get
                Return _readingDate
            End Get
            Set(ByVal value As Date)
                _readingDate = value
            End Set
        End Property

        Property KW() As String
            Get
                Return _kw
            End Get
            Set(ByVal value As String)
                _kw = value
            End Set
        End Property

        Public Sub New()

        End Sub

        Public Sub New(ByVal pDate As Date, ByVal p As String)
            Me.ReadingDate = pDate
            Me.KW = p
        End Sub
    End Class

End Class

Open in new window

Commented:
Addendum:
PowerMeterObj.jpg

Author

Commented:
ddayx10,
I am going over you example.
Thanks for your input., Actually I was looking at posting a seperate question on the correct use of datalink layers. At this stage it is the use of objects I want to get right. Its the use of methods I am struggling with at the moment, when to use and how to use. Ideally I want all objects in the ASP.net project created as much as possible from the database tables.

Author

Commented:
ddayx10,
Its going to take a while to get my head around it but I am sure the principle is there for what I am after. I am having trouible getting it to run though, just give me while.

Commented:
Not a big deal but I made a mistake of sorts in the PowerReadings class. I would change that name to just PowerReading if it was me. Then when I looped through the list it would make more sense(its a small thing but it bugs me).

Ex:

for each p as PowerReadings in PowerMeter.PowerReadings

VS.

for each p as PowerReading in PowerMeter.PowerReadings

The latter makes contextual sense.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.