A reporting service using SOAP calls passing XML to a Data Extension

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;


namespace RSTestWS
{
	/// <summary>
	/// Summary description for BusinessDataAccess.
	/// </summary>
	public class BusinessDataAccess
	{
		public BusinessDataAccess()
		{
		}

		public DataSet GetCustomers(string sXml)
		{
			DataSet ds = null;
			SqlConnection conn = null;
			try
			{
				string sSQL = "select * from customers";

				if (sXml != string.Empty)
				{
					XmlDocument doc = new XmlDocument();
					doc.LoadXml(sXml);
					

					XmlNode Filters = doc.SelectSingleNode("//Filters");
					if (Filters != null && Filters.InnerText != string.Empty)
					{
						sSQL += " where " + Filters.InnerText;
					}
				}

				ds = new DataSet();
				conn = new SqlConnection(@"Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=(local);");
				SqlDataAdapter adapt = new SqlDataAdapter(sSQL, conn);
				adapt.Fill(ds);
				adapt.Dispose();
			}
			catch (Exception ex)
			{
				throw new Exception("Retrieve data failed", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
					conn.Dispose();
				}
			}

			return ds;
		}

		public DataSet GetTerritorySales(string sXml)
		{
			DataSet ds = null;
			SqlConnection conn = null;

			try
			{
				StringBuilder sSQL = new StringBuilder();
				sSQL.Append("SELECT   dbo.SalesTerritory.Name, dbo.SalesPerson.SalesPersonID, dbo.Employee.FirstName, dbo.Employee.LastName, ");
				sSQL.Append("         dbo.SalesOrderHeader.SalesOrderNumber, dbo.SalesOrderHeader.TotalDue ");
				sSQL.Append("FROM     dbo.SalesTerritory INNER JOIN ");
				sSQL.Append("         dbo.SalesPerson ON dbo.SalesTerritory.TerritoryID = dbo.SalesPerson.TerritoryID INNER JOIN ");
				sSQL.Append("         dbo.Employee ON dbo.SalesPerson.SalesPersonID = dbo.Employee.EmployeeID INNER JOIN ");
				sSQL.Append("         dbo.SalesOrderHeader ON dbo.SalesTerritory.TerritoryID = dbo.SalesOrderHeader.TerritoryID AND ");
				sSQL.Append("         dbo.SalesPerson.SalesPersonID = dbo.SalesOrderHeader.SalesPersonID ");

				if (sXml != string.Empty)
				{
					XmlDocument doc = new XmlDocument();
					doc.LoadXml(sXml);

					XmlNode TerritoryID = doc.SelectSingleNode("//TerritoryID");
					if (TerritoryID != null && TerritoryID.InnerText != string.Empty)
					{
						sSQL.Append("WHERE dbo.SalesTerritory.TerritoryID=" + TerritoryID.InnerText + " ");
					}

					XmlNode SalesPersonID =doc.SelectSingleNode("//SalesPersonID");
					if (SalesPersonID != null && SalesPersonID.InnerText != string.Empty)
					{
						sSQL.Append("dbo.SalesPerson.SalesPersonID=" + SalesPersonID.InnerText + " ");
					}
				}

				sSQL.Append("GROUP BY dbo.SalesTerritory.Name, dbo.SalesPerson.SalesPersonID, dbo.Employee.FirstName, dbo.Employee.LastName, ");
				sSQL.Append("         dbo.SalesOrderHeader.SalesOrderNumber, dbo.SalesOrderHeader.TotalDue ");
				sSQL.Append("ORDER BY dbo.SalesTerritory.Name ");

				ds = new DataSet();
				conn = new SqlConnection(@"Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorks2000;Data Source=(local);");
				SqlDataAdapter adapt = new SqlDataAdapter(sSQL.ToString(), conn);
				adapt.Fill(ds);
			}
			catch (Exception ex)
			{
				throw new Exception("Retrieve data failed", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
					conn.Dispose();
				}
			}

			return ds;
		}

