C#でSQLiteを使う
NuGet 参照の追加
- ツール → NuGet パッケージマネージャー → ソリューションのNuGet パッケージの管理
- 参照でSQLiteを検索
- System.Data.SQLiteを選択してプロジェクトにインストール
Select文の読み込み
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
// Connection String の作成string dataSource = @"C:\Test.db";SQLiteConnectionStringBuilder sQLiteConnectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = dataSource };using (var cn = new SQLiteConnection(sQLiteConnectionStringBuilder.ToString())){ cn.Open(); // Connection オープン using (var cmd = new SQLiteCommand(cn)) { cmd.CommandText = "select * from Categories;"; using (var reader = cmd.ExecuteReader()) // SELECT 読み込み { while (reader.Read()) { int ID = reader.GetInt32(0); string CategoryName = reader.GetString(2); DateTime Date = reader.GetDateTime(2); double Price = reader.GetDouble(4); string Memo = reader.GetTextReader(6).ReadToEnd(); } } }} |
INSERT
// Connection String の作成
string dataSource = @"C:\Test.db";
SQLiteConnectionStringBuilder sQLiteConnectionStringBuilder
= new SQLiteConnectionStringBuilder { DataSource = dataSource };
using (var connection
= new SQLiteConnection(sQLiteConnectionStringBuilder.ToString()))
{
// Insert
using (var command = new SQLiteCommand(connection))
{
// トランザクションを開始
using (var transaction = connection.BeginTransaction())
{
foreach (var Member in list)
{
command.CommandText
= "INSERT INTO Table1(Id, FirstName, Age) VALUES(@Id, `firstName, @Age);";
command.Parameters
.Add(new SQLiteParameter("@Id", Member.TaxiMap));
command.Parameters
.Add(new SQLiteParameter("@FirstName", Member.FirstName));
command.Parameters
.Add(new SQLiteParameter("@Age", Member.Age));
command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}