オレンジの国

残念ながらテーブルを直接書き込むうまい方法はない

SQLiteデータベースにインポートする大量のデータセットがありますが、Microsoft.Data.Sqliteに一括挿入APIが見つかりません 。ないからです！SQLiteには、データを一括挿入する特別な方法はありません。

Microsoft.Data.Sqliteへの一括挿入

Datatableあるのにな、そのまま一行書けばテーブルが出来ちゃえばいいのにと。

だけどそんなうまい方法はないらしいです。

２つのPrimaryKeyを持ったテーブル作成

using (var con = new SqliteConnection(connectionString))
{
    con.Open();
    using (SqliteCommand command = con.CreateCommand())
    {
        command.CommandText = "create table tm_product(ProductID INTEGER , AtributeCD INTEGER, AtrName TEXT,AtrJun INTEGER,PRIMARY KEY(ProductID,AtributeCD))";
        command.ExecuteNonQuery();
    }

    con.Close();
}

参考にしたサイトはこちら

書き込みサンプル

インサートを高速化するため、以下を留意する

1. トランザクションを使用
2. 同じINSERTコマンドを再利用

SqliteConnection con = new SqliteConnection(connectionString);
con.Open();
using (var transaction = con.BeginTransaction())
{
    var command = con.CreateCommand();
    command.CommandText =
    @"
        INSERT INTO tm_product (ProductID,AtributeCD,AtrName,AtrJun)
        VALUES ($ProductID,$AtributeCD,$AtrName,$AtrJun)
    ";


    var ProductIDParameter = command.CreateParameter();
    ProductIDParameter.ParameterName = "$ProductID";
    command.Parameters.Add(ProductIDParameter);

    var AtributeCDParameter = command.CreateParameter();
    AtributeCDParameter.ParameterName = "$AtributeCD";
    command.Parameters.Add(AtributeCDParameter);

    var AtrNameParameter = command.CreateParameter();
    AtrNameParameter.ParameterName = "$AtrName";
    command.Parameters.Add(AtrNameParameter);

    var AtrJunParameter = command.CreateParameter();
    AtrJunParameter.ParameterName = "$AtrJun";
    command.Parameters.Add(AtrJunParameter);


    // Insert a lot of data

    foreach(DataRow dr in dt.Rows)
    {
        ProductIDParameter.Value = dr["ProductID"];
        AtributeCDParameter.Value = dr["AtributeCD"];
        AtrNameParameter.Value = dr["AtrName"];
        AtrJunParameter.Value = dr["AtrJun"];
        command.ExecuteNonQuery();
    }

    transaction.Commit();
}
con.Close();

条件的には

dtは、他データベースから別で取得したdatatable

結論

620万件のデータをローカルSQLiteに書き込むのに18秒

ファイル容量は 24,824KBになった。

System.Data.SQLiteはパスワード暗号対応してない

SQLiteでできたDBにパスワードをつけたいと思ったのですが、System.Data.SQLiteでは対応していないようです。

パスワード文字列を入れるとエラーが

using (var con = new SQLiteConnection("Data Source=" + DBName + ";Version=3;Password=passwd;"))

Password構文が使えないらしく、暗号化libraryが組み込まれていないと怒られてしまう。

System.Data.SQLite.SQLiteException: 'SQL logic error
Cannot use "Password" connection string property: library was not built with encryption support, please see "https://www.sqlite.org/see" for more information'

https://www.sqlite.org/see

を見ると、SQLite Encryption Extension

ダウンロードしてコンパイルしないといけないの？なんかめんどくさそう。

Microsoft.Data.Sqliteを使う

ここがポイント！

dbファイルにパスワード暗号を利用したいのであれば、System.Data.SQLiteを使わないようにします。

Entity Framework Coreを利用

SQLiteEncryptionUsingEFCore

これは、Entity Framework Core（EFCore）を使用してSQLite暗号化データベースを使用する方法を示す実際の例です。

Visualstudio .NetアプリケーションでEntity Framework Coreを利用しSQLiteで暗号化できる事が記載してあります。

Microsoft.Data.Sqliteって何なの？

Entity Framework チームは、Brice のプロトタイプに基づいて Microsoft.Data.Sqlite を作成することを決定しました。 これにより、.NET Core の目標に沿った、軽量で最新の新たな実装を作成

System.Data.SQLite との比較

System.Data.Sqliteとの違い

• DataSet API (DataTable と DataAdapter を含む) は含まれていない
• データ型の処理方法がINTEGER、REAL、TEXT、および BLOB の 4 つだけ

もっと端的にいうなら

DataAdapter,DataTableが使えない（回避方法について後述）

DBType（データ型）が4種類しかない

って事です。

.net 4.7.2にてテスト

.NET framework 4.7.2にてテストを行います。新しいWPFプロジェクトを作成します。

EntityFrameworkCoreをNuGet

NuGetするもの

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Sqlite.Core

SQLitePCLRaw.bundle_e_sqlcipher

の４つです。

NuGetパッケージの管理をクリック

Microsoft.EntityFrameworkCore 3.1.10をインストール

Microsoft.EntityFrameworkCore.Design 3.1.10をインストール

Microsoft.EntityFrameworkCore.Sqlite.Core 3.1.10をインストール

SQLitePCLRaw.bundle_e_sqlcipher 2.0.4をインストール

SQLiteDB パスワード暗号付きテストアプリ

DB作成　パスワード暗号付きでSQLiteDBを作成します

DB書込　パスワード暗号付きSQLiteDBへレコードを書き込みます

DB読込　パスワード暗号付きSQLiteDBからテーブルをDataAdapterで接続TableにFillします

パスワード暗号付きDBの作成

SqliteConnectionStringBuilder　を使います

使用例

var connectionString = new SqliteConnectionStringBuilder(baseConnectionString)
{
    Mode = SqliteOpenMode.ReadWriteCreate,
    Password = password
}.ToString();

DB作成のコード

using System.Windows;
using System.Data;
using Microsoft.Data.Sqlite;

namespace sqliteMS
{
    /// <summary>
    /// MainWindow.xaml の相互作用ロジック
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        string connectionString = new SqliteConnectionStringBuilder("Data Source = test1.db ; Cache = Shared")
        {
            Mode = SqliteOpenMode.ReadWriteCreate,
            Password = "pass"
        }.ToString();

