Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

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

, 19 Oct 2005
Demostrates how to render a report by passing XML to a data extension via SOAP calls.
RSDataExtensionXml_demo.zip
RSTestApp
CustomDataExtension
CustomDataExtension.csproj.user
Web References
BusinessService
BusinessData.disco
BusinessData.wsdl
Reference.map
Global.asax
RsReportViewer
AwReportViewer.suo
Design
vssver.scc
RsReportViewer.csproj.user
Web References
RS
Reference.map
ReportService.wsdl
RSTestApp.csproj.webinfo
RSTestReports
Northwind.rds
Northwind.udl
Report1.rdl
Report1.rdl.data
RSTest.rptproj.user
RSTestReports.rptproj
RSTestReports.rptproj.user
Sales Order Detail.rdl
Sales Order Detail.rdl.data
Territory Sales Drilldown.rdl
Territory Sales Drilldown.rdl.data
RSTestWS
Global.asax
RSTestWS.csproj.webinfo
temp
Web References
ReportServer
Reference.map
ReportService.wsdl
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;
		}

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ken C. Len
Software Developer (Senior) LEN Associates Inc.
United States United States
Years of software consulting and software development using Microsoft development products such as Microsoft Content Management System, SQL Server Reporting Service, ASP.Net C# VB.Net, HTML and javascript web development, Visual Studio add-on development, C++ MFC/ATL and COM+ development, and ActiveX components.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 19 Oct 2005
Article Copyright 2005 by Ken C. Len
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid