簡單SQLite 數據庫操作Demo

public class DbHelperSQLite
    {
        public string connectionString = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + @"WIP.db;Version=3;";


        public event EventHandler<SQLiteMessageEventArg> SQLiteMessageEvent;


        public DbHelperSQLite()
        { }
   
        /// <summary>
        /// 執行單條SQL語句，返回值(bool)
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public bool ExecuteSql(string SQLString)
        {
            var eventHandle = SQLiteMessageEvent;
            string msg = "";


            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.ExecuteNonQuery();
                        msg = "SQL Command: " + SQLString + " Excute Success!";
                        if (eventHandle != null)
                        {
                            eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.Msg, msg, SQLString));
                        }
                        return true;
                    }
                    catch (SQLiteException ex)
                    {
                        msg = "Execute SQL Error Message: " + ex.Message;
                        if (eventHandle != null)
                        {
                            eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.ErrorSql, msg, SQLString));
                        }
                        return false;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 獲取最大值，返回值(int)
        /// </summary>
        /// <param name="FieldName">欄位名稱</param>
        /// <param name="TableName">表名稱</param>
        /// <returns></returns>
        public int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
	    return int.Parse(obj.ToString());
        }

        /// <summary>
        /// 單值查詢，返回查詢結果(Object)
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public object GetSingle(string SQLString)
        {
            var eventHandle = SQLiteMessageEvent;
            string msg = "";


            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            msg = "WARN : SQL Command Return null! [ " + SQLString + "]";
                            if (eventHandle != null)
                            {
                                eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.Msg, msg, SQLString));
                            }
                            return null;
                        }
                        else
                        {
                            msg = "SQL Command: " + SQLString + " Excute Success!";
                            if (eventHandle != null)
                            {
                                eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.Msg, msg, SQLString));
                            }
                            return obj;
                        }
                    }
                    catch (SQLiteException e)
                    {
                        msg = "Execute SQL Error Message: " + e.Message;
                        if (eventHandle != null)
                        {
                            eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.ErrorSql, msg, SQLString));
                        }
                        connection.Close();
                        return null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                    }
                    
                }
            }
        }

        /// <summary>
        /// 執行多條SQL語句，實現數據庫事務
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExcuteSqlTran(ArrayList SQLStringList)
        {
            var eventHandle = SQLiteMessageEvent;
            string msg = "";
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                            msg = "SQL Command: " + strsql + " Excute Success!";
                            if (eventHandle != null)
                            {
                                eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.Msg, msg, strsql));
                            }
                        }
                        else
                        {
                            msg = "Execute SQL Error Message: strsql.Trim().Length=" + strsql.Trim().Length.ToString();
                            if (eventHandle != null)
                            {
                                eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.ErrorSql, msg, strsql));
                            }
                        }
                    }
                    tx.Commit();
                }
                catch (SQLiteException ex)
                {
                    tx.Rollback();
                    msg = "Execute SQL Error Message: " + ex.Message;
                    if (eventHandle != null)
                    {
                        eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.ErrorSql, msg, "[ExcuteSqlTran]"));
                    }
                }
                finally
                {
                    conn.Close();
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// 執行單條SQL語句，返回值(DataTable)
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable SelectUse(string SQLString)
        {
            var eventHandle = SQLiteMessageEvent;
            string msg = "";


            SQLiteCommand cmd = null;
            SQLiteDataAdapter oda = null;
            SQLiteConnection connection = null;
            DataTable dt;
            try
            {
                connection = new SQLiteConnection(connectionString);
                cmd = new SQLiteCommand(SQLString, connection);
                connection.Open();
                dt = new DataTable();
                cmd.Transaction = connection.BeginTransaction();
                oda = new SQLiteDataAdapter(cmd);
                oda.Fill(dt);


                msg = "SQL Command: " + SQLString + " Excute Success!";
                if (eventHandle != null)
                {
                    eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.Msg, msg, SQLString));
                }


                return dt;


            }
            catch (SQLiteException ex)
            {
                msg = "Execute SQL Error Message: " + ex.Message;
                if (eventHandle != null)
                {
                    eventHandle(null, new SQLiteMessageEventArg(SQLiteMessageType.ErrorSql, msg, SQLString));
                }
                return null;
            }
            finally
            {
                connection.Close();
                cmd.Dispose();
                oda.Dispose();
            }
        }

        /// <summary>
        /// 測試DB連接
        /// </summary>
        /// <returns></returns>
        public bool ConnectConfirm()
        {
            var sqlStr = "select 1+1 from A";   //A是SQLite DB中的表
            var result = SelectUse(sqlStr);
            if (result != null)
            {
                if (result.Rows.Count == 0)
                {
                    return false;
                }
                return true;
            }
            else
            {
                return false;
            }
        }
    
    }

/// <summary>
    /// 回復事件
    /// </summary>
    public class SQLiteMessageEventArg:EventArgs
    {
        public SQLiteMessageType EventId { get; private set; }


        public string EventData{get;private set;}


        public string SqlCommand { get; private set; }


        public SQLiteMessageEventArg(SQLiteMessageType type, string data, string StrSql)
        {
            EventId = type;
            EventData = data;
            SqlCommand = StrSql;
        }
    }


    public enum SQLiteMessageType
    {
        None = 0,
        Info = 1,
        Msg = 2,
        Error = 3,
        ErrorSql = 4,
        ConnectBroken = 5,
        Others = 9
    }

//=======================主界面================================
        static void Main(string[] args)
        {
            bHelperSQLite _DB = new DbHelperSQLite();
            _DB.SQLiteMessageEvent += new EventHandler<SQLiteMessageEventArg>(_DB_SQLiteMessageEvent);
            string _sql = "select Seq,cstid,D_Time,flag from w_CSTinfo";
            DataTable _dt=_DB.SelectUse(_sql);
	    ... ...
        }

        /// <summary>
        /// DB執行對象返回事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void _DB_SQLiteMessageEvent(object sender, SQLiteMessageEventArg e)
        {
            switch (e.EventId)
            { 
                case SQLiteMessageType.Msg:
                    MessageBox.Show(e.EventData);
                    break;
                case SQLiteMessageType.Error:
                    MessageBox.Show(e.EventData + ", SQL: " + e.SqlCommand);
                    break;
                case SQLiteMessageType.ErrorSql:
                    MessageBox.Show(e.EventData);
                    break;
                default:
                    break;
            }
        }