Cannot insert data into Local Database in WPF

问题:

In a project, I have a local database, (I am using the datagrid to view the data), but the problem is that when I insert into database using the insertion query, the insertion is not lasting, i.e. when I rerun the application, the database is not changed and the changes are not reflected in the database view (view table data in Visual Studio).

The insertion query is:

string dbCon = new     System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\MyDatabase.sdf";
        SqlCeConnection dbconnection = new SqlCeConnection("datasource=" + dbCon);

        dbconnection.Open();

        String Insert = "insert into MainTable(LineID,Date,Connection_Type)  Values('5','5/8/2012','commercial')";
SqlCeCommand cmd = new SqlCeCommand(Insert, dbconnection);

        //// Execute the command
        cmd.ExecuteNonQuery();

        dbconnection.Close();

Now when this code runs, the datagrid is updated, but the changes are not permanent. What is it that I am doing wrong? I am using Visual Studio 2010 Ultimate and Windows 7

回答1:

In what folder is your application running? If it is in the Program Files tree you likely do not have write permissions to the sdf file (especially on Windows 7 unless you have turned off UAC or are elevating your app prior to execution).

Even if that is not your short term issue I would recommend finding a different place for the sdf file, such as one of the AppData locations. Expecting to be able to write to your install location is not a guaruntee and depends on how and where your app is deployed.

回答2:

1. Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\MyDatabase.sdf" is the path to your application folder. Depending on installation options your file may not be installed there. Use a connection string of "DataSource=|DataDirectory|\\MyDatabase.sdf;..." as described here.

2. Even if your data is actually stored there, depending on user permissions the user may not be able to write to that directory. Also, when the user uninstalls or updates your app, his data will be deleted. People don't like that. You should copy the data to a user-writable folder; the earlier link shows how to do that as well.

3. If you have a Visual Studio project with a database a part of your project, it will probably have the "Copy to Output Directory" property set to "Copy". That means each time you run in the debugger you get a brand-new database copied from your source project, overwriting whatever was there before. Any changes will be in this temporary output folder, not in your original source data. When you debug the app again, the temporary copy is deleted. Further explanation in this answer.