Could someone explain to me why this doesn't work? I've been reading
newsgroup posting all freakin' day and this is actually an example
from some site (sorry don't remember where, it's been too long!!)

--------------------------------------
string constr = @"Provider=SQLOLEDB;Data Source=LAPTOP\LAPTOP; User
ID=xxx; Password=xxx; Database=Northwind";
SqlXmlAdapter adapter = new SqlXmlAdapter("SELECT CategoryId,
CategoryName from Categories FOR XML AUTO", SqlXmlCommandType.Sql,
constr);
DataSet ds = new DataSet();
adapter.Fill( ds );
ds.Tables[0].Rows[0][1] = "Zooooooo";
adapter.Update( ds ); <<<<ERROR HAPPENS HERE
------------------------------------------
Error: "Diffgram must have a mapping schema associated with it".

I understand XSD schemas and have seen tons of examples using them
with SqlXmlCommands but can't I accomplish the above without creating
an XSD? Is that supposed to be the beauty of SqlXmlAdapters?????

Bigger question:
My ultimate goal is to load a dataset with several tables that are
related by foreign keys in SQL presenting several one-to-many
relationships. I'd like to be able to ins/upd/del rows in any of the
tables in my DataSet and then be able to post the changes to the
database. Am I heading down the right path by using SqlXml in .NET?
I have about 14 related tables and probably about 150 columns total.
I'm trying to avoid creating an XSD schema in VS.NET and then manually
adorning it with sql:field / sql:relation annotations that will just
have to be updated every time I change the database. WHAT'S THE BEST
PRACTICE HERE?????

TIA
mark

Sorry to disappoint you, but you can only update through a SqlXmlAdapter
that has a schema associated with it. Your best bet is to create an
annotated schema and use it with the SqXmlAdapter. If you frequently change
the structure of your database then I can see that this will cause problems
for you (though I'm wondering why you'd do this in the first place.)
The other option is to write code to create diffgrams or updategrams and
send your modifications that way - but that will mean a lot more code on the
client side (and you'll probably need to change it if you modify your
database schema.)

As for best practice. well, try not to have to modify your database schema
;-)

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Mark Sisson" <mark (AT) corporatedigital (DOT) com> wrote

Thanks for the reply Graeme.

Ok - well, so what we've got is an XSD schema that has to be
constantly refreshed manually anytime there's a change in the
database. Well if that's true then it is what it is and I'll just
hope that Yukon's new XML engine can help out later. This seems
really redundant given that the schema is already defined in the
database - why should I have to recreate it manually and run the risk
of fat fingering things?

Also, I thought I read that if your elements and child elements are
the same names and your tables and columns that mappings were
unnecessary? No? I thought I got that impression and maybe all that
was needed was to add the
xmlns:sql="urn:schemas-microsoft-com:mapping-schema" definition at the
top of your xsd.

Regarding your advice on the data model, I've been architected
databases in SQLServer since it ported from Sybase so I appreciate the
merits of a static schema. Unfortunately this project has a VERY
SHORT development time and the data requirements will be very static
possibly changing for several weeks or months even after we go live.
Unfortunate but true.

Isn't there anyway to have my XSD sync up with the data model without
my constant handholding? Do I need to write a function that reads the
schema and mocks up my XSD?

Further worries down the road: How does SQLXML do with the following?
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? How do I ensure that it's making sure I'm
updating rows that haven't changed since I grabbed then (no
pessimistic locking allowed)



So is SQLXML just not ready for the big time? Do I need to create and
Ins/Upd/Del/Select proc for all 14 of my core tables? Then create 56
SqlCommand objects in my Biz Object? Then call SqlbuildCommand 56
times to get the latest params for these procs because I don't want to
recompile everytime a proc changes? THERE'S GOT TO BE A BETTER WAY
HERE!!!!!!

TIA

If element/attribute names match table/column names, then yes - you can omit
the annotations, but only for flat XML (i.e. you can't use this technique if
there are relationships - otherwise how would SQLXML know what key to use in
the join?)

Yukon will replace annotated schemas with a 3-file mapping arrangement (1
file for the XSD, one that defines the DB schema, and one that maps one to
the other). From your point of view I suspect that this would be worse
because you'd have to update both the DB Schema file and the mapping file
when you change your database schema.

I think it might be a bit harsh to say SQXML isn't ready for the big time -
in many ways it can be a bit clunky but it does have a lot of great
functionality that lots of people are using in production. However, I think
it's probably fair to say that it doesn't seem to meet your particular needs
in this case without a certain amount of additional work on your part.

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


"Mark Sisson" <mark (AT) corporatedigital (DOT) com> wrote