mikeo_410

C#用SQLite3インタフェースDLL(sqlite3cs.dll)

  C#で記述したプログラムからsqlite3.dllを使うためのクラスライブラリです。

http://www.vector.co.jp/soft/winnt/prog/se467674.html
  

アプリケーション

  C#で作成したアプリケーションから、SQLite3データベース・ファイルを操作するためのインタフェース用のDLL（ラッパー）です。
  実際の動作は、SQLite3本来のsqlite3.dllが行います。
  http://www.sqlite.org/
の「DOWNLOAD」から、「Precompiled Binaries For Windows」「sqlitedll-3_6_6_2.zip」を使いました。

↓↑

Sqlite3cs.dll

↓↑

Sqlite3.dll

↓↑

データベース・ファイル

１．サンプル・プログラム 

  メニューのそれぞれのイベント処理で、データベース操作の説明をしようとするものです。

「DB作成」で、データベースファイルと、2つのテーブルを作成します。

「データ追加」で、説明のためのデータを挿入します。

「データ表示」で、ツリービューにデータのインデクスを表示します。

 

  下左隅の「DB情報」をクリックすると、sqlite3.dllのバージョン、データベース･ファイルのヘッダに書かれたバージョン、テーブル定義を表示します。

 

１．１．テーブルの構成

  menu と contents の、2つのテーブルを使います。

 

menu

 

contents

 

列名

型

 

列名

型

 

id

INTEGER PRIMARY KEY

 

id

INTEGER PRIMARY KEY

 

title

TEXT

 

title

TEXT

 

content_id

INT

 

graph

BLOB

 

level

INT

 

 

 

  menu.content_id には、cintents.idの値を入れ、menuテーブルの行から、contentsテーブルの行を指します。
  両テーブルのtitleは、テーブル名の扱いの説明のために同じ名前を付けました。行の見出しに使います。
  levelは、TreeView表示の階層を表します。
  graphには、ビットマップを入れ、型の説明に使います。

１．２．サンプル・データ

  SQLite3のデータベースは単一ファイルです。サンプルのデータベース･ファイルは、sample1.sqlite と言う名前で作られます。場所は、指定していないのでカレントディレクトリに作られます。Visual C# のデバッグモードでサンプルを実行中なら、プロジェクトのディレクトリ以下のbin/Debug/に作られます。

  10件の行を挿入します。 menu、contentsテーブルのtitleには、それぞれ「タイトル_n_M」「タイトル_n_C」の形式の文字列を入れます。
  levelには、適当に数値を入れTreeViewの階層を示します。
  graphには、レコード番号を64x64のビットマップで格納します。適当に背景色も変えてあります。

１．３．サンプルデータの表示

  menuテーブルのtitleの文字列を、levelの値に従ってTreeViewに表示します。
  TreeViewでタイトルを選ぶと、両テーブルの全てのカラムを、右のListBoxに表示します。上の図の表示でテーブル名がA.idのようになっているのは別名（アリアス）の例です。また、graphについては内容ではなく型が書かれています。これは各列を一律にrow[n].ToString()のようにして表示していることによります。
  右下の矩形は、graphに格納されていたビットマップです。

１．４．サンプルプログラムの操作

  メニューの「DB作成」「データ追加」「データ表示」を左から順にクリックします。それぞれ、成功しても、エラーでもダイアログが表示されます。
  TreeViewからタイトルを選んで、画面右側の表示が変わるのを見てください。
  やり直すには、「DB削除」をしてください。

２．プログラミング 

  サンプル・プログラムは、「Windowsフォームアプリケーション」です。メニューで各機能を起動するように、Form1.csにイベント処理メソッドとして書きました。
　エラー処理は、例外をスローする方法にしました。
　データベース・ファイルを開いたままにすると、他のプログラムがデータベースを開けないのでイベント処理ごとに、Open()－Close() します。

２．１．参照設定

　sqlite3cs.dll クラスライブラリを使うには参照の追加が必要です。

  1. 「ソリューションエクスプローラ」の「参照設定」の上でマウスの右ボタンメニューを表示。「参照の追加」「参照」で、sqlite3cs.dllを選び、参照を追加します。
  2. using SQLiteDatabase;
    を、加えます。

