Test-Drive SQL Server Data Services: C# 3.0, LINQ to XML: Autogenerate REST XML Entity Templates: Listing 1

A SqlDataReader instance and LINQ to XML functional construction combine to enable autogenerating the XML payload for the help file's sample CreateEntity() function from table metadata. Much of the code in this snippet, which is excerpted from the sample app's CreateEntitySet( ) method for the REST protocol, is devoted to replacing .NET data type designations with SSDS's more limited XML Schema type repertoire.

• 06/23/2008

string setXml = null;
SqlConnection connNwind = new SqlConnection(strNwind)
SqlCommand cmdNwind = new SqlCommand("SELECT * 
   FROM [" + tableName + "]", connNwind)
connNwind.Open();
SqlDataReader sdrNwind = cmdNwind.ExecuteReader(
   CommandBehavior.SequentialAccess);
while (sdrNwind.Read())
{
   // Create an XElement with XAttributes for each column
   var entElement = new XElement(new XElement(entityType,
         new XAttribute(XNamespace.Xmlns + "s", 
         "http://schemas.microsoft.com/sitka/2008/03/"),
         new XAttribute(XNamespace.Xmlns + "xsi", 
         "http://www.w3.org/2001/XMLSchema-instance"),
         new XAttribute(XNamespace.Xmlns + "x", 
         "http://www.w3.org/2001/XMLSchema"),
      from col in Enumerable.Range(0, sdrNwind.FieldCount)
      select new XElement(sdrNwind.GetName(col),
         new XAttribute("xsi:type", "x:" + 
         sdrNwind.GetFieldType(col).ToString().ToLower()),
         sdrNwind.GetValue(col))));
   setElement.Add(entElement);
   sdrNwind.Close();
   setXml = setElement.ToString();
   // String identifier change
   setXml = setXml.Replace('\"', '\'');
   // Type fixups with "x:system." prefix for specificity
   setXml = setXml.Replace("x:system.uint64", 
      "x:decimal");
   setXml = setXml.Replace("x:system.uint32", 
      "x:decimal");
   setXml = setXml.Replace("x:system.uint16", 
      "x:decimal");
   setXml = setXml.Replace("x:system.int64", 
      "x:decimal");
   setXml = setXml.Replace("x:system.int32", 
      "x:decimal");
   setXml = setXml.Replace("x:system.int16", 
      "x:decimal");
   setXml = setXml.Replace("x:system.byte[]",
      "x:base64Binary");
   setXml = setXml.Replace("x:system.byte", 
      "x:decimal");
   setXml = setXml.Replace("x:system.sbyte", 
      "x:decimal");
   setXml = setXml.Replace("x:system.single", 
      "x:decimal");
   setXml = setXml.Replace("x:system.double", 
      "x:decimal");
   setXml = setXml.Replace("x:system.decimal", 
      "x:decimal");
   setXml = setXml.Replace("x:system.datetime", 
      "x:dateTime");
   setXml = setXml.Replace("x:system.boolean",
      "x:boolean");
   setXml = setXml.Replace("x:system.char", "x:string");
   setXml = setXml.Replace("x:system.guid", "x:string");
   setXml = setXml.Replace("x:system.string", 
      "x:string");
   // Special case: Temporarily remove elements 
   // with nil values
   setXml = setXml.Replace("<ShippedDate 
      xsi:type='x:decimal'></ShippedDate>", "");
}
// Code to remove empty elements for value types goes here
// ...
// POST the entity using sample code from SSDS help file

CreateEntity(containerUri, setXml);