.Net SQLite 简单使用

安装Nuget包

 /// <summary>
    /// 创建数据表字段类型
    /// </summary>
    public enum AttrsType
    {
        Self = BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance,
        Default
    }

using ChromeDefaultUI.Enums;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ChromeDefaultUI.sqlite
{
    public class SQLHelper
    {

        private static SQLHelper helper = new SQLHelper();
        public static SQLHelper Instance
        {
            get
            {

                return helper;
            }
        }

        private SQLHelper()
        {
        }


        /// <summary>
        /// 根据属性创建字典
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Dictionary<string, string> CreateObjectData(object obj, AttrsType attrsType)
        {
            System.Reflection.PropertyInfo[] propertyInfos = obj.GetType().GetProperties((BindingFlags)attrsType);
            Dictionary<string, string> data = new Dictionary<string, string>();
            foreach (var item in propertyInfos)
            {
                string name = item.Name;
                string val = "";

                if (item.GetValue(obj) != null)
                {
                    val = item.GetValue(obj).ToString();
                }
                data.Add(name, val);
            }
            return data;
        }

        /// <summary>
        /// 增加数据，如果没有表，则自动创建表
        /// </summary>
        /// <param name="dic">数据</param>
        /// <param name="tableName"></param>
        public int Insert(Dictionary<string, string> dic, Tables tableName)
        {
            int ins = 0;
            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                CreateTable(dic, conn, tableName.ToString());

                string key = "";
                string val = "";
                foreach (var item in dic.Keys)
                {
                    key += (item + ",");
                    val += ("'" + dic[item] + "',");
                }
                key = key.Substring(0, key.Length - 1);
                val = val.Substring(0, val.Length - 1);

                string sql = "insert into " + tableName + " (" + key + ") values (" + val + ")";

                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {

                    ins = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
            return ins;
        }


        /// <summary>
        /// 根据条件删除数据
        /// </summary>
        /// <param name="par">查找条件 null 或 size=0 则删除所有数据</param>
        /// <param name="tableName"></param>
        public int Delete(Dictionary<string, string> par, Tables tableName)
        {
            int del = 0;
            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                string sqlPar = "";
                if (par != null && par.Count > 0)
                {

                    foreach (string item in par.Keys)
                    {
                        sqlPar += item + " = '" + par[item] + "' AND ";
                    }

                    sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
                }

                string sql = "Delete from " + tableName + (sqlPar.Length > 0 ? (" where " + sqlPar) : "");

                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    del = cmd.ExecuteNonQuery();//exq !=0 则删除成功，exq==0则删除失败或没有此数据
                }
                conn.Close();
            }
            return del;
        }


        /// <summary>
        /// 通过事务删除多条
        /// </summary>
        /// <param name="par"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int DeleteTransaction(List<Dictionary<string, string>> parList, Tables tableName)
        {

            int del = 0;
            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                SQLiteTransaction tr = conn.BeginTransaction();//事务开始
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;

                    for (int i = 0; i < parList.Count; i++)
                    {
                        Dictionary<string, string> par = parList[i];
                        string sqlPar = "";
                        if (par != null && par.Count > 0)
                        {

                            foreach (string item in par.Keys)
                            {
                                sqlPar += item + " = '" + par[item] + "' AND ";
                            }

                            sqlPar = sqlPar.Substring(0, sqlPar.Length - " AND ".Length);
                        }

                        string sql = "Delete from " + tableName + (sqlPar.Length > 0 ? (" where " + sqlPar) : "");
                        cmd.CommandText = sql;
                        int d = cmd.ExecuteNonQuery();//exq !=0 则删除成功，exq==0则删除失败或没有此数据
                        if (d > 0)
                        {
                            del++;
                        }
                    }
                    tr.Commit();
                }

                conn.Close();
            }
            return del;
        }


        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="par">查找条件，null 则查询所有</param>
        /// <param name="tableName"></param>
        public int FindRows(Dictionary<string, string> par, Tables tableName)
        {
            int row = 0;
            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                string finTab = "select * from sqlite_master where type = 'table' and name ='" + tableName + "'";
                int tabCount = 0;
                using (SQLiteCommand cmd = new SQLiteCommand(finTab, conn))
                {
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                tabCount++;
                            }
                        }
                    }
                }

                if (tabCount > 0)
                {
                    string sqlPar = "";
                    if (par != null && par.Count > 0)
                    {

                        foreach (string item in par.Keys)
                        {
                            sqlPar += item + " = '" + par[item] + "' AND ";
                        }

                        sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
                    }

                    string sql = "SELECT * FROM " + tableName + (sqlPar.Length > 0 ? (" WHERE " + sqlPar) : "");

                    using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                    {
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    row++;
                                }
                            }
                        }
                    }
                }
                conn.Close();
            }
            return row;
        }



        /// <summary>
        /// 根据条件更新数据
        /// </summary>
        /// <param name="update">更新值</param>
        /// <param name="condition">条件</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public int Update(Dictionary<string, string> update, Dictionary<string, string> condition, Tables tableName)
        {

            if (update == null || update.Count == 0 || condition == null || condition.Count == 0)
            {
                throw new Exception("条件不足");
            }

            int rowCount = 0;
            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                string finTab = "select * from sqlite_master where type = 'table' and name ='" + tableName + "'";
                int tabCount = 0;
                using (SQLiteCommand cmd = new SQLiteCommand(finTab, conn))
                {
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                tabCount++;
                            }
                        }
                    }
                }

                if (tabCount > 0)
                {

                    string value = UpdateValue(update);
                    string cond = Condition(condition);

                    string sql = "Update " + tableName + " Set " + value + " Where " + cond;

                    using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                    {
                        rowCount = cmd.ExecuteNonQuery();
                    }
                }
                conn.Close();
            }
            return rowCount;
        }



        /// <summary>
        /// 根据条件反射返回obj,条件为null时，查询所有
        /// </summary>
        /// <param name="par">查找条件</param>
        /// <param name="tableName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public ObservableCollection<Dictionary<string, object>> FindToObj(Dictionary<string, string> par, Tables tableName)
        {
            ObservableCollection<Dictionary<string, object>> o = new ObservableCollection<Dictionary<string, object>>();

            string connStr = @"URI=file:data.db";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();

                string sqlPar = "";
                if (par != null && par.Count > 0)
                {

                    foreach (string item in par.Keys)
                    {
                        sqlPar += item + " = '" + par[item] + "' AND ";
                    }

                    sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
                }

                string sql = "SELECT * FROM " + tableName + (sqlPar.Length > 0 ? (" WHERE " + sqlPar) : "");

                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var vals = reader.GetValues();

                            Dictionary<string, object> dic = new Dictionary<string, object>();

                            foreach (var item in vals.AllKeys)
                            {
                                dic.Add(item, vals[item]);
                            }

                            o.Add(dic);
                        }
                    }
                }
                conn.Close();
            }

            return o;
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="data"></param>
        /// <param name="cnn"></param>
        /// <param name="tableName"></param>
        private void CreateTable(Dictionary<string, string> data, SQLiteConnection cnn, string tableName)
        {

            string v = "";
            foreach (var item in data.Keys)
            {
                v += item;
                v += " text,";
            }
            v = v.Substring(0, v.Length - 1);

            string sql = "Create table IF NOT EXISTS " + tableName + " (Id integer primary key, " + v + ");";

            SQLiteCommand cmd = new SQLiteCommand(sql, cnn);
            cmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 返回条件语句
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private static string Condition(Dictionary<string, string> condition)
        {

            string sqlPar = "";
            if (condition != null && condition.Count > 0)
            {

                foreach (string item in condition.Keys)
                {
                    sqlPar += item + " = '" + condition[item] + "' AND ";
                }

                sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
            }
            return sqlPar;
        }

        /// <summary>
        /// 返回更新值语句
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private static string UpdateValue(Dictionary<string, string> condition)
        {

            string sqlPar = "";
            if (condition != null && condition.Count > 0)
            {

                foreach (string item in condition.Keys)
                {
                    sqlPar += item + " = '" + condition[item] + "' , ";
                }

                sqlPar = sqlPar.Substring(0, sqlPar.Length - (" , ".Length));
            }
            return sqlPar;
        }


    }
}