C#中SQLite结构化操作

最新推荐文章于 2024-01-23 10:45:36 发布

大浪淘沙胡

最新推荐文章于 2024-01-23 10:45:36 发布

阅读量348

点赞数

文章标签： c# sqlite 数据库

本文链接：https://blog.csdn.net/qq_27474555/article/details/129734529

版权

这篇博客介绍了如何在C#中使用SQLite进行结构化操作，包括通过NuGet安装System.Data.SQLite库，以及Constant、PubConstant、DESEncrypt、DbHelperSQLite等关键文件的作用，同时涉及tb_User数据表及对应的Model、DAL、BLL层实现。

摘要由CSDN通过智能技术生成

1、数据驱动库文件结构
Constant为配置文件，存放固定信息；
tb_User对应数据表；
PubConstant、DESEncrypt、DbHelperSQLite为通用文件。具体代码如下。
在这里插入图片描述
2、用NuGet安装System.Data.SQLite
3、Constant.cs

public class Constant
    {
   
        //set database path
        public static string dbPath = "";
    }

4、PubConstant.cs

public class PubConstant
    {
   
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public static string ConnectionString
        {
   
            get
            {
   
                string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
                if (ConStringEncrypt == "true")
                {
   
                    _connectionString = DESEncrypt.Decrypt(_connectionString);
                }
                return _connectionString;
            }
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public static string ConnectionStringSQLite
        {
   
            get
            {
   
                string _connectionString = Constant.dbPath;
                string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
                if (ConStringEncrypt == "true")
                {
   
                    _connectionString = DESEncrypt.Decrypt(_connectionString);
                }
                return _connectionString;
            }
        }

        /// <summary>
        /// 得到web.config里配置项的数据库连接字符串。
        /// </summary>
        /// <param name="configName"></param>
        /// <returns></returns>
        public static string GetConnectionString(string configName)
        {
   
            string connectionString = ConfigurationManager.AppSettings[configName];
            string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
            if (ConStringEncrypt == "true")
            {
   
                connectionString = DESEncrypt.Decrypt(connectionString);
            }
            return connectionString;
        }
    }

5、DESEncrypt.cs

public class DESEncrypt
    {
   
        public DESEncrypt()
        {
   

        }

        #region ========加密======== 

        /// <summary>
        /// 加密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Encrypt(string Text)
        {
   
            return Encrypt(Text, "litianping");
        }
        /// <summary> 
        /// 加密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string Encrypt(string Text, string sKey)
        {
   
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray;
            inputByteArray = Encoding.Default.GetBytes(Text);
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            StringBuilder ret = new StringBuilder();
            foreach (byte b in ms.ToArray())
            {
   
                ret.AppendFormat("{0:X2}", b);
            }
            return ret.ToString();
        }

        #endregion

        #region ========解密======== 


        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="Text"></param>
        /// <returns></returns>
        public static string Decrypt(string Text)
        {
   
            return Decrypt(Text, "litianping");
        }
        /// <summary> 
        /// 解密数据 
        /// </summary> 
        /// <param name="Text"></param> 
        /// <param name="sKey"></param> 
        /// <returns></returns> 
        public static string Decrypt(string Text, string sKey)
        {
   
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            int len;
            len = Text.Length / 2;
            byte[] inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
   
                i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }
            des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }

        #endregion
    }

6、DbHelperSQLite.cs

public abstract class DbHelperSQLite
    {
   
        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.		
        public static string connectionString = PubConstant.ConnectionStringSQLite;
        public DbHelperSQLite()
        {
   
        }


        #region 公用方法

        public static int GetMaxID(string FieldName, string TableName)
        {
   
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
   
                return 1;
            }
            else
            {
   
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
   
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
   
                cmdresult = 0;
            }
            else
            {
   
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
   
                return false;
            }
            else
            {
   
                return true;
            }
        }
        public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
        {
   
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
   
                cmdresult = 0;
            }
            else
            {
   
                cmdresult = int