Avalonia学习实践(四)--实现最简单的SQLite操作辅助类

在桌面应用程序开发中，如果有数据存储的需要，同时对性能要求不是极高，又想应用能够随时随地运行，轻便小巧的SQlite自然是不二之选。本篇其实跟Avalonia没啥关系，SQLite的操作辅助类网上也是一大堆，这里就权当温故知新了。

---

1.安装Microsoft.Data.Sqlite

为什么用Microsoft.Data.Sqlite，不用System.Data.SQLite?
其实没啥特别原因，就是System.Data.SQLite用惯，想换Microsoft.Data.Sqlite试试。从名字上就能大概区分出来，Microsoft.Data.Sqlite是微软官方出的，System.Data.SQLite是SQLite团队出的，功能上并没有太大差别。

Microsoft.Data.Sqlite概述

2.实现常用方法

    public static class SqliteHelper
    {
        public const string DbConnectString = "Data Source=data//database.db";

        #region ExecuteReader
        public static DbDataReader ExecuteReader(string commandText, IList<SqliteParameter> parameters = null)
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                return command.ExecuteReader();
            }
        } 
        #endregion

        #region ExecuteDatatable
        public static DataTable ExecuteDatatable(string commandText, IList<SqliteParameter> parameters = null)
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                DataTable dt = new DataTable();
                dt.Load(command.ExecuteReader());
                return dt;
            }
        } 
        #endregion

        #region ExecuteList<T>
        public static List<T> ExecuteList<T>(string commandText, IList<SqliteParameter> parameters = null) where T : class, new()
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                var dt=new DataTable();
                var result = new List<T>();
                var reader = command.ExecuteReader();
                //var properties = typeof(T).GetProperties().ToList();
                //while (reader.Read())
                //{
                //    var obj = new T();

                //    foreach (var property in properties)
                //    {
                //        try
                //        {
                //            var id = reader.GetOrdinal(property.Name.ToString());
                //            if (!reader.IsDBNull(id))
                //            {
                //                if (reader.GetValue(id) != DBNull.Value)
                //                {
                //                    property.SetValue(obj, Convert.ChangeType(reader.GetValue(id),reader.GetFieldType(id)),null);
                //                }
                //            }
                //        }
                //        catch(Exception e)
                //        {
                //            continue;
                //        }
                //    }

                //    result.Add(obj);
                //}
                dt.Load(reader);
                string strJson=Newtonsoft.Json.JsonConvert.SerializeObject(dt);
                result = Newtonsoft.Json.JsonConvert.DeserializeObject<List<T>>(strJson);
                return result;
            }
        }

        #endregion

        #region ExecuteScalar
        public static object ExecuteScalar(string commandText, IList<SqliteParameter> parameters, int timeout)
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                command.CommandTimeout = timeout;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                return command.ExecuteScalar();
            }
        } 
        #endregion

        #region ExecuteScalar<T>
        public static T ExecuteScalar<T>(string commandText, IList<SqliteParameter> parameters = null, int timeout = 30)
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                command.CommandTimeout = timeout;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                return (T)command.ExecuteScalar();
            }
        } 
        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string commandText, IList<SqliteParameter> parameters, int timeout = 30)
        {
            using (var connection = new SqliteConnection(DbConnectString))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (var p in parameters)
                    {
                        command.Parameters.Add(p);
                    }
                }
                return command.ExecuteNonQuery();
            }
        } 
        #endregion
    }

这里面实现了几个基本方法，均是SQL语句的执行和结果的返回，在此基础上可以进一步进行封装供业务类使用，比较复杂的业务还是老老实实找个ORM方便些。