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