Best practice for SQLite with WPF and ASP.NET MVC?

Question

What is the correct way to work with SQLite in WPF and ASP.NET MVC?

• In WPF, I have a test.sqlite file into a directory called App_Data but when I compile and run it, it doesn't copy the database into ../bin/Debug so of course it can't find it. But if I manually copy the test.sqlite file into ../bin/Debug, then it works. But this can't be how one would develop an application which has a local database that it reads and writes to, right? How can I mark the database as "part of the application" so when it is compiled and run, it gets copied and can be used? Or is this part of the development/publish process? What am I missing here, I think I have a web metaphor here that just doesn't work with the Windows application way of doing things.

• In ASP.MVC, I have copied the same code that accesses the database in WPF, but it keeps saying it "can't open the database" on the connection.Open() line. I have "../App_Data/test.sqlite" which I understand is the right place to put it to access files. However, I tried to write a text file to "../App_data" and got a UnauthorizedAccessException" so I assume the problem is that I don't have right to read/write here. How can I change that?

      private string getData()
  {
      StringBuilder sb = new StringBuilder();
      //string fullPathAndFileName = Server.MapPath("App_Data/testnew.sqlite");
      using (SQLiteConnection conn = 
            new SQLiteConnection(@"Data Source=" + Server.MapPath("App_Data/testnew.sqlite")))
      {
          StringBuilder query = new StringBuilder();
          query.Append("SELECT * ");
          query.Append("FROM members ");
          query.Append("ORDER BY firstName");
          using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn))
          {
              conn.Open();
              using (SQLiteDataReader dr = cmd.ExecuteReader())
              {
                  while (dr.Read())
                  {
                      //Console.WriteLine(dr.GetValue(0) + " " + dr.GetValue(1));  
                      string id = dr.GetValue(0).ToString();
                      string firstName = dr.GetValue(1).ToString();
                      sb.Append(firstName + ", ");
  
                  }
              }
          }
      }
      return sb.ToString();
  
  }
  

Answer 1

You can add the test.sqlite file to your WPF project and in the properties window specify the "Copy To Output Directory" : "Copy always". This way every time you compile your project, the file will be automatically copied to the output directory.

Concerning your UnauthorizedAccessException have you tried granting read and write permissions on the App_Data folder for the ASP.Net Service account? To find out the account used to run the application pool you can write a simple test.aspx page:

<%@ Page Language="C#" %>
<script runat="server">
  protected override void OnLoad(EventArgs e)
  {
      Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name);
      base.OnLoad(e);
  }
</script>