SQL DB Web Service with XML

[blatcho]

Hi all,

I've put together a few web services, trying to get my head around how XML and Datasets work.

the first method just returns all data from the database and writes the output to an xml file

the second method updates the table with hard coded values (John Smith)

the third method is where i'm having trouble; i want to read an XML file into a dataset and then update the database with that dataset, but i'm confusing myself methinks. could anyone point me in the right direction?


Imports System.Web.Services
Imports System.Data
Imports System.Data.Sql
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Xml

<WebService(Namespace:="http://microsoft.com/webservices/")> _
Public Class Service1

Public SQLConn As SqlConnection = New SqlConnection("Data Source=TEUKLAP52\SQLEXPRESS;uid=sa;pwd=******;Initial Catalog=CB_WS1")

<WebMethod(Description:="Returns all data from database CB_WS1 table USERNAMES", EnableSession:=False)> _
Public Function CB_WSB1_GetNames() As DataSet
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT FirstName, LastName FROM CB_WS1_UserNames", SQLConn)
Dim custDS As DataSet = New DataSet()
custDA.Fill(custDS, "UserNames")
CB_WSB1_GetNames = custDS
custDS.WriteXml("c:\XML\UserNames.xml")
End Function

<WebMethod(Description:="Updates the database CB_WS1 table USERNAMES (Hard coded)", EnableSession:=False)> _
Public Function CB_WSB1_UpdateNames() As DataSet
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT FirstName, LastName FROM CB_WS1_UserNames", SQLConn)
Dim custDS As DataSet = New DataSet()
Dim custDR As DataRow
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
custDA.Fill(custDS, "UserNames")

custDR = custDS.Tables("UserNames").NewRow()
custDR("FirstName") = "John"
custDR("LastName") = "Smith"
custDS.Tables("UserNames").Rows.Add(custDR)

custDA.Update(custDS, "UserNames")
custDS.AcceptChanges()

End Function

<WebMethod(Description:="Updates the database CB_WS1 table USERNAMES (XML)", EnableSession:=False)> _
Public Function CB_WSB1_UpdateNamesXML() As DataSet
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT FirstName, LastName FROM CB_WS1_UserNames", SQLConn)
Dim custDS As DataSet = New DataSet()
Dim custDR As DataRow
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
custDS.ReadXml("c:\XML\UserNamesUpdate.xml")

custDR = custDS.Tables("UserNames").NewRow()
custDR("FirstName") = custDS.Tables.Item(0)
custDR("LastName") = custDS.Tables.Item(1)
custDS.Tables("UserNames").Rows.Add(custDR)

custDA.Update(custDS, "UserNames")
custDS.AcceptChanges()


End Function

End Class

 

[blatcho]

Dataset Merge?

OK i think i've found a method for doing this, using a Dataset merge. Is there a way to read the XML file and the database into a single dataset, without the need for two datasets?

<WebMethod(Description:="Updates the database CB_WS1 table USERNAMES (XML)", EnableSession:=False)> _
Public Function CB_WSB1_UpdateNamesXML() As DataSet
Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT FirstName, LastName FROM CB_WS1_UserNames", SQLConn)
Dim custDS As DataSet = New DataSet()
Dim XMLDS As DataSet = New DataSet()
Dim custDR As DataRow
Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(custDA)

custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
XMLDS.ReadXml("c:\XML\UserNamesUpdate.xml")
custDA.Fill(custDS, "UserNames")

custDS.Merge(XMLDS)
custDA.Update(custDS, "UserNames")
custDS.AcceptChanges()
End Function