[C#]SQLiteを使う（プリペアードステートメント）

[スポンサードリンク]

本記事ではXMLからSQLを取得し、SQLiteで実行する。プリペアードステートメントを使用する。

プロジェクト仕様

【プログラム実行時】
１．SQLiteのDBファイルが存在すれば削除する。

【実行ボタン押下時】
１．ユーザマスタがすでに存在すれば、削除する。
２．ユーザーマスタを作成する。
３．ユーザーマスタにデータをインサートする。
４．ユーザーマスタからデータを取得する。
５．取得したデータを画面に表示する。

プリペアードステートメント

プリペアードステートメントとは、SQLクエリの実行前にパラメータを埋め込む方式。
たとえば通常は

StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO M_USER VALUES (");
sb.append(UserNo + "," + UserName + ")");
string sql = sb.ToString();

StringBuilder sb = new StringBuilder();

sb.append("INSERT INTO M_USER VALUES (");

sb.append(UserNo + "," + UserName + ")");

string sql = sb.ToString();

なんかこんな感じ？で、ベタでソースにSQLを書くけど、
プリペアードステートメントなら

INSERT INTO M_USER VALUES (?,?)

1	INSERT INTO M_USER VALUES (?,?)

というSQLを用意しておけば、「？」の部分にパラメータを埋め込むことで、SQLを使い回すことができる。

プリペアードステートメントを使うシチュエーションとして、SQLを別の設定ファイルで管理する場合などがある。
そこそこ大規模なシステムになると、ソースにSQLを直書きすると、仕様変更などが発生した場合に修正漏れや、どのソースを修正すればよいかなどさまざまな問題が発生する。

SQLを別ファイルで管理し、SQLのID番号で呼び出せば管理は楽である。

今回はXMLでSQLを管理することとし、XMLを読み込む処理も記載した。
XMLの読み書きについては、時間があれば別記事で投稿するつもりである。

SQLiteをC#で使うための準備

Visual Studio2017の「ツール」→「NuGetパッケージマネージャ」→「パッケージマネージャコンソール」を実行する。

プロンプトで
「Install-Package System.Data.SQLite」
を入力して実行する。

これで、Visual Studio 2017のこのC#プロジェクトでSQLiteのパッケージがインストールされ、参照設定される。

ソースコード

XMLファイル

XMLファイルはSQLNoで管理している。

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <SQL No="DR001">
        DROP TABLE IF EXISTS M_USER
    </SQL>
    <SQL No="C0001">
        CREATE TABLE M_USER (
            Id INTEGER,
            UserName TEXT,
            PRIMARY KEY(Id)
        )
    </SQL>
    <SQL No="CI001">
        INSERT INTO M_USER VALUES
        (?,?)
    </SQL>
    <SQL No="S0001">
        SELECT Id,UserName FROM M_USER
    </SQL>
</root>

<?xml version="1.0" encoding="UTF-8"?>

<root>

DROP TABLE IF EXISTS M_USER

</SQL>

CREATE TABLE M_USER (

Id INTEGER,

UserName TEXT,

PRIMARY KEY(Id)

)

</SQL>

INSERT INTO M_USER VALUES

(?,?)

</SQL>

SELECT Id,UserName FROM M_USER

</SQL>

</root>

C#プログラム

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

// ここから追加
using System.IO;
using System.Data.SQLite;
using System.Xml;   // XML読込

namespace XMLSQLite
{
    public partial class Form1 : Form
    {
        static string gDbFilePath =
            Path.Combine(Path.GetDirectoryName(Application.ExecutablePath),
                "SAMPLE.DATA");

        static string gDataSource = "Data Source=" + gDbFilePath;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // データファイルの存在確認
            if (File.Exists(gDbFilePath))
            {
                File.Delete(gDbFilePath);
            }
        }

        /// <summary>
        /// 実行ボタン押下
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BtnRun_Click(object sender, EventArgs e)
        {
            SQLiteCommand command;

            using (SQLiteConnection conn =
                new SQLiteConnection(gDataSource))
            {
                try
                {
                    // DBに接続
                    conn.Open();

                    // ユーザーマスタが存在すれば削除
                    command = new SQLiteCommand(GetSQL("DR001"), conn);
                    command.ExecuteNonQuery();

                    // ユーザーマスタの作成
                    command = new SQLiteCommand(GetSQL("C0001"), conn);
                    command.ExecuteNonQuery();

                    // ユーザーマスタ登録
                    command = new SQLiteCommand(GetSQL("CI001"), conn);
                    SQLiteParameter param;

                    // 山田さん登録
                    command.Parameters.Clear();
                    // ユーザーNo
                    param = new SQLiteParameter()
                    {
                        DbType = System.Data.DbType.Int32,
                        Value = 1
                    };
                    command.Parameters.Add(param);
                    // ユーザー名
                    param = new SQLiteParameter()
                    {
                        DbType = System.Data.DbType.String,
                        Value = "山田花子"
                    };
                    command.Parameters.Add(param);
                    command.Prepare();
                    command.ExecuteNonQuery();

                    // 田中さん登録
                    command.Parameters.Clear();
                    // ユーザーNo
                    param = new SQLiteParameter()
                    {
                        DbType = System.Data.DbType.Int32,
                        Value = 2
                    };
                    command.Parameters.Add(param);
                    // ユーザー名
                    param = new SQLiteParameter()
                    {
                        DbType = System.Data.DbType.String,
                        Value = "田中一郎"
                    };
                    command.Parameters.Add(param);
                    command.Prepare();
                    command.ExecuteNonQuery();


                    // データの表示
                    command = new SQLiteCommand(GetSQL("S0001"), conn);
                    SQLiteDataReader reader = command.ExecuteReader();

                    DGV.Rows.Clear();
                    // 取得したデータをデータグリッドビュー（表）に表示する。
                    while (reader.Read())   // データがあるだけ繰り返す
                    {
                        DGV.Rows.Add(
                            reader.GetInt32(0),     // id
                            reader.GetString(1)     // 名前
                        );
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        /// <summary>
        /// 指定したSQL番号のSQLを返す
        /// </summary>
        /// <param name="No"></param>
        /// <returns></returns>
        private string GetSQL(String No)
        {
            string xmlPath =
            Path.Combine(Path.GetDirectoryName(Application.ExecutablePath),
                "SQL.xml");

            XmlDocument doc = new XmlDocument();

            // ファイルから読み込む
            doc.Load(xmlPath);

            XmlNodeList sql = doc.SelectNodes(@"//SQL[@No='" + No + @"']");
            return sql[0].InnerXml;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

// ここから追加

using System.IO;

using System.Data.SQLite;

using System.Xml; // XML読込

namespace XMLSQLite

{

public partial class Form1 : Form

{

static string gDbFilePath =

Path.Combine(Path.GetDirectoryName(Application.ExecutablePath),

"SAMPLE.DATA");

static string gDataSource = "Data Source=" + gDbFilePath;

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

// データファイルの存在確認

if (File.Exists(gDbFilePath))

{

File.Delete(gDbFilePath);

}

/// <summary>

/// 実行ボタン押下

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void BtnRun_Click(object sender, EventArgs e)

{

SQLiteCommand command;

using (SQLiteConnection conn =

new SQLiteConnection(gDataSource))

{

try

{

// DBに接続

conn.Open();

// ユーザーマスタが存在すれば削除

command = new SQLiteCommand(GetSQL("DR001"), conn);

command.ExecuteNonQuery();

// ユーザーマスタの作成

command = new SQLiteCommand(GetSQL("C0001"), conn);

command.ExecuteNonQuery();

// ユーザーマスタ登録

command = new SQLiteCommand(GetSQL("CI001"), conn);

SQLiteParameter param;

// 山田さん登録

command.Parameters.Clear();

// ユーザーNo

param = new SQLiteParameter()

{

DbType = System.Data.DbType.Int32,

Value = 1

};

command.Parameters.Add(param);

// ユーザー名

param = new SQLiteParameter()

{

DbType = System.Data.DbType.String,

Value = "山田花子"

};

command.Parameters.Add(param);

command.Prepare();

command.ExecuteNonQuery();

// 田中さん登録

command.Parameters.Clear();

// ユーザーNo

param = new SQLiteParameter()

{

DbType = System.Data.DbType.Int32,

Value = 2

};

command.Parameters.Add(param);

// ユーザー名

param = new SQLiteParameter()

{

DbType = System.Data.DbType.String,

Value = "田中一郎"

};

command.Parameters.Add(param);

command.Prepare();

command.ExecuteNonQuery();

// データの表示

command = new SQLiteCommand(GetSQL("S0001"), conn);

SQLiteDataReader reader = command.ExecuteReader();

DGV.Rows.Clear();

// 取得したデータをデータグリッドビュー（表）に表示する。

while (reader.Read()) // データがあるだけ繰り返す

{

DGV.Rows.Add(

reader.GetInt32(0), // id

reader.GetString(1) // 名前

);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

/// <summary>

/// 指定したSQL番号のSQLを返す

/// </summary>

/// <param name="No"></param>

/// <returns></returns>

private string GetSQL(String No)

{

string xmlPath =

Path.Combine(Path.GetDirectoryName(Application.ExecutablePath),

"SQL.xml");

XmlDocument doc = new XmlDocument();

// ファイルから読み込む

doc.Load(xmlPath);

XmlNodeList sql = doc.SelectNodes(@"//SQL[@No='" + No + @"']");

return sql[0].InnerXml;

}

注意

SQLiteのデータファイルをつかんだままになる

SQLiteのコネクションはusing句を使用して開いているが、どうもプログラムを終了するまでSQLiteのデータファイルをプログラムがつかんでいるらしい。

データファイルの削除処理は、当初「実行」ボタンの冒頭に書いていたが、上図のようなエラーが出るために、フォームロード時の処理に書いた。

「実行」ボタン押下時の処理で、テーブルが存在していたら、テーブルを削除しているため、ファイルの削除処理はいらないといえばいらない。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい