SQLite に画像データを保存したい (Windows10, Visual Studio 2019, C#, NET Framework 4.7, System.Data.SQLite

Question

#経緯
Windows10,Visual Studio 2019, C#, .NET Framework 4.7.2, で、アプリケーションを作成しようと考えています。まだ、C# に触り初めてから、2週間程度の初心者です。申し訳ありません。

Visual Studio 2019 に、NuGet を用いて、System.Data.SQLite をインストールしました。これにともない、System.Data.SQLite.EF6, System.Data.SQLite.Generic 等も一緒にインストールされています。

画面には、Form1 だけがあります。また、次のような画像を用意しました。

この画像の大きさは、ヨコ 500 px タテ 300px です。このような画像の一部を切り取り、SQLite データベースに記録したいと考えています。

#ソースコード

Form1.cs は、次のような内容になっています。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.Data.SQLite.Generic;
using System.Data.SQLite.Linq;
using System.Drawing;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Grph_SQLite
{
    public partial class Form1 : Form
    {
        Image im1, im2;

        public Form1()
        {
            InitializeComponent();

            using (var conn = new SQLiteConnection("DataSource=data.db"))
            {
                conn.Open();
                var comm = conn.CreateCommand();
                string sb = "CREATE TABLE IF NOT EXISTS mydata " +
                    " (name TEXT NOT NULL, face BLOB)";
                comm.CommandText = sb;
                comm.ExecuteNonQuery();
                conn.Close();
            }

            #region 画像を切り取って、im1, im2 に格納し、表示する。

            PictureBox pic = new PictureBox();
            pic.Location = new Point(10, 10);
            pic.Size = new Size(500, 300);
            pic.Image = Image.FromFile("data.png");
            Controls.Add(pic);

            // 元のイメージを切り取って、im1 に貼り付ける
            im1 = new Bitmap(300, 70);
            Graphics g = Graphics.FromImage(im1);
            Rectangle srcRect = new Rectangle(90, 80, 300, 70);
            Rectangle desRect = new Rectangle(0, 0, 300, 70);
            g.DrawImage(pic.Image, desRect, srcRect, GraphicsUnit.Pixel);
            g.Dispose();

            PictureBox pic1 = new PictureBox();
            pic1.Location = new Point(10, 350);
            pic1.Size = new Size(300, 70);
            pic1.Image = im1;
            Controls.Add(pic1);

            // 元のイメージを切り取って、im2 に貼り付ける
            im2 = new Bitmap(300, 70);
            g = Graphics.FromImage(im2);
            srcRect = new Rectangle(90, 180, 300, 70);
            desRect = new Rectangle(0, 0, 300, 70);
            g.DrawImage(pic.Image, desRect, srcRect, GraphicsUnit.Pixel);
            g.Dispose();

            PictureBox pic2 = new PictureBox();
            pic2.Location = new Point(10, 450);
            pic2.Size = new Size(300, 70);
            pic2.Image = im2;
            Controls.Add(pic2);

            // 以上で、im1, im2 に画像データが格納されたことが確認できた。

            #endregion

            #region 画像をデータを、データベースに登録する。

            using (var conn = new SQLiteConnection("DataSource=data.db"))
            {
                conn.Open();

                var comm = conn.CreateCommand();
                string str = "insert into mydata(name) values('visual studio1');";
                comm.CommandText = str;
                comm.ExecuteNonQuery();

                str = "insert into mydata(name) values('visual studio2');";
                comm.CommandText = str;
                comm.ExecuteNonQuery();

                // ここのところがわからない
                str = "insert into mydata(name,face) values('visual studio3',@face);";
                ImageConverter converter = new ImageConverter();
                byte[] fdata = (byte[])converter.ConvertTo(im1, typeof(byte[]));
                comm.Parameters["face"].Value = fdata;
                comm.CommandText = str;
                comm.ExecuteNonQuery();

                conn.Close();
            }

            #endregion
        }
    }
}

概略を説明すると、まず、データベースに接続します(ファイルが無ければ、新しく作ります）。次に、data.png を読み込み、pic.Image に格納し、画面にも表示させます。また、pic.Image の一部を切り取り、im1 と im2 に保存し、それぞれ画面に表示させます。

次にデータベースにレコードを登録します。mydata テーブルのうち、name については、 TEXT NOT NULL ですので、必ず登録しなければなりませんが、face BLOB については、登録しない場合もありえます。

レコードとして、「visual studio1」という name をもつデータと、「visual studio2」という name をもつデータの合計２つのデータが登録できたことは、DB Browser for SQLite を用いて、確認できました。

さて、その下のところで、name と face を両方一度に登録しようと考え、

// ここのところがわからない
str = "insert into mydata(name,face) values('visual studio3',@face);";
ImageConverter converter = new ImageConverter();
byte[] fdata = (byte[])converter.ConvertTo(im1, typeof(byte[]));
comm.Parameters["face"].Value = fdata;
comm.CommandText = str;
comm.ExecuteNonQuery();

と書きました。

#エラーメッセージ

この状態でビルドしても、エラー（コンパイル・エラー）は、ありません。しかし、実際に動かしてみると（F11で一行ずつ実行してみると）、

comm.Parameters["face"].Value = fdata;

のところで、次のようなランタイム・エラーが発生します。

System.ArgumentOutOfRangeException: 'インデックスが範囲を超えています。負でない値で、コレクションのサイズよりも小さくなければなりません。

パラメーター名:index
System.ArgumentOutOfRangeException
HResult=0x80131502
Message=インデックスが範囲を超えています。負でない値で、コレクションのサイズよりも小さくなければなりません。
パラメーター名:index
Source=mscorlib
スタック トレース:
場所 System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
場所 System.Data.SQLite.SQLiteParameterCollection.GetParameter(Int32 index)
場所 System.Data.SQLite.SQLiteParameterCollection.GetParameter(String parameterName)
場所 System.Data.SQLite.SQLiteParameterCollection.get_Item(String parameterName)
場所 Grph_SQLite.Form1..ctor() (C:\hoga\Grph_SQLite\Grph_SQLite\Form1.cs):行 94
場所 Grph_SQLite.Program.Main() (C:\hoga\Grph_SQLite\Grph_SQLite\Program.cs):行 19

#お願い
画像データを、データベースに正しく格納するためには、どのような命令を書けばよいのでしょうか。また、格納されたデータを、プログラムで読むためには、どのような命令を書けばよいのか、についても、合わせて教えていただければ幸いです。

どうか、よろしくお願いいたします。

#追記（解決方法）
以下のような方法で本件は解決しましたので、本文にも追記しておきます。

まず、本文で「ここがわからない」と書いた部分については、次のようにすると画像データを保存できることが確認できました。

// こうすればできます
comm.CommandText = "insert into mydata(name,face) values ('visual studio3',@face);";
ImageConverter converter = new ImageConverter();
byte[] fdata = (byte[])converter.ConvertTo(im2, typeof(byte[]));
var param = new SQLiteParameter("@face", System.Data.DbType.Binary);
param.Value = fdata;
comm.Parameters.Add(param);
comm.ExecuteNonQuery();

つぎに、実際のプログラムでは、このような保存作業を、一度に 10000回程度実施する予定ですので、for 文を用いて、10000 回実施してみました。

            using (var conn = new SQLiteConnection("DataSource=data.db"))
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                ImageConverter converter = new ImageConverter();
                byte[] fdata;
                SQLiteParameter param;

                for (int i=0; i < 10000; i++)
                {
                    /*
                    cmd.CommandText = "insert into mydata(name) values (@ID)";
                    cmd.Parameters.Add(new SQLiteParameter("@ID", "visual studio4"));
                    cmd.ExecuteNonQuery();
                    */

                    cmd.CommandText = "insert into mydata(name,face) values ('visual studio5',@face);";
                    fdata = (byte[])converter.ConvertTo(im2, typeof(byte[]));
                    param = new SQLiteParameter("@face", System.Data.DbType.Binary);
                    param.Value = fdata;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    
                }

                conn.Close();
            }

ところが、100000回、作業を行うのに5分から7分程度必要で、とても実用に堪えないことがわかりました。このことについては、あちこちのページで「トランザクション処理が原因で、非常に速度が遅くなる」と書いてありましたので、実際に作業の前後にトランザクションに関する命令を追加しました。

var ts = conn.BeginTransaction();
＜途中の処理＞
ts.Commit();

この２行を追加したプログラムは、次のとおりです。

            using (var conn = new SQLiteConnection("DataSource=data.db"))
            {
                conn.Open();
                var ts = conn.BeginTransaction();

                SQLiteCommand cmd = conn.CreateCommand();
                ImageConverter converter = new ImageConverter();
                byte[] fdata;
                SQLiteParameter param;

                for (int i=0; i < 10000; i++)
                {
                    /*
                    cmd.CommandText = "insert into mydata(name) values (@ID)";
                    cmd.Parameters.Add(new SQLiteParameter("@ID", "visual studio4"));
                    cmd.ExecuteNonQuery();
                    */

                    cmd.CommandText = "insert into mydata(name,face) values ('visual studio5',@face);";
                    fdata = (byte[])converter.ConvertTo(im2, typeof(byte[]));
                    param = new SQLiteParameter("@face", System.Data.DbType.Binary);
                    param.Value = fdata;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    
                }

                ts.Commit();
                conn.Close();
            }

今度は、作業は数秒で終了してしまいました。
以上、報告です。

Answer 1

Parametersは勝手に設定されるものではありません。
どうやってパラメータを追加するかですが
SqliteParameterCollection.Addメソッドを参照してみてください。

Comments

[TAKASE_Hiroyuki]

御回答いただき、ありがとうございました。「そもそも」が分かっていなくて、大変恐縮なのですが、YAmaGNZ 様に教えていただいたのは、「Microsoft.Data.Sqlite 3.1」という名前がついているような気がします。私は「System.Data.SQLite 」という名前のものを使っているように思うのですが、このふたつは、同じものなのでしょうか。もし、仮に違うものだとしたら「System.Data.SQLite」でパラメータを適切に追加するためには、どのようにすればよろしいでしょうか。

[YAmaGNZ]

実際に使っているものは違うものかもしれませんが、これらのものはなるべく同じように使用できるようになっています。
ですので、もし違うとしても参考になるかと思います。
一応、紹介したのはMicrosoft.Data.Sqliteネームスペースのものです。
また、「SQLite Parameters Add」など、出てきた文言で検索してみるとよろしいかと思います。

[TAKASE_Hiroyuki]

御回答をいただき、ありがとうございました。
「SQLite Parameters Add BLOB」で検索して出てきたいくつかのページのうち、次のページが大変参考になりました。

http://maeleve.jugem.jp/?eid=19

このページに記載してあった、次の命令文を参考にして、うまく行きそうです。結果については、別途、報告いたします。ありがとうございました。

SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO T00_FILE_MNGMT (FILE_DT) VALUES (@0);");
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
param.Value = pic;
cmd.Parameters.Add(param);

Answer 2

YAmaGNZ 様からヒントをいただき、解決できました。解決方法については、本文にも追記しましたが、こちらにも、書いておきます。

まず、次のようなやり方で、画像を保存できました。

                // こうすればできます
                comm.CommandText = "insert into mydata(name,face) values ('visual studio3',@face);";
                ImageConverter converter = new ImageConverter();
                byte[] fdata = (byte[])converter.ConvertTo(im2, typeof(byte[]));
                var param = new SQLiteParameter("@face", System.Data.DbType.Binary);
                param.Value = fdata;
                comm.Parameters.Add(param);
                comm.ExecuteNonQuery();

ただし、これだけでは多数回の処理に時間がかかります。そこで、処理の前後にトランザクションに関する命令文を追加すると、速くできることがわかりました。

            using (var conn = new SQLiteConnection("DataSource=data.db"))
            {
                conn.Open();
                var ts = conn.BeginTransaction();

                SQLiteCommand cmd = conn.CreateCommand();
                ImageConverter converter = new ImageConverter();
                byte[] fdata;
                SQLiteParameter param;

                for (int i=0; i < 10000; i++)
                {
                    /*
                    cmd.CommandText = "insert into mydata(name) values (@ID)";
                    cmd.Parameters.Add(new SQLiteParameter("@ID", "visual studio4"));
                    cmd.ExecuteNonQuery();
                    */

                    cmd.CommandText = "insert into mydata(name,face) values ('visual studio5',@face);";
                    fdata = (byte[])converter.ConvertTo(im2, typeof(byte[]));
                    param = new SQLiteParameter("@face", System.Data.DbType.Binary);
                    param.Value = fdata;
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    
                }

                ts.Commit();
                conn.Close();
            }

文字情報だけを登録する場合も、画像とともに登録する場合も、ほぼ同じ時間で処理が終了しました。