Typed Datasets

[Robert Smith]

Hi,
I have a problem whereby I have one form dealing with one of two
typed datasets with almost all the same column names except two, depenind on
which option the user selects.

at the moment I have if statements like the following that set the value of
form fields etc, depeninding on the selected value

if (selectedtab = "Agency")
{
txtfield.Name = AgencyDataset.AgencyTable[currentindex].AgencyName ;
}else if (selectedbat = "Advertiser") then
{
txtfield.Name =
AdvertiserDataset.AdvertiserTable[currentindex].AdvertiserName;
}
do you know of a smarter way of doing this so I don't have to continually do
if statements to determine which dataset to use and also to avoid getting
type casting errors.
Thanx in advance
Robert

 

[Roger Frost]

Hi,
I have a problem whereby I have one form dealing with one of two
typed datasets with almost all the same column names except two, depenind
on
which option the user selects.

at the moment I have if statements like the following that set the value
of
form fields etc, depeninding on the selected value

if (selectedtab = "Agency")
{
txtfield.Name = AgencyDataset.AgencyTable[currentindex].AgencyName ;
}else if (selectedbat = "Advertiser") then
{
txtfield.Name =
AdvertiserDataset.AdvertiserTable[currentindex].AdvertiserName;
}
do you know of a smarter way of doing this so I don't have to continually
do
if statements to determine which dataset to use and also to avoid getting
type casting errors.
Thanx in advance
Robert

I can think of one quick solution that might help...

Assuming these conditions are met:


1) It's a typed Dataset (like you said) so you are 100% sure of the database
structure...

2) The _only_ (!!!) difference between AgencyTable and AdvertiserTable is
the label of the ...Name column.

3) You are only doing simple data updating or just populating the UI with
the Dataset object(s) in question.


Instead of using AdvertiserName and AgencyName, just rename the column in
both tables to "Name". From table view, its obvious what the Name is
identifying and from your UI nobody cares at runtime, just comment the
reasons for what your doing and everyone should be happy.


Where are your casting errors happening?

 

[Robert Smith]

Thanks Roger,
Will that not cause issues when updating the dataset when
the name of the dataset column does not match the column in the underlying
database name.

I tried using select AgencyName as Name etc, and then updating the dataset
but haven't changed the actual dataset column names;

Hi,
I have a problem whereby I have one form dealing with one of two
typed datasets with almost all the same column names except two, depenind
on
which option the user selects.

at the moment I have if statements like the following that set the value
of
form fields etc, depeninding on the selected value

if (selectedtab = "Agency")
{
txtfield.Name = AgencyDataset.AgencyTable[currentindex].AgencyName ;
}else if (selectedbat = "Advertiser") then
{
txtfield.Name =
AdvertiserDataset.AdvertiserTable[currentindex].AdvertiserName;
}
do you know of a smarter way of doing this so I don't have to continually
do
if statements to determine which dataset to use and also to avoid getting
type casting errors.
Thanx in advance
Robert

I can think of one quick solution that might help...

Assuming these conditions are met:


1) It's a typed Dataset (like you said) so you are 100% sure of the database
structure...

2) The _only_ (!!!) difference between AgencyTable and AdvertiserTable is
the label of the ...Name column.

3) You are only doing simple data updating or just populating the UI with
the Dataset object(s) in question.


Instead of using AdvertiserName and AgencyName, just rename the column in
both tables to "Name". From table view, its obvious what the Name is
identifying and from your UI nobody cares at runtime, just comment the
reasons for what your doing and everyone should be happy.


Where are your casting errors happening?

 

[Roger Frost]

Thanks Roger,
Will that not cause issues when updating the dataset when
the name of the dataset column does not match the column in the underlying
database name.

I tried using select AgencyName as Name etc, and then updating the dataset
but haven't changed the actual dataset column names;

I was just thinking that if your tables are already 1 column name from being
of identical schema as it is, and they hold the same basic information: a
name, you may as well rename the columns to simplify data access.

Preferably (in this case), I would combine both the tables into one standard
table (like ContactInfo for example) and add separate tables for Agencies
and Advertisers that have a foreign key relationship with ContactInfo. This
makes much more sense to me, but I have limited knowledge about your system
to go on.

On the other hand, if this database is already in use these changes will
certainly break other client applications.

The good news is you can get around this without breaking existing tools by
leaving them as they are and adding a identical column alias to each table's
....Name column.

You just need to think about what you're trying to do now and who (if
anyone) is already relying on this database. There are many ways to
simplify your Dataset dilemma, the route you take will be determined by the
issues listed above, and many others.

Just think it through (and _always_ backup before you go wild with schema
changes )

 

[Robert Smith]

Thanks Roger,
Actually the Identifer key also have different names. One is
AgencyId and the other is AdvertiserId. Does your theory still hold true
knowing this information.

 

[Roger Frost]

Thanks Roger,
Actually the Identifer key also have different names. One is
AgencyId and the other is AdvertiserId. Does your theory still hold true
knowing this information.

As far as creating column aliases, no problem there. The rest depends on if
the database is already serving critical applications or not.


If this project is still in development or beta phase consider the
following:

Move the duplicated columns from the Agency and Advertiser tables to a new
table, from my understanding, this will be everything except the current
primary keys. I'll call the new table ContactInfo

ContactInfo will also have a primary key: ContactInfoID. Create a foreign
key relationship between ContactInfo.ContactInfoID and Agency.ContactInfoID
as well as between ContactInfo.ContactInfoID and Advertiser.ContactInfoID

Now you can pull the data from the ContactInfo table and it will be in a
schema standard to your database for both Agency and Advertiser records.

As a bonus (and really the major factor in relational database design); if
it is possible for a company to be an Agency and an Advertiser at the same
time, you have now reduced the data storage footprint.

Relational databases are extremely good at what they do, (dare I say) the
most efficient of all computer processes short of the BIOS. With a handful
of keywords, a SQL server can do amazing things very quickly with vast
amounts of data if the design is well planned. When the design is right,
almost everything falls into place.

You might also try one of the database groups, since we have completely left
the scope of this one long ago. That was my fault.

Hope this helps.