Connect to SQL Server CE | Newbie | | Join Date: Sep 2007
Posts: 15
| | |
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
|  | Moderator | | Join Date: Dec 2006 Location: Northern Ireland / England
Posts: 546
| | | re: Connect to SQL Server CE
-
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\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
| | Newbie | | Join Date: Sep 2007
Posts: 15
| | | re: Connect to SQL Server CE
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.
| | Moderator | | Join Date: Dec 2006 Location: Northern Ireland / England
Posts: 546
| | | re: Connect to SQL Server CE
Quote:
Originally Posted by 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. 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
| | Newbie | | Join Date: Sep 2007
Posts: 15
| | | re: Connect to SQL Server CE
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?
| | Newbie | | Join Date: Jul 2008
Posts: 1
| | | re: Connect to SQL Server CE
bump......................................
| | Moderator | | Join Date: Dec 2006 Location: Northern Ireland / England
Posts: 546
| | | re: Connect to SQL Server CE
Quote:
Originally Posted by 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.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!)
| | Newbie | | Join Date: Sep 2007
Posts: 15
| | | re: Connect to SQL Server CE
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?
| | Moderator | | Join Date: Dec 2006 Location: Northern Ireland / England
Posts: 546
| | | re: Connect to SQL Server CE
Quote:
Originally Posted by 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? 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.
| | Newbie | | Join Date: Oct 2008
Posts: 1
| | | re: Connect to SQL Server CE
Quote:
Originally Posted by markmcgookin 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.
| | Moderator | | Join Date: Dec 2006 Location: Northern Ireland / England
Posts: 546
| | | re: Connect to SQL Server CE
Quote:
Originally Posted by eqquito 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
|  |
Similar Mobile Development bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
