C#利用System.Data.SQLiteはSQLiteに対する操作を実現する

14583 ワード
sqlite 同前
SQLiteの紹介
SQLiteはAccessのような単機版データベース管理システムです.すべてのデータベースの定義(定義、テーブル、インデックス、およびデータ自体を含む)は、単一のファイルに保存されています.また、SQLiteは、メモリ消費、ファイル体積、単純性の面で優れたパフォーマンスを発揮するCで実装されたクラスライブラリです.データが10 W以下の場合、クエリの速度もかなり速いです.SQLiteは、トランザクションを含む多くのSQL 92を実装する基準を備えています.(原子間性、一貫性、独立性、持続性)、トリガ、および多くの複雑なクエリー.挿入または更新されたデータのタイプチェックは行わず、整数列に文字列を挿入することができます(これは一部のユーザーがあまり適応していない可能性があります).Windows/Linux/Unixなどの主流システムをサポートし、AndroidやWindows Mobileなどの組み込みシステムもサポートします.
System.Data.SQLiteの前期準備
1、System.Data.SQLiteライブラリは、C#操作SQLiteのdllファイルをダウンロードします.ダウンロード先:http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki2、SQLite可視化ツールをダウンロードし、SQLiteライブラリデータテーブルデータを表示する.ダウンロード先:http://www.sqliteexpert.com/download.html
System.Data.SQLite汎用クラス
1、データベースファイルの作成、2、DataTableに戻る;3、DataReaderに戻る;4、添削を実行し、影響を受けた行数を返す.5、クエリーを実行して、最初の行の最初の列(通常はSUM/AVG/COUNTなどの行関数を持つクエリーに使用される)を返す;6、ライブラリ内のすべてのテーブルを返す;System.Data.SQLiteにはストレージプロセスが存在しないため、すべての操作はテキストベースのSQL文であり、SQL注入を避けるために、パラメータ化されたSQL文を使用した.
一、SQLiteデータベースファイルの作成、削除
フォーマットの規範性を高めるため、プロジェクトのApp.configファイルの構成テキストにデータベースファイルパスを追加します.次のconnectionStringsラベルは次のとおりです.

動作方法SQLiteConnection.Openでは、指定した名前のデータベースファイルが空になります.
using System.Data.SQLite;
using System.Configuration;

 // app.config            ,            “Data Sounce”
    private static string connStr = ConfigurationManager.ConnectionStrings["itcastCater"].ConnectionString;
    
static void Create()
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    //           
    cn.Open();
    cn.Close();
}

データベースはファイルタイプなので、直接Systemを使います.IO.File.Delete(string path)メソッドでファイルを削除します.
//     
static void Delete()
{
    if (System.IO.File.Exists(connStr))
    {
        System.IO.File.Delete(connStr);
    }
}

二、データベーステーブルの作成、削除
テーブルを作成するにはSQLコマンドを使用します.テーブルを作成する手順は、次のとおりです(ビジュアル化ツールSQLiteExpertで作成することもできます):1、データベース接続の確立、2、データベースのオープン(データベースがない場合、Openも新しいデータベースを作成する);3、SQLiteCommandクラスを宣言し、主にSQLコマンドの配置と実行に使用する;4、SQLiteCommandの接続とSQLiteConnectionを結びつける;5、SQLiteCommandのCommand TextにSQL文CREATE TABLE文を入力する;6、SQLiteCommand.ExcuteNonQuery()を呼び出すメソッドが実行されます.
//      
static void CreateTable()
{
    SQLiteConnection cn = new SQLiteConnection(connStr);//       
    if (cn.State!= System.Data.ConnectionState.Open)
    {
        cn.Open();//     
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = cn;//  SQLiteCommand  Connection SQLiteConnection     
        cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";//  SQL  
        cmd.ExecuteNonQuery();//       
    }
    cn.Close();
}

