SqliteHelper数据库查询

最新推荐文章于 2026-06-17 14:44:27 发布

原创最新推荐文章于 2026-06-17 14:44:27 发布 · 624 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

可以分享

收录于

.Net应用技术

本文介绍了一个用于操作SQLite数据库的C#类库，包括连接数据库、执行SQL语句、获取表结构等功能，并提供了一种类型映射机制，将SQLite的数据类型转换为.NET Framework中的类型。

 public sealed class SqliteHelper
    {
        private readonly string _connstring = "Data Source={0};Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";

        public SqliteHelper(string dbPath)
        {
            this._connstring = string.Format(_connstring, dbPath);
        }
        public DataTable Select(string sql, DbParameter[] parameters = null)
        {
            using (var conn = new SQLiteConnection(this._connstring))
            {
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sql;
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    var adp = new SQLiteDataAdapter(cmd);
                    var dt = new DataTable();
                    adp.Fill(dt);
                    return dt;
                }
            }
        }


        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <returns></returns>
        public DataTable ShowDataBase()
        {
            return Select("PRAGMA  database_list");
        }
        /// <summary>
        /// 查询所有表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableList()
        {
            return Select("select * from sqlite_master where  type='table'");
        }

        public DataTable GetColumn(string tableName)
        {
            return Select($"PRAGMA table_info(`{tableName}`);");
        }
    }


    public sealed class DbTypeMap
    {
        private static Dictionary<string, Type> DbList = null;
        private static void GetSQLiteDbTypeMap()
        {
            if (DbList != null) return;
            DbList = new Dictionary<string, Type>
            {
                {"BIGINT", typeof(Int64)},
                {"BIGUINT", typeof(UInt64)},
                {"BINARY", typeof(byte)},
                {"BIT", typeof(bool)},
                {"BLOB", typeof(byte)},
                {"BOOL", typeof(bool)},
                {"BOOLEAN", typeof(bool)},
                {"CHAR", typeof(string)},
                {"CLOB", typeof(string)},
                {"COUNTER", typeof(Int64)},
                {"CURRENCY",typeof(decimal)},
                {"DATE", typeof(DateTime)},
                {"DATETIME", typeof(DateTime)},
                {"DECIMAL", typeof(decimal)},
                {"DOUBLE", typeof(double)},
                {"FLOAT", typeof(double)},
                {"GENERAL", typeof(Byte)},
                {"GUID", typeof(string)},
                {"IDENTITY",typeof(Int64)},
                {"IMAGE", typeof(byte[])},
                {"INT", typeof(Int64)},
                {"INT8", typeof(byte)},
                {"INT16", typeof(Int16)},
                {"INT32", typeof(Int32)},
                {"INT64", typeof(Int64)},
                {"INTEGER", typeof(Int64)},
                {"INTEGER8", typeof(byte)},
                {"INTEGER16", typeof(Int16)},
                {"INTEGER32", typeof(Int32)},
                {"INTEGER64", typeof(Int64)},
                {"LOGICAL", typeof(bool)},
                {"LONG", typeof(Int64)},
                {"LONGCHAR", typeof(String)},
                {"LONGTEXT", typeof(String)},
                {"LONGVARCHAR", typeof(String)},
                {"MEMO", typeof(String)},
                {"MONEY", typeof(Decimal)},
                {"NCHAR", typeof(String)},
                {"NOTE", typeof(String)},
                {"NTEXT", typeof(String)},
                {"NUMBER", typeof(Decimal)},
                {"NUMERIC", typeof(Decimal)},
                {"NVARCHAR", typeof(String)},
                {"OLEOBJECT", typeof(byte[])},
                {"RAW", typeof(byte[])},
                {"REAL", typeof(double)},
                {"SINGLE", typeof(Single)},
                {"SMALLDATE", typeof(DateTime)},
                {"SMALLINT", typeof(Int16)},
                {"SMALLUINT", typeof(UInt16)},
                {"STRING", typeof(String)},
                {"TEXT", typeof(String)},
                {"TIME", typeof(DateTime)},
                {"TIMESTAMP", typeof(DateTime)},
                {"TINYINT", typeof(Int16)},
                {"TINYSINT", typeof(byte)},
                {"UINT", typeof(Int64)},
                {"UINT8", typeof(byte)},
                {"UINT16", typeof(Int16)},
                {"UINT32",typeof(UInt32)},
                {"UINT64", typeof(UInt64)},
                {"ULONG", typeof(UInt64)},
                {"VARCHAR", typeof(string)},
                {"VARCHAR2", typeof(string)},
                {"YESNO", typeof(bool)}
            };
        }

        public static string FindType(string typeName)
        {
            GetSQLiteDbTypeMap();
            int index = typeName.IndexOf("(", StringComparison.InvariantCultureIgnoreCase);
            if (index > 0)
            {
                typeName = typeName.Substring(0, index);
            }
            Type db = null;
            if (DbList.TryGetValue(typeName, out db))
            {
                return db.Name;
            }
            return typeof(string).Name;
        }

用法

 DbTypeMap.FindType(c.Type);