Πωπω δεν είχε κάποτε tag για κώδικα και έκανε και αυτόματη στοίχηση;
App: Εδώ καλώ την συνάρτηση του webservice
[code]
Me.Cursor = Cursors.WaitCursor
Dim halt As Boolean = False
Dim iterNum As Int16 = 1
Dim result As String = ""
Do Until halt
Me.Cursor = Cursors.WaitCursor
Dim dSet As DataSet = GetSomeData(iterNum)
If dSet Is Nothing Then
halt = True
Else
Me.Cursor = Cursors.WaitCursor
Dim ws As New webtest.Service()
result = ws.insertToDestination(dSet, iterNum)
If result <> "" Then
DoLog(result)
halt = True
Me.Cursor = Cursors.Default
Exit Sub
End If
End If
iterNum = iterNum + 1
Loop
Me.Cursor = Cursors.Default
[/code]
και η συνάρτηση του webservice (την κάνω trim γιατί είναι τεράστια)
[code]
Public GET_DSERVER As String, GET_DDBNAME As String, GET_DUSERNAME As String, GET_DPASSWORD As String, GET_DOWNER As String
Public WithEvents cnDest As SqlConnection
Public Function getDBConnCreds() As Integer
GET_DSERVER = ConfigurationManager.AppSettings("DBServer")
GET_DDBNAME = ConfigurationManager.AppSettings("Catalog")
GET_DUSERNAME = ConfigurationManager.AppSettings("DBUsername")
GET_DPASSWORD = ConfigurationManager.AppSettings("DBPassword")
GET_DOWNER = ConfigurationManager.AppSettings("DBUser")
End Function
Public Function connectDestination() As Integer
Try
'connect
cnDest = New SqlConnection
cnDest.ConnectionString = _
"Data Source=" & GET_DSERVER & ";Initial Catalog=" & GET_DDBNAME & ";User ID=" & GET_DUSERNAME & ";Password=" & GET_DPASSWORD _
& ";MultipleActiveResultSets=True"
cnDest.Open()
connectDestination = 200
Catch ex As Exception
connectDestination = 0
End Try
End Function
<WebMethod()> _
Public Function insertToDestination(ByVal dSet As DataSet, ByVal iteration As Int16) As String
Try
Dim status As String
status = getDBConnCreds()
If status = 0 Then
Return "insertLawyersToDestination - Settings file error"
Exit Function
End If
status = connectDestination()
If status = 0 Then
Return "insertLawyersToDestination - Connection error"
Exit Function
End If
Dim cmd As New SqlCommand()
cmd.Connection = cnDest
cmd.CommandType = CommandType.Text
Dim sql As String
If iteration = 1 Then
sql = "DELETE FROM CUSTOMER WHERE cast(CODE as bigint) < 50000"
cmd.CommandText = sql
cmd.ExecuteNonQuery()
End If
Dim p1 As New SqlParameter("@ID", SqlDbType.Int, 4)
Dim p2 As New SqlParameter("@ACCMASK", SqlDbType.VarChar, 25)
Dim p3 As New SqlParameter("@AFM", SqlDbType.VarChar, 15)
Dim p4 As New SqlParameter("@ACCCRDLIMIT", SqlDbType.Float, 8)
Dim p5 As New SqlParameter("@BILLTOCUSID", SqlDbType.Int, 4)
Dim p6 As New SqlParameter("@CARRIER", SqlDbType.VarChar, 30)
Dim p7 As New SqlParameter("@CITY1", SqlDbType.VarChar, 30)
Dim p8 As New SqlParameter("@CITY2", SqlDbType.VarChar, 30)
Dim p9 As New SqlParameter("@CNTID", SqlDbType.Int, 4)
Dim p10 As New SqlParameter("@CODE", SqlDbType.VarChar, 25)
Dim myDataRow As DataRow
For Each myDataRow In dSet.Tables(0).Rows
sql = "INSERT INTO CUSTOMER(ID,ACCMASK,AFM,ACCCRDLIMIT,BILLTOCUSID,CARRIER" _
& ",CITY1,CITY2,CNTID,CODE)" _
& " VALUES(@ID,@ACCMASK,@AFM,@ACCCRDLIMIT,@BILLTOCUSID,@CARRIER" _
& ",@CITY1,@CITY2,@CNTID,@CODE)"
With cmd
p1.Value = IIf(IsDBNull(myDataRow("ID")), DBNull.Value, myDataRow("ID"))
p2.Value = IIf(IsDBNull(myDataRow("ACCMASK")), DBNull.Value, myDataRow("ACCMASK"))
p3.Value = IIf(IsDBNull(myDataRow("AFM")), DBNull.Value, myDataRow("AFM"))
p4.Value = IIf(IsDBNull(myDataRow("ACCCRDLIMIT")), DBNull.Value, myDataRow("ACCCRDLIMIT"))
p5.Value = IIf(IsDBNull(myDataRow("BILLTOCUSID")), DBNull.Value, myDataRow("BILLTOCUSID"))
p6.Value = IIf(IsDBNull(myDataRow("CARRIER")), DBNull.Value, myDataRow("CARRIER"))
p7.Value = IIf(IsDBNull(myDataRow("CITY1")), DBNull.Value, myDataRow("CITY1"))
p8.Value = IIf(IsDBNull(myDataRow("CITY2")), DBNull.Value, myDataRow("CITY2"))
p9.Value = IIf(IsDBNull(myDataRow("CNTID")), DBNull.Value, myDataRow("CNTID"))
p10.Value = IIf(IsDBNull(myDataRow("CODE")), DBNull.Value, myDataRow("CODE"))
.Parameters.Add(p1)
.Parameters.Add(p2)
.Parameters.Add(p3)
.Parameters.Add(p4)
.Parameters.Add(p5)
.Parameters.Add(p6)
.Parameters.Add(p7)
.Parameters.Add(p8)
.Parameters.Add(p9)
.Parameters.Add(p10)
.CommandText = sql
.ExecuteNonQuery()
.Parameters.Clear()
End With
Next
Return ""
Catch ex As Exception
Return "insertToDestination - " & ex.Message
Exit Function
Finally
cnDest.Close()
cnDest.Dispose()
End Try
End Function
[/code]