２．２．Form1のメンバ変数

  1. readonly string DB_FILE = "sample1.sqlite";//DBファイルのパス
  2. int[] ContentID;    //TreeViewのノードからcontentsテーブルのIDへ

２．３．データベース・ファイルの作成とテーブルの作成

  1. SQLite3オブジェクトを生成します。
    SQLite3 db = new SQLite3();
  2. データーベースファイルを作成します。
    db.Create(DB_FILE);
  3. テーブルを作成するSQL文を実行します。
    db.Execute("CREATE TABLE menu (...);");
  4. データベースをクローズします。
    db.Close();
  1. private void dB作成ToolStripMenuItem_Click(object sender, EventArgs e)
  2. {
  3.     SQLite3 db;
  4.     //DBファイルを作成。既存ならエラー
  5.     try
  6.     {
  7.         db = new SQLite3();
  8.         db.Create(DB_FILE);
  9.     }
  10.     catch(Exception ex)
  11.     {
  12.         MessageBox.Show("データベースファイル作成失敗\r\n" + ex.Message);
  13.         return;
  14.     }
  15.     //テーブルを作成
  16.     try
  17.     {
  18.         db.Execute("CREATE TABLE menu (id INTEGER PRIMARY KEY, title text, content_id INT, level INT);");
  19.         db.Execute("CREATE TABLE contents (id INTEGER PRIMARY KEY, title text, graph BLOB);");
  20.     }
  21.     catch (Exception ex)
  22.     {
  23.         MessageBox.Show("TABLE作成失敗\r\n" + ex.Message);
  24.     }
  25.     db.Close();
  26.     MessageBox.Show("完了");
  27. }

  db.Create()だけを行って、db.Close()すると、サイズがゼロのファイルが作られます。SQLite3のデータベース・ファイルに必要なヘッダも記録されないので、かならずテーブルを作成するときにdb.Create()をするようにします。

２．４．データー追加

  1. SQLite3オブジェクトを生成します。
    SQLite3 db = new SQLite3();
  2. データーベースファイルをオープンします。
    db.Open(DB_FILE);
    ここでは、開けなかった場合の処理を含めたOpneDB()の中で行っています。
  3. 重複したデータ追加を避けるため、テーブルが空なのを確認します。
  4. 10件のデータをmenuテーブルとcontentsテーブルに追加します。
    まず、contentsテーブルに追加して、そこで発番された id を取得し、menuテーブルのcontend_idの値に使います。
  5. データベースをクローズします。
    db.Close();
  1. private void データToolStripMenuItem_Click(object sender, EventArgs e)
  2. {
  3.     int[] levelSample=new int[]{0,1,2,0,1,2,0,1,2,2};
  4.     SQLite3 db = new SQLite3();
  5.     if (!OpenDB(db)) return;
  6.     try
  7.     {
  8.         //テーブルがあり空であるか確認
  9.         SQLite3.Statement stmt = db.Query("SELECT COUNT(*) from menu;");
  10.         ROW row = stmt.NextRow();
  11.         stmt.Close();//重要です
  12.         if ((row != null) && ((int)row["COUNT(*)"] != 0))
  13.         {
  14.             MessageBox.Show("既にデータがある。（行数取得失敗の場合も含まれる。）");
  15.             return;
  16.         }
  17.         //10行分のテストデータを追加
  18.         db.Execute("BEGIN;");
  19.         for (int i = 0; i < 10; i++)
  20.         {
  21.             string title = "タイトル_" + (i+1).ToString();
  22.             stmt = db.Prepare("INSERT INTO contents (title, graph) VALUES (?,?);");
  23.             stmt.Bind(1, title + "_C");
  24.             stmt.Bind(2, CreateSampleBitmap(i));
  25.             stmt.Execute(); //実行
  26.             stmt.Close();   //重要です
  27.             long lastId = db.GetLastInsertRowId();
  28.             stmt = db.Prepare("INSERT INTO menu (title, content_id, level) VALUES (?,?,?);");
  29.             stmt.Bind(1, title + "_M");
  30.             stmt.Bind(2, lastId);
  31.             stmt.Bind(3, levelSample[i]);
  32.             stmt.Execute(); //実行
  33.             stmt.Close();   //重要です
  34.         }
  35.         db.Execute("END;");
  36.         db.Close();
  37.         MessageBox.Show("完了");
  38.     }
  39.     catch (Exception ex)
  40.     {
  41.         db.Close();
  42.         MessageBox.Show("データ追加失敗\r\n" + ex.Message);
  43.     }
  44. }

  INSERT文の実行は、Prepare()とSQLite3.Statementクラスのメソッドを使います。?を含むINSERT文に、Bind()で値をセットしてから実行します。 SQLite3.Statementのインスタンスは、確実にクローズします。クローズしないとデータベースはBUSY状態のままで他の操作ができません。

