Options for using System.Data.SQLite in a 32bit and 64bit C# world

Question

I understand WHY the System.Data.SQLite.dll is provided in 32 bit and 64 bit builds. So lets not dwell on that and move on. :)

Since it is done this way it seems to make pure C# development a tad more difficult with 3 choices to make.

1. Is to support only 32-bit and force there managed assembly to compile x86 and deal with that in running in 32 or 64 bit, and there by lose advantages of when you are on a 64 bit environment.

2. Is to force 64 bit and only support 64 bit and losing the ability to run on 32 bit but gaining all the advantages of 64 bit.

3. Is to create two versions of their assembly one that compiles x86 and uses 32 bit SQLite and another that compiles x64 and uses 64bit SQLite. It prevents using "ANY" as a compile option and being able to easily deploy a single build to either type. Its not so horrible to manage from a development point of view as we will need two projects. Only having the C# code officially in one, and the other will just use "links" to the code in the other. This is for compiling purposes only. Still leaves us with having to manage two outputs to for deployments.

With all that said I am only looking for confirmation that the above are the only correct choices.

If however there are other choices that I am overlooking please let me know. Specifically if there is way to get a single C# DLL that can compile to ANY so it can take advantage of 32 or 64 bit depending on where its run and still use System.Data.SQLite.dll.

Protozoan

Seems so far from the comments that it is confirmed that there is no no ONE DLL option that can support ANY CPU platform target solution with SQLite as a dependency. – Protozoan

You should install it from nuget. Please refer to [this answer][1]. [1]: https://mcmap.net/q/587670/-sqlite-with-vs2012-and-net-4-5-any-cpu-build – Festal

NuGet doesn't really solve the issue at hand with this very very old question from Aug 2011 which has already been answered. Also NuGet is not a possibility in all development shops, so is not always a option even when it is the best solution to an issue. I have actually evolved to a better solution over the years that allows for a wrapper assembly that is ANY CPU and properly supports 32 and 64 bit SQLite flavors dynamically at run time. However this is an old closed issue so people should move to Q&A's that are more recent as software ages quickly. – Protozoan

You're right about nuget not being used in some projects but I don't understand your first sentence: "NuGet doesn't really solve the issue at hand" Why? And since this question is showing up in the first page in google search, could you share your "Any CPU wrapper" solution? – Festal

NuGet is a package manager it cannot solve deployment or runtime issues. I cannot share the ANY CPU wrapper solution I created at this time since its is part of internal copyright source code libraries at the company I work for. While the implementation in that answer may be different than mine, if it is an ANY CPU wrapper as that answer claims it should work regardless of using NuGet or not. You should add a link to that answer as a comment to the selected answer to get the most visibility as most will skim a question and read the answer and the answers comments. – Protozoan

Answer 1

There are 2 common solutions for keeping your main application at AnyCPU:

• Install both the x86 and the x64 assemblies into the GAC: They can (should!) have identical assembly names and the GAC will automatically decide whether to use x86 or x64 version.

• Hook into AppDomain.AssemblyResolve and serve the right assemblies from subdirectories using Assembly.LoadFrom

Acuna

You could install it from nuget. Refer to: #19624323 – Festal

Answer 2

This is an elaboration of the answer from Springy76. Do this:

public class AssemblyResolver
{
    public static void HandleUnresovledAssemblies()
    {
        AppDomain currentDomain = AppDomain.CurrentDomain;
        currentDomain.AssemblyResolve += currentDomain_AssemblyResolve;
    }

    private static Assembly currentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    {
        if (args.Name == "System.Data.SQLite")
        {
            var path = Path.Combine(pathToWhereYourNativeFolderLives, "Native");

            if (IntPtr.Size == 8) // or for .NET4 use Environment.Is64BitProcess
            {
                path = Path.Combine(path, "64");
            }
            else
            {
                path = Path.Combine(path, "32");
            }

            path = Path.Combine(path, "System.Data.SQLite.DLL");

            Assembly assembly = Assembly.LoadFrom(path);
            return assembly;
        }

        return null;
    }
}

