public void Delete(DataEvent ev) { if ((ev != null) && (ev.ID >= 0)) { OnDeleting(ev); foreach (DataTableBase linkedTable in LinkedTables) { foreach (DataEvent linkedEvent in ev.LinkedDataEvents(linkedTable)) { linkedTable.Delete(linkedEvent); } } //using(SQLiteCommand cmd = this.file.CreateCommand()) { string cmdText = string.Format("delete from {0} where id = {1}", Name, ev.ID); //SQLiteParameter parameter = new SQLiteParameter(); //parameter.DbType = DbType.Int64; //parameter.Value = ev.ID;); using (DataCommandBase dtc = new NullDataCommand(cmdText)) { _file.AddCommand(dtc); dtc.Wait(); } //} ev.ID = -1; count--; OnDeleted(ev); } }
// Just bypass all this stuff so we can actually process things in a reasonable time. public void BulkDelete(string[] bossIDs) { try { // String.Join will throw an out of memory exception if our array is too large, so chunk it out into sub arrays. List <string[]> chunkedIDs = new List <string[]>(); for (int i = 0; i < bossIDs.Length; i += 100000) { int end = i + 99999; if (end > bossIDs.Length) { end = bossIDs.Length; } var newArray = bossIDs.Skip(i).Take(end).ToArray(); chunkedIDs.Add(newArray); } foreach (string[] sub in chunkedIDs) { string cmdText = String.Format("delete from {0} where bossID in ({1})", Name, String.Join(",", sub)); using (DataCommandBase dtc = new NullDataCommand(cmdText)) { _file.AddCommand(dtc); dtc.Wait(); } } } catch (Exception e) { } }
public void Reindex() { //var bld = new StringBuilder(); if (TableExists && IndexColumn >= 0) { string cmdText = string.Format("reindex {0}", Name); using (DataCommandBase dcb = new NullDataCommand(cmdText)) { _file.AddCommand(dcb); dcb.Wait(); } //Logger.WriteLine("DataTableBase.Reindex({0}, {1})", Name, _columns[IndexColumn].Name); } OnReindexed(); }
private void Update(DataEvent ev) { OnUpdating(ev); //using(SQLiteCommand cmd = this.file.CreateCommand()) { var bld = new StringBuilder("update "); bld.Append(Name).Append(" set "); if (Columns.Count > 0) { bld.Append(Columns[0].Name).Append(" = ?"); } for (int i = 1; i < Columns.Count; i++) { bld.Append(", ").Append(Columns[i].Name).Append(" = ?"); } bld.Append(" where id = ?"); var parameters = new SQLiteParameter[Columns.Count + 1]; for (int i = 0; i < Columns.Count; i++) { parameters[i] = new SQLiteParameter(); Columns[i].SetValue(parameters[i], ev[i]); } parameters[Columns.Count] = new SQLiteParameter { DbType = DbType.Int64, Value = ev.ID }; using (DataCommandBase dtc = new NullDataCommand(bld.ToString(), parameters)) { _file.AddCommand(dtc); dtc.Wait(); } //} foreach (DataTableBase linkedTable in LinkedTables) { foreach (DataEvent linkedEvent in ev.LinkedDataEvents(linkedTable)) { linkedEvent[linkedTable.IndexColumn] = ev.ID; linkedTable.Save(linkedEvent); } } OnUpdated(ev); }
/// <summary> /// sqlite commands which build the sql table /// </summary> /// <param name="dataFileBase"></param> protected void createTable(DataFileBase dataFileBase) { if (_isOpen) { return; } _file = dataFileBase; var bld = new StringBuilder(); if (!TableExists) { bld.Append("create table ").Append(Name).Append("(id integer primary key"); foreach (DataColumn col in _columns) { bld.Append(", ").Append(col.Name).Append(" ").Append(col.Type); } bld.Append(")"); //cmd.CommandText = bld.ToString(); using (DataCommandBase dcb = new NullDataCommand(bld.ToString())) { _file.AddCommand(dcb); dcb.Wait(); } //Logger.WriteLine("DataTableBase.CreatedTable({0})", Name); if (IndexColumn >= 0) { string cmdText = string.Format("create index i_{0} on {0}({1})", Name, _columns[IndexColumn].Name); using (DataCommandBase dcb = new NullDataCommand(cmdText)) { _file.AddCommand(dcb); dcb.Wait(); } //Logger.WriteLine("DataTableBase.CreatedIndex({0}, {1})", Name, _columns[IndexColumn].Name); } //add the maneual entry default here to hotlists.db because the zero folder does not get added. if (Name == "t_hotlists") { var cmdText = "insert into t_hotlists (name, priority, timestamp, color, covert, alarm, active, file, whitelist, bossid, sound) " + "values ('Manual Reports',500, " + DateTime.Now.Ticks + ", -16776961,'False','MED','True','','False','','')"; using (DataCommandBase dcb = new NullDataCommand(cmdText)) { _file.AddCommand(dcb); dcb.Wait(); } } } //adds unique constraint on hotlist tables if (Name == "t_hotlist") { /////////**************/////////// /// this is here to check the file dir is equal to 1 so we do not add the Unique Index to the Manual Entry data file. var f = _file as HotLists.HotListDataFile; if (f != null) { var path = f.Dir; var folderNum = path.Substring(path.LastIndexOf('\\')); var ini = new IniFile(Environment.CurrentDirectory + @"\Synchronizer.ini"); string type = ini.ReadString("Synchronizer", "Type", "BOF2").ToUpper(); if (folderNum != "\\0") { if (folderNum != "\\1" && type != "BOF2") //excluding uniqueness because InStation does not have a BOSS ID { try { string cmdText = string.Format("create unique index iu_{0} on {0} (", Name); cmdText += string.Format("{0} asc", Columns[8].Name); cmdText += string.Format(")"); using (DataCommandBase dcb = new NullDataCommand(cmdText)) { _file.AddCommand(dcb); dcb.Wait(); } //Logger.WriteLine("DataTableBase.CreatedUniqueIndex({0})", Name); } catch (Exception ex) { //Logger.WriteLine("Data Table {0} already has Unique Index", Name); } } ////////*****************/////////// } } } //this.lastId = this.GetLastId(); foreach (DataTableBase table in _linkedTables) { table.Initialize(DataFile); } _isOpen = true; count = IsCountInitialized ? GetCount(null) : 0; bld.Length = 0; bld.Append("insert into "); bld.Append(Name).Append(" ("); if (Columns.Count > 0) { bld.Append(Columns[0].Name); } for (int i = 1; i < Columns.Count; i++) { bld.Append(", ").Append(Columns[i].Name); } bld.Append(") values ("); if (Columns.Count > 0) { bld.Append("?"); } for (int i = 1; i < Columns.Count; i++) { bld.Append(", ?"); } bld.Append(")"); _insertCommand = bld.ToString(); OnInitialized(); }