２．５．TreeViewの表示 

  1. SQLite3オブジェクトを生成します。
    SQLite3 db = new SQLite3();
  2. データーベースファイルをオープンします。
    db.Open(DB_FILE);
    ここでは、開けなかった場合の処理を含めたOpneDB()の中で行っています。
  3. menuテーブルの全行を選択します。
    SQLite3.Statement stmt
        = db.Query("SELECT title,content_id,level FROM menu ORDER BY id;");
  4. menuテーブルを1行ずつ読み出して、levelの値でネストしながら、TreeViewを作ります。
    ROW row = stmt.NextRow();
  5. SQLite3.Statementオブジェクトをクローズします。
    stmt.Close();
  6. データベースをクローズします。
    db.Close();
  1. private void データ表示ToolStripMenuItem_Click(object sender, EventArgs e)
  2. {
  3.     SQLite3 db = new SQLite3();
  4.     if (!OpenDB(db)) return;
  5.     SQLite3.Statement stmt = db.Query("SELECT title,content_id,level FROM menu ORDER BY id;");
  6.     treeView1.Nodes.Clear();
  7.     List<TreeNode> list = new List<TreeNode>();
  8.     List<int> content_id = new List<int>();
  9.     TreeNode top = new TreeNode("INDEX");
  10.     int n = 1;
  11.     while (true)
  12.     {
  13.         ROW row = stmt.NextRow();
  14.         if (row == null) break;
  15.         TreeNode node = new TreeNode((string)row["title"]);
  16.         node.Name = n.ToString();
  17.         content_id.Add(n);
  18.         n++;
  19.         int level = (int)row["level"];
  20.         if (level == 0)
  21.         {
  22.             top.Nodes.Add(node);
  23.         }
  24.         else
  25.         {
  26.             int pos = level - 1;
  27.             while (pos > 0)
  28.             {
  29.                 if (list[pos] != null) break;
  30.             }
  31.             list[pos].Nodes.Add(node);
  32.         }
  33.         for (int i = list.Count; i <= level; i++)
  34.             list.Add(null);
  35.         list[level] = node;
  36.     }
  37.     stmt.Close();
  38.     ContentID = content_id.ToArray();
  39.     db.Close();
  40.     treeView1.Nodes.Add(top);
  41.     top.Expand();
  42. }

  TreeNodeオブジェクトのNameプロパティに、Form1のメンバ変数ContentID[]のインデクスを格納しています。ContentID[]には、各行のcontent_idの値を入れます。TreeViewの表示で、ノードが選択されたとき、contentsテーブルから対応する1行を読み出すのに使います。

 ２．６．ノード（コンテンツ）の表示

  TreeViewの表示で、ノードを選択が変更されたとき、対応するcontentsテーブルの行の内容を表示します。

  1. SQLite3オブジェクトを生成します。
    SQLite3 db = new SQLite3();
  2. データーベースファイルをオープンします。
    db.Open(DB_FILE);
    ここでは、開けなかった場合の処理を含めたOpneDB()の中で行っています。
  3. 選択されたノードのNameプロパティの値でContentID[]を引き、contentsテーブルの行を読み出します。
    SQLite3.Statement stmt = db.Prepare("SELECT ... WHERE B.id=?;");
    stmt.Bind(1, content_id);
    stmt.Execute(); //実行
    ROW row = stmt.NextRow();
    stmt.Close();   //重要
  4. contentsテーブルの内容を文字列でlistBox1に表示します。graphはビットマップで、右下に図形として描画します。
  5. データベースをクローズします。
    db.Close();
  1. private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
  2. {
  3.     int n = Convert.ToInt32(e.Node.Name);
  4.     int content_id = ContentID[n - 1];
  5.     SQLite3 db = new SQLite3();
  6.     if (!OpenDB(db, true)) return;
  7.     try
  8.     {
  9.         listBox1.Items.Clear();
  10.         SQLite3.Statement stmt = db.Prepare(
  11.               "SELECT A.*, B.* "
  12.             + "FROM menu AS A JOIN contents AS B ON A.content_id = B.id "
  13.             + "WHERE B.id=?;");
  14.  
  15.         stmt.Bind(1, content_id);
  16.         stmt.Execute(); //実行
  17.         ROW row = stmt.NextRow();
  18.         if (row != null)
  19.         {
  20.             for (int i = 0; i < row.Length; i++)
  21.                 listBox1.Items.Add(row.names[i]+" : "+row[i].ToString());
  22.             if (Array.IndexOf(row.names, "B.graph") >= 0)
  23.                 pictureBox1.Image = ToImage((byte[])row["B.graph"]);
  24.         }
  25.         stmt.Close();   //重要
  26.         db.Close();
  27.     }
  28.     catch (Exception ex)
  29.     {
  30.         db.Close();
  31.         MessageBox.Show("コンテンツ取得失敗\r\n" + ex.Message);
  32.     }
  33. }

  内容を表示するには、単にcontentsテーブルから1行読み出すだけで済みます。
  この例のSQL文は、別名（アリアス）の説明（後述）に使うことを意図しています。2つのテーブルを結合し、別名を使って読み出しています。

