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()+"'");
}
}