OTS/OTSPartA_STDEditor/BaseClass/SqlLiteClass.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Collections;

namespace OTSPartA_STDEditor
{
    /// <summary>
    /// 用于标准库模块操作SQLlite系统标准库的封装类
    /// </summary>
    public class SqlLiteClass
    {

        #region 相关变量
        //系统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 = "OTSCleanlinesSTD.db";
        //系统STD文件 表名
        private const string m_STDTableName = "STDMinerals";
        //系统ElementRange表名
        private const string m_ElementRangeTableName = "MineralElements";
        #endregion

        #region SQLLite需要的对象
        /// <summary>
        /// 数据库连接定义
        /// </summary>
        private SQLiteConnection m_dbConnection;


        /// <summary>
        /// 适配器
        /// </summary>
        private SQLiteDataAdapter m_dataAdapter;

        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public SqlLiteClass()
        {
            try
            {
                m_dbConnection = new SQLiteConnection("data source='"  + m_STDDBPath  + m_STDDBName + "'");
                m_dbConnection.Open();
            }
            catch (Exception ee)
            {
                string str = ee.ToString();
            }
        }
        public string GetSTDTableName() {
            return m_STDTableName;

        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString"></param>
        public SqlLiteClass(string connectionString)
        {
            try
            {
                m_dbConnection = new SQLiteConnection("data source='"+connectionString + "'");
                m_dbConnection.Open();
            }
            catch (Exception)
            {
            }
        }
        #endregion

        #region 封装方法
        /// <summary>
        /// 传入SQL语句，从指定的数据库，进行查询，并返回DataTable
        /// </summary>
        /// <param name="in_str_sql"></param>
        /// <returns></returns>
        public DataTable GetDTFormSysSTDBySQLString(string in_str_sql)
        {
            DataTable ret_dt = new DataTable();

            try
            {
                m_dataAdapter = new SQLiteDataAdapter(in_str_sql, m_dbConnection);
                DataSet ds = new DataSet();
                m_dataAdapter.Fill(ds);
                ret_dt = ds.Tables[0];
            }
            catch (Exception ee)
            {
                string str = ee.ToString();
            }

            return ret_dt;
        }

        /// <summary>
        /// 执行修改操作
        /// </summary>
        /// <param name="in_str_sql">执行语句</param>
        /// <returns>bool</returns>
        public bool UpdateSysDataDB(string in_str_sql)
        {
            try
            {
                SQLiteCommand sQLiteCommand = new SQLiteCommand(in_str_sql, m_dbConnection);
                int result = sQLiteCommand.ExecuteNonQuery();
                return result > 0;
            }
            catch (Exception )
            {
                return false;
            }
        }

        public bool ExecuteBlob(string strsql, string strvaluename, byte[] blobdata, int idatalen)
        {
            try
            {
                using (SQLiteCommand sqlComm = new SQLiteCommand(strsql, m_dbConnection))
                {
                    SQLiteParameter para = new SQLiteParameter(strvaluename, DbType.Binary);
                    para.Value = blobdata;
                    sqlComm.Parameters.Add(para);
                    return sqlComm.ExecuteNonQuery()>0;
                }
            }
            catch /*(SQLiteException ex)*/
            {
                return false;
            }
        }

        /// <summary>
        /// 接收指定数据库对象和sql查询语句的方法，返回datatable对象
        /// </summary>
        /// <param name="in_str_conn">指定数据库对象，比如SySSTDData.db</param>
        /// <param name="in_str_sql">传入的SQL查询语句</param>
        /// <returns></returns>
        public DataTable GetDTFormSysSTDByDBNameAndSQLString(string in_str_conn,string in_str_sql)
        {
            try
            {
                m_dbConnection = new SQLiteConnection("data source='" + m_STDDBPath + "\\" + in_str_conn + "'");
                m_dbConnection.Open();
            }
            catch (Exception ee)
            {
                string str = ee.ToString();
            }

            DataTable ret_dt = new DataTable();
            try
            {
                m_dataAdapter = new SQLiteDataAdapter(in_str_sql, m_dbConnection);
                DataSet ds = new DataSet();
                m_dataAdapter.Fill(ds);
                ret_dt = ds.Tables[0];
            }
            catch (Exception ee)
            {
                string str = ee.ToString();
            }

            return ret_dt;
        }

        public bool AddSTDAndElementInfo(ArrayList SQLStringList)
        {
            SQLiteTransaction trans = m_dbConnection.BeginTransaction();
            try
            {  //Sql语句
                SQLiteCommand sQLiteCommand = new SQLiteCommand();
                sQLiteCommand.Connection = m_dbConnection;
                sQLiteCommand.Transaction = trans;
                int result = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        sQLiteCommand.CommandText = strsql;
                        result += sQLiteCommand.ExecuteNonQuery();
                    }
                }
                trans.Commit();//提交事务
                return result > 0;
            }
            catch (Exception)
            {
                trans.Rollback();//回滚事务
                return false;
            }
        }

        public bool DelSTDInfo(ArrayList SQLStringList)
        {
            SQLiteTransaction trans = m_dbConnection.BeginTransaction();
            try
            {  //Sql语句
                SQLiteCommand sQLiteCommand = new SQLiteCommand();
                sQLiteCommand.Connection = m_dbConnection;
                sQLiteCommand.Transaction = trans;
                int result = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        sQLiteCommand.CommandText = strsql;
                        result += sQLiteCommand.ExecuteNonQuery();
                    }
                }
                trans.Commit();//提交事务
                return result > 0;
            }
            catch (Exception)
            {
                trans.Rollback();//回滚事务
                return false;
            }
        }

        public void CloseDB()
        {
            m_dbConnection.Close();
        }
        #endregion

    }


}