SQLite configuration for C# application targeting any CPU - Part 1

Introduction

SQLite database is very light-weight and does not depend on any third party products or servers. A lot of articles[2]-[5] help you to pick up quickly writing Windows Application in C# with SQLite. After you finish your windows applications, then you will face a question: how do you deploy your application to client machines smoothly? As you know, client machine can be 32-bit or 64-bit. How do you let your Windows Application working on any kind of Windows platform, no matter 32-bit or 64-bit?

I search on Internet and find scattered thoughts on this topic. So here I glean related pieces into a understandable post here.

Which version of ADO.NET?

Based on reference [1], we need to decide is to download the right type of binary packages because we will consider how to deploy our application to customer machines.Our decision process is going through the following steps:

All packages without "static" on their names require appropriate version of Microsoft Visual C++ runtime library to be installed successfully on target machine. The downloadable packages with "setup" in their names already include and will attempt to automatically install required Microsoft Visual C++ runtime library. It means the installer has more work to do on target machines. Therefore we look for the packages with "static" in their names.

We will download "static" packages because these assembly binaries are linked statically to the appropriate version of the Visual C++ runtime. When your application is deployed to customer machines, you can not assume that customer machines have the necessary version of the Visual C++ runtime installed because customers may not have enough privileges.

We will not consider "Bundle" packages because it targets to deploy the assembly to Global Assembly Cache(GAC) folder.

We also need to identify the version of the .NET Framework being targeted by our application. At this time I use Visual Studio 2015 and the application targets to .NET framework 4.5, but customer machines may not have .NET 4.5 framework installed. To be safe, we can download the static linked packages targeted to .NET framework 4.0., but based on guidelines: Choosing the package matching the version of the .NET Framework being targeted is highly recommended, we decide to choose the version targeted to .NET framework 4.5.

Based on [1], we know assembly System.Data.SQLite.dll is managed-only core assembly, and assembly SQLite.Interop.dll is native interop assembly(x32 or x64).

If we build our C# application into x86 assembly, then we run it on x64 Windows machine. We will have a problem. On customers/target 64-bit machines, because out executable that starts the process consists entirely of managed code, it will run with the native processor architecture of the machine, which will be x64 on an x64 machine. Later on, this will cause assemblies containing any native code compiled for x86 (e.g. the "System.Data.SQLite.dll" mixed-mode assembly, the "SQLite.Interop.dll" native interop assembly, or the "sqlite3.dll" native library) to fail to load, typically resulting in a BadImageFormatException being thrown based on [1].

For this x86 build scenario, we will use the native library pre-loading feature and it is available as of version 1.0.80.0 and enabled by default.

Decision

Based on above reasons, if we want our application targeting any CPU, we will use both of the following packages:

<bold>Package 1: Precompiled Statically-Linked Binaries for 32-bit Windows (.NET Framework 4.5)

1. sqlite-netFx45-static-binary-Win32-2012-1.0.101.0.zip(it contains x86 version of System.Data.SQLite.dll and SQLite.Interop.dll)

<bold>Package 2: Precompiled Statically-Linked Binaries for 64-bit Windows (.NET Framework 4.5)

1. sqlite-netFx45-static-binary-x64-2012-1.0.101.0.zip(it contains x86 version of System.Data.SQLite.dll and SQLite.Interop.dll)

Project Folder Structure

Based on above consideration, we will use XCOPY to deploy our application into targte machines with file strucutre as below:

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

Points of Interest

In this part 1, I propose a file structure. In next article, I will give a demo to approv e this concept. If you have any better ideas, please share with with CodeProject community memebers.

Thanks for reading this post.

Reference

1. System.Data.SQLite Downloads
2. Installing and Using SQLite on Windows
3. C# & SQLite 1007000
4. Using SQLite in your C# Application
5. WinForms | WPF: Using SQLite DataBase
6. How To Use Sqlite Database With Csharp
7. Using SQLite with C#
8. Getting started with SQLite in C#
9. How to Use and Connect To Sqlite in a Windows Application
10. SQLite tutorial
11. Getting started with SQLite and C#
12. How to create and connect to an SQLite database in C#
13. Portable databases: using SQLite with .NET
14. Getting started, using SQLite with .NET

History

• 04-11-2016. initialized the article.
• 04-16-2016. updated introduction and reference sections.
• 04-30-2016. finished the article.