上のSQL文には「IF NOT EXISTS」という文がありますが、この文を付けずに元のデータベースファイルに同じ名前で作成するテーブルがある場合は、エラーが発生します.SQL文の大文字と小文字は規定されていません.一般的には規範のために大文字で書きます.
テーブルを削除するのは、テーブルを作成する手順と同じです.SQL文を変更しただけです.
//      
static void DeleteTable()
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    if (cn.State != System.Data.ConnectionState.Open)
    {
        cn.Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = cn;
        cmd.CommandText = "DROP TABLE IF EXISTS t1";
        cmd.ExecuteNonQuery();
    }
    cn.Close();
}

テーブル名変更SQL文ALTER TABLEでt 1テーブル名をt 3に変更
//            
cmd.CommandText = "ALTER TABLE t1 RENAME TO t3";
cmd.ExecuteNonQuery();

三、クエリー表構造
クエリ・テーブルの構造では、SQLiteの特殊なPRAGMAコマンドを使用する必要があります.SQliteDataReaderで読み出されたデータの順序リストは、次のとおりです.
下付き文字
名前
説明
0
cid
シーケンス番号
1
name
名前
2
type
データ型
3
notnull
null値、0不可、1可
4
dflt_value
デフォルト値
5
pk
プライマリ・キーprimary key、0 No、1 Yes
コードは次のとおりです.
//        ，  PRAGMA    
SQLiteConnection cn = new SQLiteConnection(connStr);
cn.Open();
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText= "PRAGMA table_info('t1')";

//   ： DataAdapter DataTable ，     using System.Data
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
foreach(DataRow r in table.Rows)
{
    Console.WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} ");
}
Console.WriteLine();

//   ： DataReader，      
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    for(int i = 0; i < reader.FieldCount; i++)
    {
        Console.Write($"{reader[i]},");
    }
    Console.WriteLine();
}
reader.Close();

1つ以上のテーブルがある場合は、SQLiteの特殊なテーブルsqlite_を使用して、すべてのテーブルの構造を次のようにします.master type=tableの場合、nameとtbl_nameは同じで、その他type=index、viewなどの場合、tbl_nameこそテーブル名です.
//       
static void QueryAllTableInfo()
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    if (cn.State != System.Data.ConnectionState.Open)
    {
        cn.Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = cn;
        cmd.CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table' ";
        SQLiteDataReader sr = cmd.ExecuteReader();
        List tables = new List();
        while (sr.Read())
        {
            tables.Add(sr.GetString(0));
        }
        //datareader       ，   commandText     
        sr.Close();
        foreach (var a in tables)
        {
            cmd.CommandText = $"PRAGMA TABLE_INFO({a})";
            sr = cmd.ExecuteReader();
            while (sr.Read())
            {
                Console.WriteLine($"{sr[0]} {sr[1]} {sr[2]} {sr[3]}");
            }
            sr.Close();
        }
    }
    cn.Close();
}

四、データベース表の下の列に対する操作
列(フィールド)を追加するにはSQLコマンドALTER TABLEを使用し、次の例ではt 1テーブルにage、データ型intという新しい列を追加します.
cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int";
cmd.ExecuteNonQuery();

表を作成したSQL文を読み込み、表を作成したSQL文を読み込みます.SqliteExpertのDDLに表示されます.これを読み取るのは、次の削除列を追加する準備です.
cmd.CommandText = "SELECT sql FROM sqlite_master WHERE TYPE='table'";
SQLiteDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
    Console.WriteLine(sr[0].ToString());
}
sr.Close();

