private int Insert(object obj, string extra, TableMapping table) { if (obj == null || table == null) { return(0); } var has_autoinc_pk = table.PK != null && table.PK.IsAutoInc; if (has_autoinc_pk && table.PK.ColumnType == typeof(Guid)) { if (Guid.Empty.Equals(table.PK.GetValue(obj))) { table.PK.SetValue(obj, Guid.NewGuid()); } } var replacing = string.Compare(extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0; var cols = replacing ? table.InsertOrReplaceColumns : table.InsertColumns; var vals = new object[cols.Length]; for (int i = 0; i < vals.Length; i++) { vals[i] = cols[i].GetValue(obj); } var cmd = table.GetInsertCommand(this, extra); var count = cmd.ExecuteUpdate(vals); if (has_autoinc_pk) { var id = LastInserRowid(table); table.PK.SetValue(obj, id); } return(count); }
private TableQuery(DBConnection conn, TableMapping table) { this._conn = conn; this.Table = table; }
public List <T> ExecuteQuery <T>(TableMapping table) { return(ExecuteQueryDeferred <T>(table).ToList()); }
//校验当前数据表, 当与实际表 private bool ProofTestTable(TableMapping table) { //先尝试创建表 var create_sql = "CREATE TABLE IF NOT EXISTS \"" + table.TableName + "\"(\n"; create_sql += string.Join(",\n", (table.Columns.Select(p => Orm.SqlDecl(p)))); create_sql += ")"; var create_lst = Dict(create_sql); ExecuteUpdate(create_sql); //从数据库拉取表的信息 var command = CreateCommand("SELECT sql FROM sqlite_master WHERE type = \"table\" AND name = ? ;", table.TableName); var exists_sql = command.ExecuteScalar <string>(); var exists_lst = Dict(exists_sql); //比对表的差异性 bool rebuild = create_lst.Count < exists_lst.Count; var add_cols = new List <string>(); for (int i = 0; i < create_lst.Count && !rebuild; i++) { var col1 = create_lst[i]; if (i < exists_lst.Count) { var col2 = exists_lst[i]; if (!col1.Item1.Equals(col2.Item1) || !col1.Item2.Equals(col2.Item2)) { rebuild = true; } } else { add_cols.Add(col1.Item2); } } if (rebuild) { var same_cols = new List <string>(); for (int i = 0; i < create_lst.Count; i++) { for (int j = 0; j < exists_lst.Count; j++) { if (create_lst[i].Item1.Equals(exists_lst[j].Item1)) { same_cols.Add(create_lst[i].Item1); break; } } } //重建数据表(创建临时表->迁移相同字段的数据->删除临时表) RunInTransaction(() => { var table_name = table.TableName; var table_temp = table_name + "_temp"; ExecuteUpdate("PRAGMA foreign_keys = off;"); ExecuteUpdate(string.Format("DROP TABLE IF EXISTS \"{0}\" ;", table_temp)); ExecuteUpdate(string.Format("CREATE TABLE \"{1}\" AS SELECT * FROM \"{0}\" ;", table_name, table_temp)); ExecuteUpdate(string.Format("DROP TABLE \"{0}\" ;", table_name)); ExecuteUpdate(create_sql); if (same_cols.Count > 0) { var fields = string.Join(",", same_cols); //ExecuteUpdate("INSERT INTO \"{0}\" ( {2} ) SELECT {2} FROM \"{0}_temp_table\";"); ExecuteUpdate(string.Format("INSERT INTO \"{0}\" ( {2} ) SELECT {2} FROM \"{1}\";", table_name, table_temp, fields)); } ExecuteUpdate(string.Format("DROP TABLE \"{0}\" ;", table_temp)); ExecuteUpdate("PRAGMA foreign_keys = on;"); Debug.LogWarning(string.Format("column exception, rebuild sql: {0}\n{1}", table_name, create_sql)); }); } else if (add_cols != null && add_cols.Count > 0) { //追加新的字段 RunInTransaction(() => { var table_name = table.TableName; foreach (var field in add_cols) { ExecuteUpdate(string.Format("ALTER TABLE \"{0}\" ADD COLUMN {1};", table_name, field)); Debug.LogWarning(string.Format("Alter table add column {0}:{1}", table_name, field)); } }); } return(true); }
private object LastInserRowid(TableMapping map) { var cmd = CreateCommand(""); return(cmd.LastInserRowid(map)); }