Order Importer - Retrive Orders Page

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">

   protected void Page_Load(object sender, System.EventArgs e)

   {

       OleDbDataAdapter Da;

       //create dataset and set its namespace

       DataSet DsOrders = new DataSet("DsOrders");

       DsOrders.Namespace = "http://www.tempuri.org/DsOrderInfo.xsd";

       //connect to database

       OleDbConnection Conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Request.PhysicalApplicationPath + "\\DemoData\\SageDemoData.mdb;Persist Security Info=False");

       Conn.Open();

       //get customer records

       Da = new OleDbDataAdapter("SELECT * FROM Customers WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Orders WHERE ExportedToSage=0)", Conn);

       Da.Fill(DsOrders, "Customers");

       //get order records

       Da = new OleDbDataAdapter("SELECT * FROM Orders WHERE ExportedToSage=0", Conn);

       Da.Fill(DsOrders, "Orders");

       //get order item records

       Da = new OleDbDataAdapter("SELECT * FROM OrderItems WHERE OrderID IN(SELECT DISTINCT OrderID FROM Orders WHERE ExportedToSage=0)", Conn);

       Da.Fill(DsOrders, "OrderItems");

       //disconnect from database

       Conn.Close();

       //clear any response data prior to outputing any XML

       Response.Clear();

       //begin outputing XML

       Response.Write("<?xml version=\"1.0\" standalone=\"yes\" ?>\n");

       Response.Write("<DsOrders xmlns=\"http://www.tempuri.org/DsOrderInfo.xsd\">\n");

       foreach(DataRow Dr in DsOrders.Tables["Customers"].Rows)

       {

           Response.Write("\t<Customers>\n");

           //Required Fields

           AddFieldToXML("CustomerID", Dr["CustomerID"]);

           AddFieldToXML("ContactName", Dr["ContactName"]);

           AddFieldToXML("Address1", Dr["Address1"]);

           AddFieldToXML("Town", Dr["City"]);

           //Optional Fields (Used)        

           AddFieldToXML("CompanyName", Dr["Company"]);

           AddFieldToXML("Address2", Dr["Address2"]);

           AddFieldToXML("County", Dr["County"]);

           AddFieldToXML("PostCode", Dr["PostCode"]);

           AddFieldToXML("Telephone", Dr["Tel"]);

           AddFieldToXML("Fax", Dr["Fax"]);

           AddFieldToXML("VatNumber", Dr["VATNumber"]);

           AddFieldToXML("CountryCode", Dr["CountryCode"]);

           //Optional Fields (Not Used)

           //AddFieldToXML("EmailAddress", Dr["Type Field Name"]);

           Response.Write("\t\t</Customers>\n");

       }

       //output orders XML

       foreach(DataRow Dr in DsOrders.Tables["Orders"].Rows)

       {

           Response.Write("\t<Orders>\n");

           //Required Fields

           AddFieldToXML("CustomerID", Dr["CustomerID"]);

           AddFieldToXML("OrderID", Dr["OrderID"]);

           AddFieldToXML("OrderDate", Dr["OrderDate"]);

           //Optional Fields (Used)          

           AddFieldToXML("DelContactName", Dr["DelContactName"]);

           AddFieldToXML("DelCompany", Dr["DelCompany"]);

           AddFieldToXML("DelAddress1", Dr["DelAddress1"]);

           AddFieldToXML("DelAddress2", Dr["DelAddress2"]);

           AddFieldToXML("DelTown", Dr["City"]);

           AddFieldToXML("DelCounty", Dr["County"]);

           AddFieldToXML("DelPostCode", Dr["PostCode"]);

          //Optional Fields (Not Used)

           //AddFieldToXML("Carriage", Dr["Type Field Name"]);

           //AddFieldToXML("CarriageTaxCode", Dr["Type Field Name"]);

           //AddFieldToXML("CcAuthCode", Dr["Type Field Name"]);

           //AddFieldToXML("CcCardType", Dr["Type Field Name"]);

           //AddFieldToXML("CcIssueNo", Dr["Type Field Name"]);

           //AddFieldToXML("CcNameOnCard", Dr["Type Field Name"]);

           //AddFieldToXML("CcNumber", Dr["Type Field Name"]);

           //AddFieldToXML("CcValidFrom", Dr["Type Field Name"]);

           //AddFieldToXML("CcValidTo", Dr["Type Field Name"]);

           //AddFieldToXML("DelCountry", Dr["Type Field Name"]);

           Response.Write("\t</Orders>\n");

       }

       //output order items XML

       foreach(DataRow Dr in DsOrders.Tables["OrderItems"].Rows)

       {

           Response.Write("\t<OrderItems>\n");

           //Required Fields

           AddFieldToXML("OrderID", Dr["OrderID"]);

           AddFieldToXML("Description", Dr["Description"]);

           AddFieldToXML("Price", Dr["Price"]);

           AddFieldToXML("ProductCode", Dr["PartNo"]);

           AddFieldToXML("Quantity", Dr["Qty"]);

           //Optional Fields (Not Used)

           //AddFieldToXML("TaxCode", Dr["Type Field Name"]);

           Response.Write("\t</OrderItems>\n");

       }

       //finish outputing XML

       Response.Write("</DsOrders>");

       //End the response stream here to ensure nothing is send after the XML data.

       Response.End();

   }

   void AddFieldToXML(string FieldName, object Value)

   {

       if(Value != DBNull.Value)

       {

           Response.Write("\t\t<" + FieldName + ">" + Value.ToString().Replace("&", "&amp;") + "</" + FieldName + ">\n");

       }

   }

</script>