HOZ/DBManager/MeasureDB.cs

//20201104 数据库基本操作 只包含创建和写入的动作，删除和读出查询的动作不包括在其中
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Threading;
using System.Data.SQLite;

using MeasureData;
using Extender;

namespace DBManager
{
    public class MeasureDB
    {
        public SQLiteConnection m_db;
        private MeasureFile m_measureFile; //测量文件


        private List<byte[]> LineXraybyte = new List<byte[]>();
        private List<string> LineXraystring = new List<string>();
        private List<string> LineXray = new List<string>();

        private List<byte[]> PoinXraybyte = new List<byte[]>();
        private List<string> PoinXraystring = new List<string>();
        private List<string> PoinXray = new List<string>();

        public MeasureDB(MeasureFile mf)
        {
            Init(mf);
            CreateDBconn();
            CreateAnalysisPointsTable();
            CreateXrayDataTable();
            CreateAreaPositionTable();
            CreateElementTable();
        }

        public void Init(MeasureFile mf)
        {
            m_measureFile = mf;
        }

        //创建一个空的数据库
        public void CreateDBconn()
        {
            string path;//= Path.GetDirectoryName(m_measureFile.FilePath);

            //获取测量文件所在路径
            string savePath = m_measureFile.FilePath;
            //数据库名称
            string dbFileName = "MeasureDB.db";
            path = savePath + "//" + dbFileName;

            //if (File.Exists(path))
            //{
            //    File.Delete(path);
            //}

            m_db = new SQLiteConnection("data source =" + path);
            //m_db = new SQLiteConnection("data source =" + @"D:\HOZ\数据库测试\MeasureDB.db");
            m_db.Open();
        }

        //关闭数据库
        public void CloseDB()
        {
            m_db.Close();
        }

        //创建分析点表
        public void CreateAnalysisPointsTable()
        {
            string sql = "CREATE TABLE AnalysisPoints(";
            string sPrimary = ", PRIMARY KEY( ";

            //第1列， ID，是主键
            sql = sql + "ID INTEGER";
            //sPrimary = sPrimary + "ID";

            //第2列， ImageID，是主键
            sql = sql + ",";
            sql = sql + "ImageID INTEGER";

            //第3列，Name
            sql = sql + ",";
            sql = sql + "Name TEXT";

            //第4列，EDSImagePath
            sql = sql + ",";
            sql = sql + "EDSImagePath TEXT";

            //第5列，HavePoints
            sql = sql + ",";
            sql = sql + "HavePoints INTEGER";

            //第6列，PointsNum
            sql = sql + ",";
            sql = sql + "PointsNum INTEGER";

            //第7列，HaveAreas
            sql = sql + ",";
            sql = sql + "HaveAreas INTEGER";

            //第8列，AreasNum
            sql = sql + ",";
            sql = sql + "AreasNum INTEGER";

            //sPrimary = sPrimary + ")";
            //sql = sql + sPrimary + ")";
            sql = sql + ")";

            SQLiteCommand command = new SQLiteCommand(sql, m_db);
            command.ExecuteNonQuery();
        }

        //XrayData表
        public void CreateXrayDataTable()
        {
            string sql = "CREATE TABLE XrayData(";
            string sPrimary = ", PRIMARY KEY( ";

            //第1列， ID，是主键
            sql = sql + "ID INTEGER";
            sPrimary = sPrimary + "ID";

            //第2列，APID
            sql = sql + ",";
            sql = sql + "APID INTEGER";
            sPrimary = sPrimary + ", APID";

            //第3列，图像ID
            sql = sql + ",";
            sql = sql + "ImageID INTEGER";

            //第4列，Type
            sql = sql + ",";
            sql = sql + "Type INTEGER";

            //第5列，X
            sql = sql + ",";
            sql = sql + "X INTEGER";

            //第6列，Y
            sql = sql + ",";
            sql = sql + "Y INTEGER";

            //第7列，AreasID
            sql = sql + ",";
            sql = sql + "AreasID INTEGER";

            //第8列，ElementNum
            sql = sql + ",";
            sql = sql + "ElementNum INTEGER";

            //第9列，XrayData
            sql = sql + ",";
            sql = sql + "XrayData BLOB";

            sPrimary = sPrimary + ")";
            sql = sql + sPrimary + ")";

            SQLiteCommand command = new SQLiteCommand(sql, m_db);
            command.ExecuteNonQuery();
        }