３．sqlite3cs.dll クラスライブラリ

  このクラスライブラリは、sqlite3.dllの関数群を簡便に使えるようにすることを意図しています。データベースのないサーバで、直接sqlite.soを使ったことから知ったことをまとめました。

  このクラスは、ADOなど標準的なアクセス方法にはまったく準拠していません。
  また、sqlite3.dllの関数を網羅しているわけでもありません。
  出来ることは、サンプルプログラムで説明したことだけです。
  文字コードもUTF-8で使うこと以外は考えていません。

３．１．SQLiteDatabase名前空間

sqlite3cs.dllには、SQLiteDatabase名前空間に以下のクラスがあります。

  1. ROWクラス
    SQL文の実行の結果取得された「行」のデータを保持します。
  2. SQLite3クラス
    バインドを使用しないSQL文を実行します。
  3. SQLite3.Statementクラス
    バインドと「行」の読み出しの機能を行います。

３．２．ROWクラス 

  このクラスは、SQLite3.Statement.GetNext()メソッドの戻り値として使います。データベースから得られた「行」の値を保持します。「行」は、列名と値のペアの配列です。

■ プロパティ

  1. string[] names;
    列名の配列。個数は、値の個数（列数）と一致します。順番は、値の順番に対応します。順番は、SELECTなどのSQL文で指定した列名の順番になります。（*で指定した場合はテーブルの定義順になるものと思います。）
  2. int Length;
    列数。

■ 演算子

  1. [文字列]
    列名を指定して、値を取得します。列名は、テーブル名や別名が付加されている場合があります。
  2. [int]
    順番で値を取得します。

■ メソッド

  1. object[] ToArry();
    値の並びの object[] を返します。

３．３．SQLite3クラス

■ プロパティ

  1. string LibVersion;
    sqlite3.dllのバージョン番号を返します。
    sqlite3.dllがロードできない場合は例外がスローされます。
  2. string FileVersion;
    Open()が成功した場合、データベース・ファイルのヘッダから得られたバージョン番号がセットされます。現在は、"3"がセットされます。

■ コンストラクタ

  1. SQLite3();
    SQLite3の新しいインスタンスを初期化します。

