Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
260
Annotating .NET Updategrams
In certain cases, we have to provide more information than just the elements
in the <before> and <after> sections for the SQLXML engine to
compose the correct SQL statement. When we need to set a database field to NULL
for example, simply omitting the corresponding element or attribute from the
Updategram will not set the field to NULL. Instead the value in the database
remains unchanged because leaving the field we need to set out of the
Updategram only causes the value to be excluded from the generated SQL
statement.
To let SQLXML know that we want to set a field to NULL, we need to define
which value in the Updategram represents NULL in the database and include it in
the updategram. We define how NULL is represented in the updategram, by adding
the nullvalue annotation to the <sync> element. Now we can reference the
defined nullvalue in the updategram anywhere we need compare against or set
something to NULL. The following snippet demonstrates the use of the nullvalue
attribute to assign a value to WebSite field that was previously NULL:
Other situations where we need to annotate Updategrams, are retrieving
automatically generated IDENTITY values and GUIDs and updates of designated key
fields. Table 15.7 lists all annotation attributes for recognized by SQLXML.
Section 15.3.4.4 discusses all issues around identity inserts in more detail.
1.7
Annotation to updategrams provide meta information about the XML document
beyond what is in the mapping schema. The updategram processing engine reads
these annotations to generate the translate the updategrams to SQL statements
correctly.
Annotation
Valid On
Description
at-identity
Inserted element or attribute
Defines a place holder to receive SQL Server generated INDENTITY values.
Other elements and attributes can reference the value by the placeholder.
guid
Inserted element
Defines a place holder for a GUID generated when the updategram executes.
id
Inserted element
Helps the updategram engine to track corresponding elements in the before
and after sections of a sync block. Corresponding elements must carry the
same id if the mapping schema does not identify the key-fields or if the
updategram modifies a key field.
nullvalue
<sync>, <header>
Identifies the character sequence the update processor will treat as NULL.
returnid
<after>
Lists the SQL Server generated identities to return. The listed
identifiers must match identity placeholders defined in at-identity
annotations.
I mentioned that an Updategram is nothing but a special Template format.
This implies that we can also work with parameters in Updategrams just like we
can in Templates. The parameters are defined in the optional <header>
section and can be referenced throughout the rest of the Updategram, as shown
in the following example:
Parameterized Updategrams are useful in situations where we do not want to
compose an Updategram on the fly or when we want to maintain a higher level of
control over an Updategram’s content. Another benefit of parameterized
templates is the fact that we can keep the templates outside the compiled code,
for example in resource DLLs or regular files, so we can perform minor bug
fixes or simple updates without the need to recompile any code.
