欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

C#操作SQLite實(shí)現(xiàn)數(shù)據(jù)的增刪改查

 更新時(shí)間:2022年01月23日 11:27:18   作者:熊思雨  
SQLite是一個(gè)輕量級(jí)、跨平臺(tái)的關(guān)系型數(shù)據(jù)庫(kù),在小型項(xiàng)目中,方便,易用,同時(shí)支持多種開(kāi)發(fā)語(yǔ)言。本文將用C#語(yǔ)言對(duì)SQLite 的一個(gè)封裝,實(shí)現(xiàn)數(shù)據(jù)的增刪改查。需要的可以參考一下

簡(jiǎn)介

SQLite是一個(gè)輕量級(jí)、跨平臺(tái)的關(guān)系型數(shù)據(jù)庫(kù),在小型項(xiàng)目中,方便,易用,同時(shí)支持多種開(kāi)發(fā)語(yǔ)言,下面是我用C#語(yǔ)言對(duì)SQLite 的一個(gè)封裝。

Winfrom界面如下:

代碼還需要改進(jìn)部分:

下面的代碼我不覺(jué)得是完美的,讀者可以自己去實(shí)現(xiàn)這些功能:

1.如果不想用多線程可以去掉UsingLock.cs ,在SQLiteHelper.cs中刪除對(duì)應(yīng)的引用即可。

2.創(chuàng)建數(shù)據(jù)庫(kù)文件,可以從代碼中分離成單獨(dú)的方法。比如創(chuàng)建有密碼的數(shù)據(jù)庫(kù)文件。

3.在執(zhí)行SQL語(yǔ)句時(shí),有些語(yǔ)句執(zhí)行成功,也不會(huì)有影響行數(shù)。比如,創(chuàng)建表,刪除表,此時(shí)執(zhí)行SQL語(yǔ)句返回的影響行數(shù)就為0。

4.只要SQL語(yǔ)句不報(bào)錯(cuò)就是執(zhí)行成功的,在方法的返回值可以改為多個(gè),比如用 

Tuple<bool, string, int> 第一個(gè)參數(shù) bool 代表執(zhí)行結(jié)果,第二個(gè)參數(shù) string 代表錯(cuò)誤信息,第三個(gè)參數(shù) int 代表影響的行數(shù)。

同樣,也可以這樣:

Tuple<bool, string, DataSet> 第一個(gè)參數(shù) bool 代表執(zhí)行結(jié)果,第二個(gè)參數(shù) string 代表錯(cuò)誤信息,第三個(gè)參數(shù) DataSet 代表返回的表單數(shù)據(jù)。

主要代碼

SQLiteHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text;
 
namespace MySQLiteHelper
{
    public class SQLiteHelper
    {
        #region 字段
        
        /// <summary>
        /// 事務(wù)的基類(lèi)
        /// </summary>
        private DbTransaction DBtrans;
        /// <summary>
        /// 使用靜態(tài)變量字典解決多線程實(shí)例本類(lèi),實(shí)現(xiàn)一個(gè)數(shù)據(jù)庫(kù)對(duì)應(yīng)一個(gè)clslock
        /// </summary>
        private static readonly Dictionary<string, ClsLock> RWL = new Dictionary<string, ClsLock>();
        /// <summary>
        /// 數(shù)據(jù)庫(kù)地址
        /// </summary>
        private readonly string mdataFile;
        /// <summary>
        /// 數(shù)據(jù)庫(kù)密碼
        /// </summary>
        private readonly string mPassWord;
        private readonly string LockName = null;
        /// <summary>
        /// 數(shù)據(jù)庫(kù)連接定義
        /// </summary>
        private SQLiteConnection mConn;
 
        #endregion
 
        #region 構(gòu)造函數(shù)
 
        /// <summary>
        /// 根據(jù)數(shù)據(jù)庫(kù)地址初始化
        /// </summary>
        /// <param name="dataFile">數(shù)據(jù)庫(kù)地址</param>
        public SQLiteHelper(string dataFile)
        {
            this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile=null");
            //this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile;
            this.mdataFile = dataFile;
            if (!RWL.ContainsKey(dataFile))
            {
                LockName = dataFile;
                RWL.Add(dataFile, new ClsLock());
            }
        }
 