列名SQLiteを変更するには、列名を直接変更するコマンドと列を削除するコマンドはありません.1つ目は、ターゲットテーブルを名前変更することです.2、新しいカラム名を持つ新しいテーブルを作成する.3、古いテーブルのデータを新しいテーブルにコピーする(Connection.BeginTransaction()を忘れないでください).
2つ目は、sqlite_を変更することです.masterの中のschemaは、データベースを壊しやすいです.SQLiteは、SQLiteが接続するたびに、schema内のテーブルごとに作成されたCREATE TABLE文に基づいてcolumnの情報を動的に作成します.columnのデータ型と位置が変わらない限り、CREATE TABLE文を変更することでcolumnの情報を変更できます.次の2つの方法があります.
方法1:
//    1
//     ，         ，    
//params string[]  ：connStr     ，1   ，2     3    
static void RenameColumn1(params string[] str)
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    cn.Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.Connection = cn;
    
    //  str[1]       SQL   
    cmd.CommandText = "SELECT name,sql FROM sqlite_master WHERE TYPE='table' ORDER BY name";
    SQLiteDataReader sr = cmd.ExecuteReader();

    string _sql = ""; 
    while (sr.Read())
    {
        if (string.Compare(sr.GetString(connStr), str[1], true) == 0)
        {
            _sql = sr.GetString(1);
            break;
        }
    }
    sr.Close();

    //         _old 
    string _old = str[1] + "_old";
    cmd.CommandText = $"ALTER TABLE {str[1]} RENAME TO {_old}";
    cmd.ExecuteNonQuery();

    //    ，                      ，       
    _sql = _sql.Replace(str[2],str[3]);
    cmd.CommandText = _sql;
    cmd.ExecuteNonQuery();

    //    
    using (SQLiteTransaction tr = cn.BeginTransaction())
    {
        cmd.CommandText = $"INSERT INTO {str[1]} SELECT * FROM {_old}";
        cmd.ExecuteNonQuery();
        cmd.CommandText = $"DROP TABLE {_old}";
        cmd.ExecuteNonQuery();
        tr.Commit();
    }
    cn.Close();
}

方式2:
//    2,  schema     sql  
//  ：sqlite        ，        sql       column    
static void RenameColumn2(params string[] str)
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    cn.Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.Connection = cn;

    //  str[1]       SQL   
    cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'";
    SQLiteDataReader sr = cmd.ExecuteReader();
    sr.Read();
    string _sql = sr.GetString(connStr);
    sr.Close();
    //      '
    _sql =$"UPDATE sqlite_master SET sql='{_sql.Replace(str[2],str[3])}' WHERE name= '{str[1]}' ";

    //   writable_schema   true，    schema 
    cmd.CommandText = "pragma writable_schema=1";
    cmd.ExecuteNonQuery();
    cmd.CommandText = _sql;
    cmd.ExecuteNonQuery();
    //   writable_schema   false。
    cmd.CommandText = "pragma writable_schema=0";
    cmd.ExecuteNonQuery();

    cn.Close();
}

列SQLiteを削除しても、列を削除するコマンドはありません.上と同じように、2つの方法です.1つは、ターゲット・テーブルを名前変更して、列(フィールド)を削除しない新しいテーブルを作成し、古いテーブルのデータを新しいテーブルにコピーすることです.2つ目は、schemaで作成されたテーブルのSQL文を直接変更することです.その中で最も重要なのは、インデックス、デフォルト値など、作成されたテーブルの列のすべての情報を保存することです.例2つ目の方法を使用します.
//   2，string[] ,connStr      ，1   ，2       
static void DeleteColumn2(params string[] str)
{
    SQLiteConnection cn = new SQLiteConnection(connStr);
    cn.Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.Connection = cn;
    //  str[1]       SQL   
    cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'";
    SQLiteDataReader sr = cmd.ExecuteReader();
    sr.Read();
    string _sql = sr.GetString(connStr);
    sr.Close();

    //      
    //C#7.0    ，Tuple<>    ，   NuGet install-package system.valuetuple
    List list = GetColumnDefine(_sql);
    //         
    int _index = list.IndexOf(list.Where(x => x.name == str[2]).First());
    //    sql  
    StringBuilder sb = new StringBuilder();
    sb.Append($"CREATE TABLE {str[1]}(");
    for (int i = 0; i < list.Count; i++)
    {
        if (i != _index)//       ，       。
        {
            sb.Append($"{list[i].define},");
        }
    }
    sb.Remove(sb.Length - 1, 1);
    sb.Append(")");
    //  schema
    _sql = $"UPDATE sqlite_master SET sql='{sb.ToString()}' WHERE name='{str[1]}'";
    //   writable_schema   true，    schema 
    cmd.CommandText = "pragma writable_schema=1";
    cmd.ExecuteNonQuery();
    cmd.CommandText = _sql;
    cmd.ExecuteNonQuery();
    //   writable_schema   false。
    cmd.CommandText = "pragma writable_schema=0";
    cmd.ExecuteNonQuery();

    cn.Close();
}

