Pushing Data between Servers: XML, Webservices, and SQL

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.

clsDiag1

 

Database Model

dbDiag1

SalesLead.VB 

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
 

SalesLeadPush.VB

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
   3:   
   4:          Dim SiteID = 3214
   5:   
   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)
  12:   
  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
  18:   
  19:          'Set up object
  20:          Dim oSalesTools As New SalesTools.SalesLead
  21:   
  22:          xns.Add(String.Empty, String.Empty)
  23:          sqlDA.Fill(sqlDS)
  24:   
  25:          Dim i As Integer = 0
  26:          Dim sLeadID As Integer = 0
  27:          Dim sContactID As Integer = 0
  28:   
  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
  33:   
  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
  57:   
  58:              oLead.Contact = oContact
  59:   
  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
  64:              oSalesTools.Leads.Add(oLead)
  65:   
  66:          Next
  67:          mySerializer.Serialize(mStream, oSalesTools, xns)
  68:          SqlCmd.Connection.Close()
  69:          SqlCmd.Dispose()
  70:   
  71:          xmlDoc.LoadXml(UTF8ByteArrayToString(mStream.ToArray()))
  72:   
  73:          Dim ws As New SalesLeadListen.SalesLeadListen
  74:          Dim Result As New XmlDocument
  75:          Result.LoadXml(ws.LoadLeads(xmlDoc).OuterXml)
  76:   
  77:          Return Result
  78:      End Function
  79:   
  80:      Private Function UTF8ByteArrayToString(ByVal characters As Byte()) As String
  81:   
  82:          Dim encoding As New UTF8Encoding()
  83:          Dim constructedString As String = encoding.GetString(characters)
  84:          Return (constructedString)
  85:      End Function
  86:   
  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
  92:   
  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.
dbDiag2 

Stored Procedure

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.

 

Enjoy,

Technorati Tags: , , ,

Dave

388 Comments

Comments have been disabled for this content.