        //创建元素表
        public void CreateElementTable()
        {
            string sql = "CREATE TABLE Element(";
            string sPrimary = ", PRIMARY KEY( ";

            //第1列， ID，是主键
            sql = sql + "XayID INTEGER";
            sPrimary = sPrimary + "XayID";

            //第3列，ElementNum，是主键
            sql = sql + ",";
            sql = sql + "ElementNum INTEGER";
            sPrimary = sPrimary + ", ElementNum";

            //第4列，ElementID，是主键
            sql = sql + ",";
            sql = sql + "ElementID INTEGER";
            sPrimary = sPrimary + ", ElementID";

            //第5列，Name
            sql = sql + ",";
            sql = sql + "Name TEXT";

            //第6列，Percent
            sql = sql + ",";
            sql = sql + "Percent FLOAT";

            sPrimary = sPrimary + ")";
            sql = sql + sPrimary + ")";

            SQLiteCommand command = new SQLiteCommand(sql, m_db);
            command.ExecuteNonQuery();
        }

        //创建位置信息表
        public void CreateAreaPositionTable()
        {
            string sql = "CREATE TABLE AreaPosition(";
            string sPrimary = ", PRIMARY KEY( ";

            //第1列， ID，是主键
            sql = sql + "XrayID INTEGER";
            sPrimary = sPrimary + "XrayID";

            //第2列，AID
            sql = sql + ",";
            sql = sql + "AID INTEGER";
            sPrimary = sPrimary + ", AID";

            //第3列，SegNum
            sql = sql + ",";
            sql = sql + "SegNum INTEGER";
            sPrimary = sPrimary + ", SegNum";

            //第4列，SegID
            sql = sql + ",";
            sql = sql + "SegID INTEGER";
            sPrimary = sPrimary + ", SegID";

            //第5列，X
            sql = sql + ",";
            sql = sql + "X INTEGER";

            //第6列，Y
            sql = sql + ",";
            sql = sql + "Y INTEGER";

            //第7列，Length
            sql = sql + ",";
            sql = sql + "Length INTEGER";

            sPrimary = sPrimary + ")";
            sql = sql + sPrimary + ")";

            SQLiteCommand command = new SQLiteCommand(sql, m_db);
            command.ExecuteNonQuery();
        }

        public bool InsertAnylysisField(int PointsId, string PointName, string EDSImagePath, int PointsNum, int AreasNum, int ImageID)
        {
            //开启一个事务
            using (SQLiteTransaction tr = m_db.BeginTransaction())
            {
                //向分析点表插入数据
                string sql = "INSERT INTO AnalysisPoints(ID, ImageID, Name, EDSImagePath, HavePoints, PointsNum, HaveAreas, AreasNum) values ("
                    + PointsId.ToString() + ","
                    + ImageID.ToString() + ","
                    + "'" + PointName + "'" + ","
                    + "'" + EDSImagePath + "'" + ","
                    + Convert.ToInt32(Convert.ToBoolean(PointsNum)).ToString() + ","
                    + PointsNum.ToString() + ","
                    + Convert.ToInt32(Convert.ToBoolean(AreasNum)).ToString() + ","
                    + AreasNum.ToString() + ")";
             
               
                SQLiteCommand command = new SQLiteCommand(sql, m_db);
                command.ExecuteNonQuery();
                tr.Commit();
            }
            return true;
        }

