SQLite Dataset Helper Class

namespace LiuHarry.Utils.DB
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;
    using System.Runtime.InteropServices;
    using System.Text;

    public class SqliteDataSetHelper : IDisposable
    {
        private readonly string DELETED_FLAG;
        private bool disposed;
        private readonly string INSERTED_FLAG;
        private string m_ConnectionString;
        private bool m_LogSqlExecute;
        private bool m_SelfBuildConnection;
        private SQLiteConnection m_SqlConn;
        private SqlExecuteTracer m_SqlExecuteTracer;
        private readonly string MODIFIED_FLAG;
        private readonly string PARAMETER_NAME_PREFIX;
        private readonly string PARAMETER_PREFIX;

        public SqliteDataSetHelper()
        {
            this.m_LogSqlExecute = false;
            this.m_SelfBuildConnection = true;
            this.DELETED_FLAG = "DELETED_FLAG";
            this.INSERTED_FLAG = "INSERTED_FLAG";
            this.MODIFIED_FLAG = "MODIFIED_FLAG";
            this.PARAMETER_PREFIX = "@";
            this.PARAMETER_NAME_PREFIX = "@";
            this.disposed = false;
            this.m_SqlExecuteTracer = new SqlExecuteTracer();
        }

        public SqliteDataSetHelper(SQLiteConnection conn) : this()
        {
            if (conn != null)
            {
                this.m_SelfBuildConnection = false;
                this.m_SqlConn = conn;
            }
        }

        public SqliteDataSetHelper(string connectionString) : this()
        {
            this.m_ConnectionString = connectionString;
        }

        private void ActivateConnection()
        {
            if (this.m_SqlConn == null)
            {
                this.m_SqlConn = new SQLiteConnection(this.m_ConnectionString);
                this.m_SelfBuildConnection = true;
            }
        }

        public void DeleteRows(List<DataRow> listRows, string tableName, string keyFieldName)
        {
            foreach (DataRow row in listRows)
            {
                this.SetToDeleteState(row);
                this.InnerDeleteRow(row, tableName, keyFieldName);
            }
        }

        public void DeleteRows(List<long> listRowKeyFieldValue, string tableName, string keyFieldName)
        {
            foreach (int num in listRowKeyFieldValue)
            {
                this.InnerDeleteRowByKeyField(num, tableName, keyFieldName, typeof(long));
            }
        }

        public void DeleteRows(List<string> listRowKeyFieldValue, string tableName, string keyFieldName)
        {
            foreach (string str in listRowKeyFieldValue)
            {
                this.InnerDeleteRowByKeyField(str, tableName, keyFieldName, typeof(string));
            }
        }

        public void DeleteRows(DataRow row, string tableName, string keyFieldName)
        {
            this.SetToDeleteState(row);
            this.InnerDeleteRow(row, tableName, keyFieldName);
        }

        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                this.TryCloseConnection();
            }
            this.disposed = true;
        }

        public int ExecCommand(SQLiteCommand sqlcom)
        {
            int num2;
            this.ActivateConnection();
            sqlcom.Connection = this.m_SqlConn;
            try
            {
                this.TryOpenConnection();
                this.m_SqlExecuteTracer.TraceSqlToLog(sqlcom);
                num2 = sqlcom.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                this.m_SqlExecuteTracer.TraceErrorSqlToLog(sqlcom, exception.Message);
                throw exception;
            }
            finally
            {
                this.TryCloseConnection();
            }
            return num2;
        }

        public int ExecCommand(string sql)
        {
            if (sql.EndsWith(","))
            {
                sql = sql.Substring(0, sql.Length - 1);
            }
            SQLiteCommand sqlcom = new SQLiteCommand(sql);
            return this.ExecCommand(sqlcom);
        }

        public SQLiteDataReader ExecDataReader(SQLiteCommand sqlcom)
        {
            SQLiteDataReader reader;
            this.ActivateConnection();
            sqlcom.Connection = this.m_SqlConn;
            try
            {
                this.TryOpenConnection();
                this.m_SqlExecuteTracer.TraceSqlToLog(sqlcom);
                reader = sqlcom.ExecuteReader();
            }
            catch (Exception exception)
            {
                this.m_SqlExecuteTracer.TraceErrorSqlToLog(sqlcom, exception.Message);
                throw exception;
            }
            finally
            {
                this.TryCloseConnection();
            }
            return reader;
        }

        public SQLiteDataReader ExecDataReader(string sqlSelect)
        {
            if (sqlSelect.EndsWith(","))
            {
                sqlSelect = sqlSelect.Substring(0, sqlSelect.Length - 1);
            }
            SQLiteCommand sqlcom = new SQLiteCommand(sqlSelect);
            return this.ExecDataReader(sqlcom);
        }

        public DataTable ExecDataTable(SQLiteCommand sqlcom)
        {
            DataTable table2;
            DataTable dataTable = new DataTable();
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlcom);
            this.ActivateConnection();
            sqlcom.Connection = this.m_SqlConn;
            try
            {
                this.TryOpenConnection();
                this.m_SqlExecuteTracer.TraceSqlToLog(sqlcom);
                adapter.Fill(dataTable);
                table2 = dataTable;
            }
            catch (Exception exception)
            {
                this.m_SqlExecuteTracer.TraceErrorSqlToLog(sqlcom, exception.Message);
                throw exception;
            }
            finally
            {
                this.TryCloseConnection();
            }
            return table2;
        }

        public DataTable ExecDataTable(string sqlSelect)
        {
            if (sqlSelect.EndsWith(","))
            {
                sqlSelect = sqlSelect.Substring(0, sqlSelect.Length - 1);
            }
            SQLiteCommand sqlcom = new SQLiteCommand(sqlSelect);
            return this.ExecDataTable(sqlcom);
        }

        public object ExecScalar(SQLiteCommand sqlcom)
        {
            object obj2;
            this.ActivateConnection();
            sqlcom.Connection = this.m_SqlConn;
            try
            {
                this.TryOpenConnection();
                this.m_SqlExecuteTracer.TraceSqlToLog(sqlcom);
                obj2 = sqlcom.ExecuteScalar();
            }
            catch (Exception exception)
            {
                this.m_SqlExecuteTracer.TraceErrorSqlToLog(sqlcom, exception.Message);
                throw exception;
            }
            finally
            {
                this.TryCloseConnection();
            }
            return obj2;
        }

        public object ExecScalar(string sqlSelect)
        {
            if (sqlSelect.EndsWith(","))
            {
                sqlSelect = sqlSelect.Substring(0, sqlSelect.Length - 1);
            }
            SQLiteCommand sqlcom = new SQLiteCommand(sqlSelect);
            return this.ExecScalar(sqlcom);
        }

        ~SqliteDataSetHelper()
        {
            this.Dispose(false);
        }

        public DateTime GetDbTime()
        {
            string commandText = "select datetime('NOW')  ";
            SQLiteCommand sqlcom = new SQLiteCommand(commandText);
            return (DateTime) this.ExecScalar(sqlcom);
        }

        private DbType GetDbType(Type type)
        {
            DbType guid = DbType.String;
            if (type.Equals(typeof(int)) || type.IsEnum)
            {
                return DbType.Int32;
            }
            if (type.Equals(typeof(long)))
            {
                return DbType.Int32;
            }
            if (type.Equals(typeof(double)) || type.Equals(typeof(double)))
            {
                return DbType.Decimal;
            }
            if (type.Equals(typeof(DateTime)))
            {
                return DbType.DateTime;
            }
            if (type.Equals(typeof(bool)))
            {
                return DbType.Boolean;
            }
            if (type.Equals(typeof(string)))
            {
                return DbType.String;
            }
            if (type.Equals(typeof(decimal)))
            {
                return DbType.Decimal;
            }
            if (type.Equals(typeof(byte[])))
            {
                return DbType.Binary;
            }
            if (type.Equals(typeof(Guid)))
            {
                guid = DbType.Guid;
            }
            return guid;
        }

        public long GetMaxID(string primaryKeyField, string tableName)
        {
            SQLiteCommand sqlcom = new SQLiteCommand("Select Max(" + primaryKeyField + ") from " + tableName);
            return (long) this.ExecScalar(sqlcom);
        }

        private void InnerDeleteRow(DataRow dr, string TableName, string keyFieldName)
        {
            string format = "Delete from {0} where {1} =" + this.PARAMETER_PREFIX + "{1}";
            DataTable table = dr.Table;
            SQLiteCommand sqlcom = new SQLiteCommand(string.Format(format, TableName, keyFieldName));
            IDataParameter parameter = new SQLiteParameter();
            parameter.ParameterName = this.PARAMETER_NAME_PREFIX + keyFieldName;
            parameter.DbType = this.GetDbType(table.Columns[keyFieldName].DataType);
            parameter.Value = dr[keyFieldName];
            sqlcom.Parameters.Add(parameter);
            this.ExecCommand(sqlcom);
        }

        private void InnerDeleteRowByKeyField(object RowKeyFieldValue, string tableName, string keyFieldName, Type keyFieldType)
        {
            SQLiteCommand sqlcom = new SQLiteCommand(string.Format("Delete from {0} where {1} =" + this.PARAMETER_PREFIX + "{1}", tableName, keyFieldName));
            IDataParameter parameter = new SQLiteParameter();
            parameter.ParameterName = this.PARAMETER_NAME_PREFIX + keyFieldName;
            parameter.DbType = this.GetDbType(keyFieldType);
            parameter.Value = RowKeyFieldValue;
            sqlcom.Parameters.Add(parameter);
            this.ExecCommand(sqlcom);
        }

        private void InnerInsertRow(DataRow dr, string TableName, string primaryKeyField, bool primaryKeyValueIsAutoGenerated, out long primaryKeyValue)
        {
            string format = "Insert into {0}({1}) values ({2})";
            SQLiteCommand sqlcom = new SQLiteCommand();
            DataTable table = dr.Table;
            StringBuilder builder = new StringBuilder();
            StringBuilder builder2 = new StringBuilder();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                if (!primaryKeyValueIsAutoGenerated || !(table.Columns[i].ColumnName == primaryKeyField))
                {
                    IDataParameter parameter = new SQLiteParameter();
                    parameter.ParameterName = this.PARAMETER_NAME_PREFIX + table.Columns[i].ColumnName;
                    parameter.DbType = this.GetDbType(table.Columns[i].DataType);
                    parameter.Value = dr[i];
                    sqlcom.Parameters.Add(parameter);
                    builder2.Append(table.Columns[i].ColumnName);
                    builder.Append(this.PARAMETER_PREFIX + table.Columns[i].ColumnName);
                    builder2.Append(",");
                    builder.Append(",");
                }
            }
            string str2 = builder2.ToString();
            str2 = str2.Substring(0, str2.Length - 1);
            string str3 = builder.ToString();
            str3 = str3.Substring(0, str3.Length - 1);
            string str4 = string.Format(format, TableName, str2, str3);
            sqlcom.CommandText = str4;
            this.ExecCommand(sqlcom);
            if (!primaryKeyValueIsAutoGenerated)
            {
                primaryKeyValue = 0L;
            }
            else
            {
                SQLiteCommand command2 = new SQLiteCommand("Select Max(" + primaryKeyField + ") from " + TableName);
                object obj2 = this.ExecScalar(command2);
                primaryKeyValue = (long) obj2;
            }
        }

        private void InnerModifyRow(DataRow dr, string TableName, string keyFieldName)
        {
            string format = "Update {0} set {1} {2}";
            string str2 = "{0}= " + this.PARAMETER_PREFIX + "{0}";
            string str3 = " Where {0}=" + this.PARAMETER_PREFIX + "{0}";
            StringBuilder builder = new StringBuilder();
            SQLiteCommand sqlcom = new SQLiteCommand();
            DataTable table = dr.Table;
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                IDataParameter parameter = new SQLiteParameter();
                parameter.ParameterName = this.PARAMETER_NAME_PREFIX + table.Columns[i].ColumnName;
                parameter.DbType = this.GetDbType(table.Columns[i].DataType);
                parameter.Value = dr[i];
                sqlcom.Parameters.Add(parameter);
                if (table.Columns[i].ColumnName == keyFieldName)
                {
                    str3 = string.Format(str3, keyFieldName);
                }
                else
                {
                    builder.Append(string.Format(str2, table.Columns[i].ColumnName));
                    builder.Append(",");
                }
            }
            string str4 = builder.ToString();
            str4 = str4.Substring(0, str4.Length - 1);
            string str5 = string.Format(format, TableName, str4, str3);
            sqlcom.CommandText = str5;
            this.ExecCommand(sqlcom);
        }

        public void InsertRows(List<DataRow> listRows, string tableName)
        {
            foreach (DataRow row in listRows)
            {
                this.InsertRows(row, tableName);
            }
        }

        public void InsertRows(DataRow row, string tableName)
        {
            long num;
            this.SetToInsertState(row);
            string primaryKeyField = "";
            bool primaryKeyValueIsAutoGenerated = false;
            this.InnerInsertRow(row, tableName, primaryKeyField, primaryKeyValueIsAutoGenerated, out num);
        }

        public void InsertRows(List<DataRow> listRows, string tableName, string primaryKeyField, bool primaryKeyValueIsAutoGenerated, out List<long> listPrimaryKeyValue)
        {
            listPrimaryKeyValue = new List<long>();
            foreach (DataRow row in listRows)
            {
                long num;
                this.SetToInsertState(row);
                this.InnerInsertRow(row, tableName, primaryKeyField, primaryKeyValueIsAutoGenerated, out num);
                listPrimaryKeyValue.Add(num);
            }
        }

        public void InsertRows(DataRow row, string tableName, string primaryKeyField, bool primaryKeyValueIsAutoGenerated, out long primaryKeyValue)
        {
            this.SetToInsertState(row);
            this.InnerInsertRow(row, tableName, primaryKeyField, primaryKeyValueIsAutoGenerated, out primaryKeyValue);
        }

        public void ModifyRows(List<DataRow> listRows, string tableName, string keyFieldName)
        {
            foreach (DataRow row in listRows)
            {
                this.SetToModifyState(row);
                this.InnerModifyRow(row, tableName, keyFieldName);
            }
        }

        public void ModifyRows(DataRow row, string tableName, string keyFieldName)
        {
            this.SetToModifyState(row);
            this.InnerModifyRow(row, tableName, keyFieldName);
        }

        public void SetToDeleteState(DataRow row)
        {
            row.RowError = this.DELETED_FLAG;
        }

        public void SetToInsertState(DataRow row)
        {
            row.RowError = this.INSERTED_FLAG;
        }

        public void SetToModifyState(DataRow row)
        {
            row.RowError = this.MODIFIED_FLAG;
        }

        private void TryCloseConnection()
        {
            if (this.m_SelfBuildConnection && (this.m_SqlConn != null))
            {
                this.m_SqlConn.Close();
            }
        }

        private void TryOpenConnection()
        {
            if (this.m_SelfBuildConnection)
            {
                this.m_SqlConn.Open();
            }
        }

        public string ConnectionString
        {
            get
            {
                return this.m_ConnectionString;
            }
            set
            {
                this.m_ConnectionString = value;
            }
        }

        public bool LogSqlExecute
        {
            get
            {
                return this.m_LogSqlExecute;
            }
            set
            {
                this.m_LogSqlExecute = value;
                this.m_SqlExecuteTracer.LogSqlExecute = this.m_LogSqlExecute;
            }
        }
    }
}