Post date: Feb 22, 2010 8:13:34 AM
Loading big sets of data from the database can be a resource hog. Especially if you need to have the data in a hierachical structure like an xml or a series of datasets. You don't want to use loops in your C# to fetch child data from the database, that would be horrible.
This is what i used when i had to create a complex XML result from a single stored procedure. The result was to be sent from a webservice so it had to be checked against an XSD schema.
Note: All of this is wasted if all you want is the XML and using SQL 2005 Server or later. Then you would just use the XML datatype/functionallity.
Lets begin:
Add New Item -> XML Schema OrderHistory.xsd
In properties window for the newly added file, set Custom Tool to MSDataSetGenerator
Configure the schema for your needs using text or design mode.
<?xml version="1.0" encoding="utf-8"?><xs:schema id="OrderView" targetNamespace="http://tempuri.org/OrderView.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/OrderView.xsd" xmlns:mstns="http://tempuri.org/OrderView.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified"> <xs:element name="Order"> <xs:complexType> <xs:sequence minOccurs="1" maxOccurs="unbounded"> <xs:element name="Summa" type="xs:float" /> <xs:element name="OrderDate" type="xs:int" /> <xs:element name="CustomerID" type="xs:int" /> <xs:element name="OrderID" type="xs:int" /> <xs:element name="OrderLines" minOccurs="1" maxOccurs="unbounded"> <xs:complexType> <xs:sequence minOccurs="1" maxOccurs="unbounded"> <xs:element name="ProductCode" type="xs:string" /> <xs:element name="Qty" type="xs:float" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element></xs:schema>
Set up the code to get the data into the generated dataset:
using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Data.SqlClient;namespace ConsoleApplication1
{ class Program
{ static void Main(string[] args) { //Ett(); Tva(); } private static void Tva() { OrderView ds = new OrderView(); ds.EnforceConstraints = false; ExecuteStoredProcedure( ds, "dbo.GetOrdersTest2", new string[] { }, new object[] { }, new DataTableMapping[] { new DataTableMapping("Table", "Order", new DataColumnMapping[] {}), new DataTableMapping("Table1", "OrderLines", new DataColumnMapping[] {}), }, -1 ); ds.WriteXml(@"c:\temp\testar2.xml"); //ds.EnforceConstraints = true; //ds.EnforceConstraints = true; } public static void ExecuteStoredProcedure(DataSet ds, string storedProcedureName, string[] paramNames, object[] paramValues, DataTableMapping[] dataTableMappings, int timeOut) { SqlDataAdapter da; using (SqlConnection con = new SqlConnection("Data Source=.;Database=OrderSystem;User ID=;Password=;Trusted_Connection=False;")) { using (SqlCommand cmd = new SqlCommand(storedProcedureName, con)) { da = new SqlDataAdapter(); da.SelectCommand = cmd; da.TableMappings.Clear(); da.TableMappings.AddRange(dataTableMappings); cmd.CommandType = CommandType.StoredProcedure; if (timeOut > 0) cmd.CommandTimeout = timeOut; else cmd.CommandTimeout = 45; for (int i = 0; i < paramNames.Length; i++) { if (paramValues[i] == null) cmd.Parameters.AddWithValue(paramNames[i], DBNull.Value); else cmd.Parameters.AddWithValue(paramNames[i], paramValues[i]); } da.Fill(ds); } } } }}
Then create the stored procedure to fetch both these DataTables needed.
create procedure [dbo].[GetOrdersTest2]asbegincreate table #order( Id_row INT IDENTITY, OrderId INT, summa FLOAT, OrderDate int, CustomerId INT )INSERT INTO #ORDERSELECT DISTINCT ord.o02, -- OrderId NULL, -- summa NULL, -- OrderDate NULL -- CustomerId FROM orders ord
WHERE nr > 1019664update #orderset summa = 552.20
, OrderDate = ord.o03
, CustomerId = ord.o01
from
orders ord
where
ord.o02 = #order.OrderID
--[Xml parent Node]: Order Order_Id is a magic column, more on that below.select OrderId as Order_Id, Summa as Summa, OrderDate as OrderDate
from #order--[Xml parent Node]: OrderLines, you need that magic column Order_Id here aswell. That column will reference to the Order object in the dataset.select distinct OrderId as Order_Id, 'Candy' as ProductCode, 14 as Qty
from #orderend
There are two DataTables returned from this procedure, each marked with the [Xml Parent node]-tag. Each of those DataTables include a column named Order_Id, but that column is not part of our XSD so what does it do?
The Order_Id column is a hidden autogenerated column in the OrderViewDataset that have a unique constraint put on it. It is used as an internal key in the Dataset to identify relationships and dependencies between DataTables. If you provide a column from the stored procedure with that name then the value from the procedure will be used to maintain the relationships.
Since it is hidden, it will now show up in the XML Output. Since it need to be unique from the database i have created a temporary table with an identity column that i use for the Order_Id.
Imagine you want to include some deeper dependencies like Order/BillingInformation/BillToInfo, where BillToInfo should contain some address. Then you need to create another temporary table and provide another "key" column: BillingInformation_Id and reference that in the BillToInfo-datatable. An example like this is included in the attachements!
<?xml version="1.0" standalone="yes"?><OrderView xmlns="http://tempuri.org/OrderView.xsd"> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>Banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100224</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100309</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order> <Order> <Summa>552.2</Summa> <OrderDate>20100309</OrderDate> <OrderLines> <ProductCode>banana</ProductCode> <Qty>14</Qty> </OrderLines> </Order></OrderView>