OTS/OTSCommon/SQliteManageClass.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using System.IO;

using System.Windows.Forms;

namespace OTSCommon
{
    public class SQliteManageClass
    {
        private List<string> IncAId = new List<string>();
        private SqLiteHelper sql = null;
        //系统STD文件路径
        static string RunPath = Application.StartupPath;
        static string str = "\\Config\\SysData";
        static string m_ParameterPath = RunPath + str;
        private string m_STDDBPath = m_ParameterPath;
      
        //系统STD文件 数据库名称
        private string m_STDDBName = "SySSTDData.db";
        //系统STD文件 表名
        private const string m_STDTableName = "IncALib";

        //测量结果本地数据库名称
        private string m_MeasureResultInclusionDBName = "Inclution.db";
        //夹杂物表名称
        private string m_MeasureResultInclusionTableName = "IncAData";

        #region 通过FieldID、XrayID 获取归属元素名称
        /// <summary>
        /// 通过FieldID、XrayID 获取归属元素名称
        /// </summary>
        /// <param name="FieldID"></param>
        /// <param name="XrayID"></param>
        /// <param name="MeasureResultFilePath">测量结果文件路径</param>
        /// <param name="STDDBName">标准库名称</param>
        /// <returns>元素名称</returns>
        public string GetSySSTDData1IncALibName(int FieldID, int XrayID, string MeasureResultFilePath, string STDDBName)
        {
            try
            {
                //设置全局STD名称变量
                m_STDDBName = STDDBName;
                string IncAName = string.Empty;
                //需要循环文件夹判断在哪一个库Inclution.db 获取IncAId
                //判断当前路径下是否存在FIELD_FILES文件夹，返回数据文件路径
                DataTable sqldt = ReturnDataInFilePath(FieldID, XrayID, MeasureResultFilePath);
                if (sqldt != null)
                {
                    if (sqldt.Rows.Count > 0)
                    {
                        sql = new SqLiteHelper("data source='" + m_STDDBPath + "\\" + STDDBName + "'");
                        //通过元素编号获取元素名称
                        string IncAlibSqlString = CreateIncAlibQuerySql(sqldt.Rows[0]["IncAId"].ToString());
                        sqldt = sql.ExecuteQuery(IncAlibSqlString);
                        if (sqldt != null)
                        {
                            if (sqldt.Rows.Count > 0)
                            {
                                IncAName = sqldt.Rows[0]["Name"].ToString();
                            }
                        }
                    }
                }
                return IncAName;
            }
            catch (Exception)
            {
                return "";
            }
        }
        #endregion
        
        #region 创建查询语句 
        /// <summary>
        /// 创建查询分析Xray sql语句
        /// </summary>
        /// <param name="FieldID"></param>
        /// <param name="XrayID"></param>
        /// <returns></returns>
        protected string CreateInclusionQuerySql(int FieldID, int XrayID)
        {
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append("select IncAId");
            sqlString.Append("  from " + m_MeasureResultInclusionTableName + "");
            sqlString.Append(" where [FieldId] = '" + FieldID + "'");
            sqlString.Append("   and [XrayId] = '" + XrayID + "'");
            return sqlString.ToString();
        }
        /// <summary>
        /// 创建查询元素信息 sql语句
        /// </summary>
        /// <param name="IncAId"></param>
        /// <returns>IncAName</returns>
        protected string CreateIncAlibQuerySql(string IncAId)
        {
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append("select Name");
            sqlString.Append("  from " + m_STDTableName + "");
            sqlString.Append(" where IncAId = '" + IncAId + "'");
            return sqlString.ToString();
        }
        #endregion

        #region 判断当前路径下是否存在FIELD_FILES文件夹，返回数据文件路径
        public DataTable ReturnDataInFilePath(int FieldID, int XrayID, string m_FilePath)
        {
            DataTable dt = new DataTable();
            DirectoryInfo directory = new DirectoryInfo(m_FilePath);
            if (directory.Exists)
            {
                FileInfo fileInfo = new FileInfo(m_FilePath + "\\" + m_MeasureResultInclusionDBName);
                if (fileInfo.Exists)
                {
                    //查询信息
                    sql = new SqLiteHelper("data source='" + m_FilePath + "\\" + m_MeasureResultInclusionDBName + "'");
                    string InclusionSqlString = CreateInclusionQuerySql(FieldID, XrayID);
                    DataTable sqldt = sql.ExecuteQuery(InclusionSqlString);
                    if (sqldt != null)
                    {
                        if (sqldt.Rows.Count>0)
                        {
                            dt = sqldt;
                            return dt;
                        }
                        else
                        {
                            dt = ReturnDataInFilePath(FieldID, XrayID, m_FilePath + "\\FIELD_FILES");
                        }
                    }
                    else
                    {
                        dt = ReturnDataInFilePath(FieldID, XrayID, m_FilePath + "\\FIELD_FILES");
                    }
                }
            }
            return dt;
        }
        #endregion

        /// <summary>
        /// 创建查询元素信息 sql语句
        /// </summary>
        /// <param name="IncAId"></param>
        /// <returns>IncAName</returns>
        public string GetNameByIncAId(string IncAId)
        {
            string name = string.Empty;
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append("select Name");
            sqlString.Append("  from " + m_STDTableName + "");
            sqlString.Append(" where IncAId = '" + IncAId + "'");

            DataTable dt = new DataTable();
            sql = new SqLiteHelper("data source='" + m_STDDBPath + "\\" + m_STDDBName + "'");
            dt = sql.ExecuteQuery(sqlString.ToString());
            if (dt.Rows.Count > 0)
            {
                name = dt.Rows[0]["Name"].ToString();
            }
            return name;
        }

    }
}