[RESOLVED] PPC database functionality

[szlamany]

Ok - so I'm developing an app that will "download" from a SQL SERVER box two or three tables from a large database. Not all rows will go to the PPC - only those that the person is authorized to view (for instance). And not all columns - just those needed to support a mobile-display that I'm going to develop with VS2005 for the PPC.

I've already installed MS SQL CE and have a proof-of-concept app written.

Basic idea - person docks the PPC and runs the mobile-app. It finds no SDF file on the PPC so it creates it.

It needs to be encrypted - which I know I can do when I create the SDF in VS2005 - hopefully that can be done with a "create-on-the-fly" SDF. I'm concerned about how to make sure they use a good password...

At any rate - after the SDF is created the app will download somehow from the network SQL server. Not sure how it's going to authenticate - we use only window authentication - not sure how the PPC will be able to see the SQL box through the USB wire. Are there replication methods that can be used that I'm not aware of??

Once the data is on the PPC the user is able to view it remotely. If the user tries to access the SDF on a future date and cannot load the password successfully we plan on "deleting" the SDF - forcing them to return to the docking station to re-load.

Any opinions or help that can be offered in regard to these ideas - feel free!

Thanks in advance.

[petevick]

Hi,
you can use SqlCeClient or Merge/Replication or RDA - I have written articles on the first 2 on http://www.devbuzz.com

For authentication, you will need to send use/pwd for replication - hold them in some sort of config file, encrypted if need be.

What you are suggesting is a perfectly good solution

HTH

Pete

[szlamany]

I'm going to have to do more research in the sync part of the PPC to the network server. We only use WINDOWS AUTHENICATION on the network - for security reasons - and the only way to find out the "building role" of the user is to look up there Windows Username in a table of roles in the network DB...

If anyone is interested - or wants to offer some constructive criticism - here's the code so far.

Very, very easy - once you get around the difference in the CE world.

Imports System.Data.SqlServerCe
 
Public Class APC
 
    Private Dcn As SqlCeConnection
    Private Drc As SqlCeCommand
    Private Dse As SqlCeEngine
 
    Private Sub APC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox.PasswordChar = "*"
        TextBox.Focus()
    End Sub
 
    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
        Cursor.Current = Cursors.WaitCursor
        If TextBox.PasswordChar = "*" Then
            Dim dcn As New SqlCeConnection
            Dim booNotFound As Boolean
            dcn.ConnectionString = "DataSource=\My Documents\Business\APC.sdf; Password=" & TextBox.Text
            Try
                dcn.Open()
                TextBox.PasswordChar = ""
            Catch ex As SqlCeException
                If ex.NativeError = 25046 Then
                    booNotFound = True
                Else
                    MsgBox(ex.Message)
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            If booNotFound Then
                Dim dse As New SqlCeEngine("DataSource=\My Documents\Business\APC.sdf; Password=" & TextBox.Text)
                Try
                    dse.CreateDatabase()
                    If LoadDatabase() = False Then
                        System.IO.File.Delete("\My Documents\Business\APC.sdf")
                        Cursor.Current = Cursors.Default
                        MsgBox("Database did not sync!")
                        Me.Close()
                        Exit Sub
                    End If
                    TextBox.PasswordChar = ""
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
            If TextBox.PasswordChar = "*" Then
                TextBox.SelectAll()
                TextBox.Focus()
            Else
                TextBox.Text = ""
                Label.Text = "Last Name"
            End If
        End If
        Cursor.Current = Cursors.Default
    End Sub
 
    Private Function LoadDatabase() As Boolean
        LoadDatabase = False
        ' code goes here to sync database with network SQL server
    End Function
 
    Private Sub TextBox_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox.GotFocus
        InputPanel.Enabled = True
    End Sub
 
    Private Sub TextBox_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox.LostFocus
        InputPanel.Enabled = False
    End Sub
End Class

[szlamany]

Changed the connection string to this - for encryption...

Dim dse As New SqlCeEngine("DataSource=\My Documents\Business\APC.sdf; Password=" _
                                & TextBox.Text & ";encrypt database=TRUE")

From this MSDN article: http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

I did not have to install any dll's - this apparently worked so it must have been setup by my initial installs of SQL CE.