        /// <summary>
        /// 使用密碼打開(kāi)數(shù)據(jù)庫(kù)
        /// </summary>
        /// <param name="dataFile">數(shù)據(jù)庫(kù)地址</param>
        /// <param name="PassWord">數(shù)據(jù)庫(kù)密碼</param>
        public SQLiteHelper(string dataFile, string PassWord)
        {
            this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile is null");
            this.mPassWord = PassWord ?? throw new ArgumentNullException("PassWord is null");
            //this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile;
            this.mdataFile = dataFile;
            if (!RWL.ContainsKey(dataFile))
            {
                LockName = dataFile;
                RWL.Add(dataFile, new ClsLock());
            }
        }
 
        #endregion
 
        #region 打開(kāi)/關(guān)閉 數(shù)據(jù)庫(kù)
 
        /// <summary>  
        /// 打開(kāi) SQLiteManager 使用的數(shù)據(jù)庫(kù)連接  
        /// </summary>  
        public void Open()
        {
            if (string.IsNullOrWhiteSpace(mPassWord))
            {
                mConn = OpenConnection(this.mdataFile);
            }
            else
            {
                mConn = OpenConnection(this.mdataFile, mPassWord);
            }
            Console.WriteLine("打開(kāi)數(shù)據(jù)庫(kù)成功");
        }
 
        /// <summary>
        /// 關(guān)閉連接
        /// </summary>
        public void Close()
        {
            if (this.mConn != null)
            {
                try
                {
                    this.mConn.Close();
                    if (RWL.ContainsKey(LockName))
                    {
                        RWL.Remove(LockName);
                    }
                }
                catch
                {
                    Console.WriteLine("關(guān)閉失敗");
                }
            }
            Console.WriteLine("關(guān)閉數(shù)據(jù)庫(kù)成功");
        }
 
        #endregion
 
        #region 事務(wù)
 
        /// <summary>
        /// 開(kāi)始事務(wù)
        /// </summary>
        public void BeginTrain()
        {
            EnsureConnection();
            DBtrans = mConn.BeginTransaction();
        }
 
        /// <summary>
        /// 提交事務(wù)
        /// </summary>
        public void DBCommit()
        {
            try
            {
                DBtrans.Commit();
            }
            catch (Exception)
            {
                DBtrans.Rollback();
            }
        }
 
        #endregion
 
        #region 工具
 
        /// <summary>  
        /// 打開(kāi)一個(gè)SQLite數(shù)據(jù)庫(kù)文件,如果文件不存在,則創(chuàng)建(無(wú)密碼)
        /// </summary>  
        /// <param name="dataFile"></param>  
        /// <returns>SQLiteConnection 類(lèi)</returns>  
        private SQLiteConnection OpenConnection(string dataFile)
        {
            if (dataFile == null)
            {
                throw new ArgumentNullException("dataFiledataFile=null");
            }
            if (!File.Exists(dataFile))
            {
                SQLiteConnection.CreateFile(dataFile);
            }
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
            {
                DataSource = dataFile
            };
            conn.ConnectionString = conStr.ToString();
            conn.Open();
            return conn;
        }
 
        /// <summary>  
        /// 打開(kāi)一個(gè)SQLite數(shù)據(jù)庫(kù)文件,如果文件不存在,則創(chuàng)建(有密碼)
        /// </summary>  
        /// <param name="dataFile"></param>  
        /// <param name="Password"></param>
        /// <returns>SQLiteConnection 類(lèi)</returns>  
        private SQLiteConnection OpenConnection(string dataFile, string Password)
        {
            if (dataFile == null)
            {
                throw new ArgumentNullException("dataFile=null");
            }
            if (!File.Exists(Convert.ToString(dataFile)))
            {
                SQLiteConnection.CreateFile(dataFile);
            }
            try
            {
                SQLiteConnection conn = new SQLiteConnection();
                SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
                {
                    DataSource = dataFile,
                    Password = Password
                };
                conn.ConnectionString = conStr.ToString();
                conn.Open();
                return conn;
            }
            catch (Exception)
            {
                return null;
            }
        }
 
