Class question

28 Mar 2006 5:24 PM

mrmagoo

I have a class that accepts String and Long datatypes.

Class Menu

    Private FirstTier as String
    Private SecondTier as Long

    Sub New(ByVal FirstTier As String, _
                    ByVal SecondTier As Long)
....

Ok...great. this works fine but the problem arises as a result of filling
this from a datareader that reads from a stored procedure in a SQL database.

The database allows nulls for these columns, but I cannot add a null to the
class that is expecting a String or Long, right?

For my immediate problem, I can use IIf(IsDBNull(dr.Item("FirstTier"...
approach, and add a zero-length string if the DB returns a null, or a 0 for
the numeric datatype.

I poked around a saw a couple approaches to this:

   - Don't allow nulls in the database (I don't have control over this, and
the stored procedures are already written..so I can't change them).
   - I can do the above approach.
   - I suppose I can change the String and Long to object datatypes, and
fill them with Nulls, right?

If I do the above approach, I am creating a business rule independent to the
database setup. Let's say I add a zero if there's a NULL for the long
datatype. That would work for my immediate problem, because there are no
zeroes in the database, but downstream, let's say the database does begin
using zeroes. My program would then not work.

My question is..how should I handle this? I don't see a "clean" way out
without making rules that might later contradict changes made to the
database, forcing the program to be rewritten. Ideally, I would like this
class to work similarly to the way the database works. A numeric datatype
constraint is applied to a column, but it can also store nothing. That
doesn't seem to transfer well to my numeric-expecting datatype in my class.

How do developers work around this issue?

28 Mar 2006 5:51 PM

tomb

mrmagoo wrote:

Show quoteHide quote

>I have a class that accepts String and Long datatypes.
>
>Class Menu
>
>    Private FirstTier as String
>    Private SecondTier as Long
>
>    Sub New(ByVal FirstTier As String, _
>                    ByVal SecondTier As Long)
>...
>
>My question is..how should I handle this? I don't see a "clean" way out
>without making rules that might later contradict changes made to the
>database, forcing the program to be rewritten. Ideally, I would like this
>class to work similarly to the way the database works. A numeric datatype
>constraint is applied to a column, but it can also store nothing. That
>doesn't seem to transfer well to my numeric-expecting datatype in my class.
>
>How do developers work around this issue?
>
>
>
Try this:

Class Menu

    Private FirstTier as String
    Private SecondTier as Long

    Sub New(optional ByVal FirstTier As String="", _
                    optional ByVal SecondTier As object = nothing)

Then test for empty string and no object
Your database allows nulls, so now you do also.

T

28 Mar 2006 6:00 PM

Mythran

Show quote Hide quote

"tomb" <t***@technetcenter.com> wrote in message
news:LGeWf.6$ki.0@bignews1.bellsouth.net...
> mrmagoo wrote:
>
>>I have a class that accepts String and Long datatypes.
>>
>>Class Menu
>>
>>    Private FirstTier as String
>>    Private SecondTier as Long
>>
>>    Sub New(ByVal FirstTier As String, _
>>                    ByVal SecondTier As Long)
>>...
>>
>>My question is..how should I handle this? I don't see a "clean" way out
>>without making rules that might later contradict changes made to the
>>database, forcing the program to be rewritten. Ideally, I would like this
>>class to work similarly to the way the database works. A numeric datatype
>>constraint is applied to a column, but it can also store nothing. That
>>doesn't seem to transfer well to my numeric-expecting datatype in my
>>class.
>>
>>How do developers work around this issue?
>>
>>
> Try this:
>
> Class Menu
>
>    Private FirstTier as String
>    Private SecondTier as Long
>
>    Sub New(optional ByVal FirstTier As String="", _
>                    optional ByVal SecondTier As object = nothing)
>
> Then test for empty string and no object
> Your database allows nulls, so now you do also.
>
> T

And, by default, SecondTier (the private member) is set to 0 when not
initialized in the constructor or the declartion of the member. So, he's
back at square one.

What we do is use typed datasets instead of creating our own classes that we
fill (in actuality, that is all a typed class is really). Using typed
datasets, if a field value is set to null and we try to access that field
(property), we get an exception. There are methods on the datasets that
allow us to text a typed dataset field to see if it's null
(Is<typedfieldname>Null) that we check to see if it's null. When using this
route, you know when the field is null or not. But, the headache of this is
that you always have to check for nulls if the field allows nulls.

HTH,
Mythran

28 Mar 2006 6:12 PM

mrmagoo

I'm new at this...and I am missing your point on how to implement.

Can you explain this a bit more and/or give me an example?

Bottom line, are you saying I can't use a class? If so, I really want to use
a class so I can fill the listbox with the object. That way I can retrieve
any one of a number of related elements to the selected item. I am
approaching it this way because in VBA (which I have experience in) I would
fill a listbox with many hidden columns and retrieve the contents that way.
I like how listboxes in VB.Net can accept objects for this reason.

Thank you.

Show quoteHide quote

"Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message
news:eGxF9FpUGHA.776@TK2MSFTNGP09.phx.gbl...
>
> "tomb" <t***@technetcenter.com> wrote in message
> news:LGeWf.6$ki.0@bignews1.bellsouth.net...
> > mrmagoo wrote:
> >
> >>I have a class that accepts String and Long datatypes.
> >>
> >>Class Menu
> >>
> >>    Private FirstTier as String
> >>    Private SecondTier as Long
> >>
> >>    Sub New(ByVal FirstTier As String, _
> >>                    ByVal SecondTier As Long)
> >>...
> >>
> >>My question is..how should I handle this? I don't see a "clean" way out
> >>without making rules that might later contradict changes made to the
> >>database, forcing the program to be rewritten. Ideally, I would like
this
> >>class to work similarly to the way the database works. A numeric
datatype
> >>constraint is applied to a column, but it can also store nothing. That
> >>doesn't seem to transfer well to my numeric-expecting datatype in my
> >>class.
> >>
> >>How do developers work around this issue?
> >>
> >>
> > Try this:
> >
> > Class Menu
> >
> >    Private FirstTier as String
> >    Private SecondTier as Long
> >
> >    Sub New(optional ByVal FirstTier As String="", _
> >                    optional ByVal SecondTier As object = nothing)
> >
> > Then test for empty string and no object
> > Your database allows nulls, so now you do also.
> >
> > T
>
> And, by default, SecondTier (the private member) is set to 0 when not
> initialized in the constructor or the declartion of the member. So, he's
> back at square one.
>
> What we do is use typed datasets instead of creating our own classes that
we
> fill (in actuality, that is all a typed class is really). Using typed
> datasets, if a field value is set to null and we try to access that field
> (property), we get an exception. There are methods on the datasets that
> allow us to text a typed dataset field to see if it's null
> (Is<typedfieldname>Null) that we check to see if it's null. When using
this
> route, you know when the field is null or not. But, the headache of this
is
> that you always have to check for nulls if the field allows nulls.
>
> HTH,
> Mythran
>

28 Mar 2006 6:52 PM

Mythran

Show quote Hide quote

"mrmagoo" <-> wrote in message news:emD8pMpUGHA.4660@tk2msftngp13.phx.gbl...
> I'm new at this...and I am missing your point on how to implement.
>
> Can you explain this a bit more and/or give me an example?
>
> Bottom line, are you saying I can't use a class? If so, I really want to
> use
> a class so I can fill the listbox with the object. That way I can retrieve
> any one of a number of related elements to the selected item. I am
> approaching it this way because in VBA (which I have experience in) I
> would
> fill a listbox with many hidden columns and retrieve the contents that
> way.
> I like how listboxes in VB.Net can accept objects for this reason.
>
> Thank you.

Yes. You'll probably like using typed datasets for this...here is what you
can do:

To create a typed dataset:
Add New Item->DataSet (give the dataset a name, typically the entity name
(table name) of the table...in this example, we will use tblCustomer table,
so the dataset name would be Customer).
In Server Explorer window (View->Server Explorer), create a connection that
points to the database you are using.
Expand the database, then Tables, and drag and drop the table to the dataset
designer.
Save the dataset. Voila, a new typed dataset has been created.

Easiest just to fill the datatable with the data (class created under the
dataset) and use the datatable to bind to dropdown listboxes.

Normally, I use stored procedures and the Microsoft Patterns and Practices
Enterprise Library assemblies to do this, but for simplicity in this case,
I'll just use SQL and a modified version of the Northwind database. Also,
the connection string would go into a config file or somewhere other than
code (so it's configurable on a per-machine basis):

Private Sub PopulateCustomers()
    Const CONN_STR As String = _
        "server=testing;" & _
        "database=dbNorthwind;" & _
        "trusted_connection=true;"
    Dim conn As SqlConnection = New SqlConnection(CONN_STR)
    Dim adap As SqlDataAdapter = New SqlDataAdapter( _
        "select * from tblCustomer", _
        conn _
    )

    ' Create the datatable to fill
    Dim dt As Customer.tblCustomerDataTable
    dt = (New Customer()).tblCustomer

    ' Open the connection to the database.
    conn.Open()

    Try
        ' Fill the datatable.
        adap.Fill(dt)
    Finally
        ' Cleanup
        conn.Close()
    End Try

    ' Bind the data table to the listbox.
    lstCustomers.DataTextField = "CompanyName"
    lstCustomers.DataValueField = "CustomerId"
    lstCustomers.DataSource = dt
    lstCustomers.DataBind()
End Sub

HTH! :)

Mythran

29 Mar 2006 11:25 AM

Phill W.

"mrmagoo" <-> wrote in message
news:%23Uh5kxoUGHA.1976@TK2MSFTNGP09.phx.gbl...
>I have a class that accepts String and Long datatypes.
>
> Sub New(ByVal FirstTier As String, _
> ByVal SecondTier As Long)
>
> Ok...great. this works fine but the problem arises as a result of filling
> this from a datareader that reads from a stored procedure in a SQL
> database.

> The database allows nulls for these columns, but I cannot add a null
> to the class that is expecting a String or Long, right?

Sounds like you need another constructor that takes the datareader
as its argument:

    Sub New(ByVal dr As ... )
        If source.Item( "FirstTier" ) = DBNull.Value Then
            FirstTier = dr.Item( "FirstTier" ).ToString()
        Else
            FirstTier = Nothing ' say
        End If
        . . .

Regards,
    Phill W.

29 Mar 2006 8:25 PM

mrmagoo

Thanks everyone for your input...you have all been extremely helpful!

Show quoteHide quote

"mrmagoo" <-> wrote in message
news:%23Uh5kxoUGHA.1976@TK2MSFTNGP09.phx.gbl...
> I have a class that accepts String and Long datatypes.
>
> Class Menu
>
>     Private FirstTier as String
>     Private SecondTier as Long
>
>     Sub New(ByVal FirstTier As String, _
>                     ByVal SecondTier As Long)
> ...
>
> Ok...great. this works fine but the problem arises as a result of filling
> this from a datareader that reads from a stored procedure in a SQL
database.
>
> The database allows nulls for these columns, but I cannot add a null to
the
> class that is expecting a String or Long, right?
>
> For my immediate problem, I can use IIf(IsDBNull(dr.Item("FirstTier"...
> approach, and add a zero-length string if the DB returns a null, or a 0
for
> the numeric datatype.
>
> I poked around a saw a couple approaches to this:
>
>    - Don't allow nulls in the database (I don't have control over this,
and
> the stored procedures are already written..so I can't change them).
>    - I can do the above approach.
>    - I suppose I can change the String and Long to object datatypes, and
> fill them with Nulls, right?
>
> If I do the above approach, I am creating a business rule independent to
the
> database setup. Let's say I add a zero if there's a NULL for the long
> datatype. That would work for my immediate problem, because there are no
> zeroes in the database, but downstream, let's say the database does begin
> using zeroes. My program would then not work.
>
> My question is..how should I handle this? I don't see a "clean" way out
> without making rules that might later contradict changes made to the
> database, forcing the program to be rewritten. Ideally, I would like this
> class to work similarly to the way the database works. A numeric datatype
> constraint is applied to a column, but it can also store nothing. That
> doesn't seem to transfer well to my numeric-expecting datatype in my
class.
>
> How do developers work around this issue?
>
>