Unable to access SQLite Database form windows services

Question

I have developed an ADD-in for outlook 2010. on outlook startup, ADD-in stored all the mail subjects in SQLite database.

Now i have to create a windows services that will also access same database, and post it on rest-based services. But I am getting an error.

Windows services is not able to access that database.

 static void Main()
    {
        //try
        //{
            //=======Connection string=============
        string dbfile = @"D:\testdatabase.db";

        sql_con = new SQLiteConnection
            ("Data Source= " + dbfile + ";Version=3;New=False;Compress=True;");
            //=======Open Connection=============
            sql_con.Open();
            //=======Create dataset and store value in dataset===================
            sql_cmd = sql_con.CreateCommand();
            string CommandText = "select * from mailbackup";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            //DS.Reset();
            DB.Fill(DS);
            //=======Store dataset value in text file============
            StringBuilder str = new StringBuilder();
            int rows = DS.Tables[0].Rows.Count;
            for (int i = 0; i < rows;i++ )
            {
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[0].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[1].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[2].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[3].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[4].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[5].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[6].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[7].ToString());
                str.AppendLine(DS.Tables[0].Rows[i].ItemArray[8].ToString());
                StreamWriter sw = null;
                sw = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\LogFile.txt", true);
                sw.WriteLine(DateTime.Now.ToString() + ": " + str.ToString());
                sw.Flush();
                sw.Close();
            }
                //=======Close Connection=============
                sql_con.Close();
            //storedata();
        //}
        //catch(System.Exception ex)
        //{

        //}
    }

I am getting an error

An unhandled exception of type 'Finisar.SQLite.SQLiteException' occurred in SQLite.NET.dll

Additional information: unsupported file format

it is working fine when we change the connection string

 sql_con = new SQLiteConnection
        ("Data Source= " + dbfile + ";Version=3;New=True;Compress=True;");

Create new database and open connection but after creating table using sqlite browser or SQLiteStudio, It start throwing the same error.

Answer 1

It doesn't matter what kind of application you are running in the windows service. The issue comes from the SQLite component.

An unhandled exception of type 'System.IO.FileLoadException' occurred in mscorlib.dll Additional information: Mixed mode assembly is built against version v2.0.50727 of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

In order to use a CLR 2.0 mixed mode assembly, you need to modify your App.Config file to include:

<?xml version="1.0"?>
 <configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
     <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>

The key is the useLegacyV2RuntimeActivationPolicy flag. This causes the CLR to use the latest version (4.0) to load your mixed mode assembly. Without this, it will not work.

Note that this only matters for mixed mode (C++/CLI) assemblies. You can load all managed CLR 2 assemblies without specifying this in app.config.

Anyway, the Considerations for server-side Automation of Office article states the following:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution.