SQLite 動手做(一)

以SQL語法直接建立資料庫，語法若不孰悉可以使用 FireFox 瀏覽器的擴充套件 sqlite-manager 作為輔助。

SQLite.NET 的dll 對於x86 與64Bit 有區分須小心。若參考錯誤執行時會產生Error。

 

1. 資料庫 : jNmsDB.sqlite

2. 資料表:

nmsGroup

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	GroupID	INTEGER	1		1
1	GroupName	CHAR[20]	1	192.168
2	Remark	TEXT	0

nmsStation

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	StationID	INTEGER	1		1
1	IP_Section	INTEGER	1	1
2	StationName	INTEGER	1
3	GroupID	INTEGER	1
4	Remark	TEXT	0

nmsDevice

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	DeviceID	INTEGER	1		1
1	StationID	INTEGER	1
2	DeviceName	CHAR[20]	1
3	IP	TEXT	1	“192.168.1.1”
4	PingEnable	BOOLB	1
5	MsgID	INTEGER	1
6	AlarmSummery	BOOLB	1
7	ValueAlarm	BOOLB	1
8	IcmpAlarm	BOOL	1
9	IcmpAlarmCnt	integer	1
10	Remark	TEXT	0

nmsMessage

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	MsgID	INTEGER	1		1
1	MSG	TEXT	1	“ ”
2	Level	INTEGER	1
3	Remark	TEXT	0

nmsDI

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	VarID	INTEGER	1		1
1	VarName	TEXT	1
3	Remark	TEXT	0

nmsBit

ColumeID	Name	Type	NotNull	DefaultVAlue	Primary Key
0	MappingID	INTEGER	1		1
1	VarID	INTEGER	1	0
2	BitNo	INTEGER	1	0
3	DevID	INTEGER	1	0
4	MsgID	INTEGER	1	0
5	LogEnabled	BOOL	1
6	Remark	TEXT	0

3. 實驗結果

4. SourceCode

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 System.Data.SQLite;
using System.IO;

namespace TestSQLite
{
    public partial class Form1 : Form
    {
        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter DB;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();
        private BindingSource BS = new BindingSource();
        

        public Form1()
        {
            InitializeComponent();

            this.cmbTable.Items.AddRange(new object[] {
            "nmsGroup",
            "nmsStation",
            "nmsDevice",
            "nmsMessage",
            "nmsDI",
            "nmsBit "});
        }

        string DbName = "jNmsDB_1.sqlite";
        string TableName = "nmsGroup";

        public void NmsDBCreate(string DbName)
        {
            SetConnection(DbName);
            sql_con.Open();            

            try
            {
                #region New DB File
                string sql = " CREATE TABLE \"nmsMessage\" (\"MsgID\" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , \"MSG\" TEXT NOT NULL  DEFAULT \"\", \"Level\" INTEGER NOT NULL  DEFAULT 0, \"Remark\" TEXT)";
                this.ExecuteNonQuery(sql);

                sql = " CREATE TABLE \"nmsBit\" (\"MappingID\" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , \"VarID\" INTEGER NOT NULL  DEFAULT 0, \"bitNo\" INTEGER NOT NULL  DEFAULT 0, \"DevID\" INTEGER NOT NULL  DEFAULT 0, \"MsgID\" INTEGER NOT NULL  DEFAULT 0, \"LogEnabled\" BOOL NOT NULL , \"Remark\" TEXT)";
                this.ExecuteNonQuery(sql);

                sql ="CREATE TABLE \"nmsDI\" (\"VarID\" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , \"VarName\" TEXT NOT NULL  UNIQUE , \"Remark\" TEXT)";
                this.ExecuteNonQuery(sql);

                sql ="CREATE TABLE \"nmsDevice\" (\"DeviceID\" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , \"StationID\" INTEGER NOT NULL , \"DeviceName\" TEXT NOT NULL  UNIQUE , \"IP\" TEXT NOT NULL  UNIQUE  DEFAULT \"192.168.1.1\", \"PingEnable\" BOOL, \"MsgID\" INTEGER NOT NULL , \"AlarmSummery\" BOOL NOT NULL , \"ValueAlarm\" BOOL NOT NULL , \"IcmpAlarm\" BOOL NOT NULL , \"IcmpAlarmCnt\" INTEGER NOT NULL  DEFAULT 0, \"Remark\" TEXT NOT NULL )";
                this.ExecuteNonQuery(sql);

                sql = "CREATE TABLE \"nmsStation\" (\"StationID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"IP_Section\" INTEGER NOT NULL  DEFAULT 1, \"StationName\" TEXT NOT NULL  UNIQUE  DEFAULT Taipei, \"GroupID\" INTEGER NOT NULL , \"Remark\" TEXT)";
                this.ExecuteNonQuery(sql);

                sql = "CREATE TABLE \"nmsGroup\" (\"GroupID\" INTEGER PRIMARY KEY  NOT NULL ,\"GroupName\" CHAR[20] NOT NULL  DEFAULT (192.168) ,\"Remark\" CHAR[20])";
                this.ExecuteNonQuery(sql);
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sql_con.Close();
            }
       
        }
        private void SetConnection(string DbName)
        {
            if (!File.Exists(DbName)) { File.Create(DbName); }
            sql_con = new SQLiteConnection(string.Format("Data Source={0};Version=3;New=False;Compress=True;", DbName));
        }
        private void LoadData(string DbName, string TAbleName)
        {
            SetConnection(DbName);
            sql_con.Open();

            try
            {
                sql_cmd = sql_con.CreateCommand();
                string CommandText = string.Format("select * from  {0}", TAbleName);
                DB = new SQLiteDataAdapter(CommandText, sql_con);
                DS.Reset();
                DB.Fill(DS);
                DT = DS.Tables[0];
                Grid.DataSource = DT;

                BS.DataSource = DT;
                Group_Navigator.BindingSource = BS;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                sql_con.Close();
            }
        }
        private void ExecuteNonQuery(string txtQuery)
        {
            SetConnection(DbName);
            sql_con.Open();

            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;

            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        private void Update(ref SQLiteDataAdapter db, ref DataTable dt )
        {
            SQLiteCommandBuilder sqCommandBuilder = new SQLiteCommandBuilder(db);
            db.AcceptChangesDuringUpdate = true;
            db.InsertCommand = sqCommandBuilder.GetInsertCommand();
            db.UpdateCommand = sqCommandBuilder.GetUpdateCommand();
            db.DeleteCommand = sqCommandBuilder.GetDeleteCommand();

            db.Update(dt.DataSet, dt.TableName);
        }

        #region 測試區
        private void button1_Click_1(object sender, EventArgs e)
        {
            DbName = txtDbName.Text;            
            NmsDBCreate(DbName);
        }
        private void btnLoadTable_Click(object sender, EventArgs e)
        {
            LoadData(DbName, cmbTable.Text);
        }
        private void btnSaveData_Click(object sender, EventArgs e)
        {
            this.Update(ref DB, ref DT);
        }
        #endregion

        private void txtDbName_TextChanged(object sender, EventArgs e)
        {
            DbName = this.txtDbName.Text;
        }        
    }
}

5. 在使用 FireFox 瀏覽器的擴充套件 sqlite-manager中檢視成果