        private void B_MakeDB_Click(object sender, RoutedEventArgs e)
        {


            // コネクションを開いてテーブル作成して閉じる  
            using (var con = new SqliteConnection(connectionString))
            {
                con.Open();
                using (SqliteCommand command = con.CreateCommand())
                {
                    command.CommandText = "create table t_product(CD INTEGER  PRIMARY KEY AUTOINCREMENT, productname TEXT, price INTEGER)";
                    command.ExecuteNonQuery();
                }

                con.Close();
            }
        }

パスワード付きSQLite DBへの書き込み

private void V_DBWrite_Click(object sender, RoutedEventArgs e)
{

    using (var con = new SqliteConnection(connectionString))
    {
        con.Open();
        using (SqliteTransaction trans = con.BeginTransaction())
        {
            SqliteCommand cmd = con.CreateCommand();

            // インサート
            cmd.CommandText = "INSERT INTO t_product (productname, price) VALUES (@Product, @Price)";

            // パラメータセット
            cmd.Parameters.Add("Product", SqliteType.Text);
            cmd.Parameters.Add("Price", SqliteType.Integer);

            // データ追加
            cmd.Parameters["Product"].Value = t_Product.Text;
            cmd.Parameters["Price"].Value = int.Parse(t_Price.Text);
            cmd.ExecuteNonQuery();

            // コミット
            trans.Commit();
        }
    }
}

テーブル読込とDataAdapterを使いたい

Microsoft.Data.Sqliteを利用していると、DataAdapter,DataTableが使えないのですが、DataAdapter,DataTableを使う方法があります。

ここでSystem.Data.SQLiteを使ってDataAdapter,DataTableを拝借します。

System.Data.SQLiteをNuGETします。

private void B_DBRead_Click(object sender, RoutedEventArgs e)
{
    System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SQLite");

    using (SqliteConnection con = new SqliteConnection(connectionString))
    {
        // DataTableを生成します。
        var dataTable = new DataTable();

        // コマンドを作る
        System.Data.Common.DbCommand command = con.CreateCommand();
        command.CommandText = "SELECT * FROM t_product";
        command.CommandType = CommandType.Text;
        command.Connection = con;

        // SQLの実行
        var adapter = factory.CreateDataAdapter();
        adapter.SelectCommand = command;
        adapter.Fill(dataTable);

        dataGrid.DataContext = dataTable;
    }
}

暗号化できてる？読込試験

A5:SQL Mk-2を使って作成したDBファイルが読込できるか確認してみました。

とりあえず、読込はできません。パスワードを指定しても読み込めません。

環境

System.Data.Sqliteにて、VisualStudio2017 C# .NET framework4.6.1でSQLiteを使ってみます。

WPFアプリの作成

NuGetでSystem.Data.SQLiteを取得

NuGetパッケージの管理をクリック

検索テキストボックスにSystem.Data.SQLiteと入力

一番上に出てきたSystem.Data.SQLiteをインストールする

依存関係のあるパッケージも併せてインストールされます

DBとテーブルの作成

using System.Data.SQLite;

を加える

Xaml上にテーブル作成ボタンを配置

イベントに追加

private void B_MakeDB_Click(object sender, RoutedEventArgs e)
{
    // コネクションを開いてテーブル作成して閉じる  
    using (var con = new SQLiteConnection("Data Source=test.db"))
    {
        con.Open();
        using (SQLiteCommand command = con.CreateCommand())
        {
            command.CommandText = "create table t_product(CD INTEGER  PRIMARY KEY AUTOINCREMENT, productname TEXT, price INTEGER)";
            command.ExecuteNonQuery();
        }

        con.Close();
    }
}

実行すると、test.dbができている事を確認

DBへの接続

DB接続ボタンを設置

イベントに追加

private void B_Con_Click(object sender, RoutedEventArgs e)
{
    using (SQLiteConnection con = new SQLiteConnection("Data Source=test.db"))
    {
        try
        {
            con.Open();
            MessageBox.Show("接続に成功しました");
        }
        catch (Exception Ex)
        {
            MessageBox.Show(Ex.Message);
        }
    }
}

接続確認

データ書き込み

DB書き込み用のテキストボックス準備

using (SQLiteConnection con = new SQLiteConnection("Data Source=test.db"))
{
    con.Open();
    using (SQLiteTransaction trans = con.BeginTransaction())
    {
        SQLiteCommand cmd = con.CreateCommand();

        // インサート
        cmd.CommandText = "INSERT INTO t_product (productname, price) VALUES (@Product, @Price)";

        // パラメータセット
        cmd.Parameters.Add("Product", System.Data.DbType.String);
        cmd.Parameters.Add("Price", System.Data.DbType.Int64);

        // データ追加
        cmd.Parameters["Product"].Value = t_Product.Text;
        cmd.Parameters["Price"].Value = int.Parse(t_Price.Text);
        cmd.ExecuteNonQuery();

        // コミット
        trans.Commit();
    }
}

※めんどくさかったので、テキストボックスのinteger判断などはしてない

A5:SQL Mk-2を使って、テーブルが出来ているか、レコードができているか確認

ちゃんとできている

データ取得

dataGridを配置

<DataGrid x:Name="dataGrid" HorizontalAlignment="Left" Height="164" Margin="336,215,0,0" VerticalAlignment="Top" Width="348"  ItemsSource="{Binding}" AutoGenerateColumns="True" />

xamlにItemSource={Binding} AutoGenerateColumns=”True” を追記

using (SQLiteConnection con = new SQLiteConnection("Data Source=test.db"))
{
    // DataTableを生成します。
    var dataTable = new DataTable();

    // SQLの実行
    var adapter = new SQLiteDataAdapter("SELECT * FROM t_product",con);
    adapter.Fill(dataTable);

    dataGrid.DataContext = dataTable;
}

データを取得できたことを確認

パスワード暗号使えない

さて、DBをパスワード暗号化しようと思ったら使えませんでした。どうも、System.Data.Sqliteには標準で組みこまれていないようです。別途ソースコンパイルなどをしないといけないようでめんどくさそう。

そこで簡単に実装できる方法を試してみました。