OTS/OTSIncAReportApp/3-ServiceCenter/DataOperation/DataAccess/SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;

namespace OTSIncAReportApp.DataOperation.DataAccess
{
    /// <summary>
    /// SQLite 操作类
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connectionString = "";

        /// <summary>
        /// 数据库连接定义
        /// </summary>
        private SQLiteConnection dbConnection;

        /// <summary>
        /// SQL命令定义
        /// </summary>
        private SQLiteCommand dbCommand;
        private SQLiteDataAdapter dataAdapter;
        /// <summary>
        /// 数据读取定义
        /// </summary>
        private SQLiteDataReader dataReader;
        private DataTable dt;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="con">连接SQLite库字符串</param>
        public SqlHelper(string con)
        {
            try
            {
                dbConnection = new SQLiteConnection(con);
                dbConnection.Open();
                connectionString = con;
                dbCommand = new SQLiteCommand();
                dbCommand.Connection = dbConnection;
            }
            catch (Exception ee)
            {
                string str = ee.ToString();
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接SQLite库字符串</param>
        public SqlHelper()
        {
            try
            {
                dbConnection = new SQLiteConnection(connectionString);
                dbConnection.Open();

                dbCommand = new SQLiteCommand();
                dbCommand.Connection = dbConnection;
            }
            catch (Exception)
            {

            }
        }

        /// <summary>
        /// 创建一个指定名字的数据库
        /// </summary>
        /// <param name="basename"></param>
        public static void CreateNewDatabase(string basename)
        {
            try
            {
                SQLiteConnection.CreateFile(basename);

            }
            catch (Exception)
            {

            }
        }


        /// <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)
            {

            }
        }


        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <returns>The query.</returns>
        /// <param name="queryString">SQL命令字符串</param>
        public DataTable ExecuteQuery(string queryString)
        {
            try
            {
                dataAdapter = new SQLiteDataAdapter(queryString, dbConnection);
                DataSet ds = new DataSet();
                dataAdapter.Fill(ds);
                dt = ds.Tables[0];
            }
            catch 
            {

            }
            return dt;
        }


        public bool ExecuteQuery_bool(string queryString)
        {
            try
            {
                dataAdapter = new SQLiteDataAdapter(queryString, dbConnection);
                DataSet ds = new DataSet();
                dataAdapter.Fill(ds);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public DataTable ExecuteQuery2(string queryString)
        {
            SQLiteCommand sqlCommand = new SQLiteCommand(queryString,dbConnection);
            try
            {
                SQLiteDataReader reader = sqlCommand.ExecuteReader();
                dt = new DataTable();
                dt.Load(reader);
            }
            catch /*(Exception ee)*/
            {

            }
            return dt;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            //销毁Commend
            if (dbCommand != null)
            {
                dbCommand.Cancel();
            }
            dbCommand = null;
            //销毁Reader
            if (dataReader != null)
            {
                dataReader.Close();
            }
            dataReader = null;
            //销毁Connection
            if (dbConnection != null)
            {
                dbConnection.Close();
            }
            dbConnection = null;
        }

        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <returns>The full table.</returns>
        /// <param name="tableName">数据表名称</param>
        public DataTable ReadFullTable(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            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 (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
            {
                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 if(value.GetType().ToString() == "System.Decimal")
                    {
                        pi.SetValue(item, Convert.ToDouble(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 DataTable CreateTable(string tableName, string[] colNames, string[] colTypes)
        {
                string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];
                for (int i = 1; i < colNames.Length; i++)
                {
                    queryString += ", " + colNames[i] + " " + colTypes[i];
                }
                queryString += "  ) ";
                return ExecuteQuery(queryString);
         
        }

        /// <summary>
        /// 删除数据库表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable DeleteTable(string tableName)
        {
            string queryString = "drop table " + tableName;

            return ExecuteQuery(queryString);
        }

        /// <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";

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


        public int ExecuteQueryResult(string queryString)
        {
            int result = -1;
            try
            {
                dbCommand.CommandText = queryString;
                result = dbCommand.ExecuteNonQuery();
            }
            catch/* (Exception e)*/
            {
            }

            return result;
        }

        public int ExecuteQueryResult(string queryString, SQLiteTransaction tr)
        {
            int result = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Transaction = tr;
                result = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }

            return result;
        }

        /// <summary>
        /// 执行SQL命令，并带有一个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;

                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }
            return iresult;
        }


        /// <summary>
        /// 执行SQL命令，并带有一个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b, SQLiteTransaction tr)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Transaction = tr;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;

                //or
                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }
            return iresult;
        }

        /// <summary>
        /// 执行SQL命令，并带有两个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <param name="in_b2"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b, string parmstr2, byte[] in_b2)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;
                dbCommand.Parameters.Add(parmstr2, System.Data.DbType.Binary).Value = in_b2;

                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }

            return iresult;
        }

        /// <summary>
        /// 执行SQL命令，并带有两个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <param name="in_b2"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b, string parmstr2, byte[] in_b2, SQLiteTransaction tr)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Transaction = tr;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;
                dbCommand.Parameters.Add(parmstr2, System.Data.DbType.Binary).Value = in_b2;

                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }

