Hank's Memo
0%

20230130 在 Ubuntu 練習 Sqlite3

Posted on Edited on In , ,

在 Ubuntu 練習 Sqlite3

安裝

1
2
3
4
5
6
sudo apt update
sudo apt upgrade
sudo apt install sqlite3
sqlite3 –version
sudo apt update
sudo apt install sqlitebrowser

How to install SQLite 3 in Ubuntu 20.04 and Linux Mint 20

建立專案

1
2
3
dotnet new console
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
touch SqliteDB.db

範例程式

create table script

1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS TB_ABC;
CREATE TABLE IF NOT EXISTS TB_ABC(
	COL1 nvarchar(10),
	COL2 numeric(18) NOT NULL,
	COL3 nvarchar(2000) NULL, --註解測試
	COL4 datetime NULL,
	COL5 int,
	PRIMARY KEY(COL1,COL2)
);

主程式 Program.cs

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
using System;
using Microsoft.Data.Sqlite;

var connectionStringBuilder = new SqliteConnectionStringBuilder();

//Use DB in project directory.  If it does not exist, create it:
connectionStringBuilder.DataSource = "./SqliteDB.db";

using (var connection = new SqliteConnection(connectionStringBuilder.ConnectionString))
{
    connection.Open();

    //Create a table (drop if already exists first):

    var delTableCmd = connection.CreateCommand();
    delTableCmd.CommandText = "DROP TABLE IF EXISTS favorite_beers";
    delTableCmd.ExecuteNonQuery();

    var createTableCmd = connection.CreateCommand();
    createTableCmd.CommandText = "CREATE TABLE favorite_beers(name VARCHAR(50))";
    createTableCmd.ExecuteNonQuery();

    //Seed some data:
    using (var transaction = connection.BeginTransaction())
    {
        var insertCmd = connection.CreateCommand();

        insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('LAGUNITAS IPA')";
        insertCmd.ExecuteNonQuery();

        insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('JAI ALAI IPA')";
        insertCmd.ExecuteNonQuery();

        insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('RANGER IPA')";
        insertCmd.ExecuteNonQuery();

        transaction.Commit();
    }

    //Read the newly inserted data:
    var selectCmd = connection.CreateCommand();
    selectCmd.CommandText = "SELECT name FROM favorite_beers";

    using (var reader = selectCmd.ExecuteReader())
    {
        while (reader.Read())
        {
            var message = reader.GetString(0);
            Console.WriteLine(message);
        }
    }
}