        /// <summary>  
        /// 讀取 或 設(shè)置 SQLiteManager 使用的數(shù)據(jù)庫(kù)連接  
        /// </summary>  
        public SQLiteConnection Connection
        {
            get
            {
                return mConn;
            }
            private set
            {
                mConn = value ?? throw new ArgumentNullException();
            }
        }
 
        /// <summary>
        /// 確保數(shù)據(jù)庫(kù)是連接狀態(tài)
        /// </summary>
        /// <exception cref="Exception"></exception>
        protected void EnsureConnection()
        {
            if (this.mConn == null)
            {
                throw new Exception("SQLiteManager.Connection=null");
            }
            if (mConn.State != ConnectionState.Open)
            {
                mConn.Open();
            }
        }
 
        /// <summary>
        /// 獲取數(shù)據(jù)庫(kù)文件的路徑
        /// </summary>
        /// <returns></returns>
        public string GetDataFile()
        {
            return this.mdataFile;
        }
 
        /// <summary>  
        /// 判斷表 table 是否存在  
        /// </summary>  
        /// <param name="table"></param>  
        /// <returns>存在返回true,否則返回false</returns>  
        public bool TableExists(string table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            EnsureConnection();
            SQLiteDataReader reader = ExecuteReader("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ", new SQLiteParameter[] { new SQLiteParameter("tableName", table) });
            if (reader == null)
            {
                return false;
            }
            reader.Read();
            int c = reader.GetInt32(0);
            reader.Close();
            reader.Dispose();
            //return false;  
            return c == 1;
        }
 
        /// <summary>
        /// VACUUM 命令(通過(guò)復(fù)制主數(shù)據(jù)庫(kù)中的內(nèi)容到一個(gè)臨時(shí)數(shù)據(jù)庫(kù)文件,然后清空主數(shù)據(jù)庫(kù),并從副本中重新載入原始的數(shù)據(jù)庫(kù)文件)
        /// </summary>
        /// <returns></returns>
        public bool Vacuum()
        {
            try
            {
                using (SQLiteCommand Command = new SQLiteCommand("VACUUM", Connection))
                {
                    Command.ExecuteNonQuery();
                }
                return true;
            }
            catch (System.Data.SQLite.SQLiteException)
            {
                return false;
            }
        } 
 
        #endregion
 
        #region 執(zhí)行SQL
 