■ メソッド

  1. void Open(string fileName);
    データベース・ファイルのパスを指定して、データベースをオープンします。
  2. void Open(string fileName, bool bFullColumnNames);
    データベース・ファイルのパスを指定して、データベースをオープンします。
    bFullColumnNamesは、SELECTなどのSQL文を実行して「行」をROWオブジェクトを取得した場合に取得される、列名の形式を指定します。
    falseの場合は、列名が使われ、trueの場合は、テーブル名.列名が使われます。テーブル名は、別名（アリアス）の場合があります。
    ※ この形式のOpen()を指定した場合は、true/falseいずれでも設定を行います。単にパスのみを指定したOpen()は、設定を行いません。デフォルトの動作になります。デフォルトは「列名のみ」です。このデフォルトを変える方法もあるのかもしれません。
  3. void Close();
    データベース・ファイルをクローズします。Open()からクローズの間は、他のスレッド、プロセスはデータベース・ファイルにアクセスできません。
  4. void Execute(string sql);
    SELECT以外のSQL文を実行します。実行の結果「行」が返されないSQL文の実行に使います。
    「行」が返されないSQL文でも、バインド機能を使う場合は、SQLite3.Statement.Prepare()を使います。
  5. Statement Query(string sql);
    SELECT文を実行します。戻り値のStatementオブジェクトで「行」を読み取ります。 
    SELECT文でも、バインド機能を使う場合は、SQLite3.Statement.Prepare()を使います。
  6. Statement Prepare(string template);
    ?マークを含むSQL文の実行に使います。実行するSQL文は文字列として用意しますが、実行ごとに変わる値を文字列にして再構成するのは手数が掛かります。可変になる箇所を?と記述しておき、Statement.Bind()でバインドして実行できます。
  7. long GetLastInsertRowId();
    最後に発番されたauto incrementキーの値を取得します。これは、DLLの機能そのもので、詳細は良く分かりません。実用上は問題ありません。
  8. int GetChanges();
    最後に実行したSQL文で更新された行数。これは、DLLの機能そのもので、詳細は良く分かりません。 

３．４．SQLite3.Statementクラス

  このクラスは、2つの目的で使います。1つは、Query()の結果の「行」を読み出します。もう一つは、SQL文のバインド機能を行います。

■ コンストラクタ

このクラスは、SQLite3.Query()かSQLite3.Prepare()で生成され、アプリケーションでコンストラクトすることはありません。

■ メソッド

  • クローズ

Close();
これを忘れると以降の操作でデータベースがBUSYだと言われる。

  • 行の読み出し

ROW NextRow();
SQL文の実行の結果取得された「行」を読み出す。行がないときはnullを返す。終了はnullで判定すること。最初に行数を知る方法はない。

  • バインドの制御

１．void ClearBindings();

バインドした状態を開放します。

２．void Reset();

同じStatementオブジェクトを繰り返し使用する場合、前回のSQL文の実行を完了し再実行可能にします。バインドしたデータは保持されます。

  • 型に応じたバインディング・メソッド

n番目のパラメータに値をバインドします。

void BindNull(int n)
void Bind(int n, int value)
void Bind(int n, long value)
void Bind(int n, double value)
void Bind(int n, byte[] value)
void Bind(int n, string value)
 

  • 一括バインディング

void Bind(object[] values);
values[]の要素の型に応じて順次バインドを行います。
int,long,double,string,byte[]が可能です。

４．参考になれば

４．１．SQLite3の型について 

  SQLite3のもともとの考え方は、入れた通りに読み出すことで、型による処理をしないことが始まりのようです。「入れた通りに読み出すこと」は、プログラミング言語やOS、文字コードの影響を受けると言うことです。また、入れたときの条件を知らないと読み出せないことも意味します。
  テーブルを作成する際の列の型定は、単に指定した文字列が格納されるだけです。何でも入ります。また、まったく指定しなくてもエラーになりません。
  ただし、現在ではまったく型が使われないわけではなく、一部使われています。

  1. INTEGER PRIMARY KEY と指定された列は、auto increment処理される。
  2. int型を指定した列を、ダブルクオートで囲んだ数字で検索するとミスマッチになる。

  DLLには sqlite3_column_type() 関数があり、内部的な型があることが分かります。この型はテーブル作成時に指定したことによって決まっているわけではないことに注意が必要です。

  これらのことから、以下のように型を扱うことにしました。

  1. アプリケーションは、各列の型を知っていると言うことを前提とする。
    データベースから読み出した列データは、C#の型に変換した上で、object型で渡す。
    使う側で、(int)row["id"]、(string)row["title"]のように使う。
  2. テーブル作成時には、以下の型のうちどれかを必ず指定する。（後々のため）
    INT、LONG、DOUBLE、TEXT、BLOB、INTEGER PRIMARY KEY

