ホーム
C#でデータ処理
C#でSQLite3のデータベースを使ってみる（ADO.NET ファクトリーデザインパターン）

C#でSQLite3のデータベースを使ってみる（ADO.NET ファクトリーデザインパターン）

C#でSystem.Data.SQLiteとADO.NETのファクトリーデザインパターンを使って、SQLite3のデータにアクセスしてみました。

SQLiteとは
ADO.NET ファクトリーデザインパターンとは
ファクトリーデザインパターンの使い方
試してみた

どういうアプリを作るか
System.Data.SQLiteをプロジェクトに追加する
アプリの外観をレイアウトする
コードを書く
動かしてみる
要注意

まとめ

SQLiteとは

アプリに組み込んでネットワーク無しで使えるリレーショナルデータベースです。

WindowsでPC内だけでデータベースを使おうとすると、まずExcelのワークシートが第1の選択肢になると思います。ただし、データの量の上限が厳しいですね。データがシートの上限を超えるようになると、シートを分けたりブックを分けたりしてデータの再利用がしづらくなっていきます。そこでデータベースの導入を考えるわけですが、手軽に導入しようとしたらAccessを考えるでしょう。Macな人ならFileMakerですね。ところが、多くのPCにバンドルされているであろうOffice Home and BuisinessにはAccessが含まれていません。そうすると、PCローカルで使うデータベースの選択肢としては、MicrosoftのSQLServerを使うかSQLiteかという選択になるのです。

SQLite自体は古くからあるもので、Androidでは標準的に使われているものです。様々なOS用のSQLiteがあるので、データがOSに依存しなさそうというのも良さげです。

ADO.NET ファクトリーデザインパターンとは

ADO.NET 2.0で導入された仕組みとのことです。DataAdapterでのデータベースアクセスを、データベースの種類に依存しないようにコーディングする仕組みと考えれば良いでしょうか。MSDNのファクトリモデルの概要に解説があります。

ADO.NET 2.0って、相当古いですよね。今時のC#では、Entity Frameworkを使うのが正道のようです。

これから作りたいアプリが下記の環境での使用を想定しているので、非接続型のデータアクセスが良かろうと考えるわけです。

ネットワークドライブにデータファイルを置く
多くの人は読み取るだけ
クライアントPCは割とリッチな環境
データベースのレコード数が6万は超えるかもしれないけど数百万とかの大規模にはならない
複雑なJOINとかはしない

非接続型のデータアクセスで読み取りの方が頻度が高いのなら、DataAdapterでもよかろうと考えたわけです。まずDataAdapterを使ってみないと、Entity Frameworkのありがたさもわからないでしょうし。

System.Data.SQLite自体で定義されているクラスを使う方がなんとなく速いかもと思うのですが、Accessを使うこともあろうとも思いまして、とりあえずコードの共有が容易なファクトリーデザインパターンを使ってみます。

ファクトリーデザインパターンの使い方

MSDNの DbDataAdapter を使用したデータの変更というページに解説があります。これに沿って作ってみます。

試してみた

どういうアプリを作るか

「hoge」というテーブルを持つSQLite3のデータベースファイルがあるとします。PupSQLiteで中を見てみると、初期のデータが下記のようになっています。idフィールドはINTEGER、field1フィールドはSTRINGです。このデータを①GridViewに表示する②レコードを追加する③データベースファイルに書き込む、というWPFでXAMLなアプリを作ります。

System.Data.SQLiteをプロジェクトに追加する

NuGetでSystem.Data.SQLiteを追加します。

そうすると、自動的にApp.configにDbProviderFactories要素が追加されます。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    <remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
  </system.data></configuration>

DbProviderFactories要素の中にSystem.Data.SQLiteという項目がありますね。そのまま編集無しで使えました。

アプリの外観をレイアウトする

ウィンドウフォームに、DataGridを１つとButtonを３つドロップして、レイアウトを整えます。

そして、XAMLのDataGridにバインディングの設定をします。下記にXAMLの一部を抜粋します。ItemsSourceの項目です。

<DataGrid x:Name="dataGrid" ItemsSource="{Binding}" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Height="106" Width="175">;

コードを書く

コードは下記のようにしてみました。

using System;
using System.Windows;
using System.Data;
using System.Data.Common;

namespace SQLite3_trial
{
    public partial class MainWindow : Window
    {
        static DataSet dataset = new DataSet(); // DataSetのインスタンスを作る
        DataTable table = dataset.Tables.Add(); // DataSetにテーブルを追加する

        public MainWindow()
        {
            InitializeComponent();
        }

