OTS/OTSIncAMeasureApp/ServiceCenter/DTLBase/SQliteHelper.cs

using OTSCLRINTERFACE;
using OTSCommon.Model;
using OTSModelSharp.DTLBase;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
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 bool ExecuteXrayForTransaction(int XrayId, int FieldId, byte[] pBlobData)
        {
            //cmdForTrans.CommandText = "UPDATE XRayData SET XRayData = @XRayData WHERE XrayIndex = " + XrayId + " and FieldId = " + FieldId + "";
            cmdForTrans.CommandText = "INSERT INTO XRayData(XrayIndex, FieldId, XRayData) VALUES (" + XrayId + "," + FieldId + ",@XRayData)";
            cmdForTrans.Parameters.Clear();
            cmdForTrans.Parameters.AddWithValue(@"XRayData", pBlobData);
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 修改元素信息
        /// </summary>
        /// <param name="XrayId"></param>
        /// <param name="FieldId"></param>
        /// <param name="ElementNum"></param>
        /// <param name="nameList"></param>
        /// <param name="perList"></param>
        /// <returns></returns>
        public bool ExecuteElementForTransaction(int XrayId, int FieldId, int ElementNum, List<string> nameList, List<double> perList)
        {
            //cmdForTrans.CommandText = "DELETE FROM ElementChemistry WHERE XrayId = " + XrayId + " and FieldId = " + FieldId + "";
            //if (cmdForTrans.ExecuteNonQuery() == 0)
            //{
            //    return false;
            //}
            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] + "')";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 合并大颗粒
        /// </summary>
        /// <param name="combinBorderParts"></param>
        /// <returns></returns>
        public bool CombinPartForTransaction(List<COTSFieldData> allFields, Dictionary<COTSParticleClr, COTSParticleClr> combinBorderParts, List<COTSParticleClr> updatePartList, List<string> combinBorderParts_style, System.Drawing.Size resolutionSize)
        {
            for (int i = 0; i < combinBorderParts.Count; i++)
            {
                COTSParticleClr particleClr_key = new COTSParticleClr();
                COTSParticleClr particleClr_value = new COTSParticleClr();
                for (int j = 0; j < allFields.Count; j++)
                {
                    for (int k = 0; k < allFields[j].GetListAnalysisParticles().Count; k++)
                    {
                        if (allFields[j].GetListAnalysisParticles()[k] == combinBorderParts.ElementAt(i).Key)
                        {
                            particleClr_key = allFields[j].GetListAnalysisParticles()[k];
                            break;
                        }
                    }
                }
                for (int j = 0; j < allFields.Count; j++)
                {
                    for (int k = 0; k < allFields[j].GetListAnalysisParticles().Count; k++)
                    {
                        if (allFields[j].GetListAnalysisParticles()[k] == combinBorderParts.ElementAt(i).Value)
                        {
                            particleClr_value = allFields[j].GetListAnalysisParticles()[k];
                            break;
                        }
                    }
                }
                COTSFeatureClr featureClr_key = particleClr_key.GetFeature();
                COTSFeatureClr featureClr_value = particleClr_value.GetFeature();

                List<COTSSegmentClr> segmentClr_key = featureClr_key.GetSegmentsList();
                List<COTSSegmentClr> segmentClr_value = featureClr_value.GetSegmentsList();

                List<int> height_left_up = new List<int>();
                List<int> height_right_down = new List<int>();
                if (combinBorderParts_style[i] == "left")
                {
                    foreach (var item in segmentClr_key)
                    {
                        if (item.GetStart() + item.GetLength() == resolutionSize.Width)
                        {
                            height_left_up.Add(item.GetHeight());
                        }
                    }
                    foreach (var item in segmentClr_value)
                    {
                        if (item.GetStart() == 0)
                        {
                            height_right_down.Add(item.GetHeight());
                        }
                    }

                    int center_left = (int)height_left_up.Average();
                    int center_right = (int)height_right_down.Average();

                    if (height_left_up.Max() < height_right_down.Max())
                    {
                        int des = center_right - center_left;
                        foreach (var item in segmentClr_key)
                        {
                            int newHeight = item.GetHeight() + des > resolutionSize.Height - 1 ? resolutionSize.Height - 1 : item.GetHeight() + des;
                            item.SetHeight(newHeight);
                        }
                    }
                    else
                    {
                        int des = center_left - center_right;
                        foreach (var item in segmentClr_value)
                        {
                            int newHeight = item.GetHeight() + des > resolutionSize.Height - 1 ? resolutionSize.Height - 1 : item.GetHeight() + des;
                            item.SetHeight(newHeight);
                        }
                    }
                }
                else
                {
                    int flag = 0;
                    foreach (var item in segmentClr_key)
                    {
                        if (item.GetHeight() == resolutionSize.Height - 1)
                        {
                            height_left_up.Add(item.GetStart());
                        }
                    }
                    foreach (var item in segmentClr_value)
                    {
                        if (item.GetHeight() == 0)
                        {
                            height_right_down.Add(item.GetStart());
                        }
                    }
                    if (height_right_down.Count == 0)
                    {
                        flag = 1;
                        foreach (var item in segmentClr_value)
                        {
                            if (item.GetHeight() == 1)
                            {
                                height_right_down.Add(item.GetStart());
                            }
                        }
                    }

                    int center_left = (int)height_left_up.Average();
                    int center_right = (int)height_right_down.Average();
                    if (height_left_up.Max() < height_right_down.Max())
                    {
                        int des = height_right_down.Max() - height_left_up.Max();
                        foreach (var item in segmentClr_key)
                        {
                            int newStart = item.GetStart() + des;
                            item.SetStart(newStart);
                            if (newStart + item.GetLength() > resolutionSize.Width)
                            {
                                item.SetLength(resolutionSize.Width - newStart);
                            }
                        }
                    }
                    else
                    {
                        int des = height_left_up.Max() - height_right_down.Max();
                        foreach (var item in segmentClr_value)
                        {
                            int newStart = item.GetStart() + des;
                            item.SetStart(newStart);
                            if (newStart + item.GetLength() > resolutionSize.Width)
                            {
                                item.SetLength(resolutionSize.Width - newStart);
                            }
                            if (flag == 1)
                            {
                                item.SetHeight(item.GetHeight() - 1);
                            }
                        }
                    }
                }

                featureClr_key.SetSegmentsList(segmentClr_key, true);
                featureClr_value.SetSegmentsList(segmentClr_value, true);

                particleClr_key.SetFeature(featureClr_key);
                particleClr_value.SetFeature(featureClr_value);

                updatePartList.Add(particleClr_key);
                updatePartList.Add(particleClr_value);

                //COTSParticleClr particleClr_new = combinBorderParts.ElementAt(i).Key;
                //particleClr_new.SetAbsolutPos(combinBorderParts.ElementAt(i).Key.GetAbsolutPos());
                //string partsStr = combinBorderParts.ElementAt(i).Key.GetFieldId() + ":" + combinBorderParts.ElementAt(i).Key.GetAnalysisId();
                //partsStr += "," + combinBorderParts.ElementAt(i).Value.GetFieldId() + ":" + combinBorderParts.ElementAt(i).Value.GetAnalysisId();
                //double allPartArea = combinBorderParts.ElementAt(i).Key.GetActualArea() + combinBorderParts.ElementAt(i).Value.GetActualArea();
                //int SegmentNum = combinBorderParts.ElementAt(i).Key.GetFeature().GetSegmentsList().Count + combinBorderParts.ElementAt(i).Value.GetFeature().GetSegmentsList().Count;
                //Point point_new = (Point)particleClr_new.GetXRayPos();
                //Point point_field = (Point)particleClr_new.GetAbsolutPos();
                //Rectangle rectangle_key = (Rectangle)combinBorderParts.ElementAt(i).Key.GetParticleRect();
                //Rectangle rectangle_value = (Rectangle)combinBorderParts.ElementAt(i).Value.GetParticleRect();
                ////rectangle_key.Width += rectangle_value.Width;
                ////rectangle_key.Height += rectangle_value.Height - Math.Abs(height_left_up.Max() - height_right_down.Max());
                //particleClr_new.SetFieldId(-1);
                //particleClr_new.SetAnalysisId(i + 1);
                //particleClr_new.SetTagId(i + 1);
                //particleClr_new.SetTypeName(combinBorderParts.ElementAt(i).Key.GetTypeName());
                //particleClr_new.SetTypeColor(combinBorderParts.ElementAt(i).Key.GetTypeColor());
                //particleClr_new.SetArea(allPartArea);
                //particleClr_new.SetParticleRect(rectangle_key);
                //particleClr_new.SetSubParticles(partsStr);

                //cmdForTrans.CommandText = "INSERT INTO MergedParticleInfo(FieldId, XrayId, AveGray, RectLeft, RectTop, RectWidth, RectHeight, Area, PosX, PosY, TypeId, ElementNum, SegmentNum, FieldPosX, FieldPosY, ParticleId, DMAX, DMIN, DPERP, PERIMETER, ORIENTATION, DINSCR, DMEAN, DELONG, DFERET, TypeName, TypeColor, SubParticles) VALUES("
                //    + particleClr_new.GetFieldId() + ", " + particleClr_new.GetAnalysisId() + ", " + particleClr_new.GetAveGray() + "," + rectangle_key.Left + ", " + rectangle_key.Top + ", " + rectangle_key.Width + ", " + rectangle_key.Height + ", " + particleClr_new.GetActualArea() + ", " + point_new.X + ", " + point_new.Y + "," + particleClr_new.GetType() + "," + 0 + "," + SegmentNum + "," + point_field.X + "," + point_field.Y + "," + particleClr_new.GetTagId() + "," + particleClr_new.GetDMAX() + "," + particleClr_new.GetDMIN() + ", " + particleClr_new.GetDMPERP() + ", " + particleClr_new.GetDPRIMETER() + ", " + particleClr_new.GetORIENTATION() + ", " + particleClr_new.GetDINSCR() + ", " + particleClr_new.GetDMEAN() + ", " + particleClr_new.GetDELONG() + "," + particleClr_new.GetFeretDiameter() + ",'" + particleClr_new.GetTypeName() + "','" + particleClr_new.GetTypeColor() + "','" + particleClr_new.GetSubParticles() + "')";
                //if (cmdForTrans.ExecuteNonQuery() == 0)
                //{
                //    return false;
                //}
            }

            return true;
        }

        /// <summary>
        /// 删除颗粒信息
        /// </summary>
        /// <param name="XrayId"></param>
        /// <param name="FieldId"></param>
        /// <param name="SegmentList"></param>
        /// <returns></returns>
        public bool DeletePartForTransaction(List<COTSParticleClr> deletePartList)
        {
            foreach (var item in deletePartList)
            {
                cmdForTrans.CommandText = "DELETE FROM IncAData WHERE FieldId = " + item.GetFieldId() + " AND XrayId = " + item.GetAnalysisId() + "";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 修改segment信息
        /// </summary>
        /// <param name="XrayId"></param>
        /// <param name="FieldId"></param>
        /// <param name="SegmentList"></param>
        /// <returns></returns>
        public bool UpdatePartForTransaction(List<COTSParticleClr> updatePartList)
        {
            foreach (var PartList in updatePartList)
            {
                Rectangle leftRectangle = (Rectangle)PartList.GetParticleRect();
                cmdForTrans.CommandText = "UPDATE IncAData SET RectLeft = " + leftRectangle.Left + ", RectTop = '" + leftRectangle.Top + "' WHERE XrayId = " + PartList.GetAnalysisId() + " and FieldId = " + PartList.GetFieldId() + "";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
                cmdForTrans.CommandText = "DELETE FROM Segment WHERE XrayId = " + PartList.GetAnalysisId() + " and FieldId = " + PartList.GetFieldId() + "";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
                for (int i = 0; i < PartList.GetFeature().GetSegmentsList().Count; i++)
                {
                    cmdForTrans.CommandText = "INSERT INTO Segment (XRayId,FieldId,SegmentId,SegmentNum,Start,Height,Length,ParticleId) VALUES ("+
                        PartList.GetAnalysisId() + "," + PartList.GetFieldId() + "," + i + "," + PartList.GetFeature().GetSegmentsList().Count + "," + PartList.GetFeature().GetSegmentsList()[i].GetStart() + "," + PartList.GetFeature().GetSegmentsList()[i].GetHeight() + "," + PartList.GetFeature().GetSegmentsList()[i].GetLength() + "," + PartList.GetAnalysisId() + ")";
                    if (cmdForTrans.ExecuteNonQuery() == 0)
                    {
                        return false;
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// 修改分类信息
        /// </summary>
        /// <returns></returns>
        public bool ExecuteIncAForTransaction(string tableName, string[] values)
        {
            //cmdForTrans.CommandText = "UPDATE IncAData SET TypeId = "+ TypeId + ", TypeName = '" + TypeName + "', TypeColor = '"+ TypeColor + "', GroupId = "+ GroupId + ", GroupName = '"+ GroupName + "', GroupColor = '"+ GroupColor + "' WHERE XrayId = " + XrayId + " and FieldId = " + FieldId + "";

            cmdForTrans.CommandText = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
            for (int i = 1; i < values.Length; i++)
            {
                cmdForTrans.CommandText += ", " + "'" + values[i] + "'";
            }
            cmdForTrans.CommandText += " )";
            //cmdForTrans.CommandText = "UPDATE IncAData SET TypeId = "+ TypeId + ", TypeName = '" + TypeName + "', TypeColor = '"+ TypeColor + "', GroupId = "+ GroupId + ", GroupName = '"+ GroupName + "', GroupColor = '"+ GroupColor + "' WHERE XrayId = " + XrayId + " and FieldId = " + FieldId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            return true;
        }

        #region 颗粒分割部分
        public int ExecuteNewPartIdForTransaction()
        {
            DataTable dt = ExecuteQuery("SELECT MAX(XrayId) FROM IncAData");
            if (dt.Rows.Count == 0)
            {
                return 0;
            }
            return int.Parse(dt.Rows[0][0].ToString());
        }
        public bool ExecuteSegmentForTransaction(Particle particle1, Particle particle2, int SegmentId)
        {
            cmdForTrans.CommandText = "DELETE FROM Segment WHERE XRayId = " + particle1.SegmentList[0].XRayId + " AND FieldId = " + particle1.SegmentList[0].FieldId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            for (int i = 0; i < particle1.SegmentNum; i++)
            {
                cmdForTrans.CommandText = "INSERT INTO Segment(XrayID, FieldID, SegmentId, SegmentNum, Start, Height, Length, ParticleId) VALUES ('" 
                    + particle1.SegmentList[0].XRayId + "','" + particle1.SegmentList[0].FieldId + "','" + i + "','" + particle1.SegmentList[0].SegmentNum + "','"
                    + particle1.SegmentList[i].Start + "','" + particle1.SegmentList[i].Height + "','" + particle1.SegmentList[i].Length + "','" + particle1.SegmentList[i].ParticleId + "')";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }

            for (int i = 0; i < particle2.SegmentNum; i++)
            {
                cmdForTrans.CommandText = "INSERT INTO Segment(XrayID, FieldID, SegmentId, SegmentNum, Start, Height, Length, ParticleId) VALUES ('"
                    + SegmentId + "','" + particle2.SegmentList[0].FieldId + "','" + i + "','" + particle2.SegmentList[0].SegmentNum + "','"
                    + particle2.SegmentList[i].Start + "','" + particle2.SegmentList[i].Height + "','" + particle2.SegmentList[i].Length + "','" + SegmentId + "')";
                if (cmdForTrans.ExecuteNonQuery() == 0)
                {
                    return false;
                }
            }

            return true;
        }
        public bool ExecuteXrayForTransaction(Particle particle1, Particle particle2, int SegmentId)
        {
            //XRayData
            cmdForTrans.CommandText = "INSERT INTO XRayData(XrayIndex, FieldId, XrayData) SELECT " + SegmentId + ", FieldId, XrayData FROM XRayData WHERE FieldId = "+ particle1.SegmentList[0].FieldId + " AND XrayIndex = "+ particle1.SegmentList[0].XRayId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            //PosXrayInfo
            cmdForTrans.CommandText = "UPDATE PosXrayInfo SET PosX = "+ particle1.PosX + ", PosY = "+ particle1.PosY + " WHERE XrayIndex = " + particle1.SegmentList[0].XRayId + " AND FieldId = " + particle1.SegmentList[0].FieldId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            cmdForTrans.CommandText = "INSERT INTO PosXrayInfo(XrayIndex, PosX, PosY, FieldId, ParticleId, FeatureId, ElementNum) SELECT " + SegmentId + ", "+ particle2.PosX + ", "+ particle2.PosY + ", FieldId, ParticleId, FeatureId, ElementNum FROM PosXrayInfo WHERE FieldId = " + particle1.SegmentList[0].FieldId + " AND XrayIndex = " + particle1.SegmentList[0].XRayId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            //ElementChemistry
            cmdForTrans.CommandText = "INSERT INTO ElementChemistry(XRayId, FieldId, ElementId, ElementNum, Name, Percentage) SELECT " + SegmentId + ", FieldId, ElementId, ElementNum, Name, Percentage FROM ElementChemistry WHERE FieldId = " + particle1.SegmentList[0].FieldId + " AND XRayId = " + particle1.SegmentList[0].XRayId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            return true;
        }
        public bool ExecuteIncAForTransaction(Particle particle1, Particle particle2, int SegmentId)
        {
            cmdForTrans.CommandText = "UPDATE IncAData SET AveGray = " + particle1.AveGray + ", RectLeft = " + particle1.RectLeft + ", RectTop = " + particle1.RectTop + ", RectWidth = " + particle1.RectWidth + ", RectHeight = " + particle1.RectHeight + ", Area = " + particle1.Area + ", PosX = " + particle1.PosX + ", PosY = " + particle1.PosY + ", DFERET = " + particle1.DFERET + ", SegmentNum = " + particle1.SegmentNum + ", ParticleId = " + particle1.ParticleId + ", DMAX = " + particle1.DMAX + ", DMIN = " + particle1.DMIN + ", DPERP = " + particle1.DPERP + ", PERIMETER = " + particle1.PERIMETER + ", ORIENTATION = " + particle1.ORIENTATION + ", DINSCR = " + particle1.DINSCR + ", DMEAN = " + particle1.DMEAN + ", DELONG = " + particle1.DELONG + " WHERE XrayId = " + particle1.SegmentList[0].XRayId + " AND FieldId = " + particle1.SegmentList[0].FieldId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            cmdForTrans.CommandText = "INSERT INTO IncAData(FieldId, XrayId, AveGray, RectLeft, RectTop, RectWidth, RectHeight, Area, PosX, PosY, DFERET, SegmentNum, FieldPosX, FieldPosY, ParticleId, DMAX, DMIN, DPERP, PERIMETER, ORIENTATION, DINSCR, DMEAN, DELONG, TypeId, TypeName, TypeColor, SEMPosX, SEMPosY, GroupId, GroupName, GroupColor) SELECT FieldId, " + SegmentId + ", " + particle2.AveGray + ", " + particle2.RectLeft + ", " + particle2.RectTop + ", " + particle2.RectWidth + ", " + particle2.RectHeight + ", " + particle2.Area + ", " + particle2.PosX + ", " + particle2.PosY + ", " + particle2.DFERET + ", " + particle2.SegmentNum + ", FieldPosX, FieldPosY," + SegmentId + ", " + particle2.DMAX + ", " + particle2.DMIN + ", " + particle2.DPERP + ", " + particle2.PERIMETER + ", " + particle2.ORIENTATION + ", " + particle2.DINSCR + ", " + particle2.DMEAN + ", " + particle2.DELONG + ", TypeId, TypeName, TypeColor, SEMPosX, SEMPosY, GroupId, GroupName, GroupColor FROM IncAData WHERE FieldId = " + particle1.SegmentList[0].FieldId + " AND XrayId = " + particle1.SegmentList[0].XRayId + "";
            if (cmdForTrans.ExecuteNonQuery() == 0)
            {
                return false;
            }
            return true;
        }
        #endregion

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

        /// <summary>
        /// 获取关键元素列表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="eleName">字段名</param>
        /// <returns></returns>
        public List<string> GetDBKeyElementList(string tableName, string eleName)
        {
            string queryString = "SELECT "+ eleName + " FROM " + tableName;
            DataTable dt = ExecuteQuery(queryString);
            List<string> nameList = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string str_name = dt.Rows[i][eleName].ToString();
                if (str_name != "")
                {
                    if (str_name.Contains(","))
                    {
                        for (int j = 0; j < str_name.Split(',').Length; j++)
                        {
                            if (!nameList.Contains(str_name.Split(',')[j]))
                            {
                                nameList.Add(str_name.Split(',')[j]);
                            }
                        }
                    }
                    else
                    {
                        if (!nameList.Contains(str_name))
                        {
                            nameList.Add(str_name);
                        }
                    }
                }
            }
            return nameList;
        }



    }
}