SQLiteManager 第二部分

// 一些常用的查询方法（其实如果熟悉SQL的话，直接用ExecuteQuery更方便）

// 这部分直接复制到SQLiteManager类里面就可以

// SQLite没有逻辑型，一般用0/1表示

// 在比照文本型时，别忘了手动给string参数值加上单引号

        public List<TableColInfo> GetTableColInfos(string tableName)
        {
            if (!IsTableExist(tableName))
                return null;
            // ---------------- ---------------- ----------------
            string queryString = "PRAGMA table_info(" + tableName + ")";
            ExecuteQuery(queryString);
            List<TableColInfo> returnCols = new List<TableColInfo>();
            while (reader.Read())
            {
                SQLiteDataType type = SQLiteDataType.BLOB;
                if (reader["type"].ToString() == "INTEGER")
                    type = SQLiteDataType.INTEGER;
                else if (reader["type"].ToString() == "REAL")
                    type = SQLiteDataType.REAL;
                else if (reader["type"].ToString() == "TEXT")
                    type = SQLiteDataType.TEXT;
                // ---------------- ---------------- ----------------
                TableColInfo newCol = new TableColInfo(reader["name"].ToString(), type);

                if (reader[3] != null && reader.GetBoolean(3))
                    newCol.isNOTNULL = true;

                if (string.IsNullOrEmpty(reader[4].ToString()))
                    newCol.dflt_value = reader[4].ToString();

                if (reader[5] != null && reader.GetBoolean(5))
                    newCol.isPK = true;

                returnCols.Add(newCol);
            }
            reader.Close();
            return returnCols;
        }
        public List<TableColInfo> GetColInfosFromMonoScript(Type type)
        {
            List<TableColInfo> returnColInfos = new List<TableColInfo>();
            FieldInfo[] fields = type.GetFields();
            foreach (FieldInfo field in fields)
            {
                SerializeToTableCol att = field.GetCustomAttribute<SerializeToTableCol>();
                if (att != null)
                    returnColInfos.Add(new TableColInfo(att.name, att.type, att.isNOTNULL, att.dflt_value, att.isPK));
            }
            return returnColInfos;
        }
        // ==================== ==================== ==================== ====================
        public int GetTableRows(string tableName)
        {
            string queryString = "SELECT count(*) FROM " + tableName;
            ExecuteQuery(queryString);

            if (!reader.HasRows) { reader.Close(); return 0; }

            reader.Read();
            int returnInt = reader.GetInt32(0);
            reader.Close();
            return returnInt;
        }
        public void GetAllRecords(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            ExecuteQuery(queryString);
        }

        public void GetRecordsByValue(string tableName, string colName, string value)
        {
            TableColInfo colInfoOfColName = null;
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            foreach (var colInfo in colInfos)
            {
                if (colInfo.name == colName)
                { colInfoOfColName = colInfo; break; }
            }
            if (colInfoOfColName == null)
            { return; }
            // ---------------- ---------------- ----------------
            string queryString = $"SELECT * FROM {tableName} WHERE {colName} = value";
            ExecuteQuery(queryString);
        }
        public void GetRecordsByValueList(string tableName, string colName, List<string> values)
        {
            if (values == null | values.Count == 0)
            { return; }
            // ---------------- ---------------- ----------------
            TableColInfo colInfoOfColName = null;
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            foreach (var colInfo in colInfos)
            {
                if (colInfo.name == colName)
                { colInfoOfColName = colInfo; break; }
            }
            if (colInfoOfColName == null)
            { return; }
            // ---------------- ---------------- ----------------
            StringBuilder queryString = new StringBuilder($"SELECT * FROM {tableName} WHERE {colName} = IN ({values[0]}");
            values.RemoveAt(0);
            foreach (var value in values)
            {
                queryString.Append($" ,{value}");
            }
            queryString.Append(")");
            ExecuteQuery(queryString.ToString());
        }
        public void GetRowIDByValue(string tableName, string colName, string value)
        {
            TableColInfo colInfoOfColName = null;
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            foreach (var colInfo in colInfos)
            {
                if (colInfo.name == colName)
                { colInfoOfColName = colInfo; break; }
            }
            if (colInfoOfColName == null)
            { return; }
            // ---------------- ---------------- ----------------
            string queryString = $"SELECT rowid FROM {tableName} WHERE {colName} = value";
            ExecuteQuery(queryString);
        }
        public void AddRecord(string tableName, List<string> values)
        {
            if (values == null | values.Count == 0)
            { return; }
            // ---------------- ---------------- ----------------
            StringBuilder querySB = new StringBuilder(values[0]);
            foreach (var value in values)
            { querySB.Append($" ,{value}"); }
            querySB.Insert(0, $"INSERT INTO {tableName} VALUES (").Append(")");

            try
            { ExecuteQuery(querySB.ToString()); }
            catch (Exception)
            { }
        }
        public void UpDateRecord(string tableName, List<string> values, int rowID)
        {
            if (values == null | values.Count == 0)
            { return; }
            // ---------------- ---------------- ----------------
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            StringBuilder querySB = new StringBuilder($"{colInfos[0].name} = {values[0]}");
            for (int n = 1; n < colInfos.Count - 1; n++)
            {
                querySB.Append($" ,{colInfos[n].name} = {values[n]}");
            }
            querySB.Append($" WHERE roeid = {rowID}");
            try
            { ExecuteQuery(querySB.ToString()); }
            catch (Exception)
            { }
        }

        public void DeleteRecordsByValue(string tableName, string colName, string value)
        {
            TableColInfo colInfoOfColName = null;
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            foreach (var colInfo in colInfos)
            {
                if (colInfo.name == colName)
                { colInfoOfColName = colInfo; break; }
            }
            if (colInfoOfColName == null)
            { return; }
            // ---------------- ---------------- ----------------
            string queryString = $"DELETE FROM {tableName} WHERE {colName} = value";
            try
            { ExecuteQuery(queryString); }
            catch (Exception)
            { }
        }
        public void DeleteRecordsByValueList(string tableName, string colName, List<string> values)
        {
            if (values == null | values.Count == 0)
            { return; }
            // ---------------- ---------------- ----------------
            TableColInfo colInfoOfColName = null;
            List<TableColInfo> colInfos = GetTableColInfos(tableName);
            foreach (var colInfo in colInfos)
            {
                if (colInfo.name == colName)
                { colInfoOfColName = colInfo; break; }
            }
            if (colInfoOfColName == null)
            { return; }
            // ---------------- ---------------- ----------------
            StringBuilder queryString = new StringBuilder($"DELETE FROM {tableName} WHERE {colName} =  IN ({values[0]}");
            values.RemoveAt(0);
            foreach (var value in values)
            {
                queryString.Append($" ,{value}");
            }
            queryString.Append(")");
            try
            { ExecuteQuery(queryString.ToString()); }
            catch (Exception)
            { }
        }