CODEWITHSUNDEEP
c#sqlitevisual-studio-2012system.data.sqliteexcel-dna
Anand
Anand

Reputation: 1671

Using ExcelDNA and System.Data.SQLite

I have no issues using ExcelDNA and I also have no issues using System.Data.SQLite in a standalone exe. Howover I have not been able to make both work together for creating an Excel Addin dll (using Visual Studio 2012 and NuGet to install the SQLite .NET package) to be used to query an SQLite database. Not sure if any SQLite dependencies are not getting resolved. All I get: "A first chance exception of type 'System.IO.FileLoadException'" occurred in Unknown Module.and giving a VALUE error in the Excel cell. So was wondering if anyone here would have successfully used both together and if there are some gotchas I don't know. I am using the Northwind SQLite database for this test.

Upvotes: 0

Views: 929

Answers (1)

Govert
Govert

Reputation: 16907

Try these steps:

  • Create a new C# Class Library project
  • PM> Install-Package Excel-DNA
  • PM> Install-Package System.Data.SQLite.Core
  • Put the Northwind.db file (created from the .sql file you point to) in C:\Temp

  • Add this code in your project:

    using System;
using System.Data;
using System.Data.SQLite;
using ExcelDna.Integration;

namespace UsingSQLite
{
    public static class MyFunctions
    {
        static SQLiteConnection _connection;
        static SQLiteCommand _productNameCommand;

        private static void EnsureConnection()
        {
            if (_connection == null)
            {
                _connection = new SQLiteConnection(@"Data Source=C:\Temp\Northwind.db");
                _connection.Open();

                _productNameCommand = new SQLiteCommand("SELECT ProductName FROM Products WHERE ProductID = @ProductID", _connection);
                _productNameCommand.Parameters.Add("@ProductID", DbType.Int32);
            }
        }

        public static object ProductName(int productID)
        {
            try
            {
                EnsureConnection();
                _productNameCommand.Parameters["@ProductID"].Value = productID;
                return _productNameCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

    }
}

  • Press F5 to load and run in Excel.

  • Try =ProductName(1) in a cell. You should see "Chai" or a detailed exception message.

It works on my machine :-)

Note that you won't be able to pack the SQLite assembly into the .xll file using ExcelDnaPack, since the packing tool does not support mixed assemblies.

I've also added a sample project for this on GitHub: https://github.com/Excel-DNA/Samples/tree/master/UsingSQLite

Upvotes: 2

Related Questions