        //插入一条点扫描Xray的信息
        public bool InsertAPointXray(int PointsId, int XrayId, int X, int Y, long[] XrayData, Dictionary<string, double> listElement, int ImageID)
        {
            byte[] bArray = new byte[8000];
            for (int a = 0; a < 2000; a++)
            {
                byte[] temps = BitConverter.GetBytes(XrayData[a]);
                bArray[a * 4] = temps[0];
                bArray[a * 4 + 1] = temps[1];
                bArray[a * 4 + 2] = temps[2];
                bArray[a * 4 + 3] = temps[3];
            }
            //开启一个事务
            //using (SQLiteTransaction tr = m_db.BeginTransaction())
            //{
            //向能谱数据表插入数据
            string sql = "INSERT INTO XrayData(ID, APID, ImageID, Type, X, Y, AreasID, ElementNum, XrayData) values ("
                + XrayId.ToString() + ","
                + PointsId.ToString() + ","
                + ImageID.ToString() + ","
                + 0.ToString() + ","
                + X.ToString() + ","
                + Y.ToString() + ","
                + (-1).ToString() + ","
                + listElement.Count.ToString() + ",:XrayData)";
            PoinXraybyte.Add(bArray);
            PoinXraystring.Add(sql);
            //SQLiteCommand command = new SQLiteCommand(sql, m_db);
            //command.Parameters.Add("XrayData", System.Data.DbType.Binary).Value = bArray;  // BLOB
            //command.ExecuteNonQuery();
            //向能谱元素表插入数据
            int i = 1;
            foreach (KeyValuePair<string, double> element in listElement)
            {
                sql = "INSERT INTO Element(XayID, ElementNum, ElementID, Name, Percent) values ("
                   + XrayId.ToString() + ","
                   + listElement.Count.ToString() + ","
                   + i.ToString() + ",'"
                   + element.Key + "',"
                   + element.Value.ToString() + ")";
                PoinXray.Add(sql);
                //command = new SQLiteCommand(sql, m_db);
                //command.ExecuteNonQuery();
                i++;
            }
            //向位置表插入数据
            sql = "INSERT INTO AreaPosition(XrayID, AID, SegNum, SegID, X, Y, Length) values ("
               + XrayId.ToString() + ","
               + PointsId.ToString() + ","
               + (-1).ToString() + ","
               + (-1).ToString() + ","
               + X.ToString() + ","
               + Y.ToString() + ","
               + (0).ToString() + ")";
            PoinXray.Add(sql);
            //command = new SQLiteCommand(sql, m_db);
            //command.ExecuteNonQuery();
            //tr.Commit();
            Thread.Sleep(200);
            //}
            return true;
        }

        //插入一条面扫描Xray的信息
        public bool InsertAAreaXay(int PointsId, int XrayId, int AreaId, List<Segment> listSeg, long[] XrayData, Dictionary<string, double> listElement, int ImageID)
        {
            byte[] bArray = new byte[8000];

            for (int i = 0; i < 2000; i++)
            {
                byte[] temps = BitConverter.GetBytes(XrayData[i]);
                bArray[i*4] = temps[0];
                bArray[i*4 + 1] = temps[1];
                bArray[i*4 + 2] = temps[2];
                bArray[i*4 + 3] = temps[3];
            }
            //开启一个事务
            using (SQLiteTransaction tr = m_db.BeginTransaction())
            {
                //向能谱数据表插入数据
                string sql = "INSERT INTO XrayData(ID, APID, ImageID, Type, X, Y, AreasID, ElementNum, XrayData) values ("
                    + XrayId.ToString() + ","
                    + PointsId.ToString() + ","
                    + ImageID.ToString() + ","
                    + 1.ToString() + ","
                    + (-1).ToString() + ","
                    + (-1).ToString() + ","
                    + AreaId.ToString() + ","
                    + listElement.Count.ToString() + ",:XrayData)";

                SQLiteCommand command = new SQLiteCommand(sql, m_db);
                command.Parameters.Add("XrayData", System.Data.DbType.Binary).Value = bArray;  // BLOB
                command.ExecuteNonQuery();

                //向能谱元素表插入数据
                int i = 1;
                foreach (KeyValuePair<string, double> element in listElement)
                {

                    sql = "INSERT INTO Element(XayID, ElementNum, ElementID, Name, Percent) values ("
                       + XrayId.ToString() + ","
                       + listElement.Count.ToString() + ","
                       + i.ToString() + ",'"
                       + element.Key + "',"
                       + element.Value.ToString() + ")";

                    command = new SQLiteCommand(sql, m_db);
                    command.ExecuteNonQuery();
                    i++;
                }
                //向位置表插入数据

                i = 1;
                foreach (Segment seg in listSeg)
                {

                    sql = "INSERT INTO AreaPosition(XrayID, AID, SegNum, SegID, X, Y, Length) values ("
                       + XrayId.ToString() + ","
                       + PointsId.ToString() + ","
                       + listSeg.Count.ToString() + ","
                       + i.ToString() + ","
                       + seg.X.ToString() + ","
                       + seg.Y.ToString() + ","
                       + seg.Length.ToString() + ")";

                    command = new SQLiteCommand(sql, m_db);
                    command.ExecuteNonQuery();
                    i++;
                }
                tr.Commit();
            }
            return true;
        }

