SqlXml - Is it worth the effort?

[Mark Sisson]

Ok gurus, what would you do? I'm developing an app in C# and trying to
use SqlXml but there seems to be a problem at every turn. I want to
create a biz object that represents an order to sell a home. The
database schema for this order has about 15 related tables therefore I
need to also have 15 related biz classes (for my brokers, appraisals,
repairs, offers yada yada yada). The database schema may change
slightly from week to week with possibly new fields being added,
others being lengthed or changed from varchar to text, and possible a
new one-to-many relationship every blue moon or so.

So the requirements are:
1. Biz-specific objects with biz-specific methods
EXAMPLE: Order.AddAppraisal(new Appraisal(xxx));
EXAMPLE: iAppraisalCount = Order.Appraisals.Count;
2. Allow easy loading of the Order object and all it's related biz
objects with one statement
EXAMPLE: myOrder = new Order(iOrderId);
3. Provide single-method update capability to simplify development for
my GUI-tier developers
EXAMPLE: mySale.SaveToDatabase();
4. Minimize the amount of maintenance work to "redefine" the database
schema into XSD's or mapping-schemas.

I know #1 can be satisfied with a typed-dataset and #2 can be done by
inheriting from the Order class built by VS.Net from the typed-dataset
and adding my owning Load and Save routines. But #3 and #4 are
KILLING ME!! I thought that SqlXml was going to come to my rescue but
the more I've learned the more I feel like SqlXml is for amateurs. Can
anyone shed some light on the brick walls that I've been running into
below??

============ PROBLEMS ============
**I'd like to use SqlXml with DiffGrams but does that mean that I've
got to basically redo all the work that I put into designing the
database schema into an xsd mapping-schema with sql:xxx annotations?
What do I need to adorn: FKs, PKs, constraints, what else?

**Why doesn't VS.Net do any of this?

**Are there any tools that add the annotations like sql:field,
sql:relation, sql:relationship etc?

**If I were to build my own utility or add-in that fixed the VS.Net
typed-dataset xsd would that be the right approach? If so, would I
have to go trudging through SQLServer systables (or the
INFORMATION_SCHEMA views) to find all my FK's, PK's, constraints,
defaults, etc.? Am I going to know all of the correct metadata that
is needed by the SQLXML engine to properly annotate my XSD?

**Does SqlXml know how to properly apply deletes/inserts/updates in
the correct order for relational integrity?

**Am I wasting my time with Yukon on the way? When is it coming??
Where can I find info on the Yukon vision? Or is what I'm looking for
in the ADO.NET domain??

**Is the existing SqlXml layer a not-ready-for-prime-time player? Does
it require more work than it's worth? Why not stick to rolling my own
procs to update all of my tables and wrap things inside a transaction?
This seems silly when all of the relationship metadata is already
defined in SQLServer and I've got the entire relational model sitting
in my dataset.

**And how does SqlXml handle the following real-world data updating:
1. Denoting null values in the DiffGram.
2. Reporting errors when any TSQL statements don't work when using
SqlXmlAdapter.Update()
3. Refreshing my DataSet after inserts generate new AutoIncrement
fields.
4. Optimistic updates?

Thanks in advance for any thoughts. I really want to use SqlXml but
this is too painful!!!!!

mark

 

[Bryant Likes]

[snip]

============ PROBLEMS ============
**I'd like to use SqlXml with DiffGrams but does that mean that I've
got to basically redo all the work that I put into designing the
database schema into an xsd mapping-schema with sql:xxx annotations?
What do I need to adorn: FKs, PKs, constraints, what else?

If you want to use DiffGrams, you will need an annotated schema.

**Why doesn't VS.Net do any of this?

I think it will begin to do this in the next version with the mapping files
(annotations are going away).

**Are there any tools that add the annotations like sql:field,
sql:relation, sql:relationship etc?

Xml Spy does this: http://www.altova.com/features_sql.html

**If I were to build my own utility or add-in that fixed the VS.Net
typed-dataset xsd would that be the right approach? If so, would I
have to go trudging through SQLServer systables (or the
INFORMATION_SCHEMA views) to find all my FK's, PK's, constraints,
defaults, etc.? Am I going to know all of the correct metadata that
is needed by the SQLXML engine to properly annotate my XSD?

Not sure what you mean here...

**Does SqlXml know how to properly apply deletes/inserts/updates in
the correct order for relational integrity?

I think diffgrams are the only SqlXml option that provides this.

**Am I wasting my time with Yukon on the way? When is it coming??
Where can I find info on the Yukon vision? Or is what I'm looking for
in the ADO.NET domain??

There are lots of good things on the way when it comes to SqlXml.
Yukon: http://www.microsoft.com/sql/yukon/productinfo/
Whidbey: http://msdn.microsoft.com/vstudio/whidbey/

**Is the existing SqlXml layer a not-ready-for-prime-time player? Does
it require more work than it's worth? Why not stick to rolling my own
procs to update all of my tables and wrap things inside a transaction?
This seems silly when all of the relationship metadata is already
defined in SQLServer and I've got the entire relational model sitting
in my dataset.

Some parts of SqlXml definately require a lot of work. You have to decide if
it is worth it.

**And how does SqlXml handle the following real-world data updating:
1. Denoting null values in the DiffGram.
2. Reporting errors when any TSQL statements don't work when using
SqlXmlAdapter.Update()
3. Refreshing my DataSet after inserts generate new AutoIncrement
fields.
4. Optimistic updates?

I haven't spent a lot of time using the Diffgram/DataSet approach. Most of
the SqlXml work that I do now consists of using Xml Serialization to map
data from SQL Server to business objects.