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.
Enjoy,
Technorati Tags:
.NET,
XML,
XMLSerializer,
SQLDave