        /// <summary>
        /// 執(zhí)行SQL, 并返回 SQLiteDataReader 對(duì)象結(jié)果 
        /// </summary>  
        /// <param name="sql"></param>
        /// <param name="paramArr">null 表示無(wú)參數(shù)</param>
        /// <returns></returns>  
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            EnsureConnection();
            using (RWL[LockName].Read())
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
                {
                    if (paramArr != null)
                    {
                        cmd.Parameters.AddRange(paramArr);
                    }
                    try
                    {
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        cmd.Parameters.Clear();
                        return reader;
                    }
                    catch (Exception)
                    {
                        return null;
                    }
                }
            }
        }
 
        /// <summary>
        /// 執(zhí)行查詢(xún),并返回dataset對(duì)象
        /// </summary>
        /// <param name="sql">SQL查詢(xún)語(yǔ)句</param>
        /// <param name="paramArr">參數(shù)數(shù)組</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();
            using (RWL[LockName].Read())
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection))
                {
                    if (paramArr != null)
                    {
                        cmd.Parameters.AddRange(paramArr);
                    }
                    try
                    {
                        SQLiteDataAdapter da = new SQLiteDataAdapter();
                        DataSet ds = new DataSet();
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        da.Dispose();
                        return ds;
                    }
                    catch (Exception)
                    {
                        return null;
                    }
                }
            }
        }
 
        /// <summary>
        /// 執(zhí)行SQL查詢(xún),并返回dataset對(duì)象。
        /// </summary>
        /// <param name="strTable">映射源表的名稱(chēng)</param>
        /// <param name="sql">SQL語(yǔ)句</param>
        /// <param name="paramArr">SQL參數(shù)數(shù)組</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string strTable, string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();
            using (RWL[LockName].Read())
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection))
                {
                    if (paramArr != null)
                    {
                        cmd.Parameters.AddRange(paramArr);
                    }
                    try
                    {
                        SQLiteDataAdapter da = new SQLiteDataAdapter();
                        DataSet ds = new DataSet();
                        da.SelectCommand = cmd;
                        da.Fill(ds, strTable);
                        cmd.Parameters.Clear();
                        da.Dispose();
                        return ds;
                    }
                    catch (Exception)
                    {
                        return null;
                    }
                }
            }
        }
 
        /// <summary>  
        /// 執(zhí)行SQL,返回受影響的行數(shù),可用于執(zhí)行表創(chuàng)建語(yǔ)句,paramArr == null 表示無(wú)參數(shù)
        /// </summary>  
        /// <param name="sql"></param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();
            using (RWL[LockName].Read())
            {
                try
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
                    {
                        if (paramArr != null)
                        {
                            foreach (SQLiteParameter p in paramArr)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        int c = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return c;
                    }
                }
                catch (SQLiteException)
                {
                    return 0;
                }
            }
        }
 
        /// <summary>  
        /// 執(zhí)行SQL,返回結(jié)果集第一行,如果結(jié)果集為空,那么返回空 List(List.Count=0), 
        /// rowWrapper = null 時(shí),使用 WrapRowToDictionary  
        /// </summary>  
        /// <param name="sql"></param>  
        /// <param name="paramArr"></param>  
        /// <returns></returns>  
        public object ExecuteScalar(string sql, SQLiteParameter[] paramArr)
        {
            if (sql == null)
            {
                throw new ArgumentNullException("sql=null");
            }
            this.EnsureConnection();
            using (RWL[LockName].Read())
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
                {
                    if (paramArr != null)
                    {
                        cmd.Parameters.AddRange(paramArr);
                    }
                    try
                    {
                        object reader = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        cmd.Dispose();
                        return reader;
                    }
                    catch (Exception)
                    {
                        return null;
                    }
                }
            }
        }
 
        /// <summary>  
        /// 查詢(xún)一行記錄,無(wú)結(jié)果時(shí)返回 null,conditionCol = null 時(shí)將忽略條件,直接執(zhí)行 select * from table   
        /// </summary>  
        /// <param name="table">表名</param>  
        /// <param name="conditionCol"></param>  
        /// <param name="conditionVal"></param>  
        /// <returns></returns>  
        public object QueryOne(string table, string conditionCol, object conditionVal)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = "select * from " + table;
            if (conditionCol != null)
            {
                sql += " where " + conditionCol + "=@" + conditionCol;
            }
            object result = ExecuteScalar(sql, new SQLiteParameter[] { new SQLiteParameter(conditionCol, conditionVal) });
            return result;
        }
 
        #endregion
 
        #region 增 刪 改
 
        /// <summary>  
        /// 執(zhí)行 insert into 語(yǔ)句 
        /// </summary>  
        /// <param name="table"></param>  
        /// <param name="entity"></param>  
        /// <returns></returns>  
        public int InsertData(string table, Dictionary<string, object> entity)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = BuildInsert(table, entity);
            return this.ExecuteNonQuery(sql, BuildParamArray(entity));
        }
 
        /// <summary>  
        /// 執(zhí)行 update 語(yǔ)句,注意:如果 where = null,那么 whereParams 也為 null,
        /// </summary>  
        /// <param name="table">表名</param>  
        /// <param name="entity">要修改的列名和列名的值</param>  
        /// <param name="where">查找符合條件的列</param>  
        /// <param name="whereParams">where條件中參數(shù)的值</param>  
        /// <returns></returns>  
        public int Update(string table, Dictionary<string, object> entity, string where, SQLiteParameter[] whereParams)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = BuildUpdate(table, entity);
            SQLiteParameter[] parameter = BuildParamArray(entity);
            if (where != null)
            {
                sql += " where " + where;
                if (whereParams != null)
                {
                    SQLiteParameter[] newArr = new SQLiteParameter[(parameter.Length + whereParams.Length)];
                    Array.Copy(parameter, newArr, parameter.Length);
                    Array.Copy(whereParams, 0, newArr, parameter.Length, whereParams.Length);
                    parameter = newArr;
                }
            }
            return this.ExecuteNonQuery(sql, parameter);
        }
 
        /// <summary>  
        /// 執(zhí)行 delete from table 語(yǔ)句,where不必包含'where'關(guān)鍵字,where = null 時(shí)將忽略 whereParams  
        /// </summary>  
        /// <param name="table"></param>  
        /// <param name="where"></param>  
        /// <param name="whereParams"></param>  
        /// <returns></returns>  
        public int Delete(string table, string where, SQLiteParameter[] whereParams)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table=null");
            }
            this.EnsureConnection();
            string sql = "delete from " + table + " ";
            if (where != null)
            {
                sql += "where " + where;
            }
            return ExecuteNonQuery(sql, whereParams);
        }
 
        /// <summary>
        /// 將 Dictionary 類(lèi)型數(shù)據(jù) 轉(zhuǎn)換為 SQLiteParameter[] 類(lèi)型
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SQLiteParameter[] BuildParamArray(Dictionary<string, object> entity)
        {
            List<SQLiteParameter> list = new List<SQLiteParameter>();
            foreach (string key in entity.Keys)
            {
                list.Add(new SQLiteParameter(key, entity[key]));
            }
            if (list.Count == 0)
            {
                return null;
            }
            return list.ToArray();
        }
 
        /// <summary>
        /// 將 Dictionary 類(lèi)型數(shù)據(jù) 轉(zhuǎn)換為 插入數(shù)據(jù) 的 SQL語(yǔ)句
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="entity">字典</param>
        /// <returns></returns>
        private string BuildInsert(string table, Dictionary<string, object> entity)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("insert into ").Append(table);
            buf.Append(" (");
            foreach (string key in entity.Keys)
            {
                buf.Append(key).Append(",");
            }
            buf.Remove(buf.Length - 1, 1); // 移除最后一個(gè),
            buf.Append(") ");
            buf.Append("values(");
            foreach (string key in entity.Keys)
            {
                buf.Append("@").Append(key).Append(","); // 創(chuàng)建一個(gè)參數(shù)
            }
            buf.Remove(buf.Length - 1, 1);
            buf.Append(") ");
 
            return buf.ToString();
        }
 
        /// <summary>
        /// 將 Dictionary 類(lèi)型數(shù)據(jù) 轉(zhuǎn)換為 修改數(shù)據(jù) 的 SQL語(yǔ)句
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="entity">字典</param>
        /// <returns></returns>
        private string BuildUpdate(string table, Dictionary<string, object> entity)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("update ").Append(table).Append(" set ");
            foreach (string key in entity.Keys)
            {
                buf.Append(key).Append("=").Append("@").Append(key).Append(",");
            }
            buf.Remove(buf.Length - 1, 1);
            buf.Append(" ");
            return buf.ToString();
        }
 
        #endregion
    }
}

