After writing
"Retrieving Objects from SQL Server Using SQLXML and Serialization"
I'm exploring
opportunities of saving back objects to database, using a schema definition file (that we designed in
the previous article), and another technique that is included with SQLXML called UPDATEGRAMS.
This article will describe this technique as designed from Microsoft; go through the creation of an
UPDATEGRAM class that use serialization and custom class attributes to form appropriate xml streams
can be sent to SQLSERVER to perform automatic generated statements.
I will start from an overview of UPDATEGRAMS feature of Microsoft SQLXML that you can find in the
help online when you install SQXML libraries on your machine, or on the MSDN site
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_5kkh.asp)
I will resume the concepts here saying that UPDATEGRAMS are xml documents that together with an xml
schema definition are used by SQLXML libraries to automate commands creation and execution on SQLSERVER
to update, insert and delete records.
Each UPDATEGRAM is mainly formed by a sequence of SYNC elements; each of them is a set of operations
that will be executed in the same transaction context.
Each SYNC element can contain BEFORE and AFTER elements for each set of operations to be executed.
BEFORE elements are used to locate or create records and AFTER elements hold changes to records.
So if you provide an empty BEFORE element you are performing an insert.
On the other hand, an empty AFTER is a delete operation.
When both of them are present, SQLXML will generate something like "UPDATE AFTER where BEFORE",
and this is the scenario that we'll focus on.
Now, let's go back to the windows application of the previous article and add a new form to change
some properties and update data back to SQLSERVER of any given Order.
add double click handler and the code to open the form from the grid form:
private void grd_orders_DoubleClick(object sender, EventArgs e)
{
Form f = new OrderForm(this.orders[this.grd_orders.CurrentRowIndex]);
f.Visible=true;
}
in the load event bind the order to the controls...
private void OrderForm_Load(object sender, System.EventArgs e)
{
this.lblOrderNum.Text = this.order.OrderID.ToString();
//we don't want null dates, instead the control will be set to current or default
if(this.order.OrderDate!=DateTime.MinValue)
this.dtpOrderDate.Value = this.order.OrderDate;
if(this.order.RequiredDate!=DateTime.MinValue)
this.dtpRequiredDate.Value = this.order.RequiredDate;
this.txtShipAddress.Text = this.order.ShipAddress;
this.txtShipCity.Text = this.order.ShipCity;
this.txtShipName.Text = this.order.ShipName;
this.txtShipZip.Text=this.order.ShipPostalCode;
this.txtShipCountry.Text = this.order.ShipCountry;
}
Now that you can see the Order ready to be edited let's start with the save button.
We need to write a method (like getChanges()) to get values from controls in the Form and set them to the Order object.
Our target is to generate an UPDATEGRAM, with the old order as the before node and the changed order as
the after node, send it to SQLSERVER and the update should be automatic.
First of all we need to define a method of having the before state (after state will be the current state of the object).
I decided to use binary serialization to clone and compare object and this is my little helper class that
will be used by base classes of entities in the project...
public class CloneHelpers
{
public static object DeepClone(object source)
{
MemoryStream m = new MemoryStream();
BinaryFormatter b = new BinaryFormatter();
b.Serialize(m, source);
m.Position = 0;
return b.Deserialize(m);
}
public static bool DeepEquals(object objA,object objB)
{
MemoryStream serA = serializedStream(objA);
MemoryStream serB = serializedStream(objB);
if(serA.Length!=serA.Length)
return false;
while(serA.Position<serA.Length)
{
if(serA.ReadByte()!=serB.ReadByte())
return false;
}
return true;
}
public static MemoryStream serializedStream(object source)
{
MemoryStream m = new MemoryStream();
BinaryFormatter b = new BinaryFormatter();
b.Serialize(m, source);
m.Position = 0;
return m;
}
}
Let's define the base business classes then:
[Serializable]
public abstract class BaseBusinessEntity
{
public object DeepClone()
{
return CloneHelpers.DeepClone(this);
}
public bool DeepEquals(object obj)
{
return CloneHelpers.DeepEquals(this,obj);
}
}
[Serializable]
public abstract class BaseBusinessEntityCollection : CollectionBase
{
public object DeepClone()
{
return CloneHelpers.DeepClone(this);
}
}
In the order form you can now have code and properties to define the initial state of the entity
that you are working on:
public OrderForm(OrdersMgmt.Order ord)
{
//get the order from the grid form
this.order=ord;
//set the initial state
this.orderBefore = (OrdersMgmt.Order) ord.DeepClone();
At this point you might say: let's serialize the object to xml in his initial state to make the Before node,
then when properties of object are changed re-serialize the object to xml to obtain the After node, and
that is...
Let's have a look at a way of abstracting the UPDATEGRAM generation, and design a class to hold properties
to correctly form and run the xml stream.
We instantiate this class with the most important property: the XSD file.
public updategram(string schemaFile)
{
//set the current schema to use when sending updategram to SQLSERVER
this.schemaFilePath = schemaFile;
//initialize the state of the updategram and open the xml document
this.InitUpdgDocument();
this.syncCollection = new Hashtable();
}
The InitUpdgDocument() will start the xml document (I'm using the document object for simplicity I
leave optimizations to your skills):
private void InitUpdgDocument()
{
if(this.updgDocument==null)
{
//create empty updategram
updgDocument = new XmlDocument();
updgDocument.LoadXml("<ROOT xmlns:"+prefix+"=\""+ns+"\"></ROOT>");
}
}
The syncCollection is holding all objects that must be modified with their initial state as key and in the
actual state as value. Now that we have the UPDATEGRAM and the initial state for the form we can see what
happen when we click the save button.
private void btnUpdate_Click(object sender, System.EventArgs e)
{
//get form values and set entity values
this.getChanges();
//compare to object and create new sync node if something changed
updg.Process(this.orderBefore,this.order);
//commit all sync nodes created
updg.Commit();
//set new initial state
this.orderBefore = (OrdersMgmt.Order) this.order.DeepClone();
//this.Close();
}
In the getChages() operation I set order values from controls, then I call the process method of UPDATEGRAM
to compare object and insert a new key/value entry in syncCollection if the two states are different. In
this case we are working only on one object but changing multiple objects can happen without problems.
Once all updates can be committed we call the Commit method of updategram that will do the actual update
work on sql server let's have a look...
public void Commit()
{
//cycle all syncs with original keys
foreach(object objOriginal in this.syncCollection.Keys)
{
//get reference to changed object
object objChanged = this.syncCollection[objOriginal];
//initialize a the serializer provider
UpdgXSerializer x = new UpdgXSerializer(objOriginal,objChanged);
//get the serializer for the before element
this.beforeSerializer = x.getUpdgSerializer(UpdategramElement.Before);
//get the serializer for the after element
this.afterSerializer = x.getUpdgSerializer(UpdategramElement.After);
//create the sync element
this.createSync();
//create the before element
this.BeginUpdate(objOriginal);
//create the after element
this.EndUpdate(objChanged);
}
//verify that we have a document to submit
if(this.updgDocument==null)
return;
//create the stream with the xml document
MemoryStream ms;
ms = new MemoryStream();
this.updgDocument.Save(ms);
ms.Position = 0;
try
{
//execute the xml stream
XmlReader results = sqlxmlHelper.executeUpdateGram(this.schemaFilePath,ref ms);
}
catch(Exception ex)
{
throw new Exception("Error while committing operations to DB, "+ex.Message,ex);
}
finally
{
//clean up after submit
ms.Close();
}
this.syncCollection = new Hashtable();
updgDocument = null;
}
The default serialization will form xml containing all Order Lines one after the other as child nodes...
<updg:before>
<Order>
<OrderID>10250</OrderID>
...
<OrderLines>
<OrderDetail>
<Quantity>10</Quantity>
<Discount>0</Discount>
<Item>
<ProductID>41</ProductID>
<ProductName>Jack's New England Clam Chowder</ProductName>
<UnitPrice>9.65</UnitPrice>
</Item>
<OrderID>10250</OrderID>
<ProductID>41</ProductID>
</OrderDetail>
<OrderDetail>
<Quantity>35</Quantity>
<Discount>0.15</Discount>
<Item>
<ProductID>51</ProductID>
<ProductName>Manjimup Dried Apples</ProductName>
<UnitPrice>53</UnitPrice>
</Item>
<OrderID>10250</OrderID>
<ProductID>51</ProductID>
</OrderDetail>
when sent to SQMXML will generate this error:
so for each node (objects) we need to have an index or id to supply updg:id attribute.
I took the quickest way by giving Order Detail object a read/write property with an attribute defining
the way should be named and look in xml.
[XmlAttribute(AttributeName="id",Namespace="urn:schemas-microsoft-com:xml-updategram")]
public int OrderIDKey
{
get
{
return this.ProductID;
}
set
{
}
}
In this case the productID is a good id as it is unique for details inside an order, and the empty set
is needed to make the property read/write, otherwise it would not be serialized in xml.
I'll do the same for order object to enable multiple orders batch update:
[XmlAttribute(AttributeName="id",Namespace="urn:schemas-microsoft-com:xml-updategram")]
public int OrderIDKey
{
get
{
return OrderID;
}
set
{
}
}
Now we can inspect xml for the new UPDATEGRAM with attributes for order and order detail:
<Order updg:id="10250">
<OrderLines>
<OrderDetail updg:id="41">
in this case everything goes all right when sent to SQLSERVER.
But as you should know nothing in IT is stable from the first time for ever so let's look at few other cases
where the default serialization does not work and how to solve the problem.
First null values handling. Everyone working with input fields in web forms has experienced the problem of
a user cleaning a field and a blank not null value is stored in the database for string types or a default
value for numbers or dates. Also a null property is serialized with an empty element, so SQLXML will not
update that column.
NULL handling in SQLXML is described in this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_12p3.asp
So you can use a default value defined at xml root element (in the example I use updg:nullvalue="|isnull|") then all elements containing that value will be updated with a DBNULL.
I've chosen to use a public field to hold all property names that need to be nullified.
[XmlArray("nullProps"),XmlArrayItem("prop")]
public string[] nullProps;
Expose a method for adding a property name to the list :
public void nullProperty(string propertyName)
{
foreach(PropertyInfo pInfo in this.GetType().GetProperties())
{
if(pInfo.Name==propertyName)
{
if(nullProps!=null && Array.IndexOf((Array)nullProps,propertyName)==-1){
string[] tmp = (string[]) nullProps.Clone();
nullProps = new string[tmp.Length+1];
nullProps[0] = propertyName;
tmp.CopyTo(nullProps,1);
}
else
nullProps = new string[]{propertyName};
break;
}
}
}
in the form getChanges() method will be easy to handle inserted null values like here below:
if(this.txtShipZip.Text==string.Empty)
this.order.nullProperty("ShipPostalCode");
if(this.txtShipCountry.Text==string.Empty)
this.order.nullProperty("ShipCountry");
let's have a look at writeAfterXML operation of UPDATEGRAM class where we create the After Node and set null values:
private void writeAfterXML(ref object obj,ref XmlElement after)
{
MemoryStream ms;
StreamReader sr;
ms = new MemoryStream();
XSerializer.serialize(ref ms
,obj,this.afterSerializer);
sr = new StreamReader(ms);
sr.BaseStream.Position = 0;
//remove declaration
after.InnerXml = sr.ReadToEnd().Remove(0,23);
//set null values
//get the list of properies to set DBNULL for each object at any level
XmlNodeList list = after.SelectNodes("//*[nullProps]");
foreach(XmlNode node in list)
{
//select the list of properties
XmlNodeList propsToNull = node.SelectNodes("nullProps/prop");
foreach(XmlNode prop in propsToNull)
{
XmlNode property = node.SelectSingleNode(prop.InnerText);
if(property!=null)
property.InnerText = UPDG_NULL_CODE;
}
//remove nullProps node to avoid sqlxml conflict
node.RemoveChild(node.SelectSingleNode("nullProps"));
}
//remove empty elements
list = after.SelectNodes("//*[.='']");
foreach(XmlNode node in list)
{
node.ParentNode.RemoveChild(node);
}
sr.Close();
ms.Close();
}
In the writeBeforeXML it's just a matter of removing properties to null nodes.
I did a test on one order, deleting zip & country, saving and this is the result:
Another issue is respecting relational data integrity and lookups value, let's say that we remove from the Order the first OrderLine, with this line of code
this.order.OrderLines.RemoveAt(0);
the subsequent xml serialization in the AFTER node will miss the deleted order line, and that's obvious,
but it's not evident that this will cause deletion of not only Order Detail but related Products too,
which in the case of Northwind database, where relationships are well defined will throw an exception
(see figure below).
Product is referenced by other order lines and no cascade action is performed when products are deleted. In
another case where no relationship is present you need to find a way of not including this part of object
in UPDATEGRAM. I did some meditation to decide a method of excluding these properties and I decided to
go for customizing the way the object is serialized.
So I created first an attribute that would sign properties to be excluded from xml serialization:
public class LookupAttribute:System.Attribute
{
public LookupAttribute() {}
}
then I thought about those properties that we need for sure all the time (like primary/foreign keys) and I created a second attribute :
public class IntegrityCheckAttribute:System.Attribute
{
public IntegrityCheckAttribute() {}
}
Now that I have properties defined like the Product in OrderDetail:
[Lookup]
public Product Item
and all the primary/foreign keys defined like the OrderID in Order:
[IntegrityCheck]
public int OrderID
I can create a custom Serializer for the objects that will exclude from xml serialization the marked properties depending at which state the objects are.
Please read the MSDN article that describes XmlAttributeOverrides class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemxmlserializationxmlattributeoverridesclasstopic.asp
Here the constructor:
public UpdgXSerializer(object objBefore,object objAfter)
{
//hold reference to before state
this._Before=objBefore;
//hold reference to after state
this._After=objAfter;
}
then the main operation that return the Serializer object for the UDATEGRAM node:
public XmlSerializer getUpdgSerializer(UpdategramElement updgElement)
{
//init. the collection of overrides
XmlAttributeOverrides myOverrides = new XmlAttributeOverrides();
Type objType=null;
//choose the right object
switch(updgElement)
{
case UpdategramElement.Before:
//parse the object to retrive attribute overrides
this.parseType(this._Before,ref myOverrides,updgElement);
objType = this._Before.GetType();
break;
case UpdategramElement.After:
this.parseType(this._After,ref myOverrides,updgElement);
objType = this._After.GetType();
break;
}
//advanced serializer with attribute overrides
XmlSerializer retVal = new XmlSerializer(objType, myOverrides);
return retVal;
}
the parseType private call start the actual job on finding those properties that we need to hide..
private void parseType(object source,ref XmlAttributeOverrides xmloverrides,
UpdategramElement updgElement)
{
Type objType = source.GetType();
// Iterate through all the properties of the class
foreach(PropertyInfo pInfo in objType.GetProperties())
{
//get the attribute overrides for the property
getAttributesForProperty(pInfo,ref xmloverrides,objType,updgElement);
if(typeof(IList).IsAssignableFrom(pInfo.PropertyType))
{
//when we have non empty collection we cyle iterate the parsing operations
IList val = (IList) pInfo.GetValue(source,null);
if(val !=null && val.Count>0)
{
parseType(val[0],ref xmloverrides,updgElement);
}
}
}
}
getAttributesForProperty private call do the actual insert of attribute override...
private void getAttributesForProperty(
PropertyInfo pInfo,
ref XmlAttributeOverrides xmloverrides,
Type objType,
UpdategramElement updgElement)
{
// need this as we start from the assumption that in the Before we need the
// minimum in the After we need the more
bool ignore = false;
if(updgElement==UpdategramElement.Before)
{
ignore = true;
}
// Iterate through all the Attributes for each property.
foreach (Attribute attr in Attribute.GetCustomAttributes(pInfo))
{
Type tmp = attr.GetType();
//remove from before the lookup values and leave the primary/foreign keys
if(updgElement==UpdategramElement.Before)
{
if(tmp==typeof(IntegrityCheckAttribute))
{
ignore = false;
}
if(tmp==typeof(LookupAttribute))
ignore = true;
}
//remove from after node the lookup attribute (if it's not an insert)
else if(updgElement==UpdategramElement.After)
{
if((tmp==typeof(LookupAttribute)) && this._Before!=null)
ignore=true;
}
}
if(ignore)
{
XmlAttributes myAttributes = new XmlAttributes();
myAttributes.XmlIgnore = true;
xmloverrides.Add(objType,pInfo.Name,myAttributes);
}
}
If you follow comments you should be able to understand the logic, and then see the final UPDATEGRAM for the Order object including the deletion of the first order line:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:nullvalue="|isnull|">
<updg:before>
<Order updg:id="10250">
<OrderID>10250</OrderID>
<OrderLines>
<OrderDetail updg:id="41">
<OrderID>10250</OrderID>
<ProductID>41</ProductID>
</OrderDetail>
<OrderDetail updg:id="51">
<OrderID>10250</OrderID>
<ProductID>51</ProductID>
</OrderDetail>
<OrderDetail updg:id="65">
<OrderID>10250</OrderID>
<ProductID>65</ProductID>
</OrderDetail>
</OrderLines>
</Order>
</updg:before>
<updg:after>
<Order updg:id="10250">
<OrderID>10250</OrderID>
<OrderDate>1996-07-05T00:00:00.0000000+02:00</OrderDate>
<RequiredDate>1996-08-07T00:00:00.0000000+02:00</RequiredDate>
<ShippedDate>1996-07-12T00:00:00.0000000+02:00</ShippedDate>
<Freight>65.83</Freight>
<ShipName>Hanari Carnes</ShipName>
<ShipAddress>Rua do Paulo, 69</ShipAddress>
<ShipCity>Rio de Janeiro</ShipCity>
<ShipPostalCode>283458</ShipPostalCode>
<ShipRegion>RJ</ShipRegion>
<ShipCountry>test</ShipCountry>
<OrderLines>
<OrderDetail updg:id="51">
<Quantity>35</Quantity>
<Discount>0.15</Discount>
<OrderID>10250</OrderID>
<ProductID>51</ProductID>
</OrderDetail>
<OrderDetail updg:id="65">
<Quantity>15</Quantity>
<Discount>0.15</Discount>
<OrderID>10250</OrderID>
<ProductID>65</ProductID>
</OrderDetail>
</OrderLines>
</Order>
</updg:after>
</updg:sync>
</ROOT>
I think this prototype can be improved in many parts, like the last one that we faced, with a better comparison of the 2 object states and a better clean up of xml, or maybe you want to use the all the properties in both Before and After nodes to achieve something like dataset internal DiffGram way of preserving data integrity in disconnected environments, or maybe you can use timestamps for this.... it's really up to you and your implementation, the high level image here is that we have defined a draft framework that automate at entity level database read and write operations on SQLSERVER with SQLXML and the .Net Framework.
Download Source Code
