protected void SaveTable(XmlWriter xmlWriter,DBConnect dbconnect,string table_name,Guid id,string path = "",int parent_key_number =0 ) { if (path == "") path = table_name; if (PathList != null && !PathList.Contains(path)) PathList.Add(path); if (RefTreeContains(table_name, id)) return; Dictionary<string,Ref> ForwardRefList = new Dictionary<string,Ref>(); // find forward references var foreign_keys = schema.ForeignKey.Where(x => x.Table_str == table_name && x.number != parent_key_number); if(foreign_keys.Count() > 0) { foreach (ForeignKey key in foreign_keys) { if (key.field.DontProcessing) continue; if (key.PrimaryTable.DontProcessing) continue; Guid? id_ = dbconnect.GetGuid("SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id='"+id.ToString()+"'"); if (id_ == null) continue; // is it a forward reference if (!RefTreeContains(key.PrimaryTable_str, (Guid)id_)){ ForwardRefList.Add(key.field_str,new Ref() {tablename = key.PrimaryTable_str,guid = (Guid)id_,number = key.number}); } } } // generate and execute the sql query Dictionary<int, Field> fields = new Dictionary<int, Field>(); string sql = "SELECT "; int index = 0; foreach (var fld in schema.GetTableByName(table_name).Fields) { if (fld.Value.DontProcessing) continue; if (ForwardRefList.ContainsKey(fld.Value.Name)) continue; sql += (index == 0 ? "" : ",") + "[" + fld.Value.Name + "]"; fields.Add(index, fld.Value); index++; } sql += " FROM ["+table_name+"] WHERE id='"+id.ToString()+"'"; DBConnect.Element obj = dbconnect.GetObject(sql); xmlWriter.WriteStartElement(table_name); index = 0; foreach (var elm in obj.Attributes) { Field fld = fields[index]; switch (fld.type) { case DBTypes.Boolean: xmlWriter.WriteAttributeString(elm.Key,elm.Value == "False" ? "0" : "1"); break; case DBTypes.Double: xmlWriter.WriteAttributeString(elm.Key,elm.Value.Replace(",",".")); break; case DBTypes.Datetime: xmlWriter.WriteAttributeString(elm.Key,elm.Value.Replace(".","-")); break; default: xmlWriter.WriteAttributeString(elm.Key,elm.Value); break; } index++; } xmlWriter.WriteEndElement(); AddToRefList(table_name, id,path); // upload objects by forward references foreach (var elm in ForwardRefList) { SaveTable(xmlWriter,dbconnect,elm.Value.tablename, elm.Value.guid,path + "/" +elm.Value.tablename,elm.Value.number); } // write section of xml where contains forward references info if (ForwardRefList.Count > 0) { xmlWriter.WriteStartElement("Update"); xmlWriter.WriteAttributeString("table_name", table_name); xmlWriter.WriteAttributeString("id", id.ToString()); xmlWriter.WriteAttributeString("ref_count", ForwardRefList.Count.ToString()); foreach (var dr in ForwardRefList) { xmlWriter.WriteStartElement("Ref"); xmlWriter.WriteAttributeString("field", dr.Key); xmlWriter.WriteAttributeString("value", dr.Value.guid.ToString()); xmlWriter.WriteEndElement(); } xmlWriter.WriteEndElement(); } // upload backward references if (!schema.GetTableByName(table_name).DontProcessingBackwardReferences) { foreign_keys = schema.ForeignKey.Where((x) => x.PrimaryTable_str == table_name && x.number != parent_key_number); if(foreign_keys.Count() > 0) { foreach (ForeignKey key in foreign_keys) { if (key.Table.DontProcessing) continue; List<Guid> id_list = dbconnect.GetGuidList("SELECT id FROM [" + key.Table_str + "] where [" + key.field_str + "]='"+id.ToString()+"'"); string ids = ""; foreach (Guid id_ in id_list) { ids += (ids.Count() == 0 ? "":",")+id_.ToString(); if (!RefTreeContains(key.Table_str, id_)) { SaveTable(xmlWriter,dbconnect, key.Table_str, id_, path + "/" + key.Table_str, key.number); } } } } } }
public void DeleteTable(DBConnect dbconnect,string table_name,Guid id,bool remove,string path = "",int parent_key_number =0) { string sql; if (path == "") path = table_name; if (!PathList.Contains(path)) PathList.Add(path); if (RefTreeContains(table_name, id)) return; AddToRefList(table_name, id,path); Dictionary<string,Ref> ForwardRefList = new Dictionary<string,Ref>(); // find forward references var foreign_keys = schema.ForeignKey.Where((x) => x.Table_str == table_name && x.number != parent_key_number); if(foreign_keys.Count() > 0) { foreach (ForeignKey key in foreign_keys) { if (key.field.DontProcessing) continue; if (key.PrimaryTable.DontProcessing) continue; DBConnect.Query query = new DBConnect.Query(); query.sql = "SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id=@id"; query.AddParameter("id", id); Guid? res = dbconnect.GetGuid("SELECT [" + key.field_str + "] as id FROM [" + key.Table_str + "] where id='"+id.ToString()+"'"); if (res == null) continue; Guid id_ = (Guid)res; // is it a forward reference if (!RefTreeContains(key.PrimaryTable_str, id_)){ ForwardRefList.Add(key.field_str,new Ref {tablename = key.PrimaryTable_str,guid = id_,number = key.number}); } } } // Clearing the reference fields if(ForwardRefList.Count > 0 && remove) { sql = "UPDATE ["+table_name+"] SET"+Environment.NewLine; bool FirstElement = true; foreach(var elm in ForwardRefList) { sql += (FirstElement ? "" : ",") + "[" + elm.Key + "]=null"+Environment.NewLine; FirstElement = false; } sql += "WHERE id='"+id+"'"; dbconnect.ExecuteSQL(sql); } // delete objects by forward references foreach(var elm in ForwardRefList) { DeleteTable(dbconnect,elm.Value.tablename, elm.Value.guid,remove,path + "->" +elm.Value.tablename,elm.Value.number); } // delete objects by backward references foreign_keys = schema.ForeignKey.Where((x) => x.PrimaryTable_str == table_name && x.number != parent_key_number); if(foreign_keys.Count() > 0) { foreach (ForeignKey key in foreign_keys) { if (key.Table.DontProcessing) continue; List<Guid> id_list = dbconnect.GetGuidList("SELECT id FROM [" + key.Table_str + "] where [" + key.field_str + "]='"+id.ToString()+"'"); string ids = ""; foreach (Guid id_ in id_list) { ids += (ids.Count() == 0 ? "":",")+id_; if (!RefTreeContains(key.Table_str, id_)) { DeleteTable(dbconnect,key.Table_str, id_,remove, path + "->" + key.Table_str, key.number); } } } } if (remove) { dbconnect.ExecuteSQL("DELETE FROM ["+table_name+"] WHERE id='"+id.ToString()+"'"); } }