Class question

[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?

 

[tomb]

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

 

[Mythran]

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

 

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

 

[Mythran]

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

 

[Phill W.]

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.

 

[mrmagoo]

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