UsingLock.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
 
namespace MySQLiteHelper
{
    /// <summary>
    /// 使用using代替lock操作的對(duì)象,可指定寫(xiě)入和讀取鎖定模式
    /// </summary>
    public sealed class ClsLock
    {
        #region 內(nèi)部類(lèi)
 
        /// <summary>
        /// 利用IDisposable的using語(yǔ)法糖方便的釋放鎖定操作內(nèi)部類(lèi)
        /// </summary>
        private struct Lock : IDisposable
        {
            /// <summary>
            /// 讀寫(xiě)鎖對(duì)象
            /// </summary>
            private readonly ReaderWriterLockSlim _Lock;
            /// <summary>
            /// 是否為寫(xiě)入模式
            /// </summary>
            private bool _IsWrite;
            /// <summary>
            /// 利用IDisposable的using語(yǔ)法糖方便的釋放鎖定操作構(gòu)造函數(shù)
            /// </summary>
            /// <param name="rwl">讀寫(xiě)鎖</param>
            /// <param name="isWrite">寫(xiě)入模式為true,讀取模式為false</param>
            public Lock(ReaderWriterLockSlim rwl, bool isWrite)
            {
                _Lock = rwl;
                _IsWrite = isWrite;
            }
            /// <summary>
            /// 釋放對(duì)象時(shí)退出指定鎖定模式
            /// </summary>
            public void Dispose()
            {
                if (_IsWrite)
                {
                    if (_Lock.IsWriteLockHeld)
                    {
                        _Lock.ExitWriteLock();
                    }
                }
                else
                {
                    if (_Lock.IsReadLockHeld)
                    {
                        _Lock.ExitReadLock();
                    }
                }
            }
        }
 
