【すぐに使える】C#でSQLiteを扱うための解説とサンプル集

//接続文字列の作成（Data Source ＝ ファイルのパス)
var connectString = @"Data Source = D:\SQLiteTest.DB";

// インスタンスの生成（処理終了後、自動でClose）
using (SQLiteConnection connection = new SQLiteConnection(connectString))
{
    // DBのオープン
    connection.Open();

    // コマンドのインスタンス生成
    using (SQLiteCommand cmd = connection.CreateCommand())
    {
        // SQLをコマンドに登録
         cmd.CommandText = sql;
        // コマンドの実行
        cmd.ExecuteNonQuery();
    }
}

using System.Data.SQLite;

string connectString = @"Data Source = D:\SQLiteTest.db";

/// <summary>
/// 空のデータベースファイルを作成
/// </summary>
/// <param name="connectString"></param>
/// <param name="fileName"></param>
public void Create(string connectString, string fileName)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        SQLiteConnection.CreateFile(fileName);
    }
}

/// <summary>
/// SQLの実行
/// </summary>
/// <param name="connectString"></param>
/// <param name="sqls"></param>
public void ExecuteNoneQuery(string connectString, string sql)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();

        using (SQLiteCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
    }
}

/// <summary>
/// SQLの実行
/// </summary>
/// <param name="connectString"></param>
/// <param name="sqls"></param>
public void ExecuteNoneQueryWithTransaction(string connectString, string[] sqls)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();
        SQLiteTransaction trans = connection.BeginTransaction();

        try
        {
            foreach (string sql in sqls)
            {
                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.Transaction = trans;

                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            trans.Commit();
        }
        catch
        {
            trans.Rollback();
            throw;
        }
    }
}

/// <summary>
/// スカラーによる単一データの取得
/// </summary>
/// <param name="connectString"></param>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string connectString, string sql)
{
    object result = null;

    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();

        using (SQLiteCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;
            result = cmd.ExecuteScalar();
        }
    }

    return result;
}

/// <summary>
/// DataReaderを使ったデータの取得
/// </summary>
/// <param name="connectString"></param>
/// <param name="sql"></param>
/// <returns></returns>
public List<object[]> ExecuteReader(string connectString, string sql)
{
    List<object[]> result = new List<object[]>();

    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();

        using (SQLiteCommand cmd = connection.CreateCommand())
        {
            //SQLの設定
            cmd.CommandText = sql;

            //検索
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    object[] data = Enumerable.Range(0, reader.FieldCount).Select(i => reader[i]).ToArray();
                    result.Add(data);
                }
            }
        }
    }
    return result;
}

/// <summary>
/// DataTableを使ったデータの取得
/// </summary>
/// <param name="connectString"></param>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetData(string connectString, string sql)
{
    DataTable dt = new DataTable();

    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();

        using (SQLiteCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;

            // DataAdapterの生成
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

            // データベースからデータを取得
            da.Fill(dt);
        }
    }
    return dt;
}

/// <summary>
/// DataTableの内容をデータベースに保存
/// </summary>
/// <param name="connectString"></param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public DataTable SetData(string connectString,string tableName,DataTable dt)
{
    using (SQLiteConnection connection = new SQLiteConnection(connectString))
    {
        connection.Open();
        SQLiteTransaction trans = connection.BeginTransaction();

        try
        {
            using (SQLiteCommand cmd = connection.CreateCommand())
            {
                //書き込み先テーブルの列名と型を取得するためのSQLをCommandに登録
                cmd.CommandText = "select * from " + tableName;

                // DataAdapterの生成
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

                //Insert、Delete、Update　コマンドの自動生成
                SQLiteCommandBuilder bulider = new SQLiteCommandBuilder(da);

                //DataTableの内容をデータベースに書き込む
                da.Update(dt);

                //コミット
                trans.Commit();
            }
        }
        catch
        {
            trans.Rollback();
            throw;
        }
    }
    return dt;
}

SQLiteCommandBuilder builder = new SQLiteCommandBuilder(da);
string insert = builder.GetInsertCommand().CommandText;
string update = builder.GetUpdateCommand().CommandText;
string delete = builder.GetDeleteCommand().CommandText;