４．２．並列処理

  サーバー・クライアントではないので、プロセス間での並列処理のためには、データベース・ファイルを同時に複数のプロセス間で開ける必要がありますが、これはできません。
　スレッドでも同様です。
　また、複数のSQL文も並列に処理できません。あるSELECTを実行して結果の行を順次読み出しながら、その値を使って別のSQL文を実行すると言ったこともできません。最初のSELECTの読み出しに使うsqlite3_stmtオブジェクトが閉じられるまでは、他の要求がBUSYでエラーになります。
　この件は、何か別な方法があるのかもしれませんが、わかりません。

４．３．テーブルの別名（アリアス）

　このホームページは、CakePHPが使われていますが、CakePHPはプログラム上のラベルとテーブルの列名の関係を自動的に行います。このネーミングのルールでは、列名がテーブル名を伴う必要があります。また、テーブル名は別名になっています。
　これを sqlite3.so で行いました。そのとき分かったのは、

  1. PHPインタフェースは連想記憶型の配列を使っており、同名の列は1つになってしまう。
  2. テーブル名.列名の名前は作れるが、別名は不可。

　今回、sqlite3.dllを使ったsqlite3cs.dllでは以下のようになりました。

  1. C#の配列で、インデクスでも名前でも引けるようにしたので、同名の列名があっても列数が減ってしまうようなことはありません。
  2. バージョンの差か、別名も使えます。

  ■ テーブル名.列名

　列名を「テーブル名.列名」と表すのをフル、「列名のみ」をショートと呼んでいます。
この切り替えは、SQLite3．Open()で指定します。Open("データベース・ファイル",true)、または Open("データベース・ファイル",false)とし、trueならフル形式になります。このオプションは、SELECTで行を読み出したとき、ROWオブジェクトに格納される列名がどちらの形式になるかを指定するものです。それ以外は、両形式が有効です。
　サンプル・プログラムで使用したmenuテーブルを例でtitle列（フィールド）の値を参照する場合、フル形式の場合は、row["menu.title"]となり、ショート形式の場合は、row["title"]とすることになります。

　■ テーブルの結合

　複数のテーブルがあると同じ列名（フィールド名）が使われていることがあります。サンプル・プログラムの例では、2つのテーブルでidとtitleが重複しています。複数のテーブルを同時に扱うときには、区別するためにフル形式の名前を使います。
　下記のSQL文のを実行するときに、Open("データベース・ファイル",true)としてオープンしていれば、読み出される「行」の列名もフル形式となって列名で区別できます。
　ただし、フル形式でなくても、列をインデクスで参照すれば区別できるので、Open("データベース・ファイル",true)でオープンすることは必須のことではありません。

  1. SELECT menu.*, contents.* 
  2.     FROM menu JOIN contents ON menu.content_id = contents.id 
  3.     WHERE contents.id = 1;

　■ テーブル名の別名（アリアス）

　下記は、上のSQL文と同じ検索をテーブル名の別名を使って書いたものです。
　下記のSQL文のを実行するときに、Open("データベース・ファイル",true)としてオープンしていれば、読み出される「行」の列名が「別名.列名」となります。
　menuテーブルのtitle列を参照するには、row["A.title"]とすることになります。
 

  1. SELECT A.*, B.* 
  2.     FROM menu AS A JOIN contents AS B ON A.content_id = B.id 
  3.     WHERE B.id = 1;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
不動産 船橋市 横浜 不動産 青葉区　不動産 石川県　不動産 ホームページ制作