469,326 Members | 1,537 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Connect to SQL Server CE

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
Jul 18 '08 #1
10 7318
markmcgookin
648 Expert 512MB
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
  2.         'Verify if the WildFlowers database already exists.
  3.         If File.Exists("WildFlowers.sdf") = False Then
  4.             'Allow creation of the database
  5.             Dim engine As New SqlCeEngine("Data Source = WildFlowers.sdf")
  6.             engine.CreateDatabase()
  7.             MsgBox("Wildflowers database successfully created.", MsgBoxStyle.OKOnly, "Create DataBase")
  8.         Else
  9.             MsgBox("Wildflowers database already exists, no action taken.", MsgBoxStyle.OKOnly, "Create DataBase")
  10.         End If
  11.     End Sub
  12.  
  13.     Private Sub btnCreateTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTables.Click
  14.         'Create connection with database and then Open connection
  15.         Dim conn As New SqlCeConnection("Data Source = WildFlowers.sdf")
  16.         Try
  17.             conn.Open()  'Open Connection to the Database
  18.  
  19.             'Create SQL command to create the tblDetails table
  20.             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)
  21.             'Excecute Create Table command
  22.             cmdTabla.ExecuteNonQuery()
  23.  
  24.         Catch ex As SqlCeException
  25.             MsgBox(ex.Message)
  26.  
  27.         Finally
  28.             MsgBox("Tables were successfully created.", MsgBoxStyle.OKOnly, "Create Tables")
  29.             conn.Close() 'Close database
  30.         End Try
  31.     End Sub
  32.  
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\MyDb.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
Jul 18 '08 #2
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.
Jul 21 '08 #3
markmcgookin
648 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. 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
Jul 21 '08 #4
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.GetDirectoryName(System.Reflection .Assembly.GetExecutingAssembly().GetName().CodeBas e)) + "\\productsDB.sdf;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("INSERT INTO tblProducts(ProductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

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?
Jul 22 '08 #5
bump......................................
Jul 22 '08 #6
markmcgookin
648 Expert 512MB
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.GetDirectoryName(System.Reflection .Assembly.GetExecutingAssembly().GetName().CodeBas e)) + "\\productsDB.sdf;Persist Security Info=False;)

cn.Open()

cmd = New SqlCeCommand("INSERT INTO tblProducts(ProductID, ProductName) VALUES(" & txtID.Text & ", '" & txtName.Text & "')", cn)

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

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!)
Jul 22 '08 #7
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?
Jul 23 '08 #8
markmcgookin
648 Expert 512MB
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.
Jul 23 '08 #9
Personally I tend to keep the database in my application folder to avoid issues.

i.e.

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 5 '08 #10
markmcgookin
648 Expert 512MB
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
Oct 7 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Jerry Orr | last post: by
reply views Thread by Clodoaldo Pinto | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.