        private void buttonLoad_Click(object sender, RoutedEventArgs e)
        {
            // 読み込み
            table.Clear();
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = "Data Source=test.db"; // ファイル名を指定する
                    using (connection)
                    {
                        // コマンドを作る
                        DbCommand command = connection.CreateCommand();
                        command.CommandText = "SELECT * FROM hoge";
                        command.CommandType = CommandType.Text;
                        command.Connection = connection;

                        // DataAdapterを作る
                        DbDataAdapter adapter = factory.CreateDataAdapter();
                        adapter.SelectCommand = command;

                        // 読み込み
                        adapter.Fill(table);
                    }

                    // テーブルをDataGridにバインディングする
                    if (dataGrid.DataContext == null)
                    {
                        dataGrid.DataContext = table;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonSave_Click(object sender, RoutedEventArgs e)
        {
            // 書き出し
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
                using (DbConnection connection = factory.CreateConnection())
                {
                    connection.ConnectionString = "Data Source=test.db"; // ファイル名を指定する
                    using (connection)
                    {
                        // コマンドを作る
                        DbCommand command = connection.CreateCommand();
                        command.CommandText = "SELECT * FROM hoge";
                        command.CommandType = CommandType.Text;
                        command.Connection = connection;

                        // DataAdapterを作る
                        DbDataAdapter adapter = factory.CreateDataAdapter();
                        adapter.SelectCommand = command;

                        // INSERT, UPDATE, DELETEコマンドを作る
                        DbCommandBuilder builder = factory.CreateCommandBuilder();
                        builder.DataAdapter = adapter;
                        adapter.InsertCommand = builder.GetInsertCommand();
                        adapter.UpdateCommand = builder.GetUpdateCommand();
                        adapter.DeleteCommand = builder.GetDeleteCommand();

                        // 書き込み
                        adapter.Update(table);
                    }
                    MessageBox.Show("Saved.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonAdd_Click(object sender, RoutedEventArgs e)
        {
            DataRow row = table.NewRow();
            row[1] = "add";
            table.Rows.Add(row);
        }
    }
}

忘れないようにちょっと解説します。

DataAdapterを使うので、usingのところにSystem.DataとSystem.Data.Commonを追加します。

using System;
using System.Windows;
using System.Data;
using System.Data.Common;

グローバル変数的にデータセットのインスタンスを作って、データセットにデータテーブルを追加します。こういう使い方は問題ありそうな気もしますが、どうやって回避するんだろ。

static DataSet dataset = new DataSet(); // DataSetのインスタンスを作る
DataTable table = dataset.Tables.Add(); // DataSetにテーブルを追加する

読み込み部分です。コメントの通りです。

App.configのSystem.Data.SQLiteを指定してDbProviderFactoryインスタンスを作る
DbProviderFactory.CreateConnection()でDbConnectionインスタンスを作る
DbConnectionインスタンスのConnectionStringプロパティに、ファイル名を指定する
DbConnectionインスタンスでデータベースに接続する
DbConnection.CreateCommand()でDbCommandインスタンスを作る
DbCommandインスタンスの各プロパティを設定する
DbProviderFactory.CreateDataAdapter()でDbDataAdapterインスタンスを作る
DbDataAdapterインスタンスのプロパティにDbCommandインスタンスを渡す
DbDataAdapter.Fill()メソッドでデータをデータテーブルに読み込む
データベースをクローズする

流れは上記ですが、コードを見た方が早いかも。データベースのopen/closeはusingにしてサボってます。

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = "Data Source=test.db"; // ファイル名を指定する
    using (connection)
    {
        // コマンドを作る
        DbCommand command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM hoge";
        command.CommandType = CommandType.Text;
        command.Connection = connection;

        // DataAdapterを作る
        DbDataAdapter adapter = factory.CreateDataAdapter();
        adapter.SelectCommand = command;

        // 読み込み
        adapter.Fill(table);
    }

    // テーブルをDataGridにバインディングする
    if (dataGrid.DataContext == null)
    {
        dataGrid.DataContext = table;
    }
}

続いて書き出し部分です。DbProviderFactory.CreateCommandBuilder()でコマンドを作るところが読み取りと大きく違うところです。

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = "Data Source=test.db"; // ファイル名を指定する
    using (connection)
    {
        // コマンドを作る
        DbCommand command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM hoge";
        command.CommandType = CommandType.Text;
        command.Connection = connection;

        // DataAdapterを作る
        DbDataAdapter adapter = factory.CreateDataAdapter();
        adapter.SelectCommand = command;

        // INSERT, UPDATE, DELETEコマンドを作る
        DbCommandBuilder builder = factory.CreateCommandBuilder();
        builder.DataAdapter = adapter;
        adapter.InsertCommand = builder.GetInsertCommand();
        adapter.UpdateCommand = builder.GetUpdateCommand();
        adapter.DeleteCommand = builder.GetDeleteCommand();

        // 書き込み
        adapter.Update(table);
    }
    MessageBox.Show("Saved.");
}

動かしてみる

まず、ビルドして起動して、「Load」ボタンを押した状態です。DataGridにデータが表示されます。

「Add」ボタンを押すと、レコードが追加されます。

「Save」ボタンお押すと、データベースに書き込みします。書き込みされたデータをPupSQLiteで開いてみると、レコードが追加されてますね。

要注意

接続型のデータアクセスではデータベースに接続したままデータの読み取りと書き出しをするので排他制御できますが、ここで書いたコードでは思いっきり非接続型のでデータアクセスです。ユーザーが書き込みするまでの間に別のユーザーが書き込みする可能性があるので、使い方に要注意です。

まとめ

この使い方だとRDBMSを単純にデータストアとして使うということになります。それはそれで便利です。

Previous: C#でMVVMパターンのアプリを作ってみた（Microsoft.TeamFoundation.Controls無し編） Next: C#でLinq to objectの結果をDataTableにしてみた

公開日 2016-02-08

C#でSQLite3のデータベースを使ってみる（ADO.NET ファクトリーデザインパターン）

SQLiteとは

ADO.NET ファクトリーデザインパターンとは

ファクトリーデザインパターンの使い方

試してみた

どういうアプリを作るか

System.Data.SQLiteをプロジェクトに追加する

アプリの外観をレイアウトする

コードを書く

動かしてみる

要注意

まとめ

C#でデータ処理カテゴリの投稿

某エンジニアのお仕事以外のメモ（分冊2）

Recent Posts

Tags

Categories