Database Business Layer and Database Sync Design

[morleyc]

Hi, until recently i was quite happy to add data sources from mssql
database in visual studio and drag the datasets directly onto the form
this creating a directly editable form which worked well.

However i have recently started a project which will require
synchronization to a remote database. Also the underlying database
provider may change at a later date. From what i have read it seems
that a layered approach is necessary, or at least recommended, where i
present an object to the form to be bound (i guess this is called the
business object in technical terms). The business object would then
communicate with a data layer which would then write to the database.

How easy is it to bind business object fields to the form? I don't
want to be hampered too much productivity wise messing with additional
coding. Will the business object controller have to return a list of
business objects? i.e. the function call in the controller layer

BusinessLayer.GetAllCustomerOrders(uint customerID), would return a
BindingList of type CustomerOrders?

Are there any standard interfaces the business object layer would use
to talk to the data layer?

Also, if i take a hit on portability to other platforms which is
unlikely at this stage, is it recommended for the business layer to
return DataSets directly? Although encapsulating a DataSet within the
business layer and presenting it as an object with fields is nice and
abstract, i can see a big time penalty. I would be prepared to return
DataSets if it would provide a happy compromise between segregation
and speed of development.

Finally, i need to synchronize the data in one database to another
database. Where would this synchronization take place? In the business
layer when a delete or addition is performed, i could (as i would be
in my Add or Delete function) perform two data access modification
calls, one to the local database and one to the remote database.

Another snag is that the databases cant be an exact synchronization as
the remote database would contain records for many users (many clients
will be adding to this hence the field IDs will not match between the
local and remote databases), would it be sensible to have a RemoteID
field in my local database row for each record? I'm thinking if that
field is empty the field hasn't been synchronized yet (the remote
database AddRecord function could return an ID which would then be
stored in the RemoteID field in the local database), when i delete a
record in the local database i will be able to delete the record in
the remote database by using the RemoteID field. Or would i base a
primary key for the table on the UserID and the local record ID? That
would indeed make a unique key but is it possible to define a primary
key with the composite of two fields? I hope that isn't too confusing
and illustrates my thoughts on howto solve this multiuser to singe
user database sync.

Thanks in advance,

Chris

[Dave Shooter]

As a starting point you might what to take a look at Rockford Lhotka's CSLA
framework at:

http://www.lhotka.net/
<mo*****@gmail.comwrote in message
news:11*********************@y80g2000hsf.googlegro ups.com...

Hi, until recently i was quite happy to add data sources from mssql
database in visual studio and drag the datasets directly onto the form
this creating a directly editable form which worked well.

However i have recently started a project which will require
synchronization to a remote database. Also the underlying database
provider may change at a later date. From what i have read it seems
that a layered approach is necessary, or at least recommended, where i
present an object to the form to be bound (i guess this is called the
business object in technical terms). The business object would then
communicate with a data layer which would then write to the database.

How easy is it to bind business object fields to the form? I don't
want to be hampered too much productivity wise messing with additional
coding. Will the business object controller have to return a list of
business objects? i.e. the function call in the controller layer

BusinessLayer.GetAllCustomerOrders(uint customerID), would return a
BindingList of type CustomerOrders?

Are there any standard interfaces the business object layer would use
to talk to the data layer?

Also, if i take a hit on portability to other platforms which is
unlikely at this stage, is it recommended for the business layer to
return DataSets directly? Although encapsulating a DataSet within the
business layer and presenting it as an object with fields is nice and
abstract, i can see a big time penalty. I would be prepared to return
DataSets if it would provide a happy compromise between segregation
and speed of development.

Finally, i need to synchronize the data in one database to another
database. Where would this synchronization take place? In the business
layer when a delete or addition is performed, i could (as i would be
in my Add or Delete function) perform two data access modification
calls, one to the local database and one to the remote database.

Another snag is that the databases cant be an exact synchronization as
the remote database would contain records for many users (many clients
will be adding to this hence the field IDs will not match between the
local and remote databases), would it be sensible to have a RemoteID
field in my local database row for each record? I'm thinking if that
field is empty the field hasn't been synchronized yet (the remote
database AddRecord function could return an ID which would then be
stored in the RemoteID field in the local database), when i delete a
record in the local database i will be able to delete the record in
the remote database by using the RemoteID field. Or would i base a
primary key for the table on the UserID and the local record ID? That
would indeed make a unique key but is it possible to define a primary
key with the composite of two fields? I hope that isn't too confusing
and illustrates my thoughts on howto solve this multiuser to singe
user database sync.

Thanks in advance,

Chris

[PS]

<mo*****@gmail.comwrote in message
news:11*********************@y80g2000hsf.googlegro ups.com...

There seems to be 3 main questions you are asking.

1. Synchronization of databases
2. Transparent switching between different database providers
3. Using business objects rathers than datasets.

For the synchronization of databases I would look into resolving this issue
at the database levels rather than in your application. I have no experience
with this and there are probably som instances where your business objects
need to have some awareness that synchronization is "pending" but if you can
address this purely at a database level then it would be a cleaner solution.

The second and third questions are somewhat related as there are third party
tools/libraries that can create the core business objects for you and also
support multiple database providers and the persistence of your BOs to the
database.You can google for ORM or object relation mapping. There are
various flavors of products out there. A previous post mentions Rockford
Lhotka's CSLA framework. I have heard good things about LLBLGen.

I have added some other comments inline.

Hi, until recently i was quite happy to add data sources from mssql
database in visual studio and drag the datasets directly onto the form
this creating a directly editable form which worked well.

At the moment it would seem to be a CRUD application.

>
However i have recently started a project which will require
synchronization to a remote database. Also the underlying database
provider may change at a later date. From what i have read it seems
that a layered approach is necessary, or at least recommended, where i
present an object to the form to be bound (i guess this is called the
business object in technical terms). The business object would then
communicate with a data layer which would then write to the database.

To switch database provides you would need to abstract out the access to the
database. This would effectively be refactoring until all your database
calls were using interfaces / abstract classes. At this point you could then
use alternate concrete classes for your db access. .Net 2.0 provides
factories for DBConnection, DbCommand etc that help you with this.

>
How easy is it to bind business object fields to the form? I don't
want to be hampered too much productivity wise messing with additional
coding. Will the business object controller have to return a list of
business objects? i.e. the function call in the controller layer

If you are building them yourself then it can be a little painful. You have
to implement a lot of repetitive code to do with binding notifications. Code
generation is usually a beeter way to go, or using an ORM tool that
generates base classes for you. That is what makes DataSets attractive
initially.

>
BusinessLayer.GetAllCustomerOrders(uint customerID), would return a
BindingList of type CustomerOrders?

Are there any standard interfaces the business object layer would use
to talk to the data layer?

Also, if i take a hit on portability to other platforms which is
unlikely at this stage, is it recommended for the business layer to
return DataSets directly? Although encapsulating a DataSet within the
business layer and presenting it as an object with fields is nice and
abstract, i can see a big time penalty. I would be prepared to return
DataSets if it would provide a happy compromise between segregation
and speed of development.

Once you move away from the CRUD type application I believe that you should
move away from datasets.

>
Finally, i need to synchronize the data in one database to another
database. Where would this synchronization take place? In the business
layer when a delete or addition is performed, i could (as i would be
in my Add or Delete function) perform two data access modification
calls, one to the local database and one to the remote database.

Another snag is that the databases cant be an exact synchronization as
the remote database would contain records for many users (many clients
will be adding to this hence the field IDs will not match between the
local and remote databases), would it be sensible to have a RemoteID
field in my local database row for each record? I'm thinking if that
field is empty the field hasn't been synchronized yet (the remote
database AddRecord function could return an ID which would then be
stored in the RemoteID field in the local database), when i delete a
record in the local database i will be able to delete the record in
the remote database by using the RemoteID field. Or would i base a
primary key for the table on the UserID and the local record ID? That
would indeed make a unique key but is it possible to define a primary
key with the composite of two fields? I hope that isn't too confusing
and illustrates my thoughts on howto solve this multiuser to singe
user database sync.

Try to deal with the database synchronization at the purely database level
if possible.

PS

[Chris]

Thanks for the replies, i have a few further questions. CSLA looks very
interesting.

For the synchronization of databases I would look into resolving this
issue at the database levels rather than in your application. I have no
experience with this and there are probably som instances where your
business objects need to have some awareness that synchronization is
"pending" but if you can address this purely at a database level then it
would be a cleaner solution.

This may be the way to go as i will have a local embedded SQLite database,
and a remote MS SQL database, two different products. I guess i would need
to create a sync layer between the business object and the data layer which
would handle this synchronisation. Is it possible to form a primary key on
the composite of two fields?

The second and third questions are somewhat related as there are third
party tools/libraries that can create the core business objects for you
and also support multiple database providers and the persistence of your
BOs to the database.You can google for ORM or object relation mapping.
There are various flavors of products out there. A previous post mentions
Rockford Lhotka's CSLA framework. I have heard good things about LLBLGen.

LLBLGen is a ORM code generation tool? I'll check this out.

Cheers,

Chris

[Chris]

The second and third questions are somewhat related as there are third

party tools/libraries that can create the core business objects for you
and also support multiple database providers and the persistence of your
BOs to the database.You can google for ORM or object relation mapping.
There are various flavors of products out there. A previous post mentions
Rockford Lhotka's CSLA framework. I have heard good things about LLBLGen.

I was just looking into code generation tools to map between my database and
business objects, are there any opensource of free ones available?

What i would really like is a generation tool for Lhotka's CSLA framework as
that has some excellent features and i like the idea of portable business
objects. Any pointers to simple examples for CSLA would also be great.

Many thanks,

Chris

[PS]

"Chris" <mo*****@no.spam.gmail.remove.comwrote in message
news:A4**********************************@microsof t.com...

Thanks for the replies, i have a few further questions. CSLA looks very
interesting.