        /// <summary>
        /// 空的可釋放對(duì)象,免去了調(diào)用時(shí)需要判斷是否為null的問(wèn)題內(nèi)部類(lèi)
        /// </summary>
        private class Disposable : IDisposable
        {
            /// <summary>
            /// 空的可釋放對(duì)象
            /// </summary>
            public static readonly Disposable Empty = new Disposable();
            /// <summary>
            /// 空的釋放方法
            /// </summary>
            public void Dispose() { }
        }
 
        #endregion
 
        /// <summary>
        /// 讀寫(xiě)鎖
        /// </summary>
        private readonly ReaderWriterLockSlim _LockSlim = new ReaderWriterLockSlim();
        /// <summary>
        /// 使用using代替lock操作的對(duì)象,可指定寫(xiě)入和讀取鎖定模式構(gòu)造函數(shù)
        /// </summary>
        public ClsLock()
        {
            Enabled = true;
        }
        /// <summary>
        /// 是否啟用,當(dāng)該值為false時(shí),Read()和Write()方法將返回 Disposable.Empty
        /// </summary>
        public bool Enabled { get; set; }
 
        /// <summary> 
        /// 進(jìn)入讀取鎖定模式,該模式下允許多個(gè)讀操作同時(shí)進(jìn)行,
        /// 退出讀鎖請(qǐng)將返回對(duì)象釋放,建議使用using語(yǔ)塊,
        /// Enabled為false時(shí),返回Disposable.Empty,
        /// 在讀取或?qū)懭腈i定模式下重復(fù)執(zhí)行,返回Disposable.Empty;
        /// </summary>
        public IDisposable Read()
        {
            if (Enabled == false || _LockSlim.IsReadLockHeld || _LockSlim.IsWriteLockHeld)
            {
                return Disposable.Empty;
            }
            else
            {
                _LockSlim.EnterReadLock();
                return new Lock(_LockSlim, false);
            }
        }
 
        /// <summary> 
        /// 進(jìn)入寫(xiě)入鎖定模式,該模式下只允許同時(shí)執(zhí)行一個(gè)讀操作,
        /// 退出讀鎖請(qǐng)將返回對(duì)象釋放,建議使用using語(yǔ)塊,
        /// Enabled為false時(shí),返回Disposable.Empty,
        /// 在寫(xiě)入鎖定模式下重復(fù)執(zhí)行,返回Disposable.Empty
        /// </summary>
        /// <exception cref="NotImplementedException">讀取模式下不能進(jìn)入寫(xiě)入鎖定狀態(tài)</exception>
        public IDisposable Write()
        {
            if (Enabled == false || _LockSlim.IsWriteLockHeld)
            {
                return Disposable.Empty;
            }
            else if (_LockSlim.IsReadLockHeld)
            {
                throw new NotImplementedException("讀取模式下不能進(jìn)入寫(xiě)入鎖定狀態(tài)");
            }
            else
            {
                _LockSlim.EnterWriteLock();
                return new Lock(_LockSlim, true);
            }
        }
    }
 
}

Form1.cs

using MySQLiteHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
 
