OTS/OTSIncAMeasureApp/ServiceCenter/DTLBase/CSQLiteDBStore.cs

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

namespace OTSModelSharp.DTLBase
{
   public class CSQLiteDBStore : IDBStoreBase,IDBFileBase
    {
        SQLiteHelper helper;
        NLog.Logger log ;
        string m_DBFileName;
        SQLiteConnection connForTrans;
        SQLiteCommand cmdForTrans;
        SQLiteTransaction trans;
        public CSQLiteDBStore(string dbFileName)
        {
            log = NLog.LogManager.GetCurrentClassLogger();
            helper = new SQLiteHelper(dbFileName);
            connForTrans = helper.GetDBConnection();
           
            m_DBFileName = dbFileName;
        }
        public CSQLiteDBStore()
        {
            
        }
        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 void Close()
        {
            connForTrans.Close();
        }

        public bool CloseSynchronous()
        {
            if (connForTrans.State != ConnectionState.Open)
            {
                connForTrans.Open();
            }
            var cmd = new SQLiteCommand(connForTrans);
            cmd.CommandText = "pragma synchronous = 0;";
            cmd.ExecuteNonQuery();
            //RunCommand("pragma synchronous = 0; "); //cSQLiteStore.CloseSynchronous();//  PRAGMA synchronous = OFF;
            return true;
        }



        public bool CommitTransaction()
        {
            
            //RunCommandForTrans("commit transaction;",connForTrans); //cSQLiteStore.CloseSynchronous();
            trans.Commit();
            connForTrans.Close();
            return true;
        }

        public bool Create(string a_sFileName, bool a_bOverwrite)
        {
            if (System.IO.File.Exists(a_sFileName))
            {
                helper = new SQLiteHelper(a_sFileName );
                return true;
            }
            else
            {
                SQLiteConnection.CreateFile(a_sFileName);
                //conStr = a_sFileName;
                helper = new SQLiteHelper(a_sFileName );
                return true;
            }
        }

        public bool DeleteTable(string a_sTableName)
        {
            if (!IsTableExists(a_sTableName))
            {
                return true;
            
            }

            string sSQLCommand ;
            sSQLCommand=string.Format("DROP TABLE \'{0}\'", a_sTableName);

            return RunCommand(sSQLCommand);
           
        }

        public string GetFileName()
        {
            return m_DBFileName;
        }

        public long GetLastRowId(string a_sTableName = "")
        {
            string sSQLCommand;
            sSQLCommand=string.Format("SELECT max(ROWID) FROM {0}", a_sTableName);
           var nRet =helper.ExecuteQuery(sSQLCommand);
            return (long)nRet.Columns[0].DefaultValue;
        }

        public List<string> GetTableList()
        {
            List<string> tableList=new List<string>();

            string sSQLCommand=string.Format("SELECT name FROM sqlite_master WHERE type = \'table\';");
            var query = helper.ExecuteQuery(sSQLCommand);
            for (int i = 0; i < query.Rows.Count; i++)
            {
                tableList.Add(query.Rows[i][0].ToString());
            
            }
            return tableList;
        }

        public bool InsertBlobData(string szSql, byte[] pBlobData, int iBlobDataLen)
        {
       
            //byte[] data = new byte[iBlobDataLen];
            if (connForTrans.State == ConnectionState.Open)
            {
              
                cmdForTrans.CommandText = szSql; //"INSERT INTO sequence(name,type,code,defaultParFile) VALUES (@name,@type,@code,@defaultFile)";
                cmdForTrans.Parameters.Clear();
                cmdForTrans.Parameters.AddWithValue(@"blob", pBlobData);


                cmdForTrans.ExecuteNonQuery();

            }
            else
            {
                using (var dbconnection = helper.GetDBConnection())
                {
                    dbconnection.Open();
                    SQLiteCommand sqlCommand = new SQLiteCommand();
                    sqlCommand.Connection = dbconnection;
                    sqlCommand.CommandText = szSql; //"INSERT INTO sequence(name,type,code,defaultParFile) VALUES (@name,@type,@code,@defaultFile)";
                    sqlCommand.Parameters.Clear();
                    sqlCommand.Parameters.AddWithValue(@"blob", pBlobData);


                    sqlCommand.ExecuteNonQuery();
                }

            }
            
            return true;
        }

      

        public bool IsTableExists(string a_sTableName)
        {
           return helper.IsExist(a_sTableName);
        }

        public bool Open(string a_sFileName, bool a_bForce)
        {
            try
            {
                m_DBFileName = a_sFileName;
                helper = new SQLiteHelper(a_sFileName);
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public System.Data.DataTable QueryByCmdForDataTable(string a_sQueryString)
        {
            var t = helper.ExecuteQuery(a_sQueryString);
            return t;
        }

        public IDBQueryBase QueryByCommand(string a_sQueryString)
        {
            var t=helper.ExecuteReader(a_sQueryString);
            return new CSQLiteQuery(t);
        }

        public IDBQueryBase QueryByTableName(string a_sTableName, string a_sOrderColumnName="")
        {
            string sSQLCommand;
            if (a_sOrderColumnName!="")
            {
                sSQLCommand=string.Format("SELECT * FROM \'{0}\' ORDER BY \'{1}\'", a_sTableName, a_sOrderColumnName);
            }
            else
            {
                sSQLCommand=string.Format("SELECT * FROM \'{0}\'", a_sTableName);
            }

            return QueryByCommand(sSQLCommand);
        }

        public System.Data.DataTable QueryByTableNameForDataTable(string a_sTableName, string a_sOrderColumnName)
        {
            string sSQLCommand;
            if (a_sOrderColumnName != "")
            {
                sSQLCommand = string.Format("SELECT * FROM \'{0}\' ORDER BY \'{1}\'", a_sTableName, a_sOrderColumnName);
            }
            else
            {
                sSQLCommand = string.Format("SELECT * FROM \'{0}\'", a_sTableName);
            }

            return QueryByCmdForDataTable(sSQLCommand);
        }

        public bool RunCommand(string a_sCommandString, bool a_bIgnoreExist = false)
        {
           
                if (connForTrans.State == ConnectionState.Open)
                {
                //RunCommandForTrans(a_sCommandString);
                cmdForTrans.CommandText = a_sCommandString;
             
                cmdForTrans.ExecuteNonQuery();
                //log.Info("transaction excute!");
                }
                else 
                {
                    helper.ExecuteNonQuery(a_sCommandString);
                }
               
                return true;
            
           
        }
        private bool RunCommandForTrans(string a_sCommandString, bool a_bIgnoreExist = false)
        {
           
                helper.ExecuteNonQueryForTransaction(cmdForTrans,a_sCommandString);
                return true;
           

        }
        public void ExecuteNonQueryBatch(ref List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            helper.ExecuteNonQueryBatch(ref list);
        }

    }
}