XML Web Service 数据交换
客户端调用服务器端的 Web 服务并传递包含数据的 DataSet (ds):
Private Sub Synchronize()
Dim username As String = "JohnS"
Dim blnSuccess As Boolean
' 使用 XML Web Service 进行同步
Cursor.Current = Cursors.WaitCursor
Dim wsFeedback As New wsFeedback.feedback
blnSuccess = wsFeedback.InsertFeedback(ds, username)
Cursor.Current = Cursors.Default
End Sub
服务器端的 Web 服务接受 DataSet 并将 XML 直接传递到 SQL Server 2000 存储的过程中,该过程使用 SQLXML(英文)和 OPENXML 解析 XML 并将新数据插入适当的表格。
<WebMethod()> _
Public Function InsertFeedback(ByVal ds As DataSet, ByVal username As
String) As Boolean
Dim con As New SqlConnection(connectionstring)
Dim cmd As New SqlCommand("p_Feedback_i", con)
cmd.CommandType = CommandType.StoredProcedure
' 设置参数
Dim prmXML As SqlParameter = cmd.Parameters.Add("@XML",
SqlDbType.NText)
prmXML.Direction = ParameterDirection.Input
prmXML.Value = ds.GetXml
Dim prmUsername As SqlParameter = cmd.Parameters.Add("@Username",
SqlDbType.NVarChar)
prmUsername.Direction = ParameterDirection.Input
prmUsername.Value = username
Try
con.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
' 处理、记录并重掷错误
Throw ex
Finally
con.Close()
End Try
Return True
End Function
The stored procedure inserts the new data:
CREATE PROCEDURE p_Feedback_i
@XML ntext,
@Username nvarchar(50)
AS
SET NOCOUNT ON
DECLARE @iDoc integer
DECLARE @Error integer
/* Create XML document. */
EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML
/* Insert new records */
INSERT INTO Feedback
(
FeedbackID,
PlantSection,
Part,
DefectScope,
ScopeID,
DefectType,
RichInk,
Username
)
SELECT ID,
PlantSection,
Part,
DefectScope,
ScopeID,
DefectType,
RichInk,
@Username
FROM OPENXML (@iDoc, '/DataSet/Feedback',2) WITH
(
ID uniqueidentifier,
PlantSection int,
Part int,
DefectScope int,
ScopeID nvarchar(50),
DefectType int,
RichInk nvarchar(50)
)
SELECT @Error = @@ERROR
IF (@Error <> 0)
BEGIN
GOTO Errorhandler
END
/* Remove the XML document*/
EXEC sp_xml_removedocument @iDoc
RETURN
Errorhandler:
IF NOT @iDoc IS NULL
EXEC sp_xml_removedocument @iDoc
RAISERROR (@Error,16,1)
RETURN