Conversion of Business Objects to Flat Data Table and Viceversa

[Shibu]

Hi,
I have a situation where I need to convert business objects to a flat table.
The reverse is also required. I am using c# and
Oracle ODP. I am looking for an easier method to do the below steps.

Steps I follows for populating Business Objects is as follows
(1) Get a list of records containing various tables joined together from DB
using a single PL/SQL
(This is a specific requirement, So cannot change the design)

Data will be redundent as shown below.

eg:- COLUMNS
ParentTable.Id, ParentTable.Name, ChildTable1.Id,
ChildTable1.Name, ChildTable2.Id, ChildTable2.Name

Sample data with respect to the above columns

1 "A" 1 "a" 1 "aa"
1 "A" 1 "a" 2 "bb"
1 "A" 2 "b" 1 "aa"
1 "A" 2 "b" 2 "bb"
1 "A" 2 "b" 3 "cc"
1 "A" 3 "c" 1 "bb"

(2) Iterate through each row and applying logic to separate data required to
build Buiness object
Eg., of business object :-

ParentClass
{
//contains collection of child 1

}

Child1_Class
{
//contains Collection of child 2
}

The reverse of this process is done to update/insert records from Buiness
objects.

My requirement is, to know whether there's any mechanism by which i can
convert
Business objects to Flat Table (DataTable) and Viceversa.

Looking for suggestions
Shibu

