Update One Database from Another Using a WebService and Two DataSets...

[Jon Jaques]

Hello, I have a database application used by a small company that needs to
transfer some updates back and forth, to and from, their website. The
database uses an Access frontend connected to a MSSQL 2005 server backend,
but I've created an ASP.net intranet application to handle the data
transfers independantly of the access frontend.

The application uses a web services, located on the local intranet; I had
been using a more traditional approach of simply pushing CSV files back and
forth, but performance is suffering now that the number of records has
increased, and, as I said, I need to offload that process so that the normal
functioning of the access application is not affected.

I wish the web host supported SSIS, but it does not, plus I think it's SQL
2000, anyhow.

The code I've been trying to use looks like this, but does not work:

' Call update method with get method as parameter
<WebMethod()> Public Function AutoUpdateTripsClientsTravelers() As
String
Try
UpdateTripsClientsTravelers(GetTripsClientsTravelers)
Return "Success!"
Catch ex As Exception
Return "Failed: #: (" & Err.Number & ") " & Err.Description
End Try
End Function

' Accept a dataset as a param and update external database
<WebMethod()> Public Sub UpdateTripsClients(ByVal ds As DataSet)
Dim con As New SqlConnection(strGTPcon)
Dim daCust As New SqlDataAdapter("Select * From tblTripsClients",
con)
Dim cbCust As New SqlCommandBuilder(daCust)
Dim ds2 As New DataSet
daCust.Fill(ds2, "TripsClients")
' Dim strOut As String
' books.Merge(dsBooks3, False, MissingSchemaAction.Add)
ds.Merge(ds2, False, MissingSchemaAction.Add)
'If ds.HasChanges Then
daCust.Update(ds, "TripsClients")
'End If
' Return ds
End Sub

' Get data from local database and return as dataset
<WebMethod()> Public Function GetTripsClientsTravelers() As DataSet
'Modify this Connection string to use your SQL Server and log on.
Dim con As New SqlConnection(strKTGPDATAcon)
Dim daCust As New SqlDataAdapter("Select * From
qryTripsClientsTravelers", con)
Dim ds As New DataSet()
daCust.Fill(ds, "TripsClientsTravelers")
Return ds
End Function


Any ideas? Or, a better way to push/pull those updates?

Much Appreciated,

--Jon

 

[CoastalData]

Hmmm, a little more research seems to say that I should go back to using
some variation of CSV, as there's less overhead in my data, but I'd still
like to use a web service for this... How might I modifiy this?

The challenge to the updates will be updating new records where they exist,
and inserting new records where they don't; there will never be two-way
updates, though.

Any suggestions?

Thanks in advance,

--Jon