            return iresult;
        }

        /// <summary>
        /// 执行SQL命令，并带有三个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <param name="in_b2"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b, string parmstr2, byte[] in_b2, string parmstr3, byte[] in_b3)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;
                dbCommand.Parameters.Add(parmstr2, System.Data.DbType.Binary).Value = in_b2;
                dbCommand.Parameters.Add(parmstr3, System.Data.DbType.Binary).Value = in_b3;

                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }

            return iresult;
        }

        /// <summary>
        /// 执行SQL命令，并带有三个BLOB字段对象的参数输入
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="parmstr"></param>
        /// <param name="in_b"></param>
        /// <param name="in_b2"></param>
        /// <returns></returns>
        public int ExecuteQuery(string queryString, string parmstr, byte[] in_b, string parmstr2, byte[] in_b2, string parmstr3, byte[] in_b3, SQLiteTransaction tr)
        {
            int iresult = -1;
            try
            {
                dbCommand.CommandText = queryString;
                dbCommand.Transaction = tr;
                dbCommand.Parameters.Add(parmstr, System.Data.DbType.Binary).Value = in_b;
                dbCommand.Parameters.Add(parmstr2, System.Data.DbType.Binary).Value = in_b2;
                dbCommand.Parameters.Add(parmstr3, System.Data.DbType.Binary).Value = in_b3;

                iresult = dbCommand.ExecuteNonQuery();
            }
            catch /*(Exception e)*/
            {
            }

            return iresult;
        }


        /// <summary>
        /// 开始事务
        /// </summary>
        /// <returns></returns>
        public SQLiteTransaction BeginTranscation()
        {
            return dbConnection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="tx"></param>
        public void TranscationCommit(SQLiteTransaction tr)
        {
            tr.Commit();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="tx"></param>
        public void TranscationRollback(SQLiteTransaction tr)
        {
            tr.Rollback();
        }

        public KeyValuePair<string, SQLiteParameter[]> UpdateINCAEntryData(List<int> particleID,List<string> particleData)
        {
            string sSQLCommand = "Update IncAData set TypeId=@TypeId,TypeName= @TypeName," +
                "TypeColor=@TypeColor,GroupName=@GroupName where FieldID =@FieldId and XrayId =@XrayId ";  //sSQLCommand.Format(sSQLCommand,a_pParticle->GetAveGray(),a_pParticle->GetArea(),a_pParticle->GetType(),a_pParticle->GetFieldId(),a_pParticle->GetTagId());
            //using (var command = new SQLiteCommand(sSQLCommand, dbCommand.Connection))
            //{
            //    // 添加参数并赋值
            //    command.Parameters.AddWithValue("@TypeName", particleData[1]); 
            //    command.Parameters.AddWithValue("@TypeId", particleData[0]); 
            //    command.Parameters.AddWithValue("@TypeColor", particleData[2]);
            //    command.Parameters.AddWithValue("@GroupName", particleData[3]);

            //    command.Parameters.AddWithValue("@FieldId", particleID[0]);
            //    command.Parameters.AddWithValue("@XrayId", particleID[1]);
            //    // 执行更新命令
            //    int rowsAffected = command.ExecuteNonQuery();
            //    //Console.WriteLine($"{rowsAffected} 行受到影响。");
            //}


            var paras = new SQLiteParameter[6];
            paras[0] = new SQLiteParameter("TypeId");
            paras[0].Value = particleData[0];
            paras[1] = new SQLiteParameter("TypeName");
            paras[1].Value = particleData[1];
            paras[2] = new SQLiteParameter("TypeColor");
            paras[2].Value = particleData[2];
            paras[3] = new SQLiteParameter("GroupName");
            paras[3].Value = particleData[3];

            paras[4] = new SQLiteParameter("XrayId");
            paras[4].Value = particleID[1];
            paras[5] = new SQLiteParameter("FieldId");
            paras[5].Value = particleID[0];

            return new KeyValuePair<string, SQLiteParameter[]>(sSQLCommand, paras);
        }

        /// <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 ex)
                        {
                            tran.Rollback();
                            //log.Error(ex.ToString());
                        }
                    }
                }
            }
        }
    }
}