public bool Exists(string connectString, string tableName)
{
    object val = ExecuteScalar(connectString, 
        "select count(*) from sqlite_master where type in ('table','view') and name='" + tableName + "'");
    return (int.Parse(val.ToString()) == 0) ? false : true;
}

public string[] GetTableList(string connectString)
{
    var dt = GetData(connectString,"select tbl_name from sqlite_master where type in ('table','view') ");
    return dt.AsEnumerable().Select(i => i[0].ToString()).ToArray();
}

public string[] GetColumnNames(string connectString, string tableName)
{
    var dt = GetData(connectString, "select * from " + tableName + " where 1=2");
    return dt.Columns.Cast<DataColumn>().Select(i=>i.ColumnName).ToArray();
}

public void CreateTable(string connectString, string tableName,string fieldList,string primaryKeyList)
{
    var primary =  (primaryKeyList == "") ? "" : (",primary key (" + primaryKeyList + ")");
    var sql = string.Format("create table {0}({1} {2})", tableName, fieldList, primary);

    ExecuteNoneQuery(connectString, sql); 
}

public void CreateTable(string connectString, string tableName,DataTable dt,string primaryKeyList)
{
    var fileds = dt.Columns.Cast<DataColumn>().Select(i => i.ColumnName).ToArray();
    var sql = "create table " + tableName + "(" + string.Join(",", fileds);
    sql += (primaryKeyList == "") ? ")" : ",primary key (" + primaryKeyList + "))";

    ExecuteNoneQuery(connectString,sql);
}

public void DropTable(string connectString, string tableName)
{
    ExecuteNoneQuery(connectString, "drop table  " + tableName);
}

public void Vacuum(string connectString)
{
    ExecuteNoneQuery(connectString, "VACUUM");
}

using CommonClass;

SQLiteUtil sqlite = new SQLiteUtil(".\sqlite.db");
DataTable dt = sqlite .ExecuteReader("select * from 商品マスター");
DataGridView.DataSource = dt;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Drawing.Imaging;

namespace CommonClass
{
    public class SQLiteUtil
    {
        //接続文字列
        public string ConnectString { get; set; }
        //データベースファイルのフルパス
        public string SQLitePath { get; set; }

        /// <summary>
        /// コンストラクタ
        /// </summary>
        /// <param name="path"></param>
        public SQLiteUtil(string path)
        {
            SQLitePath = path;
            ConnectString = "Data Source = " + SQLitePath;
        }

        /// <summary>
        /// DBファイルの作成（既に存在する場合、中身はクリアされる）
        /// </summary>
        public void CreateDatabase()
        {
            SQLiteConnection.CreateFile(SQLitePath);
        }

