首页 > 数据库 >[WPF]C#连接使用sqllite数据库,,支持多线程操作

[WPF]C#连接使用sqllite数据库,,支持多线程操作

时间:2023-03-06 22:24:41浏览次数:64  
标签:return string C# cmd tablename ex new WPF 多线程

https://www.zhaokeli.com/article/8192.html

项目开发环境

win764

vs2015

.net4.6

下载   System.Data.SQLite.dll-for-.net4.0.zip  引用到项目中

使用sqllite扩展的时候,项目编译成 Any cpu 的话就要引用sqllite对应的32和64位 SQLite.Interop.dll  直接把压缩包解压下来

image.png

引用 System.Data.SQLite.dll  这个,然后把上面两个文件夹放到程序运行目录中,运行的时候 System.Data.SQLite.dll  会自动根据运行环境选译32或64位目录进行加载

下面提供一个操作sqllite的类方便使用

 

里面用到啦一个记录日志的Log类,可以到下面地址下载

https://www.zhaokeli.com/article/8180.html

复制代码
MySQLite lite = new MySQLite("./data/mysqllite");
//创建一个表
var r0 = lite.query("create table article (id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT NULL,name varchar(20), sex varchar(10))");
//插入一些数据
for (int i = 0; i < 10; i++)
{
Console.WriteLine(lite.insert("article", new Dictionary<string, string>() {
        {"name","张三"+(new Random().Next()) },
        {"sex","男1"+(new Random().Next()) }
    }));
}
//查询出来
var datalist = lite.select("article");
foreach (var item in datalist)
{
    Console.WriteLine(item["name"]);
    Console.WriteLine(item["sex"]);
}
var re = lite.update("article", new Dictionary<string, string>() {
        {"name","修改1"+(new Random().Next()) },
        {"sex","修改2"+(new Random().Next()) }
    }, "id=3");

 

如图

image.png

初始化的时候需要提供数据库的路径,如果不存在会自动创建一个空数据库

MySQLite.cs  

复制代码
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Threading;
using System.Windows;

namespace Ank.Class
{
    class MySQLite
    {
        private SQLiteConnection m_conn;
        private string connstr = "";
        private string _sql = "";
        private Log m_log = null;
        private SQLiteCommand m_cmd = null;
        private SQLiteDataReader m_reader = null;
        private Mutex m_mutex = null;//多线程时互斥锁
        //数据库连接
        public MySQLite(string databasepath = null)
        {
            try
            {
                if (databasepath == null)
                {
                    MessageBox.Show("亲,数据库路径弄哪啦?");
                    Environment.Exit(0);
                    return;
                }
                var pat = Path.GetDirectoryName(databasepath);
                if (Directory.Exists(pat) == false)
                {
                    Directory.CreateDirectory(pat);
                }
                if (File.Exists(databasepath) == false)
                {
                    SQLiteConnection.CreateFile(databasepath);
                }
                m_conn = new SQLiteConnection("Data Source=" + databasepath + ";Version=3;");
                m_conn.Open();

                m_mutex = new Mutex();
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
        }

        /**
         * 取数据表主键
         **/
        public string getPrimary(string tablename = "")
        {
            string key = "";
            try
            {
                m_cmd = new SQLiteCommand("PRAGMA table_info( " + tablename + ")", m_conn);
                m_reader = m_cmd.ExecuteReader();

                //如果有数据就输出
                if (m_reader.HasRows)
                {

                    //逐行读取数据输出
                    while (m_reader.Read())
                    {
                        string prid = m_reader["pk"].ToString();
                        if (prid == "1")
                        {
                            key = m_reader["name"].ToString();
                            break;
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                this.closeHandle();
            }
            return key;
        }

        public int query(string sql = "")
        {
            //安全时才可以访问共享资源,否则挂起。检测到安全并访问的同时会上锁。
            m_mutex.WaitOne();
            this.reConn();
            try
            {
                if (sql != null)
                {
                    this._sql = sql;
                    SQLiteCommand command = new SQLiteCommand(this._sql, m_conn);
                    return command.ExecuteNonQuery();
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                this.closeHandle();
                m_mutex.ReleaseMutex();
            }
            return 0;
        }




        /**
         * 插入数据成功返回自增id,没有自增id的返回1
         **/
        public int insert(string tablename, Dictionary<string, string> updatedata)
        {
            //安全时才可以访问共享资源,否则挂起。检测到安全并访问的同时会上锁。
            m_mutex.WaitOne();
            this.reConn();
            try
            {
                m_cmd = m_conn.CreateCommand();
                //取更新的所有键
                string field1 = "";
                string field2 = "";
                foreach (string key in updatedata.Keys)
                {

                    field1 += "," + key;
                    field2 += ",@" + key;
                    m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
                }
                this._sql = "INSERT INTO " + tablename + " (" + field1.Trim(',') + ") VALUES (" + field2.Trim(',') + ")";
                m_cmd.CommandText = this._sql;
                int result = m_cmd.ExecuteNonQuery();
                closeHandle();
                if (result == 1)
                {
                    string prid = this.getPrimary(tablename);
                    if (prid != "")
                    {
                        var data = this.get(tablename, prid, "", prid + " desc");
                        return int.Parse(data[prid]);
                    }
                    else
                    {
                        return 1;
                    }
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
                this.closeHandle();
                throw;
            }
            finally
            {

                m_mutex.ReleaseMutex();
            }
            return 0;
        }
        /**
        * 插入数据
        **/
        public int update(string tablename, Dictionary<string, string> updatedata, string where = "")
        {
            //安全时才可以访问共享资源,否则挂起。检测到安全并访问的同时会上锁。
            m_mutex.WaitOne();
            this.reConn();
            try
            {
                m_cmd = m_conn.CreateCommand();
                //取更新的所有键
                string field1 = "";
                foreach (string key in updatedata.Keys)
                {
                    field1 += "," + key + "=@" + key;
                    m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
                }
                this._sql = "UPDATE  " + tablename + " SET " + field1.Trim(',') + " WHERE " + where;
                m_cmd.CommandText = this._sql;
                return m_cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                this.closeHandle();
                m_mutex.ReleaseMutex();
            }
            return 0;
        }

        public List<Dictionary<string, string>> select(string tablename = "", string fields = "", string where = "", string order = "", string limit = "")
        {
            //安全时才可以访问共享资源,否则挂起。检测到安全并访问的同时会上锁。
            m_mutex.WaitOne();
            this.reConn();
            List<Dictionary<string, string>> datalist = new List<Dictionary<string, string>>();
            this._sql = "select " + (fields != "" ? fields : "*") + " from " + tablename + "  " + (where != "" ? (" where " + where) : "") + " " + (order != "" ? (" order by " + order) : "") + (limit != "" ? (" limit " + limit) : "");
            try
            {
                m_cmd = new SQLiteCommand(this._sql, m_conn);
                m_reader = m_cmd.ExecuteReader();
                //如果有数据就输出
                if (m_reader.HasRows)
                {

                    //逐行读取数据输出
                    while (m_reader.Read())
                    {
                        Dictionary<string, string> coldata = new Dictionary<string, string>();
                        //取所有field
                        for (int i = 0; i < m_reader.FieldCount; i++)
                        {
                            string fieldname = m_reader.GetName(i).Trim();
                            string value = m_reader.GetValue(i).ToString();
                            coldata.Add(fieldname, value);

                        }
                        datalist.Add(coldata);

                    }
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                this.closeHandle();
                m_mutex.ReleaseMutex();
            }

            return datalist;
        }
        /**
         * 返回一条记录
         * */
        public Dictionary<string, string> get(string tablename = "", string fields = "", string where = "", string order = "")
        {
            var dict = this.select(tablename, fields, where, order, "1");
            if (dict.Count > 0)
            {
                return dict[0];
            }
            else
            {
                return new Dictionary<string, string>();
            }

        }
        public int count(string tablename = "", string where = "")
        {
            var dict = this.get(tablename, "", where);
            return dict.Count;
        }
        public bool has(string tablename = "", string where = "")
        {
            var num = this.count(tablename, where);
            if (num > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        ~MySQLite()
        {
            try
            {
                if (m_conn.State == System.Data.ConnectionState.Open)
                {
                    m_conn.Close();
                    m_conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                this.closeHandle();
            }

        }
        /************************************************************************/
        /* 如果连接已经关闭就重新连接                                                                */
        /************************************************************************/
        private void reConn()
        {
            try
            {
                //防止因网络或其它情况下连接断开时重新连接
                if (m_conn.State == System.Data.ConnectionState.Closed)
                {
                    m_conn = new SQLiteConnection(this.connstr);
                    m_conn.Open();
                }
                this.closeHandle();

            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
        }
        private void errorMsg(Exception ex = null)
        {
            if (ex != null)
            {
                Console.WriteLine(ex.Message);
                m_log.write(ex.Message, "litedb");
                m_log.write(ex.ToString(), "litedb");
            }
        }
        private void closeHandle()
        {
            try
            {
                if (m_cmd != null)
                {
                    m_cmd.Dispose();
                }
                if (m_reader != null && m_reader.IsClosed == false)
                {
                    m_reader.Close();
                }
            }
            catch (Exception ex)
            {
                this.errorMsg(ex);
            }
            finally
            {
                m_cmd = null;
                m_reader = null;
            }

        }
    }
}

 

标签:return,string,C#,cmd,tablename,ex,new,WPF,多线程
From: https://www.cnblogs.com/chinasoft/p/17185734.html

相关文章

  • MySQL创建函数报错:1418 - This function has none of DETERMINISTIC, NO SQL, or READ
    创建函数时报错:1418-ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wantto......
  • Rocky Linux 系列3 --- KVM
    一、概要1.环境RockyLinux9.12.KVMKVM是Kernel-basedVirtualMachine的简称,这是一个开源的针对Linux内核的虚拟化解决方案。它同时支持Intel和AMD的CPUs并且允许......
  • CSS实现文字颜色渐变效果
    略微搜索查阅了网上的实现方法: 1.给元素添加背景渐变色,通过背景裁剪其中文字,再将文字设置为透明即可实现。(兼容性问题请自行添加浏览器前缀)background-color:linear-gr......
  • c++基础学习路线
    c++基础学习路线类和对象c/c++内存管理模板IO流继承多态c++11新特新异常智能指针特殊类设计c++的类型转换STL......
  • 面试复习总结-tcp三次握手四次挥手
    1.TCP/IP协议:应用层:HTTPFTPTFTPHTTPS会话层表达层传输层:TCPUDP网络层:IPICMPARP 数据链路层:PPP,PPTP物理层:帧 tcp三次握手四次挥手: 1.客户端发送连接......
  • 33. CF-Divisor Paths
    链接求从\(x\)到\(y\)的最短路径的数量。显然应该从\(x\)走到\(\gcd(x,y)\)再走到\(y\),容易证明这样走是最优的。那么现在只需要把两段的最短路径数量分别求出......
  • (C语言)实现对注册表创建修改,查询,删除操作的简单操作
    最初是发现有款软件是根据注册表的数据判断限免额度,但手动更改注册表比较麻烦,然后尝试用bat写一个一键更改的,但bat写条件判断比较麻烦,然后想到了C语言可以直接编译exe执行......
  • (C语言)实现对注册表创建修改,查询,删除操作的简单操作(省略版)
    前言本文为省略版直接贴代码,用就完事了,下文三个方法都为独立模块可以单独调用,详细步骤版在这,点击跳转。一、代码实现#include<stdio.h>#include<Windows.h>//删......
  • Mac安装配置SVN
    https://blog.csdn.net/weixin_44629395/article/details/127916512问题结局参考https://blog.csdn.net/xingmeiok/article/details/128001185brewinstallsvnsvn--v......
  • 【THM】Hacking with PowerShell(Powershell脚本基础)-学习
    本文相关的TryHackMe实验房间链接:https://tryhackme.com/room/powershell通过学习相关知识点:了解PowerShell攻击和PowerShell脚本的基础知识。PowerShell教程参考链......