第128回 C#でSQLiteを使用する方法

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Threading;
using System.Data.SQLite;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //★独り言によるプログラミング講座

            //■C#でSQLiteを使用する方法
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //DBを作成します
            String path = "test.db";
            if (File.Exists(path) == false) {
                using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path))
                {
                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = "create table d_test(id INTEGER PRIMARY KEY, data TEXT);";
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            //データの追加
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = "insert into d_test(id, data) values (@ID, @DATA)";
                        cmd.Parameters.Add(new SQLiteParameter("@ID", 1));
                        cmd.Parameters.Add(new SQLiteParameter("@DATA", "test"));
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }

            //データの更新
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = "update d_test set data = @DATA where id = @ID";
                        cmd.Parameters.Add(new SQLiteParameter("@ID", 1));
                        cmd.Parameters.Add(new SQLiteParameter("@DATA", "ABC"));
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }

            //検索
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path))
            {
                connection.Open();

                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    //SQLの設定
                    cmd.CommandText = "select * from d_test where id = @ID";

                    //パラメータの設定
                    cmd.Parameters.Add(new SQLiteParameter("@ID", 1));

                    //準備
                    cmd.Prepare();

                    //検索
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["id"].ToString());
                            Console.WriteLine(reader["data"].ToString());
                        }
                    }
                }
            }

            //データの削除
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = "delete from d_test where id = @ID";
                        cmd.Parameters.Add(new SQLiteParameter("@ID", 1));
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
        }
    }
}