Using SQLite in a managed C++ CLI Application

SQLite is a great embedded database engine written in C.  However, I did not find any good C++ CLI examples, so I wrote the following and posted it to the MSDN forums a few months ago in response to a question.  I decided to clean it up and post it here, I hope it is helpful.

First download and install the following .NET version of SQLite:

http://sqlite.phxsoftware.com/

Be sure to checkout the SQLite.NET.chm help file (this should have been installed with System.Data.SQLite).
On my machine was located at “C:\Program Files\SQLite.NET\Doc”.

Here is some sample code to get you going (add this first part at the top of your file with the other using namespace statements):

using namespace System::Data::SQLite;
using namespace System::Text;

Add this code in an event or function (I put it in my Form1_Load event for testing):

// Create The Connection Object
SQLiteConnection ^db = gcnew SQLiteConnection();
try
{
   // Open Database Connection
   MessageBox::Show("Opening Database Connection To MyDb.db ...");
   db->ConnectionString = "Data Source=MyDb.db";
   db->Open();
   MessageBox::Show("Database Connection To MyDb.db Opened.");

   // Create A Table
   try
   {
      MessageBox::Show("Creating Table ...");
      SQLiteCommand ^cmdCreateTable = db->CreateCommand();
      cmdCreateTable->CommandText = "CREATE TABLE MyTable (id INTEGER PRIMARY KEY, value STRING);";
      cmdCreateTable->ExecuteNonQuery();
      MessageBox::Show("Table Created.");
   }
   catch (Exception ^e)
   {
      MessageBox::Show("Error Executing SQL: " + e->ToString(), "Exception While Creating MyTable ...");
   }

   // Insert A Value
   try
   {
      MessageBox::Show("Inserting Value ...");
      SQLiteCommand ^cmdInsertValue = db->CreateCommand();
      cmdInsertValue->CommandText = "INSERT INTO MyTable VALUES(NULL, 'A Value');";
      cmdInsertValue->ExecuteNonQuery();
      MessageBox::Show("Value Inserted.");
   }
   catch (Exception ^e)
   {
      MessageBox::Show("Error Executing SQL: " + e->ToString(), "Exception While Inserting Value Into MyTable ...");
   }

   // Display Table
   try
   {
      MessageBox::Show("Displaying Table ...");
      SQLiteCommand ^cmdSelect = db->CreateCommand();
      cmdSelect->CommandText = "SELECT * FROM MyTable;";
      SQLiteDataReader ^reader = cmdSelect->ExecuteReader();
      StringBuilder ^sb = gcnew StringBuilder();
      for (int colCtr = 0; colCtr < reader->FieldCount; ++colCtr)
      {
         // Add Seperator (If After First Column)
         if (colCtr > 0) sb->Append("|");

         // Add Column Name
         sb->Append(reader->GetName(colCtr));
      }
      sb->AppendLine();
      sb->Append("~~~~~~~~~~~~");
      sb->AppendLine();
      while (reader->Read())
      {
         for (int colCtr = 0; colCtr < reader->FieldCount; ++colCtr)
         {
            // Add Seperator (If After First Column)
            if (colCtr > 0) sb->Append("|");

            // Add Column Text
            sb->Append(reader->GetValue(colCtr)->ToString());
         }
         sb->AppendLine();
      }
      MessageBox::Show(sb->ToString(), "SQLite MyTable");
   }
   catch (Exception ^e)
   {
      MessageBox::Show("Error Executing SQL: " + e->ToString(), "Exception While Displaying MyTable ...");
   }

   // Close Database Connection
   MessageBox::Show("Closing Database Connection To MyDb.db ...");
   db->Close();
   MessageBox::Show("Database Connection To MyDb.db Closed.");
}
finally
{
   // Dispose Database Connection
   delete (IDisposable^)db;
}

Note that if you are using Visual Studio 2010 and targeting the 4.0 .NET framework you will need to add a “Configuration File (app.config)” to your project and add the following to it (this is because the latest official version of SQLite.Net is compiled for 3.5 NET and you need to enable mixed mode loading):