		public DataSet GetSalesOrder(string sXml)
		{
			DataSet ds = null;
			SqlConnection conn = null;
			try
			{
				StringBuilder sSQL = new StringBuilder();
				sSQL.Append("SELECT SalesOrderHeader.SalesOrderNumber, Store.Name AS Store, SalesOrderHeader.OrderDate, Employee.FirstName AS SalesFirstName, ");
				sSQL.Append("       Employee.LastName AS SalesLastName, Employee.Title AS SalesTitle, SalesOrderHeader.PurchaseOrderNumber, ShipMethod.Name AS ShipMethod, ");
				sSQL.Append("       BillAddress.AddressLine1 AS BillAddress1, BillAddress.AddressLine2 AS BillAddress2, BillAddress.City AS BillCity, ");
				sSQL.Append("       BillAddress.PostalCode AS BillPostalCode, BillStateProvince.Name AS BillStateProvince, BillCountryRegion.Name AS BillCountryRegion, ");
				sSQL.Append("       BillAddress.Phone AS BillPhone, ShipAddress.AddressLine1 AS ShipAddress1, ShipAddress.AddressLine2 AS ShipAddress2, ");
				sSQL.Append("       ShipAddress.City AS ShipCity, ShipAddress.PostalCode AS ShipPostalCode, ShipStateProvince.Name AS ShipStateProvince, ");
				sSQL.Append("       ShipCountryRegion.Name AS ShipCountryRegion, ShipAddress.Phone AS ShipPhone ");
				sSQL.Append("FROM   SalesOrderHeader LEFT OUTER JOIN ");
				sSQL.Append("       Address AS BillAddress INNER JOIN ");
				sSQL.Append("       StateProvince AS BillStateProvince ON BillAddress.StateProvinceID = BillStateProvince.StateProvinceID INNER JOIN ");
				sSQL.Append("       CountryRegion AS BillCountryRegion ON BillAddress.CountryRegionCode = BillCountryRegion.CountryRegionCode ON  ");
				sSQL.Append("       SalesOrderHeader.BillToAddressID = BillAddress.AddressID LEFT OUTER JOIN ");
				sSQL.Append("       Address AS ShipAddress INNER JOIN ");
				sSQL.Append("       StateProvince AS ShipStateProvince ON ShipAddress.StateProvinceID = ShipStateProvince.StateProvinceID INNER JOIN ");
				sSQL.Append("       CountryRegion AS ShipCountryRegion ON ShipAddress.CountryRegionCode = ShipCountryRegion.CountryRegionCode ON ");
				sSQL.Append("       SalesOrderHeader.ShipToAddressID = ShipAddress.AddressID LEFT OUTER JOIN ");
				sSQL.Append("       Employee ON SalesOrderHeader.SalesPersonID = Employee.EmployeeID LEFT OUTER JOIN ");
				sSQL.Append("       ShipMethod ON SalesOrderHeader.ShipMethodID = ShipMethod.ShipMethodID LEFT OUTER JOIN ");
				sSQL.Append("       Store ON SalesOrderHeader.CustomerID = Store.CustomerID ");

				if (sXml != string.Empty)
				{
					XmlDocument doc = new XmlDocument();
					doc.LoadXml(sXml);

					XmlNode SalesOrderNumber = doc.SelectSingleNode("//SalesOrderNumber");
					if (SalesOrderNumber != null && SalesOrderNumber.InnerText != string.Empty)
					{
						sSQL.Append("WHERE SalesOrderHeader.SalesOrderNumber = '" + SalesOrderNumber.InnerText + "'");
					}
				}

				ds = new DataSet();
				conn = new SqlConnection(@"Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorks2000;Data Source=(local);");
				SqlDataAdapter adapt = new SqlDataAdapter(sSQL.ToString(), conn);
				adapt.Fill(ds);
			}
			catch (Exception ex)
			{
				throw new Exception("Retrieve data failed", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
					conn.Dispose();
				}
			}

			return ds;
		}

		public DataSet GetSalesOrderDetail(string sXml)
		{
			DataSet ds = null;
			SqlConnection conn = null;
			try
			{
				StringBuilder sSQL = new StringBuilder();
				sSQL.Append("SELECT SalesOrderDetail.LineNumber, SalesOrderDetail.OrderQty, SalesOrderDetail.UnitPrice, CASE WHEN dbo.SalesOrderDetail.UnitPriceDiscount IS NULL ");
				sSQL.Append("       THEN 0 ELSE dbo.SalesOrderDetail.UnitPriceDiscount END AS UnitPriceDiscount, SalesOrderDetail.LineTotal, ");
				sSQL.Append("       SalesOrderDetail.CarrierTrackingNumber, SalesOrderDetail.SalesOrderID, Product.Name, Product.ProductNumber ");
				sSQL.Append("FROM   SalesOrderDetail INNER JOIN ");
				sSQL.Append("       Product ON SalesOrderDetail.ProductID = Product.ProductID INNER JOIN ");
				sSQL.Append("       SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID ");

				if (sXml != string.Empty)
				{
					XmlDocument doc = new XmlDocument();
					doc.LoadXml(sXml);

					XmlNode SalesOrderNumber = doc.SelectSingleNode("//SalesOrderNumber");
					if (SalesOrderNumber != null && SalesOrderNumber.InnerText != string.Empty)
					{
						sSQL.Append("WHERE SalesOrderHeader.SalesOrderNumber = '" + SalesOrderNumber.InnerText + "'");
					}
				}

				sSQL.Append("ORDER BY SalesOrderDetail.LineNumber ");

				ds = new DataSet();
				conn = new SqlConnection(@"Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorks2000;Data Source=(local);");
				SqlDataAdapter adapt = new SqlDataAdapter(sSQL.ToString(), conn);
				adapt.Fill(ds);
			}
			catch (Exception ex)
			{
				throw new Exception("Retrieve data failed", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
					conn.Dispose();
				}
			}

			return ds;
		}

	}
}