For the past few weeks we've been looking at utilizing XML to work with DATA from our SQL Server. We've talked about building a custom XML document with XMLSerializer, Using the XML Datatype built in SQL 2005, and finally using OPENXML to insert records into our Database. Today we are going to see how we can use each of those tools to solve a common issue for businesses, which is how to push data between different servers located on different networks. In this scenario our requirements are to push the daily sales leads from a Car dealer up to the corporate office. Our SQL servers are on disparate servers so we don't have the luxury of using DTS to get the job done. For this example we are going to create 2 projects. A client project which will serialize the leads from the database and send them to a webservice; which is our second project, to be inserted into the database. If you chose to automate the process of pushing the data you could create a window service and schedule a job, but for the purpose of this project I have chosen to go with a web project for our client.
So lets get started and create a web project for our client, and a webservice project for our server.
For our client we are going to create 2 class files; SalesLead and SalesLeadPush. We will use SalesLeadPush to serialize our data and send it to the server, and SalesLead is our object we are using to serialize.
Taking a brief look at the class file you can see how we are using class objects and attributes to define our xml structure.
Imports System.Xml Imports System.Xml.Serialization Namespace SalesTools Public Enum ContactType As Integer Email = 1 Phone = 2 End Enum Public Class SalesLead Public Leads As New List(Of Lead) Public Sub New() End Sub End Class Public Class Lead <XmlAttribute("LeadID")> _ Public LeadID As Integer <XmlAttribute("ContactID")> _ Public ContactID As Integer <XmlAttribute("VehicleID")> _ Public VehicleID As Integer <XmlAttribute("SiteID")> _ Public SiteID As Integer Public Contact As Contact Public Sub New() End Sub End Class
Here is where we use a MemoryStream object to serialize our data and then send it to the webserivice.
1: Public Class SalesLeadPush
2: Public Function SerializeXML() As XmlDocument
4: Dim SiteID = 3214
6: Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("salesPush").ConnectionString)
7: Dim CmdTxt As String = "getSalesLeads"
8: Dim SqlCmd As New SqlCommand(CmdTxt, sqlCon)
9: SqlCmd.CommandType = CommandType.StoredProcedure
10: Dim sqlDS As New DataSet
11: Dim sqlDA As New SqlDataAdapter(SqlCmd)
13: 'Set up Serializer
14: Dim mStream As MemoryStream = New MemoryStream
15: Dim xns As XmlSerializerNamespaces = New XmlSerializerNamespaces
16: Dim mySerializer As New XmlSerializer(GetType(SalesTools.SalesLead))
17: Dim xmlDoc As New XmlDocument
19: 'Set up object
20: Dim oSalesTools As New SalesTools.SalesLead
22: xns.Add(String.Empty, String.Empty)
25: Dim i As Integer = 0
26: Dim sLeadID As Integer = 0
27: Dim sContactID As Integer = 0
29: For Each Row As DataRow In sqlDS.Tables(0).Rows
30: Dim oContact As New SalesTools.Contact
31: Dim oVehicleList As New SalesTools.Vehicle
32: Dim oLead As New SalesTools.Lead
34: oContact.ContactID = CInt(Row("ContactID").ToString)
35: oContact.AddressLine1 = Row("contactAddressLine1").ToString
36: oContact.City = Row("contactCity").ToString
37: oContact.State = Row("contactState").ToString
38: oContact.Postal = Row("contactPostal").ToString
Break in Code Snippet
58: oLead.Contact = oContact
60: oLead.LeadID = CInt(Row("LeadID").ToString)
61: oLead.ContactID = CInt(Row("ContactID").ToString)
62: oLead.VehicleID = CInt(Row("VehicleID").ToString)
63: oLead.SiteID = SiteID
67: mySerializer.Serialize(mStream, oSalesTools, xns)
73: Dim ws As New SalesLeadListen.SalesLeadListen
74: Dim Result As New XmlDocument
77: Return Result
78: End Function
80: Private Function UTF8ByteArrayToString(ByVal characters As Byte()) As String
82: Dim encoding As New UTF8Encoding()
83: Dim constructedString As String = encoding.GetString(characters)
84: Return (constructedString)
85: End Function
87: Private Function StringToUTF8ByteArray(ByVal pXmlString As String) As Byte()
88: Dim encoding As New UTF8Encoding()
89: Dim byteArray As Byte() = encoding.GetBytes(pXmlString)
90: Return byteArray
91: End Function
93: End Class
Before we move on to the webservice we will add code to our default.aspx to call the SerializeXML function and display the returned result.
Now lets move over to our 2nd project and look at the webservice. We are going to start off by creating a small class object which we will use to build our result to return to the client.
Public Class Result Public Sucess As String Public exception As String Public Sub New(ByVal suc, ByVal ex) Sucess = suc exception = ex End Sub Public Sub New() End Sub End Class
Looking at our DB Model we can see that our table schema is slightly different than that of the clients.
Depending on your business rules, you will either want to handle duplicate entries during this process, or afterwards. I'm not taking duplicate entries into consideration in this example.
CREATE PROCEDURE dbo.LoadSiteLead @leadXML As XML AS DECLARE @XMLDocPointer INT EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @leadXML BEGIN TRANSACTION INSERT INTO Lead(siteLeadID, siteContactID, siteVehicleID, siteID) SELECT LeadID, ContactID, VehicleID, SiteID FROM OpenXML(@XMLDocPointer, '/SalesLead/Leads/Lead', 1) WITH (LeadID Int, ContactID Int, VehicleID Int, SiteID Int) INSERT INTO Contact( siteContactID, contactFullName, contactFirstName, contactLastName, contactAddressLine1, contactCity, contactState, contactPostal, contactEmail, contactPhone, contactBestContactTime, contactBestContactMethod) SELECT ContactID, FullName, FirstName, LastName, AddressLine1, City, State, Postal, Email, Phone, BestContactTime, ContactMethod FROM OpenXML(@XMLDocPointer, '/SalesLead/Leads/Lead/Contact', 2) WITH(ContactID Int '@ID', FullName varchar(250), FirstName varchar(125), LastName varchar(125), AddressLine1 varchar(200), City varchar(50), State char(2), Postal VarChar(10), Email VarChar(100), Phone VarChar(100), BestContactTime varchar(50), ContactMethod varchar(50) '@ContactMethod') INSERT INTO Vehicle( siteVehicleID, vehicleMake, vehicleModel, vehicleYear, vehicleVIN, vehicleColor, vehicleBasePrice, vehicleSRP) SELECT VehicleID, Make, Model, [Year], VIN, Color, BasePrice, SRP FROM OpenXML(@XMLDocPointer, '/SalesLead/Leads/Lead/Contact/Vehicle', 2) WITH (VehicleID Int '@ID', Make varchar(50), Model varchar(50), [Year] char(4), VIN varchar(200), Color varchar(50), BasePrice Money, SRP Money) COMMIT EXEC sp_xml_removedocument @XMLDocPointer
WebService : SalesLeadListen
You will notice in the Try Catch block that we are loading the Result Object with Success / Exception message. This object is then returned to the client as serialized XML.
Public Class SalesLeadListen Inherits System.Web.Services.WebService <WebMethod()> _ Public Function LoadLeads(ByVal LeadsXML As XmlDocument) As Result Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("salesPush").ConnectionString) Dim CmdTxt As String = "LoadSiteLead" Dim SqlCmd As New SqlCommand(CmdTxt, sqlCon) Dim rst As Result SqlCmd.CommandType = CommandType.StoredProcedure SqlCmd.Parameters.AddWithValue("@LeadXML", LeadsXML.OuterXml) Dim SB As New StringBuilder Try SqlCmd.Connection.Open() SqlCmd.ExecuteNonQuery() rst = New Result("Success", "0") Catch SQLex As SqlException rst = New Result("Failure", SQLex.Message) Catch ex As Exception rst = New Result("Failure", ex.Message) Finally SqlCmd.Connection.Close() SqlCmd.Dispose() End Try Return rst End Function End Class
Wrapping this up, you can now see how using XML and Webservices we are able to push data from one server to another server regardless of Database Schema and Server Location.
That's all for now, I'll see you on the flip side of things.