C#Sqlite数据库操作介绍以及类库介绍

Python Excellent

已于 2023-03-20 18:39:30 修改

阅读量912

点赞数 2

分类专栏： C#交流文章标签：数据库 sqlite c#

于 2023-03-10 10:26:11 首次发布

本文链接：https://blog.csdn.net/zylgxt/article/details/129436328

版权

C#交流专栏收录该内容

5 篇文章 0 订阅

订阅专栏

有段时间未更新过博客了，觉得有用的对象，有时间的话会继续更新

Sqlite数据库操作介绍以及类库介绍

1. VS2022中，用Nug包管理器，引入System.Data.sqlite包
2. 新建一个Sqlite帮助类SqliteHelper.cs
3. 列举部分实际操作：

1. VS2022中，用Nug包管理器，引入System.Data.sqlite包

转载请标明出处：Python Excellent的博客
在这里插入图片描述

2. 新建一个Sqlite帮助类SqliteHelper.cs

具体方法如下：基本可以应对增删改查，以及事务处理数据库，保证当数据库操作数据失败，具有回滚操作，而不影响数据库的查询。

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data.Common;
using System.Collections;

namespace  ZYL9527
{
    class SqliteHelper : IDisposable
    {
        public SQLiteConnection conn;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
        }

        ~SqliteHelper()
        {
            Dispose(false);
        }

        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="dataBaseName">数据库名</param>
        public SqliteHelper(string dataBaseName)
        {
            string connString = string.Format(@"Data Source={0}", dataBaseName);
            conn = new SQLiteConnection(connString);
            //conn.Open();
        }

        /// <summary>
        /// 手动打开数据库。
        /// </summary>
        public bool SqliteOpen()
        {
            if (conn == null || conn.State != ConnectionState.Closed)
                return false;
            try
            {

                conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 通过执行SQL语句，获取表中数据。
        /// </summary>
        /// <param name="sError">错误信息</param>
        /// <param name="sSQL">执行的SQL语句</param>
        public DataTable GetDataTable(out string sError, string sSQL)
        {
            DataTable dt = null;
            sError = string.Empty;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand() { CommandText = sSQL, Connection = conn };
                SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
                dt = new DataTable();

                dao.Fill(dt);
                //SQLiteDataReader dr = cmd.ExecuteReader();
                //if (dr.HasRows)
                //  dt.Load(dr);
            }
            catch (Exception e)
            {
                sError = e.Message;
            }
            return dt;
        }

        /// <summary>
        /// 通过执行SQL语句，获取表中数据个数。
        /// </summary>
        /// <param name="sError">错误信息</param>
        /// <param name="sSQL">执行的SQL语句</param>
        public int GetDataCount(out string sError, string sSQL)
        {
            DataTable dt = new DataTable();
            sError = string.Empty;
            SQLiteCommand cmd = new SQLiteCommand() { CommandText = sSQL, Connection = conn };
            try
            {
                SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
                dao.Fill(dt);
                cmd.Dispose();
            }
            catch (Exception e)
            {
                sError = e.Message;
            }
            finally { cmd.Dispose(); }
            return dt.Rows.Count;
            //return int.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 通过执行SQL语句，执行insert,update,delete 动作，也可以使用事务。
        /// </summary>
        /// <param name="sError">错误信息</param>
        /// <param name="sSQL">执行的SQL语句</param>
        /// <param name="bUseTransaction">是否使用事务</param>
        public bool UpdateData(out string sError, string sSQL, bool bUseTransaction)
        {
            bool iResult = false;
            sError = string.Empty;
            if (!bUseTransaction)
            {
                try
                {
                    SQLiteCommand comm = new SQLiteCommand(conn) { CommandText = sSQL };
                    iResult = comm.ExecuteNonQuery() > 0;
                    comm.Dispose();
                }
                catch (Exception ex)
                {
                    sError = ex.Message;
                }
            }
            else// 使用事务
            {
                DbTransaction trans = null;
                trans = conn.BeginTransaction();
                SQLiteCommand comm = new SQLiteCommand(conn) { CommandText = sSQL };
                try
                {
                    iResult = comm.ExecuteNonQuery() > 0;
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    sError = ex.Message;
                    iResult = false;
                    trans.Rollback();
                }
                finally { comm.Dispose(); trans.Dispose(); }
            }
            return iResult;
        }

        /// <summary>
        /// 使用事务执行多条相同的带参数的SQL语句。
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqLiteParameters">每次SQL执行的参数</param>
        public bool ExecuteSqlTran(string sqlString, object[][] sqLiteParameters)
        {
            if (sqLiteParameters.Length == 0)
                return false;
            using (DbTransaction trans = conn.BeginTransaction())
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                try
                {
                    for (int i = 0; i < sqLiteParameters[0].Length; i++)
                    {
                        cmd.Parameters.Add(cmd.CreateParameter());
                    }
                    //循环
                    foreach (object[] sqlParameters in sqLiteParameters)
                    {
                        ExecuteSqlNonQuery(cmd, sqlString, sqlParameters);
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    cmd.Dispose(); trans.Dispose();
                }
            }
            return true;
        }


        public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SQLiteParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 使用事务执行多条相同的带参数的SQL语句。
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqLiteParameters">每次SQL执行的参数</param>
        public bool ExecuteSqlTran(ArrayList SQLStringList)
        {
            if (SQLStringList.Count == 0)
                return false;
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                try
                {

                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }                  
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    cmd.Dispose(); trans.Dispose();
                }
            }
            return true;
        }


        /// <summary>
        /// 不使用事务执行一条带参数的SQL语句。
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqLiteParameters">SQL执行的参数</param>
        public void ExecuteSql(string sqlString, object[] sqLiteParameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SQLiteCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = sqlString;
            try
            {
                for (int i = 0; i < sqLiteParameters.Length; i++)
                {
                    cmd.Parameters.Add(cmd.CreateParameter());
                    cmd.Parameters[i].Value = sqLiteParameters[i];
                }
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
            }
        }

        private void ExecuteSqlNonQuery(SQLiteCommand cmd, string cmdText, object[] cmdParms)
        {
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                for (int i = 0; i < cmdParms.Length; i++)
                {
                    cmd.Parameters[i].Value = cmdParms[i];
                }
            }
            cmd.ExecuteNonQuery();
        }
    }
}

3. 列举部分实际操作：

在列举之前需要进行初始化连接数据库：

Main函数中获取数据库文件

 bool IsOk = DataCenterInit(Environment.CurrentDirectory + "\\db\\data.db", ref ErrMsg);

首先进行数据库文件初始化

/// <summary>
/// 数据库文件初始化
/// </summary>
/// <param name="_dbFileName"></param>
/// <returns></returns>
public static bool DataCenterInit(string _dbFileName, ref string ErrMsg) 
{
	ErrMsg = "";
	if (File.Exists(_dbFileName))
	{
	   dbFileName = _dbFileName;
	   SqlHelper = new SqliteHelper(dbFileName);
	   bool ret = SqlHelper.SqliteOpen();
	   ErrMsg = ret ? "" : "打开失败";
	   return ret;
	}
	else
	{
	   ErrMsg = "数据库文件不存在!";
	   return false;
	}
}

实际应用如下：

1.新增操作：

// 添加用户
public static bool AddUser(R_User user)
{
    string msg = "";
    string sql = string.Format("INSERT INTO Users VALUES(null,{0},'{1}','{2}' )",
        UserID,
        UserName,
        Password,
        );
    bool ret = SqlHelper.UpdateData(out msg, sql, false);
    return ret;
}

2.删除操作：

public static bool DeleteUser(R_User user)
{
    string msg = "";
    string sql = string.Format("DELETE FROM Users WHERE UserID = {0}",UserID);
    bool ret = SqlHelper.UpdateData(out msg, sql, false);
    return ret;
}

3.更新操作：

public static bool ModifyUserRole(R_UserRole role,out string ErrMsg)
{
    string msg = "";
    sql = string.Format("UPDATE Users SET UserName = '{1}' WHERE UserID= {0}", UserID, UserName );
    bool ret = SqlHelper.UpdateData(out msg, sql, false);
    return ret;
}

4.查询操作：

//获取用户表
string msg = "";
string sql = "SELECT * FROM Users ";
var dt = SqlHelper.GetDataTable(out msg, sql);
//查询用户表中有无该用户
string sql = string.Format("SELECT * FROM Users WHERE UserName = '{0}' AND UserID != {1}", UserName, UserID);
int Count = SqlHelper.GetDataCount(out msg, sql);
if(Count>0) { ErrMsg = "用户表中已存在该用户名！";return false; }

5.用事务执行删除操作：

//同步删除用户和角色
 string ErrMsg = "";
 ArrayList sqlList = new ArrayList();
 string sql1 = string.Format("DELETE FROM Users WHERE UserID = {0}", UserID );
  sqlList.Add(sql1);
 string sql2 = string.Format("DELETE FROM UserRoles WHERE UserRoleID = {0}", UserRoleID );
 sqlList.Add(sql2);
 bool IsOk = SqlHelper.ExecuteSqlTran(sqlList);
 return IsOk;