        /// <summary>
        /// SQLの実行
        /// </summary>
        /// <param name="sqls"></param>
        public void ExecuteNoneQuery(string sql)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// スカラーの実行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            object result = null;

            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    result = cmd.ExecuteScalar();
                }
            }

            return result;
        }

        /// <summary>
        /// SQLの実行
        /// </summary>
        /// <param name="sqls"></param>
        public void ExecuteNoneQueryWithTransaction(string[] sqls)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();
                SQLiteTransaction trans = connection.BeginTransaction();

                try
                {
                    foreach (string sql in sqls)
                    {
                        using (SQLiteCommand cmd = connection.CreateCommand())
                        {
                            cmd.Transaction = trans;

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                        }
                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

         /// <summary>
        /// DataReaderを使ったデータの取得
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteReader(string sql)
        {
            DataTable dt = new DataTable();

            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    //SQLの設定
                    cmd.CommandText = sql;

                    //検索
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        create_columns(dt, reader);

                        while (reader.Read())
                        {
                            object[] data = Enumerable.Range(0, reader.FieldCount).Select(i => reader[i]).ToArray();
                            dt.Rows.Add(dt.NewRow().ItemArray = data);
                        }
                    }
                }
            }
            return dt;

            //列名が重複した場合、列名に連番を付加した上でDataColumnを追加
            void create_columns(DataTable p_dt, SQLiteDataReader p_reader)
            {
                Dictionary<string,int> l_dic = new Dictionary<string,int>();
                for(int i = 0;i < p_reader.FieldCount; i ++)
                {
                    string p_name = p_reader.GetName(i);
                    if (l_dic.ContainsKey(p_name))
                    {
                        int p_cnt = l_dic[p_name] ++;
                        p_dt.Columns.Add(p_name + p_cnt.ToString());
                    }
                    else
                    {
                        p_dt.Columns.Add(p_name);
                        l_dic.Add(p_name, 1);
                    }
                }
            }
        }

        /// <summary>
        /// DataAdapterを使ったデータの取得
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetData(string sql)
        {
            DataTable dt = new DataTable();

            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;

                    // DataAdapterの生成
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

                    // データベースからデータを取得
                    da.Fill(dt);
                }
            }
            return dt;
        }

        /// <summary>
        /// DataTableの内容をデータベースに保存
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public DataTable SetData(string tableName, DataTable dt)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectString))
            {
                connection.Open();
                SQLiteTransaction trans = connection.BeginTransaction();

                try
                {
                    using (SQLiteCommand cmd = connection.CreateCommand())
                    {
                        cmd.Transaction = trans;

                        //書き込み先テーブルの列名と型を取得するためのSQLをCommandに登録
                        cmd.CommandText = "select * from " + tableName;

                        // DataAdapterの生成
                        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

                        //Insert、Delete、Update　コマンドの自動生成
                        SQLiteCommandBuilder bulider = new SQLiteCommandBuilder(da);

                        //DataTableの内容をデータベースに書き込む
                        da.Update(dt);

                        trans.Commit();
                    }
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
            return dt;
        }

        /// <summary>
        /// カラム一覧の取得
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string[] GetColumnNames(string tableName)
        {
            var dt = GetData("select * from " + tableName + " where 1=2");
            return dt.Columns.Cast<DataColumn>().Select(i => i.ColumnName).ToArray();
        }

        /// <summary>
        /// カラム情報の取得
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetColumnInfo(string tableName)
        {
            return GetData("PRAGMA table_info('" + tableName + "')");
        }

        /// <summary>
        /// テーブル一覧の取得
        /// </summary>
        /// <returns></returns>
        public string[] GetTableList()
        {
            var dt = GetData("select tbl_name from sqlite_master where type in ('table','view') ");
            return dt.AsEnumerable().Select(i => i[0].ToString()).ToArray();
        }

        /// <summary>
        /// テーブル／Viewの存在チェック
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool Exists(string tableName)
        {
            object val = ExecuteScalar("select count(*) from sqlite_master where type in ('table','view') and name='" + tableName + "'");
            return (int.Parse(val.ToString()) == 0) ? false : true;
        }

        /// <summary>
        /// テーブルの削除
        /// </summary>
        /// <param name="tableName"></param>
        public void DropTable(string tableName)
        {
            ExecuteNoneQuery("drop table if exists " + tableName);
        }

        /// <summary>
        /// 未使用エリアの開放
        /// </summary>
        public void Vacuum()
        {
            ExecuteNoneQuery( "VACUUM");
        }

        /// <summary>
        /// テーブルの作成
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldList"></param>
        /// <param name="primaryKeyList"></param>
        public void CreateTable(string tableName, string fieldList, string primaryKeyList = "")
        {
            var primary = (primaryKeyList == "") ? "" : (",primary key (" + primaryKeyList + ")");
            var sql = string.Format("create table {0}({1} {2})", tableName, fieldList, primary);

            ExecuteNoneQuery(sql);
        }


        /// <summary>
        /// DataTableからカラムと型を推測する
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string[] CreateFieldList(DataTable dt)
        {
            List<string> fields = new List<string>();

            foreach (DataColumn dr in dt.Columns)
            {
                int num = dt.AsEnumerable().Count(i => int.TryParse(i[dr.ColumnName].ToString(), out int val1));
                int dbl = dt.AsEnumerable().Count(i => double.TryParse(i[dr.ColumnName].ToString(),out double val2));
                string type = (num == dt.Rows.Count) ? "INTEGER" : (dbl == dt.Rows.Count) ? "REAL" : "TEXT";
                fields.Add(dr.ColumnName + " " + type);
            }

            return fields.ToArray();
        } 
    }
}