473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Best practice keeping business object collection synced to DB

I'm testing the waters of n-tier development and I ran into a scenario that
I'm not sure what the best solution would be. I have a Company object which
contains a collection of contacts retrieved from a database.

In the presentation layer, the user will be able to add/delete/modify this
collection in which case it needs to be synced with the database.

The question is basically how best to do this? Aside from overriding the
add/remove methods of the collection, I can't think of any way to maintain a
1:1 correspondence between what's in the collection and what's in the DB.

I would do this, but something tells me it's not a good idea to have the
collection know about database access.

Or am I asking too much to have the collection synced? Should I just
provide methods on the Company object which let me add/delete/edit a
contact? Which means if a developer modifies the collection directly, it
won't occur in the DB.

Thanks,
-A
Nov 17 '05 #1
9 5450
"Alfred Taylor" <om***@R-E-M-O-V-Eyahoo.com> wrote in message
news:e9**************@TK2MSFTNGP09.phx.gbl...
I'm testing the waters of n-tier development and I ran into a scenario
that I'm not sure what the best solution would be. I have a Company
object which contains a collection of contacts retrieved from a database.

In the presentation layer, the user will be able to add/delete/modify this
collection in which case it needs to be synced with the database.

The question is basically how best to do this? Aside from overriding the
add/remove methods of the collection, I can't think of any way to maintain
a 1:1 correspondence between what's in the collection and what's in the
DB.


Why not simply replace your collection with a strongly typed dataset? That
still allows you independance from the data source, while at the same time
allowing you to pass a dataset back to a data layer (DataSet.GetChanges())
for much easier updates.

What is a collection, really? It's just a list of items, each one with
properties like LastName, PhoneNumber, etc. A strongly typed dataset gives
you the exact same thing, but with built in ways to update the data source.

HTH,

Mike Rodriguez
Nov 17 '05 #2
Hrmm. That's an interesting suggestion. I just spent the past hour or so
reading MSDN articles and it seems like using a DataSet would greatly
simplify a lot of things.

I'll take a closer look at it tomorrow when I have more time. Thanks for
the suggestion.

Anybody else have any input?

-A

"Michael Rodriguez" <mi**@nospamforme.com> wrote in message
news:e3**************@TK2MSFTNGP10.phx.gbl...
"Alfred Taylor" <om***@R-E-M-O-V-Eyahoo.com> wrote in message
news:e9**************@TK2MSFTNGP09.phx.gbl...
I'm testing the waters of n-tier development and I ran into a scenario
that I'm not sure what the best solution would be. I have a Company
object which contains a collection of contacts retrieved from a database.
In the presentation layer, the user will be able to add/delete/modify this collection in which case it needs to be synced with the database.

The question is basically how best to do this? Aside from overriding the add/remove methods of the collection, I can't think of any way to maintain a 1:1 correspondence between what's in the collection and what's in the
DB.

Why not simply replace your collection with a strongly typed dataset?

That still allows you independance from the data source, while at the same time
allowing you to pass a dataset back to a data layer (DataSet.GetChanges())
for much easier updates.

What is a collection, really? It's just a list of items, each one with
properties like LastName, PhoneNumber, etc. A strongly typed dataset gives you the exact same thing, but with built in ways to update the data source.
HTH,

Mike Rodriguez

Nov 17 '05 #3
Alfred Taylor wrote:
I'm testing the waters of n-tier development and I ran into a
scenario that I'm not sure what the best solution would be. I have a
Company object which contains a collection of contacts retrieved from
a database.

In the presentation layer, the user will be able to add/delete/modify
this collection in which case it needs to be synced with the database.

The question is basically how best to do this? Aside from overriding
the add/remove methods of the collection, I can't think of any way to
maintain a 1:1 correspondence between what's in the collection and
what's in the DB.

I would do this, but something tells me it's not a good idea to have
the collection know about database access.

Or am I asking too much to have the collection synced? Should I just
provide methods on the Company object which let me add/delete/edit a
contact? Which means if a developer modifies the collection
directly, it won't occur in the DB.


Keeping a disconnected datastore, be it a dataset or collection of
objects, in sync with the DB is always a challenge. THere are two
different scenario's.

1) simply remove the state for the data in the db and replace it with
the current state in the object collection
or
2) track changes made to the object collection, and replay them on the
datastore.

Another person suggested a typed dataset, but that's not going to help
you, you still have to sort the changes out, create sql to get things
done.

take for example the removal of a contact from a company's contacts
list. Is that just the removal of the contact from the list (i.e. an
in-memory action, not affecting any data in the db)?, or is that equal
to: delete the contact? or is that equal to: reset the FK in contact to
company?

A dataset won't solve that for you, you have to write code like that
yourself, always, it then comes down to: what's the easiest way out?

Most of the time, you go for option 1: first perform a delete from
contacts where companyid = @companyID and then perform inserts for the
contacts still in company.Contacts. Then you perform a refetch of the
contacts to read default constraint values and calculated column values.

It can be that's not feasable for your situation so you have to track
the changes made to the collection in memory and replay them one by one
onto the db. A typical object to do taht with is a UnitOfWork, using
the Unit of work pattern, almost every O/R mapper has one.

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Nov 17 '05 #4

"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com...

Keeping a disconnected datastore, be it a dataset or collection of
objects, in sync with the DB is always a challenge. THere are two
different scenario's.
Tell me about it. ;)

1) simply remove the state for the data in the db and replace it with
the current state in the object collection
or
2) track changes made to the object collection, and replay them on the
datastore.
Those are the _exact_ problems that led me to make this post. I'm glad I'm
not the only one feeling lost here.

Another person suggested a typed dataset, but that's not going to help
you, you still have to sort the changes out, create sql to get things
done.
I'm completely new to DataSets, but correct me if I'm wrong in saying the
DataSet will keep track of those changes for me. I believe it's the
RowState property that will let me know if the row has been updated,
deleted, etc. I also did a quick example and it looks like the
update/delete/insert statements can be automatically generated for me if I
want. All I have to do is specify the initial SELECT statement.

It seems like the DataSet will solve a lot of problems (and reduce code).
I'm just not going to get a 1:1 correspondence which I'm beginning to be
okay with. Like I said, I'm not an expert on DataSets but it seems like all
the problems you're describing can be solved using a DataSet.

Thanks,
-A

take for example the removal of a contact from a company's contacts
list. Is that just the removal of the contact from the list (i.e. an
in-memory action, not affecting any data in the db)?, or is that equal
to: delete the contact? or is that equal to: reset the FK in contact to
company?

A dataset won't solve that for you, you have to write code like that
yourself, always, it then comes down to: what's the easiest way out?

Most of the time, you go for option 1: first perform a delete from
contacts where companyid = @companyID and then perform inserts for the
contacts still in company.Contacts. Then you perform a refetch of the
contacts to read default constraint values and calculated column values.

It can be that's not feasable for your situation so you have to track
the changes made to the collection in memory and replay them one by one
onto the db. A typical object to do taht with is a UnitOfWork, using
the Unit of work pattern, almost every O/R mapper has one.

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Nov 17 '05 #5
"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com...
Alfred Taylor wrote:

Another person suggested a typed dataset, but that's not going to help
you, you still have to sort the changes out, create sql to get things
done.


It's true that you still have to create sql statements to get things done.
That is going to be the case no matter what approach you take. The
advantages of a dataset are:

- Better built-in support data binding
- Built-in abilities to keep track of which rows have been modified,
deleted, etc.
- The biggest benefit, as far as code reduction, being able to use a
DataAdapter. With the data adapter, all you have to do is define the
insert, update and delete statements. Then, just give it your dataset and
VS will call the appropriate sql statements for each row. That is a major
code saver. A colleague of mine had a data layer based on a collection. He
had to write code to figure out when and where to call which sql statements.
I showed him my approach with the data adapter and it cut his code size to
1/4 of what it had been before.

Some notes about using a data adapter. The select statement is pretty
self-explanatory. For the insert, update and delete statements, I would
modify the ones VS generates. For the insert statement, immediately after
doing the insert, reselect the record like so:

INSERT INTO customers(id, name) ; SELECT id, name FROM customers WHERE id =
@@IDENTITY -- sql server specific

This allows you to get the values of any autoincrement fields, default
values, etc. that sql changed and that your UI will need to know about. Do
the same thing for the update statement. Also, in my update statements, I
only select the record based on the primary key and a timestamp field. That
way you will know if another user has changed the data since you queried it.
Again, the nice thing about the dataset/dataadapter approach is VS will
throw the concurrency error for you automatically in this case (0 rows
affected = concurrency error). For the delete statement, I only delete
based on the primary key.

Using this approach I have been able to generate a data layer is very
minimal. Also, get a code generator! I use My Generation. Let it generate
your entire data layer for you, saving you hours of repetitive work.

HTH,

Mike Rodriguez
Nov 17 '05 #6
Hi Michael,

Thanks for your posts. They've been extremely helpful and have opened my
eyes to a whole new world. ;)

Initially I guess I wasn't even going to have the data layer and have my
business object get data from the DB directly. Now that I have a data layer
(a DataSet with all my data), I'm having problems exposing it in the
business logic.

For example, I can have business object called Company which has properties
that will expose the fields of a DataRow. That's fine and seems like the
right thing to be doing.

But I also have the now famous Contacts collection which I talked about in
the initial post. With this new model, my Contacts collection is now a
DataTable with rows of Contacts. How would I go about exposing this in the
Company object?

I don't want to have a property on the Comany that returns the DataTable as
that seems to violate OOP. People who use the Company object shouldn't know
the underlying datastore . . .

I thought about creating a new collection which uses the datatable as
storage and exposes the DataTable methods that I need, but this doesn't
"sit" right with me.

