Q: Persistent Data Objects architecture

[Sky]

Hello:
Looking for some insight into using Persistent Data Objects instead of SQL
calls everywhere, but have a couple of arch. questions I don't
get/understand.

a) For each table (eg: Customers) one has a class called cCustomers. The
basics are that instead of Connection.Open(), DataReader, etc. one simply
can cCustomers.Load(tGUID); using a function to map the database recordset's
fields to the classes properties, populating that class with a record. And
after changes are made to a Property, reversing the process to build an
insert or Update statement via reflection, and synch it back to the db.
As for working with a group of them use a cCustomerCollection based on
IList, and one can also just as easily use it as a DataSrc for a
aspataGrid or other. Nice. Very nice.
But what I don't get, and worries me a bit is:

a) Scenario: User A loads a cCustomerCollection (WHERE NAME LIKE 'S%') of
cCustomer objects. Then comes along User B who loads the same or other
cCollection (WHERE NAME LIKE 'SMI%'). At this point the sever has two
collections in memory. Ok. Now, User A modifies the properties of one of the
cCustomers in his cCustomerCollection, and Synch()'s it back to the DBMS.
Great. Except -- and here is my question -- it appears to me that UserB will
now be looking at an out of date Record! How can one guarantee persistent
synchronized data across users????? If you could explain how this works, or
what to do to make it possible, or why it is NOT important to have this work
this way I would feel a Lot better about using the approach.

b) Secondly scenario that I use a lot: if you have cCustomers, and
cPermissions, and generally I Join these together so that I have one
virtual table to display with, and choose whether to display a record, line
by line, then how does one approach this via persitent DataObjects? If we
do this by first querying for cCustomers to get a cCustomerCollection, do we
then have to requery for each cCustomer.Permissions !???! This would to
increase the number of sql calls dramatically. Or does one still build an
Join statement like before, but resplit the resulting recordset into two
objects: cCustomers, and it's related cRights? Or another technique? Ie: how
many calls would it take to do the above? Or is this not a good scenario for
Persistent Data Objects?

Anyway - sorry for the sort of complex questions -- but the few code
examples of PDO I've found have concentrated on the very basics (ie Mapping
and Synching), but not the common day-to-day problems that I deal with
currently with SQL.

Any and all help greatly appreciated!!!

PS: I've purchased "Data Access Patterns" by Nock -- but it didn't address
these two issues as far as I can tell, and I downloaded DNN 2.0 to see how
theirs approach is implemented -- but it has proved a little to intricate
for me to figure out what's going on under the hood -- does anyone know of a
good web article or other source that starts from basics and goes into
detail on how to write a PDO layer?

Thanks!

 

[Erik Frey]

Hi Sky,

Comments in-line:

But what I don't get, and worries me a bit is:

a) Scenario: User A loads a cCustomerCollection (WHERE NAME LIKE 'S%') of
cCustomer objects. Then comes along User B who loads the same or other
cCollection (WHERE NAME LIKE 'SMI%'). At this point the sever has two
collections in memory. Ok. Now, User A modifies the properties of one of the
cCustomers in his cCustomerCollection, and Synch()'s it back to the DBMS.
Great. Except -- and here is my question -- it appears to me that UserB will
now be looking at an out of date Record! How can one guarantee persistent
synchronized data across users????? If you could explain how this works, or
what to do to make it possible, or why it is NOT important to have this work
this way I would feel a Lot better about using the approach.

You are describing a single-server environment, but I am going to answer
your question from two sides, server-side and client-side. The server-side
solution is straightforward. You need to provide methods that guarantee
that a unique row in the database has a single unique representation in your
cCustomer collection. Your example of two collections (WHERE NAME LIKE
'S%') and (WHERE NAME LIKE 'SMI%') might be different collections, but if
they have SQL rows in common, those should correlate to sharing the same
customer objects. There are a number of ways to guarantee this kind of
object integrity, for example, using a global hashtable where the keys are
the unique ID's in the customer rows, and the values are the customer
objects.

The client-side solution is a bigger concern, and there are a number of ways
that the problem is approached. In the .NET world, the most common way to
address this problem is called optimistic concurrency. You can find tons of
articles about it on google, but here's a quick one I found that looks
pretty good:

