I have a problem with inserting to my SQL CE Database.
I have wrote some code, then when I needed a DB, I have right clicked on projected, added new item, Local Database, after that it offered me to choose a datamodel - I selected 'dataset'.
This has created a DB for me, under Server Explorer on my left, and same .sdf file is seen on my right, in solution explorer.
I start my application, I run an insert query, it gives me output that insert was successful, I see that .sdf file under root/bin/Debug/db.sdf was just modified, I close my application, but my original database located at /root/db.sdf. If I query DB from Server explorer, I see no changes/inserted rows. Here is the code I use:
First I have tried sever Data Source options, all uncommented ones did not work for me.
//String connectString = @"Data Source=" + '"' + @"C:\Users\Alex\Documents\Visual Studio 2012\Projects\my\my\myDB.sdf;" + '"';
//String connectString = "Data Source:=" + '"' + @"C:\Users\Alex\Documents\Visual Studio 2012\Projects\my\my\my.sdf" + '"';
//String connectString = "Data Source:=C:\\Users\\Alex\\Documents\\Visual Studio 2012\\Projects\\my\\my\\my.sdf";
//String connectString =@"Data Source:=C:\Users\Alex\Documents\Visual Studio 2012\Projects\my\my\myDB.sdf";
String connectString = "Data Source=myDB.sdf";
using (SqlCeConnection connection = new SqlCeConnection(connectString))
{
connection.Open();
String query = "Insert into items (id, title) VALUES ('" + ID + "', '" + title + "');
SqlCeCommand cmd = new SqlCeCommand(string.Format(query), connection);
int result = cmd.ExecuteNonQuery();
}
After closing application I do a right click on the items table, and 'show table data' - no inserts. What am I doing wrong?
Visual studio is probably creating a copy of your original solution item into the bin/Debug sub-folder (on every build). The original file is never touched while executing your code and your changes are possibly discarded the next time you build the application.
So depending on your use-case you either have to open the database in bin/Debug for viwewing the data or change your connection string to use the one located in the root of your project/solution.