第128回 C#でSQLiteを使用する方法
公開日:2014-12-10 更新日:2019-05-11
1. 概要
独り言によるプログラミング講座「第128回 C#でSQLiteを使用する方法」です。
2. 動画
3. 動画中に書いたソース
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();
}
}
}
}
}