1. Right click on the project name in the Solution Explorer
2. Click on the “Add” menu and then the “New Item” sub menu item.
3. Make sure you have selected “Visual C++” in the “Installed Templates” list (the left hand panel).
4. Select the “Configuration File (app.config) template in the center panel.
5. Click the “Add” button in the bottom right corner.
6. Add the the following:

<configuration>
   <startup useLegacyV2RuntimeActivationPolicy="true">
      <supportedRuntime version="v4.0"/>
   </startup>
</configuration>

The app.config file needs to be copied to be used by the running program.  C++ does not do this automatically, so you have to add a post-build step.

To add this post-build step:

1. Right Click on the Project Name in Solution Explorer.
2. Click On the Properties menu item.
3. In the left pane, expand the Configuration Properties node.
4. Then expand the Build Events node
5. Then click on the Post-Build Events leaf
6. Enter the following for the command line: copy app.config “$(TargetPath).config”
7. Hit Ok to save the property changes
8. Build and run your project.

NOTE: You will need to do this for both the DEBUG and the RELEASE versions of your project if you plan to distribute your program.

Don’t forget you will need to distribute the SQLite.Net files with your application.

I plan to post a Native Visual C++ Version soon.

Please let me know if this post was helpful to you.

// Create The Connection Object
SQLiteConnection ^db = gcnew SQLiteConnection();
try
{
// Open Database Connection
MessageBox::Show(“Opening Database Connection To MyDb.db …”);
db->ConnectionString = “Data Source=MyDb.db”;
db->Open();
MessageBox::Show(“Database Connection To MyDb.db Opened.”); 

// Create A Table
try
{
MessageBox::Show(“Creating Table …”);
SQLiteCommand ^cmdCreateTable = db->CreateCommand();
cmdCreateTable->CommandText = “CREATE TABLE MyTable (id INTEGER PRIMARY KEY, value STRING);”;
cmdCreateTable->ExecuteNonQuery();
MessageBox::Show(“Table Created.”);
}
catch (Exception ^e)
{
MessageBox::Show(“Error Executing SQL: ” + e->ToString(), “Exception While Creating MyTable …”);
}

// Insert A Value
try
{
MessageBox::Show(“Inserting Value …”);
SQLiteCommand ^cmdInsertValue = db->CreateCommand();
cmdInsertValue->CommandText = “INSERT INTO MyTable VALUES(NULL, ‘A Value’);”;
cmdInsertValue->ExecuteNonQuery();
MessageBox::Show(“Value Inserted.”);
}
catch (Exception ^e)
{
MessageBox::Show(“Error Executing SQL: ” + e->ToString(), “Exception While Inserting Value Into MyTable …”);
}

// Display Table
try
{
MessageBox::Show(“Displaying Table …”);
SQLiteCommand ^cmdSelect = db->CreateCommand();
cmdSelect->CommandText = “SELECT * FROM MyTable;”;
SQLiteDataReader ^reader = cmdSelect->ExecuteReader();
StringBuilder ^sb = gcnew StringBuilder();
for (int colCtr = 0; colCtr < reader->FieldCount; ++colCtr)
{
// Add Seperator (If After First Column)
if (colCtr > 0) sb->Append(“|”);

// Add Column Name
sb->Append(reader->GetName(colCtr));
}
sb->AppendLine();
sb->Append(“~~~~~~~~~~~~”);
sb->AppendLine();
while (reader->Read())
{
for (int colCtr = 0; colCtr < reader->FieldCount; ++colCtr)
{
// Add Seperator (If After First Column)
if (colCtr > 0) sb->Append(“|”);

// Add Column Text
sb->Append(reader->GetValue(colCtr)->ToString());
}
sb->AppendLine();
}
MessageBox::Show(sb->ToString(), “SQLite MyTable”);
}
catch (Exception ^e)
{
MessageBox::Show(“Error Executing SQL: ” + e->ToString(), “Exception While Displaying MyTable …”);
}

// Close Database Connection
MessageBox::Show(“Closing Database Connection To MyDb.db …”);
db->Close();
MessageBox::Show(“Database Connection To MyDb.db Closed.”);
}
finally
{
// Dispose Database Connection
delete (IDisposable^)db;
}