[Nicholas Paldino [.NET/C# MVP]]

Shibu,

You will have to provide the mapping yourself. This is the reason for
data layers. Your objects will have to expose a mechanism by which they can
say "this value should go in column x of table y".

You could use reflection, and name the fields internally to match the
columns, and the type to match the table, but I think that wouldn't be a
good idea.

I recommend using an attribute. You can create a TableAttribute, which
is applied to the class. Then, you can search through your types for the
class that has the table attribute with a table name that matches the table
part of the column. Once you have that, you can have a ColumnAttribute
class which will indicate which field in the class should be populated with
that value from the table.

Then, when storing the values back, you can use reflection just the same
to determine which columns in which tables are to be written to.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Shibu" <sh*****@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...

Hi,
I have a situation where I need to convert business objects to a flat
table.
The reverse is also required. I am using c# and
Oracle ODP. I am looking for an easier method to do the below steps.

Steps I follows for populating Business Objects is as follows
(1) Get a list of records containing various tables joined together from
DB
using a single PL/SQL
(This is a specific requirement, So cannot change the design)

Data will be redundent as shown below.

eg:- COLUMNS
ParentTable.Id, ParentTable.Name, ChildTable1.Id,
ChildTable1.Name, ChildTable2.Id, ChildTable2.Name

Sample data with respect to the above columns

1 "A" 1 "a" 1 "aa"
1 "A" 1 "a" 2 "bb"
1 "A" 2 "b" 1 "aa"
1 "A" 2 "b" 2 "bb"
1 "A" 2 "b" 3 "cc"
1 "A" 3 "c" 1 "bb"

(2) Iterate through each row and applying logic to separate data required
to
build Buiness object
Eg., of business object :-

ParentClass
{
//contains collection of child 1

}

Child1_Class
{
//contains Collection of child 2
}

The reverse of this process is done to update/insert records from Buiness
objects.

My requirement is, to know whether there's any mechanism by which i can
convert
Business objects to Flat Table (DataTable) and Viceversa.

Looking for suggestions
Shibu

[Shibu]

Hi Nicholas ,

Thanks for your reply.. As you were saying, using attribute mapping can make
the whole thing bit easy.
In addition to using attributes, I guess I am looking for a logic to
implement it using a single query containing multiple table joins.
Like looping through each record to fill child class collections (tree).

And while inserting/updating data I have to iterate through each child class
collection and making PL/SQL call using each row data.
(I am not sure about calling ORACLE PL/SQL with fields as parameters
instead of a set of records)

Any suggestions...
Shibu
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:uG**************@TK2MSFTNGP14.phx.gbl...

Shibu,

You will have to provide the mapping yourself. This is the reason for
data layers. Your objects will have to expose a mechanism by which they can say "this value should go in column x of table y".

You could use reflection, and name the fields internally to match the
columns, and the type to match the table, but I think that wouldn't be a
good idea.

I recommend using an attribute. You can create a TableAttribute, which is applied to the class. Then, you can search through your types for the
class that has the table attribute with a table name that matches the table part of the column. Once you have that, you can have a ColumnAttribute
class which will indicate which field in the class should be populated with that value from the table.

Then, when storing the values back, you can use reflection just the same to determine which columns in which tables are to be written to.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Shibu" <sh*****@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...

Hi,
I have a situation where I need to convert business objects to a flat
table.
The reverse is also required. I am using c# and
Oracle ODP. I am looking for an easier method to do the below steps.

Steps I follows for populating Business Objects is as follows
(1) Get a list of records containing various tables joined together from
DB
using a single PL/SQL
(This is a specific requirement, So cannot change the design)

Data will be redundent as shown below.

eg:- COLUMNS
ParentTable.Id, ParentTable.Name, ChildTable1.Id,
ChildTable1.Name, ChildTable2.Id, ChildTable2.Name

Sample data with respect to the above columns

1 "A" 1 "a" 1 "aa"
1 "A" 1 "a" 2 "bb"
1 "A" 2 "b" 1 "aa"
1 "A" 2 "b" 2 "bb"
1 "A" 2 "b" 3 "cc"
1 "A" 3 "c" 1 "bb"

(2) Iterate through each row and applying logic to separate data required to
build Buiness object
Eg., of business object :-

ParentClass
{
//contains collection of child 1

}

Child1_Class
{
//contains Collection of child 2
}

The reverse of this process is done to update/insert records from Buiness objects.

My requirement is, to know whether there's any mechanism by which i can
convert
Business objects to Flat Table (DataTable) and Viceversa.

Looking for suggestions
Shibu

[Nicholas Paldino [.NET/C# MVP]]

Shibu,

Writing the logic for creating all of those joins is going to be quite a
task. The problem is that for every child object in your heiarchy, you are
adding overhead at an exponential rate.

For that part, if I had to do it that way (and I most definitely would
not do it that way), I would check the relationships in the tables. For
each child table, I would query the database structure for the relationship
between the parent/child tables, and then construct the join statement based
on that information, while selecting all of the fields from all of the
tables.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Shibu" <sh*****@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP14.phx.gbl...

Hi Nicholas ,

Thanks for your reply.. As you were saying, using attribute mapping can
make
the whole thing bit easy.
In addition to using attributes, I guess I am looking for a logic to
implement it using a single query containing multiple table joins.
Like looping through each record to fill child class collections (tree).

And while inserting/updating data I have to iterate through each child
class
collection and making PL/SQL call using each row data.
(I am not sure about calling ORACLE PL/SQL with fields as parameters
instead of a set of records)

Any suggestions...
Shibu
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:uG**************@TK2MSFTNGP14.phx.gbl...

Shibu,

You will have to provide the mapping yourself. This is the reason
for
data layers. Your objects will have to expose a mechanism by which they

can

say "this value should go in column x of table y".

You could use reflection, and name the fields internally to match the
columns, and the type to match the table, but I think that wouldn't be a
good idea.

I recommend using an attribute. You can create a TableAttribute,

which

is applied to the class. Then, you can search through your types for the
class that has the table attribute with a table name that matches the

table

part of the column. Once you have that, you can have a ColumnAttribute
class which will indicate which field in the class should be populated

with

that value from the table.

Then, when storing the values back, you can use reflection just the

same

to determine which columns in which tables are to be written to.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Shibu" <sh*****@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...

> Hi,
> I have a situation where I need to convert business objects to a flat
> table.
> The reverse is also required. I am using c# and
> Oracle ODP. I am looking for an easier method to do the below steps.
>
> Steps I follows for populating Business Objects is as follows
> (1) Get a list of records containing various tables joined together
> from
> DB
> using a single PL/SQL
> (This is a specific requirement, So cannot change the design)
>
> Data will be redundent as shown below.
>
> eg:- COLUMNS
> ParentTable.Id, ParentTable.Name, ChildTable1.Id,
> ChildTable1.Name, ChildTable2.Id, ChildTable2.Name
>
> Sample data with respect to the above columns
>
> 1 "A" 1 "a" 1 "aa"
> 1 "A" 1 "a" 2 "bb"
> 1 "A" 2 "b" 1 "aa"
> 1 "A" 2 "b" 2 "bb"
> 1 "A" 2 "b" 3 "cc"
> 1 "A" 3 "c" 1 "bb"
>
> (2) Iterate through each row and applying logic to separate data required > to
> build Buiness object
> Eg., of business object :-
>
> ParentClass
> {
> //contains collection of child 1
>
> }
>
> Child1_Class
> {
> //contains Collection of child 2
> }
>
>
>
> The reverse of this process is done to update/insert records from Buiness > objects.
>
> My requirement is, to know whether there's any mechanism by which i can
> convert
> Business objects to Flat Table (DataTable) and Viceversa.
>
> Looking for suggestions
> Shibu
>
>

[Shibu]

Nicholas,

I have PL/SQL query which returns all the fields. But the problem I face is,
it is tedious to construct the
logic to convert the records back to business object heirarchy. Extracting
data from business objects
to give to PL/SQL is also tedious and have to use lot of logical checkings.

So I think, because of bad design, I have to go through all logic
implementation for each business objects
and no other way around right? :(

Any way thanks for you suggestions...
Shibu

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:Oa**************@TK2MSFTNGP09.phx.gbl...

Shibu,

Writing the logic for creating all of those joins is going to be quite a task. The problem is that for every child object in your heiarchy, you are adding overhead at an exponential rate.

For that part, if I had to do it that way (and I most definitely would
not do it that way), I would check the relationships in the tables. For
each child table, I would query the database structure for the relationship between the parent/child tables, and then construct the join statement based on that information, while selecting all of the fields from all of the
tables.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Shibu" <sh*****@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP14.phx.gbl...

Hi Nicholas ,

Thanks for your reply.. As you were saying, using attribute mapping can
make
the whole thing bit easy.
In addition to using attributes, I guess I am looking for a logic to
implement it using a single query containing multiple table joins.
Like looping through each record to fill child class collections (tree).

And while inserting/updating data I have to iterate through each child
class
collection and making PL/SQL call using each row data.
(I am not sure about calling ORACLE PL/SQL with fields as parameters
instead of a set of records)

Any suggestions...
Shibu
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:uG**************@TK2MSFTNGP14.phx.gbl...

Shibu,

You will have to provide the mapping yourself. This is the reason
for
data layers. Your objects will have to expose a mechanism by which they

can

say "this value should go in column x of table y".

You could use reflection, and name the fields internally to match

the columns, and the type to match the table, but I think that wouldn't be a good idea.

I recommend using an attribute. You can create a TableAttribute,

which

is applied to the class. Then, you can search through your types for the class that has the table attribute with a table name that matches the

table

part of the column. Once you have that, you can have a ColumnAttribute
class which will indicate which field in the class should be populated

with

that value from the table.

Then, when storing the values back, you can use reflection just the

same

to determine which columns in which tables are to be written to.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Shibu" <sh*****@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a situation where I need to convert business objects to a flat
> table.
> The reverse is also required. I am using c# and
> Oracle ODP. I am looking for an easier method to do the below steps.
>
> Steps I follows for populating Business Objects is as follows
> (1) Get a list of records containing various tables joined together
> from
> DB
> using a single PL/SQL
> (This is a specific requirement, So cannot change the design)
>
> Data will be redundent as shown below.
>
> eg:- COLUMNS
> ParentTable.Id, ParentTable.Name, ChildTable1.Id,
> ChildTable1.Name, ChildTable2.Id, ChildTable2.Name
>
> Sample data with respect to the above columns
>
> 1 "A" 1 "a" 1 "aa"
> 1 "A" 1 "a" 2 "bb"
> 1 "A" 2 "b" 1 "aa"
> 1 "A" 2 "b" 2 "bb"
> 1 "A" 2 "b" 3 "cc"
> 1 "A" 3 "c" 1 "bb"
>
> (2) Iterate through each row and applying logic to separate data

required

> to
> build Buiness object
> Eg., of business object :-
>
> ParentClass
> {
> //contains collection of child 1
>
> }
>
> Child1_Class
> {
> //contains Collection of child 2
> }
>
>
>
> The reverse of this process is done to update/insert records from

Buiness

> objects.
>
> My requirement is, to know whether there's any mechanism by which i can > convert
> Business objects to Flat Table (DataTable) and Viceversa.
>
> Looking for suggestions
> Shibu
>
>

[Ignacio Machin \( .NET/ C# MVP \)]

Hi ,
There have been several threads here regarding this, and some poster have
send links for utilities that perform/help this conversion, take a look at
the archives and you will find them.

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Shibu" <sh*****@yahoo.com> wrote in message
news:Ow**************@TK2MSFTNGP12.phx.gbl...

Nicholas,

I have PL/SQL query which returns all the fields. But the problem I face
is,
it is tedious to construct the
logic to convert the records back to business object heirarchy. Extracting
data from business objects
to give to PL/SQL is also tedious and have to use lot of logical
checkings.

So I think, because of bad design, I have to go through all logic
implementation for each business objects
and no other way around right? :(

Any way thanks for you suggestions...
Shibu

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:Oa**************@TK2MSFTNGP09.phx.gbl...

Shibu,

Writing the logic for creating all of those joins is going to be
quite

a

task. The problem is that for every child object in your heiarchy, you

are

adding overhead at an exponential rate.

For that part, if I had to do it that way (and I most definitely
would
not do it that way), I would check the relationships in the tables. For
each child table, I would query the database structure for the

relationship

between the parent/child tables, and then construct the join statement

based

on that information, while selecting all of the fields from all of the
tables.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Shibu" <sh*****@yahoo.com> wrote in message
news:Oh**************@TK2MSFTNGP14.phx.gbl...

> Hi Nicholas ,
>
> Thanks for your reply.. As you were saying, using attribute mapping can
> make
> the whole thing bit easy.
> In addition to using attributes, I guess I am looking for a logic to
> implement it using a single query containing multiple table joins.
> Like looping through each record to fill child class collections
> (tree).
>
> And while inserting/updating data I have to iterate through each child
> class
> collection and making PL/SQL call using each row data.
> (I am not sure about calling ORACLE PL/SQL with fields as parameters
> instead of a set of records)
>
> Any suggestions...
> Shibu
>
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote
> in
> message news:uG**************@TK2MSFTNGP14.phx.gbl...
>> Shibu,
>>
>> You will have to provide the mapping yourself. This is the reason
>> for
>> data layers. Your objects will have to expose a mechanism by which they > can
>> say "this value should go in column x of table y".
>>
>> You could use reflection, and name the fields internally to match the >> columns, and the type to match the table, but I think that wouldn't be a >> good idea.
>>
>> I recommend using an attribute. You can create a TableAttribute,
> which
>> is applied to the class. Then, you can search through your types for the >> class that has the table attribute with a table name that matches the
> table
>> part of the column. Once you have that, you can have a
>> ColumnAttribute
>> class which will indicate which field in the class should be populated
> with
>> that value from the table.
>>
>> Then, when storing the values back, you can use reflection just
>> the
> same
>> to determine which columns in which tables are to be written to.
>>
>> Hope this helps.
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>>
>> "Shibu" <sh*****@yahoo.com> wrote in message
>> news:%2****************@TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I have a situation where I need to convert business objects to a
>> > flat
>> > table.
>> > The reverse is also required. I am using c# and
>> > Oracle ODP. I am looking for an easier method to do the below steps.
>> >
>> > Steps I follows for populating Business Objects is as follows
>> > (1) Get a list of records containing various tables joined together
>> > from
>> > DB
>> > using a single PL/SQL
>> > (This is a specific requirement, So cannot change the design)
>> >
>> > Data will be redundent as shown below.
>> >
>> > eg:- COLUMNS
>> > ParentTable.Id, ParentTable.Name, ChildTable1.Id,
>> > ChildTable1.Name, ChildTable2.Id, ChildTable2.Name
>> >
>> > Sample data with respect to the above columns
>> >
>> > 1 "A" 1 "a" 1 "aa"
>> > 1 "A" 1 "a" 2 "bb"
>> > 1 "A" 2 "b" 1 "aa"
>> > 1 "A" 2 "b" 2 "bb"
>> > 1 "A" 2 "b" 3 "cc"
>> > 1 "A" 3 "c" 1 "bb"
>> >
>> > (2) Iterate through each row and applying logic to separate data
> required
>> > to
>> > build Buiness object
>> > Eg., of business object :-
>> >
>> > ParentClass
>> > {
>> > //contains collection of child 1
>> >
>> > }
>> >
>> > Child1_Class
>> > {
>> > //contains Collection of child 2
>> > }
>> >
>> >
>> >
>> > The reverse of this process is done to update/insert records from
> Buiness
>> > objects.
>> >
>> > My requirement is, to know whether there's any mechanism by which i can >> > convert
>> > Business objects to Flat Table (DataTable) and Viceversa.
>> >
>> > Looking for suggestions
>> > Shibu
>> >
>> >
>>
>>
>
>