Access SQL CE Mobile Database through RAPI

Jun 22, 2010 at 5:01 PM

Hello all:

 

I need to access an SQL CE Database on a Windows Mobile device. The database is a .SDF type file, which is used by one of the applications on the device. I need to read and write to this database. (I cannot use standard sync features because I don't have a standard database on the server side.) Does RAPI allow me to do this? Is there any sample code available anywhere?

 

Thank you,

 

Joe.

Jun 22, 2010 at 5:33 PM

With Rapi, you can copy the sdf from your device to your desktop PC.

Once on your PC you can use C# or VB.Net to access/edit the data in the SDF.

I use a webservice to transfer data to the server since my users are spread around the country, but you could also access a local server to transfer the data.

Let me know if you need help with this.

 

 

Jun 24, 2010 at 6:54 PM

Here's VB code to copy it from device to PC:

    Public Sub CopySDFfromIpaqToPC()
        UpDateCaption("Copying database from iPAQ")

        'Me.DeleteSDFonPC() 'Delete SDF located on the PC, if it already exists
        Me.ConnectToiPAQ()

        If Me.VerifyFileExistsOnIpaq() = True Then
            Try
                UpDateCaption("Copying SDF from iPAQ")
                myrapi.CopyFileFromDevice(MomsUpdPath & SDFfile, PathiPAQ)
                UpDateCaption("Finished copying SDF from iPAQ")
            Catch ex As Exception
                Throw New Exception("Error copying SDF from iPAQ: " & ex.Message)
            End Try
        Else
            Throw New Exception("SDFdoesntExistoniPAQ")
            'We need to change this to change a flag that iPAQ doesnt exist, indicating that this is a new/blank install
        End If

        Me.DisconnectFromIpaq()

    End Sub

 

    Public Sub DisconnectFromIpaq()
        myrapi.Disconnect()
        myrapi.Dispose()
    End Sub

 

    Public Sub ConnectToiPAQ()

        If myrapi.DevicePresent Then
            myrapi.Connect()
            'Me.Invoke(New MethodInvoker(ShowConnected))
        Else
            MessageBox.Show("Please connect your device to your PC using " & Me.SyncMethod() & " before clicking the OK button.", "iPAQ was not detected")
            If myrapi.DevicePresent Then
                myrapi.Connect()
                'Me.Invoke(New MethodInvoker(ShowConnected))
            Else
                MessageBox.Show("Please connect your device to your PC using " & Me.SyncMethod() & " before clicking the OK button. If you have just docked your unit, please wait for " & Me.SyncMethod() & " to start up and connect to your iPAQ before pressing OK", "iPAQ still has not been detected")
                If myrapi.DevicePresent Then
                    myrapi.Connect()
                    'Me.Invoke(New MethodInvoker(ShowConnected))
                Else
                    MessageBox.Show("Please verify " & Me.SyncMethod() & " is running and displays 'Connected' before clicking the OK button. If the iPAQ cannot be detected at this point, this application will shut down.", "iPAQ was not detected, last try.")
                    If myrapi.DevicePresent Then
                        myrapi.Connect()
                        'Me.Invoke(New MethodInvoker(ShowConnected))
                    Else
                        Throw New Exception("IpaqNotConnected")
                    End If
                End If
            End If
        End If
    End Sub

 

 

Jun 24, 2010 at 6:56 PM

Here's VB code to upload a table from the SDF to a webservice which passes it to SQL:

 

    Private Sub moveInvHdrFromSdfToSQL()

        UpDateCaption("Uploading InvHdr to WebService")

        Dim MyObj As localhost.Service1 = New localhost.Service1
        Dim CEconn As SqlServerCe.SqlCeConnection

        CEconn = New SqlServerCe.SqlCeConnection("Data Source =" & PathPC & FileName)
        CEconn.Open()

        Dim strSQL As String = "SELECT * FROM InvHdr"
        Dim CEda As New SqlServerCe.SqlCeDataAdapter(strSQL, CEconn)
        Dim ds As New DataSet
        CEda.Fill(ds, "InvHdr")

        Dim RtnMsg As String = MyObj.InsertInvHdr(ds)
        If (RtnMsg <> "InvHdr data inserted") Then
            Throw New Exception(RtnMsg)
        End If
        CEda.Dispose()
        CEconn.Close()
        CEconn.Dispose()
    End Sub

 

 

Jun 24, 2010 at 7:02 PM

This is how I populate a table in the SDF:

 

    Private Sub retrieveData(ByVal intMode As Integer)
        Try
            'call webservice and populate dataset
            Dim MyObj As localhost.Service1 = New localhost.Service1
            Dim ds As DataSet
            ds = MyObj.RetrieveSQLdata(Me.txtSecureString.Text, intMode)
            'At this point, the dataset has already been filled by the web service

            Dim CEconn As SqlServerCe.SqlCeConnection
            Dim CEcmd As SqlServerCe.SqlCeCommand

            CEconn = New SqlServerCe.SqlCeConnection("Data Source =" & PathPC & FileName)
            CEconn.Open()

            CEcmd = New SqlServerCe.SqlCeCommand
            CEcmd.Connection = CEconn

            'Delete the exisiting data from the iPAQ tables before populating it
            Dim TableName As String = "OrderHdr"

            CEcmd.CommandText = "DELETE OrderHdr"
            CEcmd.ExecuteNonQuery()

            UpdateStatus("Retrieving OrderHdr data from server")
            System.Windows.Forms.Application.DoEvents()

            Dim row As DataRow
            Dim mystr As String = ""
            Dim RowCount As Integer = 0

            For Each row In ds.Tables(0).Rows
                mystr = "INSERT INTO OrderHdr (OrderID, StoreNo, AMNo, CustNo, OrderDate, LastUpdated, InvoiceStatus, SSRNo, CustPoNo, Notes) "
                    mystr = mystr & "values("
                    mystr = mystr & "'" & row("OrderID").ToString & "', "
                    mystr = mystr & "'" & row("StoreNo").ToString & "', "
                    mystr = mystr & "'" & row("AMNo").ToString & "', "
                    mystr = mystr & "'" & row("CustNo").ToString & "', "
                    mystr = mystr & "'" & row("OrderDate").ToString & "', "
                    mystr = mystr & "'" & row("LastUpdated").ToString & "', "
                    mystr = mystr & row("InvoiceStatus").ToString & ", "
                    mystr = mystr & "'" & row("SSRNo").ToString & "', "
                    mystr = mystr & "'" & row("CustPoNo").ToString & "', "
                    mystr = mystr & "'" & row("Notes").ToString.Replace("'", "''") & "')"
               
                CEcmd.CommandText = mystr
                CEcmd.ExecuteNonQuery()
            Next

            UpdateStatus("OrderHdr data retrieved from server")
            System.Windows.Forms.Application.DoEvents()
            CEcmd.Dispose()
            CEconn.Close()
            CEconn.Dispose()

        Catch ex As Exception
            Throw New Exception("updateOrderHdr() Failed")
        End Try
    End Sub