取得カラムの定義
//      
static List GetColumnDefine(string SqlStr)
{
    int n = 0;
    int _start = 0;
    string _columnStr = "";
    for (int i = 0; i < SqlStr.Length; i++)
    {
        if (SqlStr[i] == '(')
        {
            if (n++ == 0) { _start = i; }
        }
        else
        {
            if (SqlStr[i] == ')')
            {
                if (--n == 0)
                {
                    _columnStr = SqlStr.Substring(_start + 1, i - _start - 1);
                    break;
                }
            }

        }
    }
    string[] ss = _columnStr.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
    //C#7.0    ，Tuple<>    ，   NuGet install-package system.valuetuple
    List reslut = new List();
    foreach (var a in ss)
    {
        string s = a.Trim();
        n = 0;
        for (int i = 0; i < s.Length; i++)
        {
            if (s[i] == ' ')
            {
                reslut.Add((s.Substring(0, i), s));
                break;
            }
        }
    }
    return reslut;
}

五、データを追加削除して調べる
データの挿入
挿入データは主にSQL文INSERT INTO例1(単純挿入):
cmd.CommandText = "INSERT INTO t1 VALUES('99999',11)";
cmd.ExecuteNonQuery();

例2(変数挿入、System.Data参照):
using System.Data;

string s = "123456";
int n = 10;
cmd.CommandText = "INSERT INTO t1(id,age) VALUES(@id,@age)";
cmd.Parameters.Add("id", DbType.String).Value = s;
cmd.Parameters.Add("age", DbType.Int32).Value = n;
cmd.ExecuteNonQuery();

データの置換
SQLコマンドREPLACE INTO.次の例では、t 1テーブルのidはプライマリキーであり、同じプライマリキー値のUPDATEであり、そうでない場合INSERT
string s = "123456";
int n = 30;
cmd.CommandText = "REPLACE INTO t1(id,age) VALUES(@id,@age)";
cmd.Parameters.Add("id", DbType.String).Value = s;
cmd.Parameters.Add("age", DbType.Int32).Value = n;
cmd.ExecuteNonQuery();

データの更新
SQLコマンドUPDATE tablename SET column 1=value,column 2=value…WHERE条件
string s = "333444";
int n = 30;
cmd.CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='0123456789'";
cmd.Parameters.Add("id", DbType.String).Value = s;
cmd.Parameters.Add("age", DbType.Int32).Value = n;
cmd.ExecuteNonQuery();

データの削除
SQLコマンド:DELETE FROM tablename WHERE条件
cmd.CommandText = "DELETE FROM t1 WHERE id='99999'";
cmd.ExecuteNonQuery();

データの問合せ
SQLコマンド:SELETE文、詳細はSQLチュートリアルを参照してください.
//   1   ，      ,rowid       ，         
cmd.CommandText = "SELECT * FROM t1 WHERE rowid=1";
SQLiteDataReader sr = cmd.ExecuteReader();
while (sr.Read())
{
    Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}");
}
sr.Close();
//          rowid         
cmd.CommandText = "SELECT rowid FROM t1 ";
sr = cmd.ExecuteReader();
while (sr.Read())
{
    Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}");
}
sr.Close();

クエリデータを取得する行数(レコード数)上記の例から分かるように、rowidは正しい行数(レコード数)ではなく、INSERTの場合のB-Treeの相関数です.表の行数(レコード数)を知るには、次のようにします.
cmd.CommandText = "SELECT count(*) FROM t1";
sr = cmd.ExecuteReader();
sr.Read();
Console.WriteLine(sr.GetInt32(0).ToString());
sr.Close();
Golang で標準出力テストの方法の調査
2つの簡単なphp設計モード