[筆記] C# 讀寫 SQLite

class TestRecord
    {

        private string mDbPath = string.Empty;
        private SQLiteConnection dbConnection;
        public TestRecord(string _DbPath)
        {
            mDbPath = _DbPath;
            dbConnection = new SQLiteConnection("data source=" + mDbPath);
        }

        public void Insert()
        {
            dbConnection.Open();

            string strInsertSql = "insert into TestRecord (ID,DataTime,Point1) values (null,@Updatedate,@Value1)";
            SQLiteCommand command = new SQLiteCommand(strInsertSql, dbConnection);
            command.Parameters.Add(new SQLiteParameter("@Updatedate", DbType.DateTime2));
            command.Parameters.Add(new SQLiteParameter("@Value1", DbType.Decimal));
            DbTransaction trans = dbConnection.BeginTransaction();

            for (int a = 0x01; a < 1000000; a++)
            {
                command.Parameters[0].Value = DateTime.Now;
                command.Parameters[1].Value = 11.1;
                command.ExecuteNonQuery();
            }
            trans.Commit();
            dbConnection.Close();
        }

        public void Delete()
        {
            dbConnection.Open();
            //刪除所有資料
            string strInsertSql = "delete from TestRecord";
            SQLiteCommand command = new SQLiteCommand(strInsertSql, dbConnection);
            command.ExecuteNonQuery();

            //歸零 ID
            strInsertSql = "delete from sqlite_sequence where name = 'TestRecord'";
            command = new SQLiteCommand(strInsertSql, dbConnection);
            command.ExecuteNonQuery();

            //壓縮資料庫
            command = new SQLiteCommand("VACUUM", dbConnection);
            command.ExecuteNonQuery();
            dbConnection.Close();

        }

        public void Query()
        {
            dbConnection.Open();
            string strInsertSql = "SELECT * FROM TestRecord";
            SQLiteCommand command = new SQLiteCommand(strInsertSql, dbConnection);
            SQLiteDataReader sr = command.ExecuteReader();
            int a = 0x00;
            while (sr.Read())
            {
                string ss = sr["ID"].ToString();
                string aaaa = sr["DataTime"].ToString();
                string CCC = sr["Point1"].ToString();
                a++;
                
            }
            dbConnection.Close();

        }

        public void LastRecord()
        {
            dbConnection.Open();
            string strInsertSql = "SELECT * FROM `TestRecord` ORDER BY id DESC LIMIT 0 , 1";
            SQLiteCommand command = new SQLiteCommand(strInsertSql, dbConnection);
            SQLiteDataReader sr = command.ExecuteReader();
            int a = 0x00;
            while (sr.Read())
            {
                string ss = sr["ID"].ToString();
                string aaaa = sr["DataTime"].ToString();
                string CCC = sr["Point1"].ToString();
                a++;

            }
            dbConnection.Close();
        }

        public void aa()
        {
            string path = "d:\\CH01.db";
            SQLiteConnection cn = new SQLiteConnection("data source=" + mDbPath);
            cn.Open();
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = "PRAGMA table_info('TestRecord')";
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.Write($"{reader[i]},");
                }
                Console.WriteLine();
            }
            reader.Close();
        }
    }