http://www.ftponline.com/vsm/2003_08/magazine/columns/databasedesign/

b) Secondly scenario that I use a lot: if you have cCustomers, and
cPermissions, and generally I Join these together so that I have one
virtual table to display with, and choose whether to display a record, line
by line, then how does one approach this via persitent DataObjects? If we
do this by first querying for cCustomers to get a cCustomerCollection, do we
then have to requery for each cCustomer.Permissions !???! This would to
increase the number of sql calls dramatically. Or does one still build an
Join statement like before, but resplit the resulting recordset into two
objects: cCustomers, and it's related cRights? Or another technique? Ie: how
many calls would it take to do the above? Or is this not a good scenario for
Persistent Data Objects?

I'm not entirely sure I understand what you are getting at here, but I will
offer this: The System.Data.DataSet is a very good tool for maintaining
multiple related tables in a single object. It supports table
relationships, key cascading, uniqueness, nulls, etcetera, right off the
bat. Your cCustomer object could be a strongly typed DataSet with a
Customer table, and a Permissions table, and the appropriate related rows
inside each.

PS: I've purchased "Data Access Patterns" by Nock -- but it didn't address
these two issues as far as I can tell, and I downloaded DNN 2.0 to see how
theirs approach is implemented -- but it has proved a little to intricate
for me to figure out what's going on under the hood -- does anyone know of a
good web article or other source that starts from basics and goes into
detail on how to write a PDO layer?

I'm think this article will help you (particularly the section entitled
Implementing Business Entities):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp

Erik

 

[Sky]

Dear Erik:

Yours and Suresh's answers gave me a larger picture to work with. Thanks!

I also came across a good article (in french though) at
http://www.dotnetguru.org/articles/Persistance/livreblanc/ormapping.htm
written by the author of the Persistent Data Objects class at evaluant.com.
Excellent read. Have to find one in English now that is as clear...


First reactions to both your comments.

