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; //测量文件

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

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

        //创建一个空的数据库
        public void CreateDB()
        {
            string path = Path.GetDirectoryName(m_measureFile.FileName);
            path = "D:\\HOZ\\test\\MeasureDB.db";

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

            m_db = new SQLiteConnection("data source =" + path);
            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 InsetAPoint(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 InsertAPointXay(int PointsId, int XrayId, int X, int Y, 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() + ","
                    + 0.ToString() + ","
                    + X.ToString() + ","
                    + Y.ToString() + ","
                    + (-1).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++;
                }

                tr.Commit();

                Thread.Sleep(500);
            }
            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] = temps[0];
                bArray[i + 1] = temps[1];
                bArray[i + 2] = temps[2];
                bArray[i + 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() + ","
                       + AreaId.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;
        }
    }
}