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.
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);