>For the synchronization of databases I would look into resolving this
issue at the database levels rather than in your application. I have no
experience with this and there are probably som instances where your
business objects need to have some awareness that synchronization is
"pending" but if you can address this purely at a database level then it
would be a cleaner solution.

This may be the way to go as i will have a local embedded SQLite database,
and a remote MS SQL database, two different products. I guess i would need
to create a sync layer between the business object and the data layer
which would handle this synchronisation. Is it possible to form a primary
key on the composite of two fields?

Also check out the Compact Edition of SQL Server . Not sure if replication
is available but at least it uses the same core database structure.

>

>The second and third questions are somewhat related as there are third
party tools/libraries that can create the core business objects for you
and also support multiple database providers and the persistence of your
BOs to the database.You can google for ORM or object relation mapping.
There are various flavors of products out there. A previous post mentions
Rockford Lhotka's CSLA framework. I have heard good things about LLBLGen.

LLBLGen is a ORM code generation tool? I'll check this out.

ORM tools sometimes generate code based off a schema, will make you inherit
a base class/interface, or will have you use attributes. Not sure which one
LLBLGen does though.

>
Cheers,

Chris

[Mr. Arnold]

You're asking a lot of questions about Business Objects. I suggest that you
get one of the two books in the link, read the book, put the framework
together and put the project together to understand Business Object
concepts.

Most companies put their own frameworks together based on their needs. But
the concept of the framework and the Business Object(s) usages in various
situations will be solid for you with the CSLA concepts.

I suggest that you don't go into this blindly, but rather, know what you're
doing and why you're doing it.

http://www.lhotka.net/Article.aspx?i...b-e0059cc82ee7

[Andy]

On May 17, 12:28 pm, "Chris" <[e-mail]:

What i would really like is a generation tool for Lhotka's CSLA framework as
that has some excellent features and i like the idea of portable business
objects. Any pointers to simple examples for CSLA would also be great.

There are some templates (for LLBLGen actually, and others) that help
you generate Csla based classes. Check out the forums: http://forums.lhotka.net.
You'll find a very active community there.

Before you start with Csla though, you may have to shift from data
driven design to behavior based design. That's the biggest stumbling
block many that first try Csla hit. The book, Expert 2005 C# Business
objects, explains every detail of the framework, as well as explains
why you'll want to do behavior based design.

I've been building applications off of Csla for two years now, and
have nothing but praise for it. To answer one of your previous
questions, yes you can bind controls to business objects. The only
limitiation on object databinding is that you can only bind to
properties, not fields.

HTH
Andy

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

mo*****@gmail.com wrote:

However i have recently started a project which will require
synchronization to a remote database. Also the underlying database
provider may change at a later date. From what i have read it seems
that a layered approach is necessary, or at least recommended, where i
present an object to the form to be bound (i guess this is called the
business object in technical terms). The business object would then
communicate with a data layer which would then write to the database.

That is classic 3 layers.

How easy is it to bind business object fields to the form? I don't
want to be hampered too much productivity wise messing with additional
coding. Will the business object controller have to return a list of
business objects? i.e. the function call in the controller layer

BusinessLayer.GetAllCustomerOrders(uint customerID), would return a
BindingList of type CustomerOrders?

That is also very classic.

Are there any standard interfaces the business object layer would use
to talk to the data layer?

There exist a few patterns, but some of them are rather obvious:
GetOne, GetAll, Save and Delete will bring you a long way.

Also, if i take a hit on portability to other platforms which is
unlikely at this stage, is it recommended for the business layer to
return DataSets directly? Although encapsulating a DataSet within the
business layer and presenting it as an object with fields is nice and
abstract, i can see a big time penalty. I would be prepared to return
DataSets if it would provide a happy compromise between segregation
and speed of development.

DataSet are database independent.

But it is somewhat tied to databases.

List<MyDataClassread via a DataReader should be at least as
efficient as DataSet.

Finally, i need to synchronize the data in one database to another
database. Where would this synchronization take place? In the business
layer when a delete or addition is performed, i could (as i would be
in my Add or Delete function) perform two data access modification
calls, one to the local database and one to the remote database.

If the two databases are the same brand and version, then you could
consider using replication in the database tier.

If you do it in app tier, then remember to use transactions to ensure
they are indeed in synch.

Another snag is that the databases cant be an exact synchronization as
the remote database would contain records for many users (many clients
will be adding to this hence the field IDs will not match between the
local and remote databases), would it be sensible to have a RemoteID
field in my local database row for each record? I'm thinking if that
field is empty the field hasn't been synchronized yet (the remote
database AddRecord function could return an ID which would then be
stored in the RemoteID field in the local database), when i delete a
record in the local database i will be able to delete the record in
the remote database by using the RemoteID field. Or would i base a
primary key for the table on the UserID and the local record ID? That
would indeed make a unique key but is it possible to define a primary
key with the composite of two fields? I hope that isn't too confusing
and illustrates my thoughts on howto solve this multiuser to singe
user database sync.

If you use GUID's as keys, then they will be unique across all clients.

Arne