CNAS/SyncLocalDAL/SQLiteDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using CnasSynchronousCommon;

namespace SyncLocalDAL
{

    public static class SQLiteHelper
    {
        private static string connectionString = string.Empty;

        /// <summary>
        /// 根据数据源、密码、版本号设置连接字符串。
        /// </summary>
        /// <param name="datasource">数据源。</param>
        /// <param name="password">密码。</param>
        /// <param name="version">版本号（缺省为3）。</param>
        public static void SetConnectionString(string datasource, string password, int version = 3)
        {
            connectionString = string.Format("Data Source={0};Version={1};password={2}",
                datasource, version, password);
        }

        public static void SetConnectionString(string datasource)
        {
            connectionString = datasource;
        }

        /// <summary>
        /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
        /// </summary>
        /// <param name="dbName">数据库文件名。为null或空串时不创建。</param>
        /// <param name="password">（可选）数据库密码，默认为空。</param>
        /// <exception cref="Exception"></exception>
        public static void CreateDB(string dbName)
        {
            if (!string.IsNullOrEmpty(dbName))
            {
                try { SQLiteConnection.CreateFile(dbName); }
                catch (Exception) { throw; }
            }
        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句。</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    try
                    {
                        connection.Open();
                        command.CommandText = sql;
                        if (parameters.Length != 0)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        AppLog.Error(ex.Message);
                        throw ex;
                    }
                }
            }
            return affectedRows;
        }

        /// <summary>
        /// 批量处理数据操作语句。
        /// </summary>
        /// <param name="list">SQL语句集合。</param>
        /// <exception cref="Exception"></exception>
        public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                try { conn.Open(); }
                catch { throw; }
                using (SQLiteTransaction tran = conn.BeginTransaction())
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        try
                        {
                            foreach (var item in list)
                            {
                                cmd.CommandText = item.Key;
                                if (item.Value != null)
                                {
                                    cmd.Parameters.AddRange(item.Value);
                                }
                                cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            AppLog.Error(ex.Message);
                            throw ex;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，并返回第一个结果。
        /// </summary>
        /// <param name="sql">查询语句。</param>
        /// <returns>查询结果。</returns>
        /// <exception cref="Exception"></exception>
        public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandText = sql;
                        if (parameters.Length != 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        return cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        AppLog.Error(ex.Message);
                        throw ex;
                    }
                }
            }
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable。 
        /// </summary> 
        /// <param name="sql">要执行的查询语句。</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters.Length != 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    try { adapter.Fill(data); }
                    catch (Exception ex)
                    {
                        AppLog.Error(ex.Message);
                        throw ex;
                    }
                    return data;
                }
            }
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 
        /// </summary> 
        /// <param name="sql">要执行的查询语句。</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            try
            {
                if (parameters.Length != 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                connection.Open();
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
        }

        /// <summary> 
        /// 查询数据库中的所有数据类型信息。
        /// </summary> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public static DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return connection.GetSchema("TABLES");
                }
                catch (Exception ex)
                {
                    AppLog.Error(ex.Message);
                    throw ex;
                }
            }
        }



        /// <summary>
        /// 读取表名和表结构
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> ReadSQLiteTables()
        {
            Dictionary<string, DataTable> dictTables = new Dictionary<string, DataTable>();
            try
            {
                string sql = "";
                DataTable TablesName = ExecuteQuery("select * from sqlite_master;", new SQLiteParameter[] { });//得到所有表
                foreach (DataRow dr in TablesName.Rows)
                {
                    string strTableName = dr[1].ToString();

                    sql = string.Format("SELECT * FROM [{0}] where 1=0", strTableName); //查询字符串
                    

                    dictTables.Add(strTableName.ToUpper(), ExecuteQuery(sql, new SQLiteParameter[] { }));
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
                //throw ex;
            }
            return dictTables;
        }

        public static DataTable ReadSQLiteTablesByDate(string strTableName,string strDateColumn,string strDate)
        {
            DataTable dtReturn = new DataTable();
            try
            {
                string sql = string.Format("SELECT * FROM [{0}] where {1}>'{2}'", strTableName, strDateColumn, Convert.ToDateTime(strDate).ToString("yyyy-MM-dd HH:mm:ss")); //查询字符串
                DataTable dt = ExecuteQuery(sql, new SQLiteParameter[] { });

                Dictionary<string, string> dictFiled = GetSpecialOperaField(strTableName);
                if (dictFiled.Count > 0)
                    dtReturn = DateAndTimeTypeOpera(dt, dictFiled);
                else
                    dtReturn = dt;
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
                throw ex;
            }
            return dtReturn;
        }


        /// <summary>
        /// 获取所有数据字段，然后记录其中是否存在需要特殊处理的字段
        /// </summary>
        /// <returns></returns>
        private static Dictionary<string, string> GetSpecialOperaField(string strTableName)
        {
            Dictionary<string, string> DictFiled = new Dictionary<string, string>();
            DataTable dt = new DataTable();
            try
            {
                string sql = string.Format("PRAGMA table_info({0});", strTableName); //查询字符串
                dt = ExecuteQuery(sql, new SQLiteParameter[] { });

                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["Type"].ToString().ToLower() == "date" || dr["Type"].ToString().ToLower() == "time")
                    {
                        DictFiled.Add(dr["name"].ToString(), dr["Type"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return DictFiled;
        }

        /// <summary>
        /// 获取数据时，单独处理某些（Date和Time）类型数据，并把数据类型转换为字符串类型
        /// </summary>
        private static DataTable DateAndTimeTypeOpera(DataTable dt, Dictionary<string, string> DictSpecialField)
        {
            DataTable dtNewFormat = new DataTable();

            //添加列
            foreach (DataColumn dc in dt.Columns)
            {
                if (DictSpecialField.ContainsKey(dc.ColumnName))
                {
                    string strDateType = DictSpecialField[dc.ColumnName];
                    switch (strDateType.ToUpper())
                    {
                        case "DATE":
                        case "TIME":
                        case "DATETIME":
                            dtNewFormat.Columns.Add(dc.ColumnName, typeof(string));  //使用字符串来存储该字段，而不是采用它的数据库格式（C#无法区分Date, Time，DateTime，前两种格式会自动补充数据，导致数据的不准确）
                            break;
                        default:
                            dtNewFormat.Columns.Add(dc.ColumnName, dc.DataType);
                            break;
                    }
                }
                else
                {
                    dtNewFormat.Columns.Add(dc.ColumnName,dc.DataType);
                }
            }

            //添加数据行
            foreach (DataRow dr in dt.Rows)
            {
                DataRow drNewRow = dtNewFormat.NewRow();
                foreach (DataColumn dc in dtNewFormat.Columns)
                {
                    if (!DictSpecialField.ContainsKey(dc.ColumnName))
                        drNewRow[dc.ColumnName] = dr[dc.ColumnName];
                    else
                    {
                        switch (DictSpecialField[dc.ColumnName].ToUpper())
                        {
                            case "DATE":
                                if (dr[dc.ColumnName] != null && dr[dc.ColumnName].ToString() != "")
                                    drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd");
                                break;
                            case "TIME":
                                if(dr[dc.ColumnName]!=null&& dr[dc.ColumnName].ToString()!="")
                                    drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("HH:mm:ss");
                                break;
                            case "DATETIME":
                                if (dr[dc.ColumnName] != null && dr[dc.ColumnName].ToString() != "")
                                { 
                                    string strTime= Convert.ToDateTime(dr[dc.ColumnName]).ToString("HH:mm:ss");
                                    if (strTime=="00:00:00")
                                        drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd");
                                    else
                                        drNewRow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                break;
                            default:
                                drNewRow[dc.ColumnName] = dr[dc.ColumnName];
                                break;
                        }
                    }
                }
                dtNewFormat.Rows.Add(drNewRow);
            }

            //返回数据
            return dtNewFormat;
        }
    }
}