namespace SQLiteDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private SQLiteHelper SQLiteHelpers = null;
        private const string DBAddress = "D:\\SQLiteData\\test_record.db3";
 
        private void Form1_Load(object sender, EventArgs e)
        {
            SQLiteHelpers = new SQLiteHelper(DBAddress,"123456");
        }
 
        /// <summary>
        /// 打開(kāi)數(shù)據(jù)庫(kù)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_OpenDB_Click(object sender, EventArgs e)
        {
            SQLiteHelpers.Open();
            Label_DBOpenState.Text = "打開(kāi)";
        }
 
        /// <summary>
        /// 關(guān)閉數(shù)據(jù)庫(kù)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_CloseDB_Click(object sender, EventArgs e)
        {
            SQLiteHelpers.Close();
            Label_DBOpenState.Text = "關(guān)閉";
        }
 
        /// <summary>
        /// 查詢(xún)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Query_Click(object sender, EventArgs e)
        {
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("address", "濟(jì)南")
            };
            string sql = "SELECT * FROM student WHERE address = @address";
            DataSet dataSet = SQLiteHelpers.ExecuteDataSet(sql, parameter);
            if (dataSet != null)
            {
                dataGridView1.DataSource = dataSet.Tables[0];
            }
        }
 
        /// <summary>
        /// 插入數(shù)據(jù)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Add_Click(object sender, EventArgs e)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("ID", 6);
            dic.Add("name", "王二麻子");
            dic.Add("age", 44);
            dic.Add("address", "陜西");
 
            int result = SQLiteHelpers.InsertData("student", dic);
            Console.WriteLine("插入結(jié)果,受影響的行數(shù):" + result);
        }
 
        /// <summary>
        /// 修改數(shù)據(jù)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Modify_Click(object sender, EventArgs e)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            //將列名 name 的值改為 “猴子”
            dic.Add("name", "猴子");
            //將列名 address 的值改為 花果山
            dic.Add("address", "花果山");
            //where條件
            string where = "ID = @ID AND age = @Age";
            //where條件中對(duì)應(yīng)的參數(shù)
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("ID", 4),
                new SQLiteParameter("Age",23)
            };
            
            int result = SQLiteHelpers.Update("student", dic, where, parameter);
            Console.WriteLine("修改結(jié)果,受影響的行數(shù):" + result);
        }
 
        /// <summary>
        /// 刪除數(shù)據(jù)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Delete_Click(object sender, EventArgs e)
        {
            //where條件
            string where = "ID = @ID";
            //where條件中對(duì)應(yīng)的參數(shù)
            SQLiteParameter[] parameter = new SQLiteParameter[]
            {
                new SQLiteParameter("ID", 6),
            };
 
            int result = SQLiteHelpers.Delete("student", where, parameter);
            Console.WriteLine("刪除結(jié)果,受影響的行數(shù):" + result);
        }
 
        /// <summary>
        /// 判斷表是否存在
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_TableExists_Click(object sender, EventArgs e)
        {
            string title = "dddd";
            bool result = SQLiteHelpers.TableExists(title);
            Console.WriteLine(string.Format("{0} 表是否存在,結(jié)果:{1}", title, result));
        }
 
        //輸出各表中的數(shù)據(jù)
        //public static void PrintValues(DataSet ds)
        //{
        //    foreach (DataTable table in ds.Tables)
        //    {
        //        Console.WriteLine("表名稱(chēng):" + table.TableName);
        //        foreach (DataRow row in table.Rows)
        //        {
        //            foreach (DataColumn column in table.Columns)
        //            {
        //                Console.Write(row[column] + "");
        //            }
        //            Console.WriteLine();
        //        }
        //    }
        //}
 
    }
}

