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