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 Finisar.SQLite;//http://einboch.pixnet.net/blog/post/248187728-c%23%E6%93%8D%E4%BD%9Csqlite%E8%B3%87%E6%96%99%E5%BA%AB

namespace CS_SQLite
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //產生SQLite的資料庫文件，副檔名為.db
            CreateSQLiteDatabase("data.db");

            //建立資料表test
            string createtablestring = "create table test (speed double, dist double);";
            CreateSQLiteTable("data.db", createtablestring);

            //插入資料到test表中
            string insertstring = "insert into test (speed,dist) values (’10’,’100′);insert into test (speed,dist) values (’20’,’200′);";
            SQLiteInsertUpdateDelete("data.db", insertstring);

            //讀取資料
            DataTable dt = GetDataTable("data.db", "select * from test");
            dataGridView1.DataSource = dt;
        }
        public SQLiteConnection OpenConn(string Database)//資料庫連線程式
        {
            string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=False;Compress=True;");
            SQLiteConnection icn = new SQLiteConnection();
            icn.ConnectionString = cnstr;
            if (icn.State == ConnectionState.Open) icn.Close();
            icn.Open();
            return icn;
        }
        public void CreateSQLiteDatabase(string Database)//建立資料庫程式
        {
            string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=True;Compress=True;");
            SQLiteConnection icn = new SQLiteConnection();
            icn.ConnectionString = cnstr;
            icn.Open();
            icn.Close();
        }
        public void CreateSQLiteTable(string Database, string CreateTableString)//建立資料表程式
        {
            SQLiteConnection icn = OpenConn(Database);
            SQLiteCommand cmd = new SQLiteCommand(CreateTableString, icn);
            SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
            try
            {
                cmd.Transaction = mySqlTransaction;
                cmd.ExecuteNonQuery();
                mySqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlTransaction.Rollback();
                throw (ex);
            }
            if (icn.State == ConnectionState.Open) icn.Close();
        }
        public void SQLiteInsertUpdateDelete(string Database, string SqlSelectString)//新增資料程式
        {
            SQLiteConnection icn = OpenConn(Database);
            SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
            SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
            try
            {
                cmd.Transaction = mySqlTransaction;
                cmd.ExecuteNonQuery();
                mySqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlTransaction.Rollback();
                throw (ex);
            }
            if (icn.State == ConnectionState.Open) icn.Close();
        }
        public DataTable GetDataTable(string Database, string SQLiteString)//讀取資料程式
        {
            DataTable myDataTable = new DataTable();
            SQLiteConnection icn = OpenConn(Database);
            SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds);
            myDataTable = ds.Tables[0];
            if (icn.State == ConnectionState.Open) icn.Close();
            return myDataTable;
        }

    }
}