OTS/OTSIncAMeasureApp/ServiceCenter/DTLBase/SQliteHelper.cs

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

namespace OTSModelSharp.DTLBase
{
    /// <summary>
    /// SQLite 操作类
    /// </summary>
    public class SQLiteHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        
        private string  connectionString;
        SQLiteConnection connForTrans;
        SQLiteCommand cmdForTrans;
        SQLiteTransaction trans;


        private NLog.Logger log;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="con">连接SQLite库字符串</param>
        public SQLiteHelper(string a_sFileName)
        {
            log = NLog.LogManager.GetCurrentClassLogger();

            connectionString = "data source='" + a_sFileName + "'" + ";Version=3;";
             
            
        }

        /// <summary>
        /// 根据数据源、密码、版本号设置连接字符串。
        /// </summary>
        /// <param name="datasource">数据源。</param>
        /// <param name="password">密码。</param>
        /// <param name="version">版本号（缺省为3）。</param>
        public  void SetConnectionString(string datasource, string password, int version = 3)
        {
               connectionString = string.Format("Data Source={0};Version={1};password={2}",
                datasource, version, password);
        }
        public SQLiteConnection GetDBConnection()
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            connForTrans = connection;
            return connection;
        }
        /// <summary>
        /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
        /// </summary>
        /// <param name="dbName">数据库文件名。为null或空串时不创建。</param>
        /// <param name="password">（可选）数据库密码，默认为空。</param>
        /// <exception cref="Exception"></exception>
        public static void CreateNewDatabase(string dbName)
        {
            if (!string.IsNullOrEmpty(dbName))
            {
                try { SQLiteConnection.CreateFile(dbName); }
                catch (Exception) { throw; }
            }
        }

       

        /// <summary>
        /// 删除指定的数据库文件
        /// </summary>
        /// <param name="basename"></param>
        public void DeleteDataBase(string basename)
        {
            try
            {
                if (System.IO.File.Exists(basename))
                {
                    System.IO.File.Delete(basename);
                }
            }
            catch (Exception)
            { throw; }
        }



        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <returns>The query.</returns>
        /// <param name="queryString">SQL命令字符串</param>
        public 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) { throw; }
                    return data;
                }
            }
        }
        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句。</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public 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) { throw; }
                }
            }
            return affectedRows;
        }
        public int ExecuteNonQueryForTransaction(SQLiteCommand command ,string sql,  params SQLiteParameter[] parameters)
        {
            int affectedRows = 0;

        
                    
                    command.CommandText = sql;
            if (parameters.Length != 0)
            {
                command.Parameters.AddRange(parameters);
            }
            affectedRows = command.ExecuteNonQuery();
     

            return affectedRows;
        }

        /// <summary>
        /// 单独修改指定位置的能谱数据
        /// </summary>
        /// <param name="XrayId"></param>
        /// <param name="FieldId"></param>
        /// <param name="pBlobData"></param>
        /// <returns></returns>
        public int ExecuteXrayForTransaction(int XrayId, int FieldId, byte[] pBlobData)
        {
            int affectedRows = 0;
            cmdForTrans.CommandText = "UPDATE XRayData SET XRayData = @XRayData WHERE XrayIndex = " + XrayId + " and FieldId = " + FieldId + "";
            cmdForTrans.Parameters.Clear();
            cmdForTrans.Parameters.AddWithValue(@"XRayData", pBlobData);
            affectedRows = cmdForTrans.ExecuteNonQuery();
            return affectedRows;
        }

        public int ExecuteElementForTransaction(int XrayId, int FieldId, int ElementNum, List<string> nameList, List<double> perList)
        {
            int affectedRows = 0;
            cmdForTrans.CommandText = "DELETE FROM ElementChemistry WHERE XrayId = " + XrayId + " and FieldId = " + FieldId + "";
            affectedRows = cmdForTrans.ExecuteNonQuery();
            for (int i = 0; i < ElementNum; i++)
            {
                cmdForTrans.CommandText = "INSERT INTO ElementChemistry(XrayID, FieldID, ElementId, ElementNum, Name, Percentage) VALUES ('" + XrayId + "','" + FieldId + "','" + i + "','" + ElementNum + "','" + nameList[i] + "','" + perList[i] + "')";
                affectedRows = cmdForTrans.ExecuteNonQuery();
            }

            return affectedRows;
        }

        public bool BeginTransaction()
        {
            if (connForTrans.State != ConnectionState.Open)
            {
                connForTrans.Open();
            }
            trans = connForTrans.BeginTransaction();
            cmdForTrans = new SQLiteCommand();
            cmdForTrans.Connection = connForTrans;
            cmdForTrans.Transaction = trans;
            return true;
        }

        public bool CommitTransaction()
        {
            trans.Commit();
            connForTrans.Close();
            return true;
        }

        /// <summary>
        /// 批量处理数据操作语句。
        /// </summary>
        /// <param name="list">SQL语句集合。</param>
        /// <exception cref="Exception"></exception>
        public void ExecuteNonQueryBatch(ref List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                try { conn.Open(); }
                catch { throw; }
                SQLiteCommand cmd1 =conn.CreateCommand();
                cmd1.CommandText = "pragma synchronous = 0;";
                cmd1.ExecuteNonQuery();
                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();
                                cmd.Parameters.Clear();
                            }
                            tran.Commit();
                           
                        }
                        catch (Exception) 
                        { 
                            tran.Rollback(); 
                            throw;
                        }
                    }
                }
            }
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 
        /// </summary> 
        /// <param name="sql">要执行的查询语句。</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public 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) { throw; }
        }
        /// <summary>
        /// 执行查询语句，并返回第一个结果。
        /// </summary>
        /// <param name="sql">查询语句。</param>
        /// <returns>查询结果。</returns>
        /// <exception cref="Exception"></exception>
        public 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) { throw; }
                }
            }
        }


        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <returns>The full table.</returns>
        /// <param name="tableName">数据表名称</param>
        public DataTable ReadFullTable(string tableName,string order)
        {
            string queryString = "SELECT * FROM " + tableName+" order by "+ order;
            return ExecuteQuery(queryString);
        }
        /// <summary>         
        /// /// 执行一个查询语句，返回一个包含查询结果的DataTable         
        /// /// </summary>        
        /// /// <param name="sql">要执行的查询语句</param>         
        /// /// <param name="parameters">执行查询语句的参数</param>         
        /// /// <returns></returns>         
        public DataTable ExecuteDataTable(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, conn))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
            
        }
        /// <summary>        
        /// /// tbale转list        
        /// /// </summary>        
        /// /// <typeparam name="T"></typeparam>        
        /// /// <returns></returns>        
         public List<T> TableToList<T>(DataTable table) where T : class, new()        
         {
            var result = new List<T>();
            var propertys = typeof(T).GetProperties();
            foreach (DataRow dr in table.Rows)
            {
                var item = new T();
                result.Add(item);
                foreach (var pi in propertys)
                {
                    if (!table.Columns.Contains(pi.Name))
                        continue;
                    var value = dr[pi.Name];
                    if (value is DBNull || value == null)
                        continue;
                    if (value.GetType().ToString() == "System.Int64")
                    {
                        pi.SetValue(item, Convert.ToInt32(value));
                    }
                    else
                    {
                        pi.SetValue(item, value);
                    }
                }
            }
            return result;
        }
        /// <summary>        
        /// /// tbale转list        
        /// /// </summary>        
        /// /// <typeparam name="T"></typeparam>        
        /// /// <returns></returns>        
        public List<T> RowsToList<T>(DataRow[] table) where T : class, new()
        {
            var result = new List<T>();
            var propertys = typeof(T).GetProperties();
            foreach (DataRow dr in table)
            {
                var item = new T();
                result.Add(item);
                foreach (var pi in propertys)
                {
                    if (!dr.Table.Columns.Contains(pi.Name))
                        continue;
                    var value = dr[pi.Name];
                    if (value is DBNull || value == null)
                        continue;
                    if (value.GetType().ToString() == "System.Int64")
                    {
                        pi.SetValue(item, Convert.ToInt32(value));
                    }
                    else
                    {
                        pi.SetValue(item, value);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 向指定数据表中插入数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="values">插入的数值</param>
        public DataTable InsertValues(string tableName, string[] values)
        {
            //获取数据表中字段数目
            int fieldCount = ReadFullTable(tableName,"").Columns.Count;
            //当插入的数据长度不等于字段数目时引发异常
            if (values.Length != fieldCount)
            {
                throw new SQLiteException("values.Length!=fieldCount");
            }

            string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
            for (int i = 1; i < values.Length; i++)
            {
                queryString += ", " + "'" + values[i] + "'";
            }
            queryString += " )";
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 更新指定数据表内的数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colValues">字段名对应的数据</param>
        /// <param name="key">关键字</param>
        /// <param name="value">关键字对应的值</param>
        /// <param name="operation">运算符：=,<,>,...，默认“=”</param>
        public DataTable UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation = "=")
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length");
            }

            string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";
            }
            queryString += " WHERE " + key + operation + "'" + value + "'";
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 删除指定数据表内的数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colValues">字段名对应的数据</param>
        public DataTable DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
            }

            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";
            }
            return ExecuteQuery(queryString);
        }

        /// <summary>
        /// 删除指定数据表内的数据
        /// </summary>
        /// <returns>The values.</returns>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colValues">字段名对应的数据</param>
        public DataTable DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
            }

            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
            }
            return ExecuteQuery(queryString);
        }


        /// <summary>
        /// 创建数据表
        /// </summary> +
        /// <returns>The table.</returns>
        /// <param name="tableName">数据表名</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colTypes">字段名类型</param>
        public int CreateTable(TableModel table)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("CREATE TABLE IF NOT EXISTS " + table.TableName+"(");
            int ind = 0;
            foreach (var item in table.columns)
            {
                builder.Append(item.ColumName + " " + item.ColumType + " " + (item.IsPrimarykey ? "PRIMARY KEY" : ""));
                if (++ind == table.columns.Count)
                {
                    builder.Append(")");
                }
                else
                {
                    builder.Append(",");
                }
            }
           
            return ExecuteNonQuery(builder.ToString());
        }
        /// <summary>
        /// 判断表存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool IsExist(string tableName)
        {
            string queryString = "select count(*) from sqlite_master where [type]='table' and [name]='"+ tableName + "'" ;
            Int64 i = (Int64)ExecuteScalar(queryString);
            
            return i > 0 ? true : false;
        }
        /// <summary>
        /// 删除数据库表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool DeleteTable(string tableName)
        {
            string queryString = "drop table " + tableName;
           return ExecuteNonQuery(queryString)>0?true:false;
        }
        /// <summary>
        /// 删除数据库表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool RemoveAllRows(string tableName)
        {
            string queryString = "DELETE FROM  " + tableName;
            return ExecuteNonQuery(queryString) > 0 ? true : false;
        }
        /// <summary>
        /// Reads the table.
        /// </summary>
        /// <returns>The table.</returns>
        /// <param name="tableName">Table name.</param>
        /// <param name="items">Items.</param>
        /// <param name="colNames">Col names.</param>
        /// <param name="operations">Operations.</param>
        /// <param name="colValues">Col values.</param>
        public DataTable ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            string queryString = "SELECT " + items[0];
            for (int i = 1; i < items.Length; i++)
            {
                queryString += ", " + items[i];
            }
            queryString += " FROM " + tableName + " where 1=1";

            //+ " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
            for (int i = 0; i < colNames.Length; i++)
            {
                queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
            }
            return ExecuteQuery(queryString);
        }


      

      
    }
}