public static List<Guid> GetGuidListStatic(string sqlStatement) { DBConnect conn = new DBConnect(); conn.Open(); List<Guid> list = conn.GetGuidList(sqlStatement); conn.Close(); return list; }
public SQL_XML_IO_statuses CreateInitialDatapack() { EFDbContext.SaveChgs(); RefTree.Clear(); string path = FileService.GetMainDataDirectory()+"Resources\\InitialData.xml"; XmlWriter xmlWriter = XmlWriter.Create(path,new XmlWriterSettings() {Encoding = Encoding.UTF8 }); DBConnect dbconnect = new DBConnect(); dbconnect.Open(); xmlWriter.WriteStartDocument(); xmlWriter.WriteStartElement(SQL_XML_IOSystem.StartElementName); xmlWriter.WriteAttributeString("version", Data_Versions.version_1_1.ToString()); xmlWriter.WriteAttributeString("MainElement_Guid", Guid.NewGuid().ToString()); xmlWriter.WriteAttributeString("MainElement_Name", "DataPack"); logging = true; PathList = new List<string>(); // schema.ClearRules(); schema.GetTableByName(Language.TableName).DontProcessingBackwardReferences = true; //schema.GetTableByName(Language.TableName).DontProcessingField("FrequencyDictionary_id"); //schema.GetTableByName(Dictionary.TableName).DontProcessing = true; //schema.GetTableByName(ProcessingWordsRules.TableName).DontProcessing = true; //schema.GetTableByName(Subtitles.TableName).DontProcessing = true; // upload the languages foreach(Language lang in ViewModelLocator.Instance.Languages) { SaveTable(xmlWriter,dbconnect,Language.TableName,lang.id); } // upload the LearningWordStrategy //schema.GetTableByName(LearningWordStrategy.TableName).GetFieldByName("") schema.GetTableByName(AppSetting.TableName).DontProcessing = true; schema.GetTableByName(LearningItem.TableName).DontProcessing = true; schema.GetTableByName(SubtitleItem.TableName).DontProcessing = true; foreach (LearningWordStrategy lws in EFDbContext.DataBase.LearningWordStrategy) { SaveTable(xmlWriter, dbconnect, LearningWordStrategy.TableName, lws.id); } xmlWriter.WriteEndDocument(); xmlWriter.Close(); // Save the dbconnect.Close(); return SQL_XML_IO_statuses.Ok; }
public SQL_XML_IO_statuses DeleteLearningItem(LearningItem learningitem) { if (learningitem == null) return SQL_XML_IO_statuses.OtherError; RefTree.Clear(); DBConnect dbconnect = new DBConnect(); dbconnect.Open(); //logging = true; //FindAllReferences_("LearningItems",learningitem.id); if(ViewModelLocator.Instance.AppSetting.CurrentLearningItem.id == learningitem.id) { ViewModelLocator.Instance.AppSetting.CurrentLearningItem = null; } EFDbContext.SaveChgs(); // Rules schema.ClearRules(); schema.GetTableByName(AppSetting.TableName).DontProcessing = true; schema.GetTableByName(Dictionary.TableName).DontProcessing = true; schema.GetTableByName(Language.TableName).DontProcessing = true; schema.GetTableByName(WordOfDictionary.TableName).DontProcessing = true; // optimization for speed DBConnect.Query query = new DBConnect.Query(); query.sql = SQLqueries.Delete_Update_Subtitle_Items_Words; query.AddParameter("id", learningitem.id); dbconnect.ExecuteSQL(query); query.sql = SQLqueries.Delete_Subtitile_Items_Words; query.AddParameter("id", learningitem.id); dbconnect.ExecuteSQL(query); query.sql = SQLqueries.Delete_Subtitle_Items; query.AddParameter("id", learningitem.id); dbconnect.ExecuteSQL(query); PathList.Clear(); DeleteTable(dbconnect,"LearningItems",learningitem.id,true); dbconnect.Close(); ((IObjectContextAdapter)ViewModelLocator.Instance.DataBase).ObjectContext.Detach(learningitem); EFDbContext.SaveChgs(); return SQL_XML_IO_statuses.Ok; }
public SQL_XML_IO_statuses Load(string path,out Guid guid) { RefTree.Clear(); guid = Guid.Empty; FakeElements = new List<Ref>(); if (!FileService.FileExists(path)) return SQL_XML_IO_statuses.NoFile; DBConnect dbconnect = new DBConnect(); dbconnect.Open(); using (XmlReader reader = XmlReader.Create(path)) { int MaxElements = 1; XML_Element element; List<XML_Element> xml_elements = new List<XML_Element>(); element = ReadElement(reader,Data_Versions.version_1_0,"xml"); element = ReadElement(reader,Data_Versions.version_1_0,StartElementName); guid = new Guid(element.GetAttibute("MainElement_Guid")); string version_string = element.GetAttibute("version"); if (version_string.Contains(".")) version_string = version_string.Replace(".0",""); short VersionOfData = short.Parse(version_string); int Counter = 0; while (true) { element = ReadElement(reader,VersionOfData); if (element.Name == StartElementName) break; AddToRefList(element.Name, Guid.Parse(element.guid), "", true); xml_elements.Add(element); Counter++; if(Counter == MaxElements) { LoadElements(dbconnect,xml_elements,VersionOfData); xml_elements.Clear(); Counter = 0; } } LoadElements(dbconnect,xml_elements,VersionOfData); } if (FakeElements.Count > 0) { foreach (var elm in FakeElements) { dbconnect.ExecuteSQL("DELETE FROM ["+elm.tablename+"] WHERE id = '"+elm.guid+"'"); } } dbconnect.Close(); return SQL_XML_IO_statuses.Ok; }
public static bool ObjectExistStatic(string table_name,Guid id) { DBConnect conn = new DBConnect(); conn.Open(); bool res = conn.ObjectExist(table_name, id); conn.Close(); return res; }
private string GetFakeValue(DBConnect dbconnect,Table tbl, Field field,short version_of_data) { switch (field.type) { case DBTypes.String: return ""; case DBTypes.Datetime: return DateTime.Now.ToString(CultureInfo.InvariantCulture); case DBTypes.Integer: case DBTypes.Boolean: case DBTypes.Double: return "0"; case DBTypes.Guid: ForeignKey key = schema.ForeignKey.FirstOrDefault(x => x.Table_str == tbl.Name && x.field_str == field.Name); if (key == null) throw new ArgumentNullException(nameof(key)); Guid id = Guid.Empty; var felm = FakeElements.FirstOrDefault(x=> x.tablename == key.PrimaryTable_str); if (felm != null) { id = felm.guid; } else { string flds = "", values=""; foreach (var fld in key.PrimaryTable.Fields) { if (fld.Value.IsNullable) continue; string value = ""; if (fld.Key == "id") { id = Guid.NewGuid(); value = id.ToString(); } else { value = GetFakeValue(dbconnect, key.PrimaryTable, fld.Value, version_of_data); } flds += (flds == "" ? "":",")+"["+fld.Key+"]"+Environment.NewLine; values += (values==""?"":",") + FormatValueForSQL(fld.Value,value,version_of_data); } string sql = "INSERT INTO ["+key.PrimaryTable_str+"] ("+flds+" ) VALUES ("+values+")"; dbconnect.ExecuteSQL(sql); FakeElements.Add(new Ref {guid=id,tablename = key.PrimaryTable_str}); } return id.ToString(); default: throw new NotImplementedException(); } }
private void LoadElements(DBConnect dbconnect,List<XML_Element> xml_elements,short version_of_data) { if (xml_elements.Count == 0) return; // aggregates set of xml_elements, groups it by tables Dictionary<string, List<string>> table_aggregator = new Dictionary<string, List<string>>(); foreach(XML_Element elm in xml_elements) { if (elm.Name == "Update") continue; List<string> cur_table; if(!table_aggregator.TryGetValue(elm.Name, out cur_table)) { cur_table = new List<string>(); table_aggregator.Add(elm.Name, cur_table); } if (elm.guid == "") throw new NotImplementedException(); cur_table.Add(elm.guid); } // build the query to the id's for testing is elements in the database StringBuilder sql = new StringBuilder(); sql.Append("Select tmp_tbl.id from (" + Environment.NewLine); List<Guid> ids = new List<Guid>(); if(table_aggregator.Count > 0) { bool firsttable = true; foreach (var tbl in table_aggregator) { sql.Append((firsttable ? "" : " UNION ALL "+Environment.NewLine) + "select id from [" + tbl.Key + "] where id in ("); bool firstelm = true; foreach (var elm in tbl.Value) { sql.Append((firstelm ? "":",") +"'"+elm+"'"); firstelm = false; } sql.Append(")" + Environment.NewLine ); firsttable = false; } sql.Append(") as tmp_tbl"); ids = dbconnect.GetGuidList(sql.ToString()); } // build the main query insert/update sql.Clear(); foreach(XML_Element elm in xml_elements) { bool isInsert = !ids.Contains(new Guid(elm.guid)); if (!isInsert && elm.Attributes.Count == 1) continue; // no need to update because element haven't any attributes string names = "", values = "",names_values = "",name,value,table_name; Table tbl = schema.GetTableByName(elm.Name); bool FirstElement = true; if (!elm.IsForwardRef) // if it isn't forward references update { table_name = elm.Name; List<string> ListOfProcessedFields = new List<string>(); foreach (var attr in elm.Attributes) { Field fld = tbl.GetFieldByName(attr.Key); if (fld == null) continue; name = attr.Key; ListOfProcessedFields.Add(name); //value = fld.FormatValueForInsert(attr.Value); value = FormatValueForSQL(fld,attr.Value,version_of_data); if (value == null) continue; if (isInsert) { names += (FirstElement ? "" : ",") + "[" + name + "]" + Environment.NewLine; values += (FirstElement ? "" : ",") + value + Environment.NewLine; } else // update { if (name == "id") continue; names_values += (FirstElement ? "" : ",") + "[" + name + "]=" + value + Environment.NewLine; } FirstElement = false; } // testing is there empty non-nullable field foreach (var fld in tbl.Fields) { if (!fld.Value.IsNullable && !ListOfProcessedFields.Contains(fld.Key)) { string fakevalue = GetFakeValue(dbconnect,tbl,fld.Value,version_of_data); names += (FirstElement ? "" : ",") + "[" + fld.Key + "]" + Environment.NewLine; values += (FirstElement ? "" : ",") + FormatValueForSQL(fld.Value,fakevalue,version_of_data) + Environment.NewLine; } } } else { table_name = elm.GetAttibute("table_name"); foreach (var fld in elm.fields) { names_values += (FirstElement ? "" : ",") + "[" + fld.Field + "]='" + fld.Value + "'"+ Environment.NewLine; FirstElement = false; } } if (isInsert && !elm.IsForwardRef) { string insert = "INSERT INTO [" + table_name + "] (" + Environment.NewLine + names + ") VALUES (" + Environment.NewLine + values + ") "+Environment.NewLine+Environment.NewLine; sql.Append(insert); UpdateOnLoad.UpdateAfterLoading(elm, version_of_data, false); } else { if(names_values != "") { string update = "UPDATE [" + table_name + "] SET" + Environment.NewLine + names_values + "WHERE id='" + elm.guid+"'"+Environment.NewLine+Environment.NewLine; sql.Append(update); UpdateOnLoad.UpdateAfterLoading(elm, version_of_data, true); } } } string sql_string = sql.ToString(); if(sql_string.Length != 0) { dbconnect.ExecuteSQL(sql); } }
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 SQL_XML_IO_statuses Save(string path, string table_name, Guid guid) { EFDbContext.SaveChgs(); RefTree.Clear(); XmlWriter xmlWriter = XmlWriter.Create(path,new XmlWriterSettings() {Encoding = Encoding.UTF8 }); DBConnect dbconnect = new DBConnect(); dbconnect.Open(); xmlWriter.WriteStartDocument(); xmlWriter.WriteStartElement(StartElementName); xmlWriter.WriteAttributeString("version", Data_Versions.version_1_1.ToString()); xmlWriter.WriteAttributeString("MainElement_Guid", guid.ToString()); xmlWriter.WriteAttributeString("MainElement_Name", table_name); logging = true; //PathList = null; PathList = new List<string>(); SaveTable(xmlWriter,dbconnect,table_name,guid); dbconnect.Close(); xmlWriter.WriteEndDocument(); xmlWriter.Close(); return SQL_XML_IO_statuses.Ok; }
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()+"'"); } }