以上就是C#操作SQLite實(shí)現(xiàn)數(shù)據(jù)的增刪改查的詳細(xì)內(nèi)容,更多關(guān)于C# SQLite增刪改查的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

  • C#多線程學(xué)習(xí)之(三)生產(chǎn)者和消費(fèi)者用法分析

    C#多線程學(xué)習(xí)之(三)生產(chǎn)者和消費(fèi)者用法分析

    這篇文章主要介紹了C#多線程學(xué)習(xí)之生產(chǎn)者和消費(fèi)者用法,實(shí)例分析了C#中線程沖突的原理與資源分配的技巧,非常具有實(shí)用價(jià)值,需要的朋友可以參考下
    2015-04-04
  • C#根據(jù)身份證號(hào)碼判斷出生日期和性別

    C#根據(jù)身份證號(hào)碼判斷出生日期和性別

    這篇文章主要為大家詳細(xì)介紹了C#根據(jù)身份證號(hào)碼判斷出生日期和性別的方法,感興趣的小伙伴們可以參考一下
    2016-08-08
  • C#實(shí)現(xiàn)由四周向中心縮小的窗體退出特效

    C#實(shí)現(xiàn)由四周向中心縮小的窗體退出特效

    這篇文章主要介紹了C#實(shí)現(xiàn)由四周向中心縮小的窗體退出特效,通過(guò)簡(jiǎn)單的C#窗口調(diào)用參數(shù)設(shè)置實(shí)現(xiàn)該退出特效功能,非常簡(jiǎn)單實(shí)用,需要的朋友可以參考下
    2015-08-08
  • C#使用TCP協(xié)議實(shí)現(xiàn)數(shù)據(jù)發(fā)送和接受的方法

    C#使用TCP協(xié)議實(shí)現(xiàn)數(shù)據(jù)發(fā)送和接受的方法

    這篇文章主要介紹了c#使用TCP協(xié)議實(shí)現(xiàn)數(shù)據(jù)發(fā)送和接受,使用TCP協(xié)議實(shí)現(xiàn)數(shù)據(jù)的發(fā)送和接受包括客戶(hù)端和服務(wù)端兩個(gè)部分,本文通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2024-04-04
  • C#實(shí)現(xiàn)密碼驗(yàn)證與輸錯(cuò)密碼賬戶(hù)鎖定

    C#實(shí)現(xiàn)密碼驗(yàn)證與輸錯(cuò)密碼賬戶(hù)鎖定

    這篇文章介紹了C#實(shí)現(xiàn)密碼驗(yàn)證與輸錯(cuò)密碼賬戶(hù)鎖定的方法,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2022-04-04
  • C#如何將DataTable導(dǎo)出到Excel解決方案

    C#如何將DataTable導(dǎo)出到Excel解決方案

    由于公司項(xiàng)目中需要將系統(tǒng)內(nèi)用戶(hù)操作的所有日志進(jìn)行轉(zhuǎn)存?zhèn)浞?,考慮到以后可能還需要還原,所以最后決定將日志數(shù)據(jù)備份到Excel中
    2012-11-11
  • 詳解C#異步多線程使用中的常見(jiàn)問(wèn)題

    詳解C#異步多線程使用中的常見(jiàn)問(wèn)題

    本文主要介紹了C#異步多線程使用中的常見(jiàn)問(wèn)題,文中通過(guò)示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2022-01-01
  • C# DataTable的詳細(xì)用法分享

    C# DataTable的詳細(xì)用法分享

    在項(xiàng)目中經(jīng)常用到DataTable,如果DataTable使用得當(dāng),不僅能使程序簡(jiǎn)潔實(shí)用,而且能夠提高性能,達(dá)到事半功倍的效果,現(xiàn)對(duì)DataTable的使用技巧進(jìn)行一下總結(jié)
    2013-11-11
  • WPF自定義實(shí)現(xiàn)IP地址輸入控件

    WPF自定義實(shí)現(xiàn)IP地址輸入控件

    這篇文章主要給大家介紹了關(guān)于WPF自定義實(shí)現(xiàn)IP地址輸入控件的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用WPF具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2019-06-06
  • C#實(shí)現(xiàn)格式化文本并導(dǎo)入到Excel

    C#實(shí)現(xiàn)格式化文本并導(dǎo)入到Excel

    這篇文章主要為大家詳細(xì)介紹了如何使用C#實(shí)現(xiàn)格式化文本并導(dǎo)入到Excel,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下
    2024-05-05

最新評(píng)論