Using Any CPU from multiple files (x64/x86)

Question

Brief: I'm using C-Sharp on VS2017 for targeting of an Sqlite3 file on a 64-bit Win10 machine. Framework usage is 4.5, as migration with Win7 is therefore graspable. Client does not want split versions of the release, therefore the product must support 'any cpu'.

Error ref: 'Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies. '

Target Platform: Any CPU

Cause of error (In this scenario): The DLL file targets x64 systems, whereas I'm forced to continue using the 'Any CPU' option.

Furthermore: I've downloaded both SQLite DLL files from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki . From there, I have also recovered accurate source files for SQLite3. Integration however, had an end result of my "using" line not 'seeing' the Filehandler folder, which contained SQLite source.

This is a repeated question, I've seen solutions ranging from NuGet packages to multiple responses referencing the line(s) 'myapp.exe/x86/sqlite3.data.dll' and 'myapp.exe/x64/sqlite3.data.dll' without identifying the app file location in which to insert those lines. Leaving one to wonder how they'd then be reference-able within class files.
Dynamic results show that with DLLImport, you're then able to rebuild methods (ie MYSQLConnector) as a method. Though then I suspect much repetition within the class, some client-side requirements on loading the project such as windows version reading.

repeat! repeat! repeat!

The client does not want NuGet packages to be used. As this project is not to include 3rd-party reference handling.

How do I resolve this? So that my form, using methods:

SQLiteConnection(connectionString)

and

SQLiteDataReader

Can be used with 'Any CPU' option.

Update

This is the underlying code for execution, resulting in the error as of 'Any CPU' being prioritised

void doWork(string path) {
            string valueFound = "";
            if (!System.IO.File.Exists(path)) throw new System.IO.FileNotFoundException("Cant find file", path);

            var connectionString = "Data Source=" + path + ";pooling=false";
            using (var conn = new SQLiteConnection(connectionString))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT value FROM values WHERE value = ''";
                    conn.Open();
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            valueFound = (String)reader["value1"];
                        });
                    }
                }
            }
            MessageBox.Show("We have a value: " + valueFound);
        } 

Answer 1

You can use the native library pre-loading feature as described on the System.Data.SQLite website:

If the development and customer machines may have different processor architectures, more than one binary package may be required. For this situation, using the native library pre-loading feature is highly recommended. It is available as of version 1.0.80.0 and enabled by default. In order to take advantage of this feature, the separate managed and interop assemblies must be used with XCOPY deployment (i.e. this feature is not supported by the mixed-mode assembly, nor when the assembly is deployed to the global assembly cache), resulting in an application deployment that looks something like this: 

• <bin>\App.exe (optional, managed-only application executable assembly)
• <bin>\App.dll (optional, managed-only application library assembly)
• <bin>\System.Data.SQLite.dll (required, managed-only core assembly)
• <bin>\System.Data.SQLite.Linq.dll (optional, managed-only LINQ assembly)
• <bin>\System.Data.SQLite.EF6.dll (optional, managed-only EF6 assembly)
• <bin>\x86\SQLite.Interop.dll (required, x86 native interop assembly)
• <bin>\x64\SQLite.Interop.dll (required, x64 native interop assembly)

The string "<bin>" above represents the directory where the application binaries are to be deployed on the target machine. With the native library pre-loading feature enabled and the application deployment shown above, the System.Data.SQLite managed-only assembly will attempt to automatically detect the processor architecture of the current process and pre-load the appropriate native library.

---

Step by Step instruction on how to use the pre-loading feature without the Nuget package.

1. Remove all copies of any System.Data.SQLite.dll you have in your project and make sure there is no System.Data.SQLite.dll registered in the Global Assembly Cache on your system.

2. In Your Project folder, add a subfolder names "x64" and a subfolder named "x86".

3. In order to use this, you must download two ZIP archives from the System.Data.SQLite website.

4. Download the second ZIP file listed under "Precompiled Binaries for 64-bit Windows (.NET Framework 4.5)", the one where it does NOT say "mixed-mode". Currently, this is http://system.data.sqlite.org/downloads/1.0.109.0/sqlite-netFx45-binary-x64-2012-1.0.109.0.zip

Copy the file System.Data.SQLite.dll from this zip to your project folder. Copy the file SQLite.Interop.dll from this zip to the x64 folder below your project folder.

5. Download the second ZIP file listed under "Precompiled Binaries for 32-bit Windows (.NET Framework 4.5)", the one where it does NOT say "mixed-mode". Currently. this is http://system.data.sqlite.org/downloads/1.0.109.0/sqlite-netFx45-binary-Win32-2012-1.0.109.0.zip

Copy the file SQLite.Interop.dll from this zip to the x86 folder below your project folder.

6. In your Visual Studio project, add these three files as links to your project, so that it looks like this:

7. In your Visual Studio project, select these three files and set their property "Copy to Output Directory" to "Copy always".

8. In your Visual Studio project, under references, remove a reference to System.Data.SQLite if there is already one and instead add a reference to the System.Data.SQLite.dll that you copied into your project directory. Set the property "Specific Version" to true.

9. Build the project.

10. You will now see this file structure in your output directory and everything will work as expected independent of whether the application runs in 32bit or 64bit mode.

11. For distributing the application, those three dlls have to be distributed as well keeping the same file structure and not registering the DLLs in the Global Assembly Cache.

---

The statically linked variant:

Note, that following the above steps you also have to distribute the x86 and x64 versions of the "Visual C++ 2012 Update 4 runtime" by Microsoft on customer pcs. If you don't do that, then download the ZIP files that are marked as "Precompiled Statically-Linked Binaries" instead in steps 4 and 5.

The only difference lies in how the two files named SQLite.Interop.dll are compiled in this variant.

---

Explanation on the background:

You can download two different variants of the System.Data.SQLite.dll:

mixed-mode

There is the mixed-mode variant. In this variant, this same dll (System.Data.SQLite.dll) contains both the actual unmanaged SQLite source code as well as the managed .net wrapper. Mixed-Mode assemblies (because they contain unmanaged native code) are bound to either a 32bit or 64bit environment during compilation. Therefore, there is a 32bit version of the mixed-mode System.Data.SQLite.dll, and there is a 64bit version of the mixed-mode System.Data.SQLite.dll.

When you reference either of these two mixed-mode assemblies, your application can only run in either 32bit or 64bit mode.

managed-only

Then there is the managed-only variant. In this variant, the System.Data.SQLite.dll contains only the managed wrapper code. In this variant, the assembly is an ANY CPU assembly. It can be used in both 32bit and 64bit processes.

The actual unmanaged SQLite code is then contained in a dll named SQLite.Interop.dll. This is a native (unmanaged, no .net) dll. There is a 32bit version of SQLite.Interop.dll. And there is a 64bit version of SQLite.Interop.dll.

The managed-only variant of System.Data.SQLite.dll determins the processor architecture of the current process when loaded and then tries to load the appropriate SQLite.Interop.dll from a subdirectory with the processor architecture name.

---

Alternative approach using the Global Assembly Cache

Alternatively, you can get both version (32bit and 64bit) of the mixed-mode variant of System.Data.SQLite.dll and install both of them in the Global Assembly Cache both on your development machine and on customer computers. Your application will then automatically choose the version with the correct processor architecture from the Global Assembly Cache during runtime.