Best practice keeping business object collection synced to DB

[Alfred Taylor]

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

 

[Michael Rodriguez]

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

 

[news.microsoft.com]

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

 

[Frans Bouma [C# MVP]]

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



--

 

[Alfred Taylor]

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

 

[Michael Rodriguez]

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

 

[Alfred Taylor]

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

 

[Michael Rodriguez]

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

 

[Alfred Taylor]

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

 

[Steve Walker]

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.