Example #1
0
        public static bool SelectRow(string conninfo, string table, string key, string key_value, out string data)
        {
            data = "";
            if (IsNullOrEmpty(key))
            {
                return(false);
            }
            if (IsNullOrEmpty(key_value))
            {
                return(false);
            }

            string    query  = $"SELECT * FROM {table} WHERE ({key} = '{key_value}')";
            CDBColumn column = new CDBColumn();

            column.Name = "data";

            if (DBManager.ExecuteSelectQuery(conninfo, query, column) == false)
            {
                return(false);
            }

            data = column.Value;
            if (IsNullOrEmpty(data))
            {
                return(false);
            }

            return(true);
        }
Example #2
0
        public static bool InsertRow(string conninfo, string table, string key, string key_value, string data,
                                     bool backup = false, string backup_conninfo = "")
        {
            if (IsNullOrEmpty(key_value))
            {
                return(false);
            }
            if (IsNullOrEmpty(data))
            {
                return(false);
            }

            // 0. initialize
            DateTime now = DateTime.Now;

            // 1. select previous row for backup
            string create_query = $"CREATE TABLE IF NOT EXISTS {table} (name string primary key, created datetime, op_number string, op_type string, data string)";

            if (ExecuteNonQuerys(conninfo, create_query) == false)
            {
                return(false);
            }

            string query;

            if (backup == true && IsNullOrEmpty(backup_conninfo) == false)
            {
                // 2. select previous and check change
                query = $"SELECT * FROM {table} WHERE ({key} = '{key_value}')";
                CDBColumn column1 = new CDBColumn();
                column1.Name = "data";
                CDBColumn column2 = new CDBColumn();
                column2.Name = "created";

                if (ExecuteSelectQuery(conninfo, query, column1, column2) == false)
                {
                    return(false);
                }

                // if data are same, return ok
                if (data == column1.Value)
                {
                    return(true);
                }

                // 3. backup
                if (IsNullOrEmpty(column1.Value) == false)
                {
                    create_query = $"CREATE TABLE IF NOT EXISTS {table} (name string, created datetime, modified datetime, op_number string, op_type string, data string)";
                    query        = $"INSERT INTO {table} VALUES ('{key_value}', '{column2.Value}', '{DateTimeSQLite(now)}', '{op_number}', '{op_type}', '{column1.Value}')";

                    ExecuteNonQuerys(backup_conninfo, create_query, query);
                }
            }

            // 4. delete & insert
            query = $"DELETE FROM {table} WHERE ({key} = '{key_value}')";
            string query1 = $"INSERT INTO {table} VALUES ('{key_value}', '{DateTimeSQLite(now)}', '{op_number}', '{op_type}', '{data}')";

            if (ExecuteNonQuerys(conninfo, query, query1) == false)
            {
                return(false);
            }

            return(true);
        }