        public bool SaveToMemory(int PointsId, int XrayId, int AreaId, List<Segment> listSeg, long[] XrayData, Dictionary<string, double> listElement, int ImageID)
        {
            byte[] bArray = new byte[8000];
            for (int a = 0; a < 2000; a++)
            {
                byte[] temps = BitConverter.GetBytes(XrayData[a]);
                bArray[a * 4] = temps[0];
                bArray[a * 4 + 1] = temps[1];
                bArray[a * 4 + 2] = temps[2];
                bArray[a * 4 + 3] = temps[3];
            }
            //向能谱数据表插入数据
            string sql = "INSERT INTO XrayData(ID, APID, ImageID, Type, X, Y, AreasID, ElementNum, XrayData) values ("
                + XrayId.ToString() + ","
                + PointsId.ToString() + ","
                + ImageID.ToString() + ","
                + 1.ToString() + ","
                + (-1).ToString() + ","
                + (-1).ToString() + ","
                + AreaId.ToString() + ","
                + listElement.Count.ToString() + ",:XrayData)";
            LineXraystring.Add(sql);
            LineXraybyte.Add(bArray);
            //向能谱元素表插入数据
            int i = 1;
            foreach (KeyValuePair<string, double> element in listElement)
            {
                sql = "INSERT INTO Element(XayID, ElementNum, ElementID, Name, Percent) values ("
                   + XrayId.ToString() + ","
                   + listElement.Count.ToString() + ","
                   + i.ToString() + ",'"
                   + element.Key + "',"
                   + element.Value.ToString() + ")";
                LineXray.Add(sql);
                i++;
            }
            //向位置表插入数据
            i = 1;
            foreach (Segment seg in listSeg)
            {
                sql = "INSERT INTO AreaPosition(XrayID, AID, SegNum, SegID, X, Y, Length) values ("
                   + XrayId.ToString() + ","
                   + PointsId.ToString() + ","
                   + listSeg.Count.ToString() + ","
                   + i.ToString() + ","
                   + seg.X.ToString() + ","
                   + seg.Y.ToString() + ","
                   + seg.Length.ToString() + ")";
                LineXray.Add(sql);
                i++;
            }
            return true;
        }
        public bool PointDataSubmitted()
        {
            using (SQLiteTransaction tr = m_db.BeginTransaction())
            {
                for (int i = 0; i < PoinXraystring.Count(); i++)
                {
                    SQLiteCommand command = new SQLiteCommand(PoinXraystring[i], m_db);
                    command.Parameters.Add("XrayData", System.Data.DbType.Binary).Value = PoinXraybyte[i];  // BLOB
                    command.ExecuteNonQuery();
                }
                for (int i = 0; i < PoinXray.Count(); i++)
                {
                    SQLiteCommand command = new SQLiteCommand(PoinXray[i], m_db);
                    command.ExecuteNonQuery();
                }
                tr.Commit();
            }
            PoinXraystring.Clear();
            PoinXraybyte.Clear();
            PoinXray.Clear();
            return true;
            
        }
        public bool LineDataSubmitted()
        {
            using (SQLiteTransaction tr = m_db.BeginTransaction())
            {
                for (int i = 0; i < LineXraystring.Count(); i++)
                {
                    SQLiteCommand command = new SQLiteCommand(LineXraystring[i], m_db);
                    command.Parameters.Add("XrayData", System.Data.DbType.Binary).Value = LineXraybyte[i];  // BLOB
                    command.ExecuteNonQuery();
                }
                for (int i = 0; i < LineXray.Count(); i++)
                {
                    SQLiteCommand command = new SQLiteCommand(LineXray[i], m_db);
                    command.ExecuteNonQuery();
                }
                tr.Commit();
            }
            LineXraystring.Clear();
            LineXraybyte.Clear();
            LineXray.Clear();
            return true;
        }
    }

    
}