Suresh, good to know that not everybody likes it. As for the number of
articles coming out on it these days, you'd think it was the best thing
since Sliced Bread....
I also just found out that Microsoft's upcoming Whitbey solution is
targetted ONLY at SqlServer users. ;-(
Which was precisely what I was looking to circumvent by making Generic
Adaptors, and Generic DataObjects rather than custom SQL.

So, I was not at all intending to write my own -- and will still try to
avoid this direction if I can, although I am having a real hard time
understanding the code that i just downloaded from evaluant.com to see how
it all works.
So working instead from the ground up (small steps) rather than back from a
finished product (huge steps (huge hole at present ;-) ) let me see, if you
have a second, whether I am getting the whole picture.

To summarize what we have now we could start with the following broad
strokes:

class IPersistentObject {
//pseudo code....
private bool _Sychronized =false;
public bool DataChanged = false;

bool Load (System.Guid qGUID){...use of Reflection to load a DataReader
or DataRow...}
bool Save (){if (_DataChanged){...use of Reflection to create an INSERT
or CREATE..}

}

from this, enherit ones cCustomer class:

class cCustomer : IPersistentObject{
//pseudo code....
public UserName {get {return _UserName;}{set
_UserName=value;DataChanged=true;}
public new Load() { optionally override the Recursive base.method with a
hard-coded SQL method for more speed;}
public new Save() {same...}
}

This addresses what Suresh brought up: that Reflection is nice and all..but
slow. Don't rely on it if you can. Maybe for dev, but not for production?
Anyway.


As for the second point I brought up, as to how to solve problems of
concurrent changes, I think I got a lead on how to think about it from the
article I mentioned, and what you just wrote below in terms of the
Hashtable.
What was missing from the last post, was some form of global Hashtable /
cache that holds a unique copy of the above cCustomer class.

The article pointed out that the problem with this part is the garbage
cleaning: if it isn't cleaned up, you sooner or later end up with every
single record in memory...Timers won't work for cleanup,
as you don't know if you will saturate before the Timer kicks in. Etc. To
work around this, the writer suggested a Hashtable of 'pointers', plus a
use-counter.
In other words, we still need one more base class:

class cPersistantPointer {
int Counter=0;
IPersistentObject Object=null;
}

and make a MyHash to hold these :

cPersistentObjects _MyHash; //enherited from Hashtable, but wired to only
accept objects enherited from cPersistentPointer.
And where the Key used is always something like "TABLENAME" + "_" +
RecordID; for example. Something unique that can always be resolved (a
random GUID of course will not do...)

BTW: the Counter is increment/decrement as to the number of users currently
holding copies of this class, and the Object points to the IPersistentObject
(cCustomer) we are talking about.


This causes a rewrite of the Load function to somehow look FIRST in the
MyHash and if not there load it from the DB, but if there, load it from the
Hashtable. And increment the counter in both cases.

Coding question:
I can see that when collecting together a cCustomerCollection via a
Datareader it's possible, but when you creating a single cCustomer(qUserID);
how do you do it? Do we have to use an external
factory (ps: I'm not totally up on what term is right here 'factory' I think
is the right term, no?) to create the cCustomer() -- and not allow one to
simply instantiate one on the fly?
Because, if you did, and it then trys to load a single RecordRow from WITHIN
the cCustomerClass.Load() method, and you have found out that there is
already a copy of thyself in the Hashtable, how do you delete thyself and
map instead to the hashtable...Never heard of such a move, and methinks it
be impossible. So how is it done? Beats me.

Tells me that I am still missing a piece of the puzzle. Any ideas?

Whew (My head hurts from this all this twisting around the problem) hows
yours?!? ;-)

And as for the cCustomerCollections always updating
incrementing/decrementing the cPointers Counter each and every time...Ugh.
Sounds like it will be a lot of work!


Final point I brought up, which you both responded to in regards to how to
make TableA, TableB joins, and use them, I think, upon after thought, we are
talking about an overloaded Load() function -- something like LoadEx() which
loads a DataSet of the Join select, and then takes some columns to create a
cTableA, and some columns to make the cTableB. and link cTableB as a
property of cTableA. It's not as bad as I first thought... Since we
discussed overridding the basic Load() {using Reflection...} with a
hard-coded SQL approach, this is just a little bit more work based on the
same principles...




PS: As to why I am trying to figure this all out, I'm about to layout the
architecture of this rather large project I have in mind, OSS, so I really
want to get a good data layer down that others can easily build on, yet
remain portable. I've tried just using Generic DataAdaptors/standard SQL...
but that quickly looks really messy, and using @arguments is only SqlServer
complient (had to write my own IDbCommand)...so I feel like each road I've
tested has sort of left me close...but no cigar. Nothing so far has been
truly portable, and I feel really pissed off that MS didn't make their data
connectors strategies more universal. It appears from where I am sitting,
that they're grasping at straws trying to hold everyone to their products
only, and we're paying the price. Shame on them.
Last direction I am trying this DataObjects stuff. And if that fails, I'm
going back to PHP and portable code ...although in script! ;-(

Anyway, rant aside. Thanks so much for the feedback so far. Any other
feedback you (or others) have to share?

 

[Sky]

Dear Suresh:

Thanks for the quick response! Yours and Erik Frey's input was just the kind
of stuff I was looking for.

I just responded to yours and his comments both, all in one post, but under
his thread: hope you don't mind!
I would love to hear if you have any further comments as to where I am
heading with the new input you both gave me.

Thanks!
And hoping to hear from you soon as your input was very valuable!

Here's my take(Experiences and Opinions). I've followed a similar

approach in some of my projects.an insert or Update statement via reflection, and synch it back to the db.

Reflection inherently is slower than manually creating update statements.

Although establishing the db connection is the main bottleneck you should
consider the cost of relfection based on your application usage.IList, and one can also just as easily use it as a DataSrc for a

aspataGrid or other. Nice. Very nice.

Yes. This is very cool and it keeps your code very object oriented. But

you do lose a lot free stuff. One thing you do have to form your own
collection. The other is sorting. DataTable to DataView and sorting is
very easy. Object to DataView or simply sorting the object is another
story. If some one has an easy way to do this please let us know.

I don't think this is an issue of how you are planning to represent the

data. You'll have the same issue even if you are using DataSet and
DataTables. There detached nature of datasets doesn't go well for
synchronization. So I think in either case you have to design a solution
that will handle synchronization.cPermissions, and generally I Join these together so that I have one
virtual table to display with, and choose whether to display a record, line
by line, then how does one approach this via persitent DataObjects?

Yes this is another problem. In my opinion your business layer shouldn't

be a straight 1-1 reflection of your data layer. In other words classes
shouldn't map to tables in the database 1-1. In a typical normalized
database schema you'll have several look up tables. You don't want to have
to create seperate classes for these look up tables so you can look up an
ID's description.

Instead designing the business layer based on business rules I think is a

better approach. So you are classes are business rule oriented objects and
not datasource table oriented objects. Just my opinion.

Bottom line is you do what's best for your problem. There isn't really a

standard way to solve every problem.

BTW. I have a few Java developers in my team. It seems that this is the

same concept followed in Sun's JavaBeans architecture. You might want to
read up on it a little. They handle all the scenarios you have questions
about. If remember correctly I believe they have an engine that takes care
of transactions, joins and synchronization. The consensus among our
developers was that they didn't like it.

 

[Erik Frey]

Sky,

Once again, some comments in-line:

This addresses what Suresh brought up: that Reflection is nice and all..but
slow. Don't rely on it if you can. Maybe for dev, but not for production?
Anyway.

I would agree that Reflection is going to give you some poor performance. I
work on some very large projects, and so I've developed some code-generation
tools and techniques that allow me to pretty easily create and syncronize
data-structures between my app and the database server. My business
entities are all strongly-typed, so this means NO reflection/late binding AT
ALL. Personally, I find this the way to go - all the advantages of strong
typing with a -little- repetetive code maintenance, but not a whole lot.

This causes a rewrite of the Load function to somehow look FIRST in the
MyHash and if not there load it from the DB, but if there, load it from the
Hashtable. And increment the counter in both cases.

I think you're more or less on the right track here, but I'm going to point
out a few things, from the point of view of the MSDN article I sent you.
The persistent object model you showed mixed stateless and stateful methods
into one object, whereas the MSDN article suggest the following:

for each type of object you want to persist, you have:

One Business Entity, be it a custom class/DataSet/XML
representation/whatever. This is a stateful class. it knows NOTHING about
talking to db servers/loading/saving.
One Logic class , which does your CRUD actions (Create Read Update Delete).
It contains the SQL statements that actually act upon your business entity.
This class is essentially stateless. As a matter of fact, you could make
all of its functions static if you wanted.

The reason I bring this up is that you mentioned handling the uniqueness
checking in the Load and Save methods. Really, your Logic class that does
the CRUD methods should be brainless. If you request an object with a
particular ID from your logic class, it should fetch that from the DB. If
you request the same ID ten times from your Logic class, it should fetch it
ten times from the DB.

THEN, in a class ABOVE your Logic classes (maybe even as part of your
business layer), you can have a class that actually coordinates and
maintains the uniqueness of your business entities, and coordinates all the
saves and loads. In my apps, I call this class BEBroker (Business Entity
Broker). This class does the reference checking and whatnot that you
mention.

Coding question:
I can see that when collecting together a cCustomerCollection via a
Datareader it's possible, but when you creating a single cCustomer(qUserID);
how do you do it? Do we have to use an external
factory (ps: I'm not totally up on what term is right here 'factory' I think
is the right term, no?) to create the cCustomer() -- and not allow one to
simply instantiate one on the fly?
Because, if you did, and it then trys to load a single RecordRow from WITHIN
the cCustomerClass.Load() method, and you have found out that there is
already a copy of thyself in the Hashtable, how do you delete thyself and
map instead to the hashtable...Never heard of such a move, and methinks it
be impossible. So how is it done? Beats me.

If you think about it, this question is moot if you follow the object model
I described above.

Tells me that I am still missing a piece of the puzzle. Any ideas?

Whew (My head hurts from this all this twisting around the problem) hows
yours?!? ;-)

Actually, developing a generic, powerful, and versatile solution took a
number of large software projects. I still choke when I look at my first
..NET project - the "Data" class was a swamp of hundreds of uncoordinated
datadapters and sqlcommands.

So if your head hurts, it means you are on the right track

PS: As to why I am trying to figure this all out, I'm about to layout the
architecture of this rather large project I have in mind, OSS, so I really
want to get a good data layer down that others can easily build on, yet
remain portable. I've tried just using Generic DataAdaptors/standard SQL...
but that quickly looks really messy, and using @arguments is only SqlServer
complient (had to write my own IDbCommand)...so I feel like each road I've
tested has sort of left me close...but no cigar. Nothing so far has been
truly portable, and I feel really pissed off that MS didn't make their data
connectors strategies more universal. It appears from where I am sitting,
that they're grasping at straws trying to hold everyone to their products
only, and we're paying the price. Shame on them.
Last direction I am trying this DataObjects stuff. And if that fails, I'm
going back to PHP and portable code ...although in script! ;-(

To me, MS' ADO.NET platform seems a step away from complete. The reason I
say that is because where I work, I've extended their ADO.NET library with
my own DataTools library that implements a lot of what is written in that
"Designing Data Tier Components" article I sent you. It's almost as if the
article should have been written "Here's What We Would Have Done With
ADO.NET If We'd Had More Time". The DataAdapter, in particular, is lacking
in some much-needed functionality.

If you're going to try something similar you might want to look through the
Data Access Application Block for .NET. Also, if you want a solution that's
platform generic, I'd recommend some practices similar to what was done in
ADO.NET. Create an abstract DbHelper class, then create a sealed SQLHelper
class that inherits from it, and an OracleHelper class if you need one, etc.

It's a headache, but it will save you time in the long run.

Erik

 

[Sky]

Dear Erik:

I must have been so excited receiving feedback that I never got around to
reading the article ;-) Oops. Off I go to read... and considering it's
midnight here, I might not be able to get back to you till tomorrow -- but i
can't tell you how lucky I was to fall on you today. I learnt a lot, and I
think with the nuggets you laid out, I might be able to plan something that
works for once...

As for database portable code -- and the wierd things one tries and the
hundreds of lines one tries to solve this thing... you have no idea how many
(BAD!) ideas I've burnt through this month...including one stupid function
called UniversalSQL that translates "[", "]", "'","#" to various DB
inplementations (eg for MySQL it replaces the chars with "`","`", "'", "'",
respectively... Yuck.

Happy that this subject (DO) seems to be a lot more rich starting point...

Cheers,
Sky

 

[Guest]

Erik,

I am struggling with what should I use for my business entities (typed
datset, untyped datset, or custom class). In the past I have always used
custom classes to represent my business entities. Now with ADO.Net it has
opened my eyes to other solutions, namely datasets. I rather use untyped
datasets however I do not want to create a untyped dataset based on user
input from scratch. I guess I can retrieve the schema from the db but is
this the best approach? I could create the schema at design time using
typed datasets. I like the ease of use of a typed dataset when coding
however I would have to redeploy my business entity assembly if the db
structure changes. I guess the same is true if I use custom classes. More
and more I look at datasets, I like the functionality (managing concurrency,
CRUD behaviors (updating)...) With datsets versus custom classes, I do not
have to reinvent the wheel when comes to db functionality. However with
that said, in my application I need some extra functionality. For example,
my application is a calculation application thats needs to have user data
(saved to db) and application data(user data converted for application use
not saved to db as converted but rather user data) seperate. Right now I
just have properties in my class for both data and pass the user data to my
data access layer to be saved and my business objects access the application
data (converted data) to be used. So, if I look my design, I really need a
hybrid solution. So, I am thinking of using the custom class and a dataset
(untyped). I can encapsulate the dataset and use properties to access the
data from the dataset (if needed) and use the same approach for the
application data I am using now. I have read the Microsoft article you
suggested and it has helped me differentiate which solution is best. I
guess there is a not clear cut choice and I see why most developers use
custom classes because of performance, strong typing... I would love the
best of both worlds and not have to deploy a new business entity assembly
everytime my db structure changes (it will happen - not major changes only
minor).

Any opinions on mixing both approaches?
Am I correct in assumming that the best way to use a untyped dataset versus
a typed is to retrieve the schema from the db?

Thanks

 

[Brad Williams]

I like the ease of use of a typed dataset when coding

however I would have to redeploy my business entity assembly if the db
structure changes.

If the db schema changes, won't you have to redeploy no matter what
technology you use?

Brad Williams

 

[Guest]

It most cases you are correct. With that said is a hybrid approach a good
idea or is better to stick with one approach?

Thanks

 

[Erik Frey]

Mike,

Comments in-line:

I am struggling with what should I use for my business entities (typed
datset, untyped datset, or custom class). In the past I have always used
custom classes to represent my business entities. Now with ADO.Net it has
opened my eyes to other solutions, namely datasets.

And oddly enough, anything I'd write here will be stale in a couple of
months. There's a lot of work being done to streamline the whole process of
designing and maintaining business objects. Microsoft has something coming
up called Whitehorse that sounds pretty interesting, but for now I would
wholly recommend a neat little app developed by Eric J. Smith over here:

http://www.ericjsmith.net/codesmith/

It's a template-based code generator that's pretty powerful. I use it for
custom collections (which will be unnecessary when generics come out), but
it also has some handy tools for creating custom business objects (custom
classes or typed datasets) straight from database schemas.

I rather use untyped
datasets however I do not want to create a untyped dataset based on user
input from scratch. I guess I can retrieve the schema from the db but is
this the best approach? I could create the schema at design time using
typed datasets. I like the ease of use of a typed dataset when coding
however I would have to redeploy my business entity assembly if the db
structure changes. I guess the same is true if I use custom classes.

Well, using a DataAdapter.FillSchema on a blank dataset will dynamically
create the columns and column types from scratch, so that's a vote for
untyped datasets. However, if your database has multiple tables with
foreign keys you'd have to write some pretty fancy code to query your
database and build that into a dataset on the fly - that could get
complicated. Also, FillSchema doesn't handle AutoIncrement right (if I
remember correctly). So there are some issues.

The advantage you give for untyped datasets is that you don't have to
redeploy when your db structure changes. But if your underlying db
structure changes, this is probably going to mean updating your UI
assemblies, which means a redeploy anyway. So generally I prefer typed
datasets.

More
and more I look at datasets, I like the functionality (managing concurrency,
CRUD behaviors (updating)...) With datsets versus custom classes, I do not
have to reinvent the wheel when comes to db functionality. However with
that said, in my application I need some extra functionality. For example,
my application is a calculation application thats needs to have user data
(saved to db) and application data(user data converted for application use
not saved to db as converted but rather user data) seperate.

Are these complicated calculations? The DataColumn in a DataTable (in a
DataSet) has a property called Expression that you can use to create
calculated columns. This might be a simpler solution.

Right now I
just have properties in my class for both data and pass the user data to my
data access layer to be saved and my business objects access the application
data (converted data) to be used. So, if I look my design, I really need a
hybrid solution. So, I am thinking of using the custom class and a dataset
(untyped). I can encapsulate the dataset and use properties to access the
data from the dataset (if needed) and use the same approach for the
application data I am using now. I have read the Microsoft article you
suggested and it has helped me differentiate which solution is best. I
guess there is a not clear cut choice and I see why most developers use
custom classes because of performance, strong typing... I would love the
best of both worlds and not have to deploy a new business entity assembly
everytime my db structure changes (it will happen - not major changes only
minor).

Take a look at CodeSmith. It may be right up your alley for writing your
own custom templates and generating your own hybrid typed datasets.

Erik

 

[Brad Williams]

I would stick with one approach, but I am not in your shoes. If you see
real advantages to a mixture then I wouldn't dogmatize against it. The
drawback I would think is a duplication of effort. I'm not sure why you'd
want to go with a mixture, but it's hard to know enough to make an
intelligent recommendation over usenet.

My default recommendation without knowing any specifics, for at least
medium-sized projects, would be to use typed datasets generated from .xsd
files (which can be generated by a tool, but don't overestimate the
difficulty of maintaining .xsd's by hand, as I use to: it is very easy in
practice). Then create CRUD-capable data access components which use those
datasets. The DAC's can be code generated, or use the wizards to generate
the data adapter stuff and put as much of everything else as you can in a
re-usable base class.

I have to agree with Eric that programmatically adding relational
constraints into a dataset would be a lot of effort. Better to make as much
stuff declarative as possible.

Regards,
Brad Williams

 

[Guest]

Thanks for the reply. I agree with that a mixed approach might not be the
best approach (might be harder to maintain). I would rather use a typed
dataset however the problem I am having is my application saves user data
inputed by user not from the database. So, when I the user inputs the data
I save the data in my business entities (custom classes via properties) and
when the user is done I update the db using asynchronous calls (minimal
impact on my application). If I use a typed dataset, I have to manually
create a new row in the typed dataset. However with that said, when user
selects a previous record in the db I could set each form's datasource to
the business entities. So, my real problem with typed datasets (datasets in
general) is its hard to manually add data. Am I wrong with my assumption?

Thanks

 

[Brad Williams]

Every application that has users entering data needs to be able to insert
rows, and so there is this way to do it with typed datasets. There's a
NewMyTableTypeRow on the the typed datatable class that returns a typed row,
then you set its fields (which are properties), then use the typed row's
AddMyTableTypeRow method to put it is the typed datatable.

Now there are two (related) difficulties you run into in practice, however.
One is that the row you add to the typed dataset may violate constraints
which are built into your typed dataset. The other is getting the
dataadapter.Update method to not only send an INSERT to the db, but also
pull the new row back (via SELECT) so that the dataset can be updated with
any db-generated fields (if there are any), such as an identity key column.
A common way to handle this is to have the InsertCommand object of the
dataadapter call a stored proc that does an INSERT followed by a SELECT --
the dataadapter knows to update the row of the dataset automagically with
that row that comes back. Play around with the wizards: create a new item
in your project of type "Component" then drag a SqlDataAdapter database tool
onto it, and see where the wizard takes you.

If you have a good example of working with typed datasets, you should be
okay, and even become a fan like me. But otherwise you could be banging
your head trying to figure out the little tricks (like when and if to turn
EnforceConstraints to false on the dataset). Maybe someone else can point
to a good example? The good and bad thing about ADO.NET is it gives SO much
flexibility that a lot of people are doing, um, non-optimal practices with
it. I shudder to think of the ADO.NET code I wrote 1.5 years ago! A good
book to get is _ADO.NET Examples and Best Practices for C# Programmers_.
There's a ADO.NET "cookbook" by O'Reilly which some people seem to like, I
don't have it.

I see what you are saying that your own class might handle new entities
easier. Because you would build your class with the properties you want,
and then its SaveToDB method or whatever could just call a
dataadapter.ExecuteNonQuery, passing the arguments you set up manually.
Simple. This is easier to code because you are working with simpler
technology, just set up the Command object with the parameters and pull the
trigeer. There are no real gotchas going this way, whereas if you want to
use dataadapter.Update on a typed dataset, there is more that you need to
know or you will get into trouble, with non-nullable fields, identity
columns, relational constraints (if you have multitable typed datasets)...
But if you get the hang of working with typed datasets, then they can save
you some work each time you tackle a new table. After you manually set up
the parameters to a dozen or two Command objects, you will be tired of
writing so much plumbing code.

So I guess I would recommend don't use datasets for inserts & updates
(though Fill's are easy shmeasy) until you are comfortable with them. But
if get comfortable with them, by reading a good book or two and looking at
good examples, *then* they can save you some work.

In either case, take a look at Microsoft's Data Application Code Block, if
you haven't already.

There's probably people here that would recommend something completely
different (like sending you off to learn how to use the CodeSmith
templates), so keep that in mind too.

Regards,
Brad Williams

 

[Guest]

Brad, I appreciate your posts. I am getting the grasp of typed datasets and
ADO.Net in general. ADO.Net is much different from ADO, especially doing
updates to the db. I have one more question concerning how to handle
DataRelations between datasets (business entities). In my application I
have something similar to the Order and Order Details tables that Northwind
has. I have two seperate business entities that represents each table. In
the orders table the orderID is auto incremental field and I understand how
to get the field using a stored procedure with out parameters. I have read
if I have a datarelation betwen to the datasets (business entities) that
ADO.Net will automaticallly update each dataset. I understand that I could
create one entity based on Order and Order Details however in my case there
are other business entities that need to map back to Order and Order Details
via the orderID(FK). What is the best way to handle this? Should I create
one large datset that holds the schema for one order (Order, Order Details,
Customer.... tables)? Or should I keep them seperate and create
DataRelations in code? Or should I manually update each key via
delegates/events?

Thanks again for all your help.

 

[Brad Williams]

I have read

if I have a datarelation betwen to the datasets (business entities) that
ADO.Net will automaticallly update each dataset.

Are you talking about when there are two datatables (with identity keys) in
a dataset with a relation between them, then when you insert into both and
Update, the FK in the child will automatically update with the value of the
PK that the db generates? If you are doing this directly on your dataset
(and not a "diffgram" dataset derived via GetChanges and Merge'ed later),
then you may be right, this may work out of the box. I think you still will
want to set the keys in the typed dataset to autoincrement negatively:

<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true"
msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" type="xs:int" />

This article is instructive, but actually more complicated than you need if
you are not using diffgrams:
http://support.microsoft.com/default.aspx?scid=kb;[LN];320301&product=vcSnet

The dataadapter Update event here needs a bunch of stuff because in this
"diffgram" scenario, there are essentially bugs in ADO.NET. But if you
don't do diffgrams, you may have no trouble ... I don't know because I
haven't tried that myself. It should be easy to test: create a typed
dataset with parent and child tables in it, then in code add a new row to
each (such that the new child row refs the new parent row's generated id,
some negative number), then call Update on the parent table, then on the
child table, and see if the FK in the child got set correctly to the
parent's actual PK from db.

For your bigger question, I don't know if it is "best" to go with multiple
tables per dataset, or one per each, I'm sure there are advocates of each
approach. I've been liking multiple tables in just a few typed datasets
lately, for what it's worth. The only pain is that if at some point in your
code all you want to do is select one row, if that row is a child you will
get a constraint exception unless you fill the parent table first -- OR
unless you set EnableConstraints=false on the dataset before the Fill.

Brad Williams

 

[Brad Williams]

Are you talking about when there are two datatables (with identity keys)
in

a dataset with a relation between them, then when you insert into both and
Update, the FK in the child will automatically update with the value of the
PK that the db generates? If you are doing this directly on your dataset
(and not a "diffgram" dataset derived via GetChanges and Merge'ed later),
then you may be right, this may work out of the box.

Indeed it works in a test I did:

OrganizationDS ds = new OrganizationDS();

ds.EnforceConstraints = false;

OrganizationDS.OrganizationRow drOrg = ds.Organization.NewOrganizationRow();
drOrg.Name = "test org";
ds.Organization.AddOrganizationRow(drOrg);

OrganizationDS.FunctionalGroupRow drFunc =
ds.FunctionalGroup.NewFunctionalGroupRow();

drFunc.Name = "test";
drFunc.Comment = "test";
drFunc.Status = "Proposed";
drFunc.OrganizationID = drOrg.ID; // **** drOrg.ID is -1 at this point
ds.FunctionalGroup.AddFunctionalGroupRow(drFunc);

Intel.CCD.Common.DataAccess.SqlService ss = new
Intel.CCD.Common.DataAccess.SqlService();
ss.Connect();

OrganizationDAC dac = new OrganizationDAC();
dac.sqlDataAdapterFunctionalGroup.InsertCommand.Connection = ss.Connection;
dac.sqlDataAdapterOrganization.InsertCommand.Connection = ss.Connection;

dac.sqlDataAdapterOrganization.Update(ds, "Organization");

// **** drOrg.ID is now 50 (value generated by db)
// **** drFunc.OrganizationID is now 50 too, just as we'd like

dac.sqlDataAdapterFunctionalGroup.Update(ds, "FunctionalGroup");

ds.AcceptChanges();