Copying data from sqlite to SQL Server

Question

Is there a way in C# without reading into a datatable to copy a sqlite table to SQL Server?

The sqlite database is in memory and contains millions of rows so I don't want to copy it to a datatable unless absolutely necessary.

thanks KevCri

Answer 1

Even though the question is old, I'd like to answer with some source code, for future reference. I had the exact same problem and found this post. I created this console application that will do the trick. It only works for tables, not views, and I have only tested on a limited dataset. But in theory it should convert/copy any SQLite database to Microsoft SQL Server, and only use little memory.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data;

namespace SQLite2MSSQL
{
  class Program
  {
    private static SQLiteConnection m_sqlitecon;
    private static SqlConnection m_sqlcon;

    private static List<KeyValuePair<string, string>> getSQLiteSchema(string tablename)
    {
        using (var cmd = new SQLiteCommand("PRAGMA table_info(" + tablename + ");", m_sqlitecon))
        {
          var table = new DataTable();

          SQLiteDataAdapter adp = null;
          try
          {
            adp = new SQLiteDataAdapter(cmd);
            adp.Fill(table);
            List<KeyValuePair<string, string>> res = new List<KeyValuePair<string, string>>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
              string key = table.Rows[i]["name"].ToString();
              string value = table.Rows[i]["type"].ToString();
              KeyValuePair<string, string> kvp = new KeyValuePair<string, string>(key, value);

              res.Add(kvp);
            }
            return res;
          }
          catch (Exception ex) { Console.WriteLine(ex.Message); }
        }
      return null;
    }

    private static void transfer(string tablename, List<KeyValuePair<string, string>> schema)
    {
      using (SQLiteCommand cmd = new SQLiteCommand("select * from " + tablename, m_sqlitecon))
      {
        using (SQLiteDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + tablename + " (");
            bool first = true;
            foreach (KeyValuePair<string, string> column in schema)
            {
              if (first)
                first=false;
              else
                sql.Append(",");
              sql.Append("["+column.Key+"]");
            }
            sql.Append(") Values(");
            first = true;
            foreach (KeyValuePair<string, string> column in schema)
            {
              if (first)
                first = false;
              else
                sql.Append(",");
              sql.Append("@");
              sql.Append(column.Key);
            }
            sql.Append(");");
            try
            {
              using (SqlCommand sqlcmd = new SqlCommand(sql.ToString(), m_sqlcon))
              {
                foreach (KeyValuePair<string, string> column in schema)
                {
                  sqlcmd.Parameters.AddWithValue("@" + column.Key, reader[column.Key]);
                }
                int count = sqlcmd.ExecuteNonQuery();
                if (count == 0)
                  throw new Exception("Unable to insert row!");
              }
            }
            catch (Exception Exception) {
              string message = Exception.Message;
              int idx = message.IndexOf("Violation of PRIMARY KEY");
              if (idx < 0)
                throw;
            }
          }
        }
      }
    }

    private static bool SQLTableExists(string tablename)
    {
      using (SqlCommand cmd = new SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + tablename + "'", m_sqlcon))
      {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          if (reader.HasRows)
            return true;
        }
      }
      return false;
    }


    private static string ReplaceCaseInsensitive(string str, string oldValue, string newValue)
    {
      int prevPos = 0;
      string retval = str;
      // find the first occurence of oldValue
      int pos = retval.IndexOf(oldValue, StringComparison.InvariantCultureIgnoreCase);

      while (pos > -1)
      {
        // remove oldValue from the string
        retval = retval.Remove(pos, oldValue.Length);

        // insert newValue in its place
        retval = retval.Insert(pos, newValue);

        // check if oldValue is found further down
        prevPos = pos + newValue.Length;
        pos = retval.IndexOf(oldValue, prevPos, StringComparison.InvariantCultureIgnoreCase);
      }

      return retval;
    }

    static void Main(string[] args)
    {
      // Connect to SQLite and SQL Server database
      m_sqlitecon = new SQLiteConnection("Data Source=C:\\sqlitedatabases\\mydb.sqlite;Version=3;");
      m_sqlitecon.Open();
      m_sqlcon = new SqlConnection("Data Source=localhost;Initial Catalog=mydb;User Id="+userid+";password="+password+";MultipleActiveResultSets=True");
      m_sqlcon.Open();
      string sql = "SELECT * FROM sqlite_master WHERE type='table'";
      SQLiteCommand command = new SQLiteCommand(sql, m_sqlitecon);
      SQLiteDataReader reader = command.ExecuteReader();
      List<string> tables = new List<string>();
      // Loop through all tables
      while (reader.Read())
      {
        string tablename = reader["name"].ToString();
        string sqlstr = reader["sql"].ToString();
        // Only create and import table if it does not exist
        if (!SQLTableExists(tablename))
        {
          Console.WriteLine("Creating table: " + tablename);
          // Vi retter SQLite SQL til M$ SQL Server
          sqlstr = ReplaceCaseInsensitive(sqlstr,"] BOOLEAN", "] bit");
          sqlstr = ReplaceCaseInsensitive(sqlstr,"] BLOB", "] varbinary(max)"); // Note, maks 2 GB i varbinary(max) kolonner
          sqlstr = ReplaceCaseInsensitive(sqlstr,"] VARCHAR", "] nvarchar");
          sqlstr = ReplaceCaseInsensitive(sqlstr, "] nvarchar,", "] nvarchar(max),");
          sqlstr = ReplaceCaseInsensitive(sqlstr, "] nvarchar\r", "] nvarchar(max)\r"); // Case windiows
          sqlstr = ReplaceCaseInsensitive(sqlstr, "] nvarchar\n", "] nvarchar(max)\n"); // Case linux
          sqlstr = ReplaceCaseInsensitive(sqlstr, "] INTEGER", "] int");
          sqlstr = ReplaceCaseInsensitive(sqlstr, "] TEXT", "] nvarchar(max)");
          SqlCommand sqlcmd = new SqlCommand(sqlstr, m_sqlcon);
           sqlcmd.ExecuteNonQuery();
          sqlcmd.Dispose();
          List<KeyValuePair<string, string>> columns = getSQLiteSchema(tablename);
          // Copy all rows to MS SQL Server
          transfer(tablename, columns);
        }
        else
          Console.WriteLine("Table already exists: " + tablename);
      }
    }
  }
}

Answer 2

An ODBC driver exists for SQLite. If you have access to an SSIS installation, you can import the tables using a Data Import task. Even easier is using the SQL Server Import/Export Wizard.

Another option is to create an ODBC DSN for the SQLite database, then use OPENROWSET to select the data directly from SQLite into SQL Server.