Connect to SQL Server CE

[memermore]

I am new to mobile development and for now I'm trying to create a mobile application using SQL Server CE as database. Could someone please provide a sample code in vb that will connect to SQL CE? Or the connection string at least?
Any help would be greatly appreciated

[markmcgookin]

Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
        'Verify if the WildFlowers database already exists.
        If File.Exists("WildFlowers.sdf") = False Then
            'Allow creation of the database
            Dim engine As New SqlCeEngine("Data Source = WildFlowers.sdf")
            engine.CreateDatabase()
            MsgBox("Wildflowers database successfully created.", MsgBoxStyle.OKOnly, "Create DataBase")
        Else
            MsgBox("Wildflowers database already exists, no action taken.", MsgBoxStyle.OKOnly, "Create DataBase")
        End If
    End Sub

    Private Sub btnCreateTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTables.Click
        'Create connection with database and then Open connection
        Dim conn As New SqlCeConnection("Data Source = WildFlowers.sdf")
        Try
            conn.Open()  'Open Connection to the Database

            'Create SQL command to create the tblDetails table
            Dim cmdTabla As New SqlCeCommand("CREATE TABLE tblDetails(regID int IDENTITY(0,1) PRIMARY KEY, Flower_ID NTEXT, Name_Eng NTEXT, Name_Lat NTEXT, Location_ID NTEXT, Habitat_ID NTEXT, Season_ID NTEXT, Family_ID NTEXT, Height NTEXT, Flower_Shape NTEXT, Leaf_Shape NTEXT, Colour NTEXT, Plant_Type NTEXT, Bloom_Size NTEXT, Cluster_Type NTEXT, Description NTEXT)", conn)
            'Excecute Create Table command
            cmdTabla.ExecuteNonQuery()

        Catch ex As SqlCeException
            MsgBox(ex.Message)

        Finally
            MsgBox("Tables were successfully created.", MsgBoxStyle.OKOnly, "Create Tables")
            conn.Close() 'Close database
        End Try
    End Sub

Hope this helps... it is connection to a database in the root of the PDA ... so if the database is somewhere else ... i.e. \Program Files\MyApp\MyD b.sdf the path will have to represent that.

There are two subroutines in the above code the first creates the database and the second connects to the database and executes some SQL to create a table

Hope that helps

Mark

[memermore]

Thanks Mark. This is better. Just a few question though. Suppose I created the database on runtime based on your code, where can I find it? Or suppose i have created the database somewhere(like SQL server 2005), where should I place my SDF file to avoid problems in my connection string? Actually I think my problem here is my connection string.

[markmcgookin]

Thanks Mark. This is better. Just a few question though. Suppose I created the database on runtime based on your code, where can I find it? Or suppose i have created the database somewhere(like SQL server 2005), where should I place my SDF file to avoid problems in my connection string? Actually I think my problem here is my connection string.

Personally I tend to keep the database in my application folder to avoid issues.

i.e.

Dim conn As New SqlCeConnection("Data Source = \Program Files\MyApplication\myDatabase.sdf")

Then when you deploy using Visual Studio it will be in the right place. If you have already created the database and deployed it to the device but can't connect. Just use File Explorer to navigate to the folder to get the path.

If you use my code above, as I have not specified a path for the db it will be created in the device root.

Are you getting an error?

Hope this helps,

Mark

[memermore]

Thanks. I can finally connect to my database, I tried to create a function that will add record to my table tblProducts which is empty at first

Private Sub add_record()
cn = New SqlCeConnection (Data Source=" + (System.IO.Path .GetDirectoryNa me(System.Refle ction.Assembly. GetExecutingAss embly().GetName ().CodeBase)) + "\\productsDB.s df;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("I NSERT INTO tblProducts(Pro ductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Tex t

cmd.ExecuteNonQ uery()

cn.Close()

MessageBox.Show ("New record added")
End Sub

At first I thought this was working fine, my datagrid control was updated by the new record I added but when restart my application, the newly added record was gone. And tblProducts is still empty. Is there something wrong in my code?

[freedom021]

bump........... ............... ............

[markmcgookin]

Thanks. I can finally connect to my database, I tried to create a function that will add record to my table tblProducts which is empty at first

Private Sub add_record()
cn = New SqlCeConnection (Data Source=" + (System.IO.Path .GetDirectoryNa me(System.Refle ction.Assembly. GetExecutingAss embly().GetName ().CodeBase)) + "\\productsDB.s df;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("I NSERT INTO tblProducts(Pro ductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Tex t

cmd.ExecuteNonQ uery()

cn.Close()

MessageBox.Show ("New record added")
End Sub

At first I thought this was working fine, my datagrid control was updated by the new record I added but when restart my application, the newly added record was gone. And tblProducts is still empty. Is there something wrong in my code?

When you say "restart" your application did you mean that you ran debug again and deployed it to the mobile again? Chances are that the database is being copied across ontop of the original again if this is the case. Because it looks like the insert it going in properly. If it is in the solution explorer in VS you can click on properties > build action > Do not copy to avoid this (but you will obviously need it copied across at least once to show up!)

[memermore]

Yes, I did ran debug and deployed my application. I actually created my database in VS and is located in the application folder. What must I do to make sure that the database table is really updated?

[markmcgookin]

Yes, I did ran debug and deployed my application. I actually created my database in VS and is located in the application folder. What must I do to make sure that the database table is really updated?

Well when you are deploying your application you are RE-deploying the database ontop of the old one and over-writing it. A safe bet would be to deploy your application and add your row to the database. THEN run this without copying over the database; you can either stop copying it as mentioned above or go to

File Explorer (on your device/emulator) > Program Files > YourApplication > YourApplication .exe

and run it directly from the device. If you do this it will run the application without copying over a new blank database and your row should be in it.

When you deploy an application it re-copies any files you have added or altered and datasources, including your database, this is why it is getting over written.

[eqquito]

Personally I tend to keep the database in my application folder to avoid issues.

i.e.

Dim conn As New SqlCeConnection("Data Source = \Program Files\MyApplication\myDatabase.sdf")

Then when you deploy using Visual Studio it will be in the right place. If you have already created the database and deployed it to the device but can't connect. Just use File Explorer to navigate to the folder to get the path.

You cant imagine how thankfull I am, thanks for the great explanation. Sometimes we the devs are stuck in something easy like where the heck I place my db file so the emulator can use it since it is not reading from my computer. I searched lot and lots of pages, sample code and I created my own samples.

I just registered to say thanks. Now will also help if I can.

[markmcgookin]

I just registered to say thanks. Now will also help if I can.

Always glad to help!

Hope to see you around Bytes more often!

Mark