Make sure the paths generated point to the correct locations for either your 32 bit or 64 bit SQLite Dlls. Personally I've had good results with those in this NuGet package: http://www.nuget.org/packages/SQLitex64

(You only need to use the NuGet package to get hold of the compiled SQLite Dlls. Once you've got them, remove the reference to SQLite in your project created by NuGet and the NuGet package itself. Indeed, leaving the reference in place can interfere with this solution as SQLite will never be recognised as an unresolved assembly.)

Call 'HandleUnresolvedAssemblies()' as early as possible, preferably during any Bootstrapping.

Unitarianism

But with the reference to the dlls removed, the code can't find the namespace and methods of this class, and can't compile. How do you resolve this? – Forgiveness

This solution works for situations where you are using other libraries which handle the job of communicating with SQLite. Use of nHibernate with SQLite is one such example. If you want to access class methods directly then you'll need to look at another solution, such as the conditional build trick posted in another answer. – Unitarianism

Answer 3

The similar problem exists with Oracle's ODP.NET vis-a-vis native 32/64-bit OCI DLLs.

We have solved it by crating both 'x86' and 'x64' platform for our project and then manually editing our project file to use the conditional references:

<Choose>
<When Condition="'$(Platform)' == 'x64'">
  <ItemGroup>
    <Reference Include="Oracle.DataAccess, processorArchitecture=x64">
      <SpecificVersion>False</SpecificVersion>
      <HintPath>..\ThirdParty\ODP.NET\x64\Oracle.DataAccess.dll</HintPath>
    </Reference>
    <Content Include="..\ThirdParty\ODP.NET\x64\oci.dll">
      <Link>oci.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x64\orannzsbb11.dll">
      <Link>orannzsbb11.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x64\oraociei11.dll">
      <Link>oraociei11.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x64\OraOps11w.dll">
      <Link>OraOps11w.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
  </ItemGroup>
</When>
<When Condition="'$(Platform)' == 'x86'">
  <ItemGroup>
    <Reference Include="Oracle.DataAccess, processorArchitecture=x86">
      <SpecificVersion>False</SpecificVersion>
      <HintPath>..\ThirdParty\ODP.NET\x86\Oracle.DataAccess.dll</HintPath>
    </Reference>
    <Content Include="..\ThirdParty\ODP.NET\x86\oci.dll">
      <Link>oci.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x86\orannzsbb11.dll">
      <Link>orannzsbb11.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x86\oraociei11.dll">
      <Link>oraociei11.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Content Include="..\ThirdParty\ODP.NET\x86\OraOps11w.dll">
      <Link>OraOps11w.dll</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
  </ItemGroup>
</When>
</Choose>

This enabled us to avoid using 2 different projects. I'm sure you can do something similar for SQLite.

Sennar

Yeah that is a much more difficult to maintain solution because manually hacking the project files create hidden information that not all developers that come along will be aware of, that is why we have opt'd for the dual projects to accomplish this over hacking a file. Also still has a two DLL's to manage as the output. – Protozoan

This is just a variation of Option 3 with the same results. The goal of the question was finding NEW option with NEW results that gets you an ANY CPU platform target build that works with SQLite as a dependency. – Protozoan

@Rodney The goal of this solution is to avoid 2 projects, and in that sense it is "new". I never claimed it will be able to achieve "Any CPU". BTW, this is not really "hacking" - this is well-documented MSBuild syntax. If your project is at the root of project hierarchy then having 2 projects may well be a cleaner solution. If however, it is under the 10 or so levels of dependencies (as it is in our case), then I believe it is well worth avoiding to have to duplicate all the dependencies. – Sennar

Answer 4

Its not so horrible to manage from a development point of view as we will need two projects.

That's not true - you can use two build configurations within the same project. At deployment time, you'll need to build for both x86 and x64, but the code base and project can be the same.

I currently do this in a larger production project, both due to SQLite, but also other native/interop libraries that include x64 and x86 variants.

Eastbourne

That is basically the same thing as what I stated just manually hacking the project file to do it. You still end up with TWO managed DLL's a 32bit and a 64bit. – Protozoan

This is just a variation of Option 3 with the same results. The goal of the question was finding NEW option with NEW results that gets you an ANY CPU platform target build that works with SQLite as a dependency. – Protozoan

@Rodney: Yes, this is a variation of Option 3 - but it takes away one of the main pains with Option 3 in that you don't have to maintain two projects... – Eastbourne

Answer 5

Usually option one would be the way to go unless your app needs more than 4gb of memory. Remember a 32bit app on a 64bit OS has most of the advantages of 64bit without the many of the disadvantages. That is why x86 is the default target for .exe apps in VS 2010.

Natatorial

Right, and its more than just memory advantages, but that is the biggest noticeable one. There are issues access 64 bit areas of the OS from a 32 bit world, and you if you live it yo have to know how to maneuver around in this where you usually don't have to worry about these extra things with an ANY build. – Protozoan

Answer 6

Branko Dimitrijevic said, "I'm sure you can do something similar for SQLite." And that is correct. :)