So I'm kind of at a loss. Any suggestions?

Thanks,
-A
Nov 17 '05 #7
"Alfred Taylor" <om***@R-E-M-O-V-Eyahoo.com> wrote in message
news:eK**************@TK2MSFTNGP12.phx.gbl...
Hi Michael,

Thanks for your posts. They've been extremely helpful and have opened my
eyes to a whole new world. ;)

Initially I guess I wasn't even going to have the data layer and have my
business object get data from the DB directly. Now that I have a data
layer (a DataSet with all my data), I'm having problems exposing it in the
business logic.

For example, I can have business object called Company which has
properties that will expose the fields of a DataRow. That's fine and
seems like the right thing to be doing.

But I also have the now famous Contacts collection which I talked about in
the initial post. With this new model, my Contacts collection is now a
DataTable with rows of Contacts. How would I go about exposing this in
the Company object?


Hi Alfred,

Personally, I wouldn't have any custom defined business data objects. All
of my business data objects are strongly typed datasets. I use those
datasets in my business classes to expose all of the functionality I need
(Get, Update, Validate, etc). It seems like you're looking to link your
Company object with your Contacts object. Is this because of a
master-detail relationship? If so, datasets have built-in ways to manage
this as well. When you move to a new record in the master the detail
records change automatically. Also, you can write a C# program to build all
of your dataset classes for you based on your existing schema. If you'd, I
could email you a sample of that.

You can try to write your own data objects that have better functionality
for handling data than the ADO.NET DataSet, but I decided a long time ago
that Microsoft was better than me on this one. Why not take advantage of
their hard work?

HTH,

Mike Rodriguez
Nov 17 '05 #8

"Michael Rodriguez" <mi**@nospamforme.com> wrote in message
news:eL**************@TK2MSFTNGP09.phx.gbl...
Hi Alfred,

Personally, I wouldn't have any custom defined business data objects. All
of my business data objects are strongly typed datasets. I use those
datasets in my business classes to expose all of the functionality I need
(Get, Update, Validate, etc). It seems like you're looking to link your
Company object with your Contacts object. Is this because of a
master-detail relationship? If so, datasets have built-in ways to manage
this as well. When you move to a new record in the master the detail
records change automatically. Also, you can write a C# program to build
all of your dataset classes for you based on your existing schema. If
you'd, I could email you a sample of that.
You know what would _really_ help me out? If you/someone could give me a
snippet of a business object so that I can see how it's interacting
with/exposing the data objects. I conceptualize so much better with
examples. So if you wouldn't mind sending me something like that, I'd
appreciate it.

Thanks,
-A

You can try to write your own data objects that have better functionality
for handling data than the ADO.NET DataSet, but I decided a long time ago
that Microsoft was better than me on this one. Why not take advantage of
their hard work?

HTH,

Mike Rodriguez

Nov 17 '05 #9
In message <xn***************@news.microsoft.com>, "Frans Bouma [C#
MVP]" <pe******************@xs4all.nl> writes
Most of the time, you go for option 1: first perform a delete from
contacts where companyid = @companyID and then perform inserts for the
contacts still in company.Contacts. Then you perform a refetch of the
contacts to read default constraint values and calculated column values.


I've found the SQLServer XML support useful for doing this kind of thing
with fewer database calls, particularly where the related records
already exist and the operation is to populate a joining table. I've got
a converter which will take any of my standard collections and convert
them to a consistent XML format. Getting the values out and into a
temporary table is then just boilerplate code in the stored procedure.

--
Steve Walker
Nov 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
2
by: Sjaakie Helderhorst | last post by:
Hi, What's the best way of storing a dataset (+/-1000 rows, 9 columns)? I'm using a session-variable but this seems to have serious impact on performance. It's a shop and I'm using the same...
18
by: D Witherspoon | last post by:
I am developing a Windows Forms application in VB.NET that will use .NET remoting to access the data tier classes. A very simple way I have come up with is by creating typed (.xsd) datasets. For...
6
by: Nate | last post by:
I am in a slight predicament trying to determine the most efficient and effective way to connect/disconnect from a database within a business object (c# dll). I'm also keeping in mind the concept...
28
by: Michael Primeaux | last post by:
What is the recommended pattern for implementing a synchronized (thread-safe) class that inherits from Collection<T>? For example, I want to implement a SyncRoot property . I do see where I can...
3
by: cbrown | last post by:
I am rebuilding an existing application that relies on an SQL DB. The app is a scheduling/employee management program. My question pertains to best practices in dotnet and database. I use a 3...
13
by: Alan Silver | last post by:
Hello, MSDN (amongst other places) is full of helpful advice on ways to do data access, but they all seem geared to wards enterprise applications. Maybe I'm in a minority, but I don't have those...
5
by: Frank Millman | last post by:
Hi all This is not strictly a Python question, but as I am writing in Python, and as I know there are some XML gurus on this list, I hope it is appropriate here. XML-schemas are used to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.