Having the very same issue, I found Rodney's question and Branko's answer and tried it myself. For anyone who wants to see my working SQLite implementation, here you go:

<Choose>
  <When Condition="'$(Platform)' == 'x64'">
    <ItemGroup>
      <Reference Include="System.Data.SQLite, Version=1.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=AMD64">
        <SpecificVersion>False</SpecificVersion>
        <HintPath>System.Data.SQLite\x64\System.Data.SQLite.dll</HintPath>
      </Reference>
    </ItemGroup>
  </When>
  <When Condition="'$(Platform)' == 'x86'">
    <ItemGroup>
      <Reference Include="System.Data.SQLite, Version=1.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=AMD64">
        <SpecificVersion>False</SpecificVersion>
        <HintPath>System.Data.SQLite\x86\System.Data.SQLite.dll</HintPath>
      </Reference>
    </ItemGroup>
  </When>
</Choose>

<ItemGroup>
  <Reference Include="Microsoft.CSharp" />
  <Reference Include="System" />
  <Reference Include="System.configuration" />
  <Reference Include="System.Core" />
  <Reference Include="System.Windows.Forms" />
  <Reference Include="System.Xml.Linq" />
  <Reference Include="System.Data.DataSetExtensions" />
  <Reference Include="System.Data" />
  <Reference Include="System.Xml" />
</ItemGroup>

Of course, you can name HintPath to anything you like.

I found this to be the perfect solution: I can maintain a single project and quickly switch between target platforms as needed. The only potential downside is I can't see the Reference in the Solution Explorer. But that's a small price to pay for the overall functionality.

Begrime

Answer 7

You can also resolve this by changing the compile options in your Visual Studio:

To change your compile settings in Visual Studio:

1. Go to the startup project of your program.
2. Open the properties window.
3. Click the compile tab.
4. Click advanced compile options.
5. Change the target CPU options to x86.

Your program will now always run in 32 bit mode, even when run on a 64 bit machine.

You could also provide two distributions, one for each environment as mentioned above. While this will become the standard in the future, for my current project this was the best and easiest option.

Core

Answer 8

This is an old question, but I ran into the same problem with a project that has used SQLite since 2017.

I first upgraded to the latest version of System.Data.Sqlite.Core, 1.0.118. It has two interop shims, one for x86 and the other for x64. The upgrade was done with Nuget.

Changed our project to build in mixed mode, and it works fine on both 32 and 64 Windows in regards to Sqlite. No other changes were needed except that our setup kit now adds those two files.

Barbarossa