public void test()
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable recipe;
String query = "select NAME \"Name\", DESCRIPTION \"Description\",";
query += "PREP_TIME \"Prep Time\", COOKING_TIME \"Cooking Time\"";
query += "from RECIPE;";
recipe = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
/*
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
MessageBox.Show(r["Name"].ToString());
MessageBox.Show(r["Description"].ToString());
MessageBox.Show(r["Prep Time"].ToString());
MessageBox.Show(r["Cooking Time"].ToString());
}
*/
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public DateTime PropagationPathFindLastUpdated(PropagationPathDesignator path, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT LastUpdated FROM " + PropagationPathDesignator.TableName + " WHERE Lat1 = @Lat1 AND Lon1 = @Lon1 AND h1 = @h1 AND Lat2 = @Lat2 AND Lon2 = @Lon2 AND h2 = @h2 AND QRG = @QRG AND Radius = @Radius AND F1_Clearance = @F1_Clearance AND StepWidth = @StepWidth";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(path.AsDouble("Lat1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon1"));
db.DBCommand.Parameters.Add(path.AsDouble("h1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lat2"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon2"));
db.DBCommand.Parameters.Add(path.AsDouble("h2"));
db.DBCommand.Parameters.Add(path.AsDouble("QRG"));
db.DBCommand.Parameters.Add(path.AsDouble("Radius"));
db.DBCommand.Parameters.Add(path.AsDouble("F1_Clearance"));
db.DBCommand.Parameters.Add(path.AsDouble("StepWidth"));
object result = db.ExecuteScalar(db.DBCommand);
if (result != null)
{
return(SQLiteEntry.UNIXTimeToDateTime((int)result));
}
}
return(DateTime.MinValue);
}
private void getEmailFromID(int mailID)
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", TimeReceived \"TimeReceived\"";
query += "from Mails;";
query += "where ID = " + mailID;
Mails = db.GetDataTable(query);
foreach (DataRow r in Mails.Rows)
{
textBoxBody.Text = r["Body"].ToString();
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public PropagationPathDesignator PropagationPathFind(PropagationPathDesignator path, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
// save localobstruction
double obstr = path.LocalObstruction;
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT * FROM " + PropagationPathDesignator.TableName + " WHERE Lat1 = @Lat1 AND Lon1 = @Lon1 AND h1 = @h1 AND Lat2 = @Lat2 AND Lon2 = @Lon2 AND h2 = @h2 AND QRG = @QRG AND Radius = @Radius AND F1_Clearance = @F1_Clearance AND StepWidth = @StepWidth";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(path.AsDouble("Lat1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon1"));
db.DBCommand.Parameters.Add(path.AsDouble("h1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lat2"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon2"));
db.DBCommand.Parameters.Add(path.AsDouble("h2"));
db.DBCommand.Parameters.Add(path.AsDouble("QRG"));
db.DBCommand.Parameters.Add(path.AsDouble("Radius"));
db.DBCommand.Parameters.Add(path.AsDouble("F1_Clearance"));
db.DBCommand.Parameters.Add(path.AsDouble("StepWidth"));
DataTable Result = db.Select(db.DBCommand);
if ((Result != null) && (Result.Rows.Count > 0))
{
return(new PropagationPathDesignator(Result.Rows[0], obstr));
}
}
return(null);
}
public bool PropagationPathExists(PropagationPathDesignator path, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT EXISTS (SELECT LastUpdated FROM " + PropagationPathDesignator.TableName + " WHERE Lat1 = @Lat1 AND Lon1 = @Lon1 AND h1 = @h1 AND Lat2 = @Lat2 AND Lon2 = @Lon2 AND h2 = @h2 AND QRG = @QRG AND Radius = @Radius AND F1_Clearance = @F1_Clearance AND StepWidth = @StepWidth)";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(path.AsDouble("Lat1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon1"));
db.DBCommand.Parameters.Add(path.AsDouble("h1"));
db.DBCommand.Parameters.Add(path.AsDouble("Lat2"));
db.DBCommand.Parameters.Add(path.AsDouble("Lon2"));
db.DBCommand.Parameters.Add(path.AsDouble("h2"));
db.DBCommand.Parameters.Add(path.AsDouble("QRG"));
db.DBCommand.Parameters.Add(path.AsDouble("Radius"));
db.DBCommand.Parameters.Add(path.AsDouble("F1_Clearance"));
db.DBCommand.Parameters.Add(path.AsDouble("StepWidth"));
long result = (long)db.DBCommand.ExecuteScalar();
if (result > 0)
{
return(true);
}
}
return(false);
}
private PcdbFile(string path)
{
FileName = path;
database = new SQLiteDatabase(FileName);
Revision = PcdbUtil.GetRevision(database);
database.Close();
}
public PropagationHorizonDesignator PropagationHorizonFind(PropagationHorizonDesignator hor, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
// save localobstruction
LocalObstructionDesignator obstr = hor.LocalObstruction;
lock (db.DBCommand)
{
db.DBCommand.CommandText = "SELECT * FROM " + PropagationHorizonDesignator.TableName + " WHERE Lat = @Lat AND Lon = @Lon AND h = @h AND Dist = @Dist AND QRG = @QRG AND Radius = @Radius AND F1_Clearance = @F1_Clearance AND StepWidth = @StepWidth";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(hor.AsDouble("Lat"));
db.DBCommand.Parameters.Add(hor.AsDouble("Lon"));
db.DBCommand.Parameters.Add(hor.AsDouble("h"));
db.DBCommand.Parameters.Add(hor.AsDouble("Dist"));
db.DBCommand.Parameters.Add(hor.AsDouble("QRG"));
db.DBCommand.Parameters.Add(hor.AsDouble("Radius"));
db.DBCommand.Parameters.Add(hor.AsDouble("F1_Clearance"));
db.DBCommand.Parameters.Add(hor.AsDouble("StepWidth"));
DataTable Result = db.Select(db.DBCommand);
if ((Result != null) && (Result.Rows.Count > 0))
{
return(new PropagationHorizonDesignator(Result.Rows[0], obstr));
}
}
return(null);
}
private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
{
// check if there is a selection
if (listView1.SelectedItems.Count > 0)
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
for (int i = 0; i < listView1.SelectedItems.Count; i++)
{
ListViewItem item = listView1.SelectedItems[i];
db.Delete("history", String.Format("id = {0}", item.SubItems[3].Text));
}
// reload
loadItems();
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", error, -1, "Fade", "Up", "", "", "error");
commonFunctions.writeLog(fail.Message, fail.StackTrace);
}
}
}
public ScoutBaseDatabase()
{
try
{
// create dictionaries
versioninfo = new VersionInfo();
callsigns = new CallsignDictionary();
horizons = new HorizonDictionary();
// check if database path exists --> create if not
if (!Directory.Exists(DefaultDatabaseDirectory()))
{
Directory.CreateDirectory(DefaultDatabaseDirectory());
}
// check if database is already on disk
DBPath = Path.Combine(DefaultDatabaseDirectory(), "ScoutBase.db3");
if (!File.Exists(DBPath))
{
// create one on disk
System.Data.SQLite.SQLiteDatabase dbn = new System.Data.SQLite.SQLiteDatabase(DBPath);
dbn.Open();
dbn.CreateTable(new DataTableVersionInfo());
dbn.CreateTable(new DataTableCallsigns());
dbn.CreateTable(new DataTableHorizons());
dbn.CreateTable(new DataTableGLOBE());
dbn.CreateTable(new DataTableSRTM3());
dbn.CreateTable(new DataTableSRTM1());
if (String.IsNullOrEmpty(versioninfo.Version))
{
// no version info
// initally create one and store in database
versioninfo.Version = System.Reflection.Assembly.GetAssembly(typeof(ScoutBaseDatabase)).GetName().Version.ToString();
versioninfo.LastUpdated = DateTime.UtcNow;
dbn.InsertOrReplaceTable(versioninfo.WriteToTable());
}
dbn.Close();
}
if (Properties.Settings.Default.Database_InMemory)
{
db = System.Data.SQLite.SQLiteDatabase.CreateInMemoryDB(DBPath);
}
else
{
db = new System.Data.SQLite.SQLiteDatabase(DBPath);
db.Open();
}
// check version
if (String.Compare(System.Reflection.Assembly.GetAssembly(typeof(ScoutBaseDatabase)).GetName().Version.ToString(), versioninfo.Version) > 0)
{
// do any upgrade stuff here if necessary
}
// read data tables and initialize dictionaries
ReadDataTables();
}
catch (Exception ex)
{
Console.WriteLine("Error initilalizing database: " + ex.Message);
throw new TypeInitializationException(this.GetType().Name, ex);
}
}
public PcdbFileInfo(string path)
{
var db = new SQLiteDatabase(path);
db.Open();
Revision = PcdbUtil.GetRevision(db);
GameVersion = PcdbUtil.GetGameVersion(db);
db.Close();
}
public static int GetRevision(SQLiteDatabase db)
{
if (db.Execute("SELECT DATEINFO FROM NAMES WHERE 0") == null)
return (int)PcdbRevision.Rev0;
if (db.Execute("SELECT 1 FROM DBVERSION WHERE 0") == null)
return (int)PcdbRevision.Rev1;
return Convert.ToInt32(db.Execute("SELECT VERSION FROM DBVERSION").Rows[0][0]);
}
public bool IsInMemory(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
return(db.IsInMemory);
}
private PcdbFile(string path, PcdbGameVersion gameVersion)
{
FileName = path;
database = new SQLiteDatabase(FileName);
database.BeginTransaction();
{
database.ExecuteNonQuery(new[] {"PRAGMA encoding = 'UTF-8'", "PRAGMA foreign_keys = ON"});
switch (gameVersion)
{
case PcdbGameVersion.Unknown:
database.RollBackTransaction();
database.Close();
throw new NotSupportedException("Unknown game version.");
case PcdbGameVersion.SHOC:
database.RollBackTransaction();
database.Close();
throw new NotSupportedException("S.T.A.L.K.E.R.: Shadow of Chernobyl is not supported.");
case PcdbGameVersion.CS:
database.ExecuteNonQuery(new[]
{
"CREATE TABLE DBVERSION (VERSION INTEGER UNSIGNED NOT NULL)",
"INSERT INTO DBVERSION VALUES ('" + (int)SupportedRevision + "')",
"CREATE TABLE DBTYPE (TYPEID TINYINT UNSIGNED NOT NULL)",
"INSERT INTO DBTYPE VALUES ('2')",
"CREATE TABLE HASHES (HASH CHAR(32) NOT NULL PRIMARY KEY, INFO TINYTEXT NULL)",
"CREATE TABLE NAMES (HASH CHAR(32) NOT NULL, NAME TINYTEXT NOT NULL, " +
"DATEINFO TIMESTAMP NULL, FOREIGN KEY (HASH) REFERENCES HASHES(HASH), " +
"PRIMARY KEY (HASH, NAME))",
"CREATE TABLE IPS (HASH CHAR(32) NOT NULL, IP TINYTEXT NOT NULL, " +
"DATEINFO TIMESTAMP NULL, FOREIGN KEY (HASH) REFERENCES HASHES(HASH), " +
"PRIMARY KEY (HASH, IP))"
});
break;
case PcdbGameVersion.COP:
database.ExecuteNonQuery(new[]
{
"CREATE TABLE DBVERSION (VERSION INTEGER UNSIGNED NOT NULL)",
"INSERT INTO DBVERSION VALUES ('" + (int)SupportedRevision + "')",
"CREATE TABLE DBTYPE (TYPEID TINYINT UNSIGNED NOT NULL)",
"INSERT INTO DBTYPE VALUES ('3')",
"CREATE TABLE HASHES (HASH CHAR(32) NOT NULL PRIMARY KEY, INFO TINYTEXT NULL)",
"CREATE TABLE NAMES (HASH CHAR(32) NOT NULL, NAME TINYTEXT NOT NULL, " +
"DATEINFO TIMESTAMP NULL, FOREIGN KEY (HASH) REFERENCES HASHES(HASH), " +
"PRIMARY KEY (HASH, NAME))",
"CREATE TABLE IPS (HASH CHAR(32) NOT NULL, IP TINYTEXT NOT NULL, " +
"DATEINFO TIMESTAMP NULL, FOREIGN KEY (HASH) REFERENCES HASHES(HASH), " +
"PRIMARY KEY (HASH, IP))",
"CREATE TABLE GSIDS (HASH CHAR(32) NOT NULL, GSID INT UNSIGNED NOT NULL, " +
"DATEINFO TIMESTAMP NULL, FOREIGN KEY (HASH) REFERENCES HASHES(HASH), " +
"PRIMARY KEY (HASH, GSID))"
});
break;
}
}
database.CommitTransaction();
database.Close();
Revision = (int)SupportedRevision;
}
public ProjetoDAO(String tipoUtilizador = null)
{
m_projetos = new List<ProjetoDTO>(50);
//ligar a base de dados e fazer a query para obter todos os utilizadores da BD.
m_db = new SQLiteDatabase();
DataTable dt;
//getting the sequencial number
String query = "SELECT MAX(num_projeto) AS max FROM Projeto";
dt = m_db.GetDataTable(query);
DataRow row = dt.Rows[0];
try
{
m_sequencialNumber = (Int64)row[0];
}
catch (Exception e)
{
String g = e.ToString();
m_sequencialNumber = 0;
}
switch(tipoUtilizador)
{
case "tecnico":
query = "SELECT * FROM VW_ProjetoEstado WHERE num_estado = '1'";//ESTADO 1 = CANDIDATURA
break;
case "gestor":
query = "SELECT * FROM VW_ProjetoEstadoUtilizador WHERE nome_utilizador = '" + GestorLogin.Instance.Utilizador.Nome + "'";
break;
default:
query = "SELECT * FROM VW_ProjetoEstado";
break;
}
dt = m_db.GetDataTable(query);
ProjetoDTO p;
Promotor prom;
//Contacto cont;
foreach (DataRow r in dt.Rows)
{
p = new ProjetoDTO();
p.Numero = (Int64)r["num_projeto"];
p.Nome = (String)r["nome"];
prom = new Promotor();
prom.Nome = (String)r["promotor_nome"];
p.Promotor = prom;
p.Data = (String)r["data_criacao"];
p.MontantePedido = (Int64)r["montante_pedido"];
p.Estado = (String)r["nome_estado"];
m_projetos.Add(p);
}
}
public int PropagationHorizonDeleteAll(ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
lock (db.DBCommand)
{
db.DBCommand.CommandText = "DELETE FROM " + PropagationHorizonDesignator.TableName;
db.DBCommand.Parameters.Clear();
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public void ResetDBStatusBit(DATABASESTATUS statusbit, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db != null)
{
db.Status &= ~statusbit;
}
}
public static void ClearTimetable(FileInfo sqlDB)
{
List<string> network_tables = new List<string>();
network_tables.Add(SQLFieldNames.TIMETABLES);
network_tables.Add(SQLFieldNames.SERVICES);
network_tables.Add(SQLFieldNames.ROUTES);
network_tables.Add(SQLFieldNames.STOPS);
SQLiteDatabase db = new SQLiteDatabase(sqlDB);
db.ClearTable(network_tables);
}
public void SetDBStatus(DATABASESTATUS status, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db != null)
{
db.Status = status;
}
}
public long PropagationHorizonCount(ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
long count = (long)db.ExecuteScalar("SELECT COUNT(*) FROM " + PropagationHorizonDesignator.TableName);
if (count <= 0)
{
return(0);
}
return(count);
}
public DataTable Select(string sql, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(null);
}
return(db.Select(sql));
}
private void buttonDelete_Click(object sender, EventArgs e)
{
// Mail - Delete
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "delete from Mails where ID = " + selectedMail + ";";
Mails = db.GetDataTable(query);
// Delete from the listView...
listMails();
}
public static void ClearNetwork(FileInfo sqlDB)
{
List<string> network_tables = new List<string>();
network_tables.Add(SQLFieldNames.LOCATION_ALIASES);
network_tables.Add(SQLFieldNames.LOCATIONS);
network_tables.Add(SQLFieldNames.NETWORKS);
network_tables.Add(SQLFieldNames.TOUCHING_LOCATIONS);
network_tables.Add(SQLFieldNames.SWAP_LOCATIONS);
SQLiteDatabase db = new SQLiteDatabase(sqlDB);
db.ClearTable(network_tables);
}
public bool PropagationHorizonTableExists(ELEVATIONMODEL model, string tablename = "")
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
// check for table name is null or empty --> use default tablename from type instead
string tn = tablename;
if (String.IsNullOrEmpty(tn))
{
tn = PropagationHorizonDesignator.TableName;
}
return(db.TableExists(tn));
}
public void Commit(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return;
}
db.Commit();
}
public bool GetDBStatusBit(DATABASESTATUS statusbit, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db != null)
{
return((((int)db.Status) & ((int)statusbit)) > 0);
}
return(false);
}
public DATABASESTATUS GetDBStatus(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(DATABASESTATUS.UNDEFINED);
}
return(db.Status);
}
public DataTable GetTableList(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(null);
}
return(db.GetTableList());
}
public void BeginTransaction(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return;
}
db.BeginTransaction();
}
public double GetDBSize(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(0);
}
return(db.DBSize);
}
public string GetDBLocation(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return("");
}
return(db.DBLocation);
}
public SQLiteConnection GetDBConnection(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(null);
}
return(db.DBConnection);
}
public void PropagationHorizonInsertOrUpdateIfNewer(PropagationHorizonDesignator hor, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
DateTime dt = this.PropagationHorizonFindLastUpdated(hor, model);
if (dt == DateTime.MinValue)
{
this.PropagationHorizonInsert(hor, model);
}
else if (dt < hor.LastUpdated)
{
this.PropagationHorizonUpdate(hor, model);
}
}
public string GetTableDescription(string tablename, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
string desc;
if (TableDescriptions.TryGetValue(tablename, out desc))
{
return(desc);
}
return("[not found]");
}
public void PropagationPathInsertOrUpdateIfNewer(PropagationPathDesignator path, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
DateTime dt = this.PropagationPathFindLastUpdated(path, model);
if (dt == DateTime.MinValue)
{
this.PropagationPathInsert(path, model);
}
else if (dt < path.LastUpdated)
{
this.PropagationPathUpdate(path, model);
}
}
public void GroupRun(SteamFriends.ChatMsgCallback callback, Bot bot, object[] args = null)
{
string query = "select * from Jokes";
SQLiteDatabase db = new SQLiteDatabase();
DataTable jokes;
jokes = db.GetDataTable(query);
List<string> alljokes = new List<string>();
foreach (DataRow dr in jokes.Rows)
{
alljokes.Add(dr["JokeBody"].ToString());
}
bot.ChatroomMessage(bot.chatRoomID, System.Text.RegularExpressions.Regex.Unescape(Util.RandomChoice(alljokes)));
}
public void ClearTable(string tablename, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
db = this.db;
}
if (db == null)
{
return;
}
db.ClearTable(tablename);
}
public long GetTableRowCount(string tablename, System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
db = this.db;
}
if (db == null)
{
return(0);
}
return(db.TableRowCount(tablename));
}
public void PropagationHorizonCreateTable(ELEVATIONMODEL model, string tablename = "")
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
lock (db.DBCommand)
{
// check for table name is null or empty --> use default tablename from type instead
string tn = tablename;
if (String.IsNullOrEmpty(tn))
{
tn = PropagationHorizonDesignator.TableName;
}
db.DBCommand.CommandText = "CREATE TABLE " + tn + " (Lat DOUBLE NOT NULL DEFAULT 0, Lon DOUBLE NOT NULL DEFAULT 0, h DOUBLE NOT NULL DEFAULT 0, Dist DOUBLE NOT NULL DEFAULT 0, QRG DOUBLE NOT NULL DEFAULT 0, Radius DOUBLE NOT NULL DEFAULT 0, F1_Clearance DOUBLE NOT NULL DEFAULT 0, StepWidth DOUBLE NOT NULL DEFAULT 0, Horizon BLOB, LastUpdated INT32, PRIMARY KEY (Lat, Lon, h, Dist, QRG, Radius, F1_Clearance, StepWidth))";
db.DBCommand.Parameters.Clear();
db.Execute(db.DBCommand);
}
}
public DatabaseMaintenanceDlg(ScoutBaseDatabase database, ELEVATIONMODEL model = ELEVATIONMODEL.NONE)
{
InitializeComponent();
lv_Tables.FullRowSelect = true;
ListViewExtender extender = new ListViewExtender(lv_Tables);
// extend 4th & 5th column for actions
ListViewButtonColumn buttonAction1 = new ListViewButtonColumn(3);
buttonAction1.Click += OnButtonAction1Click;
buttonAction1.FixedWidth = true;
// add extender
extender.AddColumn(buttonAction1);
ListViewButtonColumn buttonAction2 = new ListViewButtonColumn(4);
buttonAction2.Click += OnButtonAction2Click;
buttonAction1.FixedWidth = true;
// add extender
extender.AddColumn(buttonAction2);
// add items
Database = database;
// get SQLite database to use for, use null füor default
if (database.GetType() == typeof(ElevationDatabase))
{
db = ((ElevationDatabase)Database).GetElevationDatabase(model);
}
if (database.GetType() == typeof(PropagationDatabase))
{
db = ((PropagationDatabase)Database).GetPropagationDatabase(model);
}
Model = model;
this.Text = Database.Name;
this.lbl_Description.Text = Database.Description;
DataTable dt = Database.GetTableList(db);
foreach (DataRow row in dt.Rows)
{
// add items
string tablename = row[0].ToString();
ListViewItem lvi_Location = lv_Tables.Items.Add(tablename);
lvi_Location.SubItems.Add(Database.GetTableDescription(tablename));
lvi_Location.SubItems.Add(Database.GetTableRowCount(tablename, db).ToString());
lvi_Location.SubItems.Add("Delete All");
lvi_Location.SubItems.Add("View");
}
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
db = new SQLiteDatabase("Books");
// Method used only once to create books table
//CreateBooksTable();
// Uncomment methods to test
//AddBook("A Game Of Thrones", "George R.R. Martin", DateTime.Parse("7/14/1996"), 1111111111);
//AddBook("SQLite For Professionals", "Homer Simpson", DateTime.Parse("7/14/2013"), 666);
//ListBooks();
SearchBook("A Game Of Thrones");
}
protected System.Data.SQLite.SQLiteDatabase OpenDatabase(string name, string defaultdatabasedirectory, bool inmemory)
{
System.Data.SQLite.SQLiteDatabase db = null;
try
{
// check if database path exists --> create if not
if (!Directory.Exists(defaultdatabasedirectory))
{
Directory.CreateDirectory(defaultdatabasedirectory);
}
// check if database is already on disk
DBPath = defaultdatabasedirectory;
if (!File.Exists(Path.Combine(DBPath, name)))
{
// create one on disk
System.Data.SQLite.SQLiteDatabase dbn = new System.Data.SQLite.SQLiteDatabase(Path.Combine(DBPath, name));
// open database
dbn.Open();
// set user version
dbn.SetUserVerion(UserVersion);
// set auto vacuum mode to full
dbn.SetAutoVacuum(AUTOVACUUMMODE.FULL);
dbn.Close();
}
// check for in-memory database --> open from disk, if not
if (inmemory)
{
db = System.Data.SQLite.SQLiteDatabase.CreateInMemoryDB(Path.Combine(DBPath, name));
}
else
{
db = new System.Data.SQLite.SQLiteDatabase(Path.Combine(DBPath, name));
db.Open();
}
// get version info
int v = db.GetUserVersion();
// do upgrade stuff here
}
catch (Exception ex)
{
Console.WriteLine("Error initilalizing database: " + ex.Message);
throw new TypeInitializationException(this.GetType().Name, ex);
}
return(db);
}
public static Dictionary<string, int> getNetworkAliases(FileInfo sqlDB)
{
SQLiteDatabase db = new SQLiteDatabase(sqlDB);
Dictionary<string, int> aliases = new Dictionary<string, int>();
DataTable table = db.GetDataTable("SELECT * FROM " + SQLFieldNames.LOCATION_ALIASES);
foreach (DataRow row in table.Rows)
{
string name = row[SQLFieldNames.LOCATION_ALIASES_NAME].ToString();
string id = row[SQLFieldNames.LOCATION_ALIASES_LOCATION_ID].ToString();
int id_value = int.Parse(id);
if (!aliases.ContainsKey(name))
{
aliases.Add(name, id_value);
}
}
return aliases;
}
protected void CloseDatabase(System.Data.SQLite.SQLiteDatabase db = null)
{
if (db == null)
{
db = this.db;
}
if (db == null)
{
return;
}
// save in-memory database to disk
if (db.IsInMemory)
{
db.BackupDatabase(db.DBLocation);
}
// else
// db.Close();
}
public int PropagationHorizonDelete(PropagationHorizonDesignator hor, ELEVATIONMODEL model)
{
System.Data.SQLite.SQLiteDatabase db = GetPropagationDatabase(model);
lock (db.DBCommand)
{
db.DBCommand.CommandText = "DELETE FROM " + PropagationHorizonDesignator.TableName + " WHERE Lat = @Lat AND Lon = @Lon AND h = @h AND Dist = @Dist AND QRG = @QRG AND Radius = @Radius AND F1_Clearance = @F1_Clearance AND StepWidth = @StepWidth";
db.DBCommand.Parameters.Clear();
db.DBCommand.Parameters.Add(hor.AsDouble("Lat"));
db.DBCommand.Parameters.Add(hor.AsDouble("Lon"));
db.DBCommand.Parameters.Add(hor.AsDouble("h"));
db.DBCommand.Parameters.Add(hor.AsDouble("Dist"));
db.DBCommand.Parameters.Add(hor.AsDouble("QRG"));
db.DBCommand.Parameters.Add(hor.AsDouble("Radius"));
db.DBCommand.Parameters.Add(hor.AsDouble("F1_Clearance"));
db.DBCommand.Parameters.Add(hor.AsDouble("StepWidth"));
return(db.ExecuteNonQuery(db.DBCommand));
}
}
public UtilizadorDAO()
{
m_utilizadores = new Dictionary<String, Utilizador>(10);
//ligar a base de dados e fazer a query para obter todos os utilizadores da BD.
m_db = new SQLiteDatabase();
DataTable dt;
String query = "SELECT * FROM Utilizador";
dt = m_db.GetDataTable(query);
//Para cada utilizador vamos introduzir no diccionário para assim termos acesso direto a ele.
Utilizador u;
foreach (DataRow r in dt.Rows)
{
u = new Utilizador((String)r["nome"], (String)r["pass"]);
u.Tipo = (String)r["tipo"];
m_utilizadores.Add(u.Nome, u);
}
}
public void StartLogger()
{
Console.WriteLine("Creating new database instance...");
database = new SQLiteDatabase(filename);
if (!File.Exists(filename))
{
Console.WriteLine("Creating blank log file...");
SQLiteConnection.CreateFile(filename);
database.RunQueryNoResult(databaseSQL);
}
Console.WriteLine("Adding session...");
DataSet result = database.RunQuery(
"INSERT INTO Sessions VALUES (NULL,datetime('now','localtime'));" +
"SELECT last_insert_rowid();");
sessionID = Convert.ToInt32(result.Tables[0].Rows[0][0]);
}
private void writeHistory(string url)
{
if (Properties.Settings.Default.history == 1)
{
// add to database
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("link", url);
try
{
db.Insert("history", data);
}
catch (Exception crap)
{
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", crap.Message, -1, "Fade", "Up", "", "", "error");
commonFunctions.writeLog(crap.Message, crap.StackTrace);
}
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
InsertTesti();
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable recipe;
String query = "select * FROM Unit";
recipe = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
Console.WriteLine("Data:");
Console.WriteLine(recipe.PrimaryKey);
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
tBox.Text += (r["UnitName"].ToString()) + "\n";
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
/*
var connectionString = @"Data Source=C:\Users\ville\Documents\GitHub\InventoryManagementApp\bin\Debug\inventory.s3db; Version=3";
var connection = new SQLiteConnection(connectionString);
connection.Open();
Console.WriteLine(connection.Database);
Console.WriteLine(connection.LastInsertRowId);
// Execute query on database
string insertSQL = "INSERT INTO Unit (UnitName)values(7, 'nakkimakkara')";
SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, connection);
insertCommand.ExecuteNonQuery();
// Execute query on database
string selectSQL = "SELECT * FROM Unit";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL, connection);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();
// Iterate every record in the AppUser table
while (!dataReader.Read())
{
tBox.Text+=("User: "******" Username: "******"/n");
}
dataReader.Close();
connection.Close();
/*Material mat = new Material(materialT.Text, Double.Parse(quantityT.Text));
shoplist.AddToList(mat);
tBox.Clear();
List<Material> content = shoplist.Content;
for (int i = 0; i < content.Count; i++)
{
tBox.Text += content[i] + "\n";
}*/
}
public static PcdbGameVersion GetGameVersion(string filename)
{
var db = new SQLiteDatabase(filename);
db.Open();
try
{
return PcdbUtil.GetGameVersion(db);
}
finally
{
db.Close();
}
}
void InsertTesti()
{
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("UnitName", sanoja[col++]);
try
{
db.Insert("Unit", data);
}
catch (Exception crap)
{
MessageBox.Show(crap.Message);
}
}
private void InsertMails(string newSender, string newSubject, string newBody)
{
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("Sender", newSender);
data.Add("Subject", newSubject);
data.Add("Body", newBody);
//data.Add("Timestamp", cookingTimeTextBox.Text);
//data.Add("COOKING_DIRECTIONS", "Placeholder");
try
{
db.Insert("Mails", data);
}
catch (Exception e)
{
//MessageBox.Show(crap.Message);
String error = "The following error has occurred:\n\n";
error += e.Message.ToString() + "\n\n";
MessageBox.Show(error);
}
}
private void GetMails()
{
try
{
textBoxBody.Text = "test";
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", TimeReceived \"TimeReceived\"";
query += "from Mails;";
Mails = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
//textBoxBody.Text = recipe.ToString();
// Or looped through for some other reason
foreach (DataRow r in Mails.Rows)
{
MessageBox.Show(r["Sender"].ToString());
MessageBox.Show(r["Subject"].ToString());
MessageBox.Show(r["Body"].ToString());
MessageBox.Show(r["TimeReceived"].ToString());
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void listMails()
{
// Function to list all mails from our database...
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select ID \"ID\", Subject \"Subject\"";
query += "from Mails;";
//String query = "select Sender \"Sender\", Subject \"Subject\",";
//query += "Body \"Body\", TimeReceived \"TimeReceived\"";
//query += "from Mails;";
Mails = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
//textBoxBody.Text = recipe.ToString();
// Or looped through for some other reason
//listViewInbox.Width = 270;
//listViewInbox.Location = new System.Drawing.Point(10, 10);
// Declare and construct the ColumnHeader objects.
ColumnHeader header1, header2;
header1 = new ColumnHeader();
header2 = new ColumnHeader();
// Set the text, alignment and width for each column header.
header1.Text = "File name";
header1.TextAlign = HorizontalAlignment.Left;
header1.Width = 10;
header2.TextAlign = HorizontalAlignment.Left;
header2.Text = "Location";
header2.Width = 20;
// Add the headers to the ListView control.
listViewInbox.Columns.Add(header1);
listViewInbox.Columns.Add(header2);
listViewInbox.View = View.Details;
int test = (3 + 0) / 6 * 100;
MessageBox.Show(test.ToString());
foreach (DataRow r in Mails.Rows)
{
/** /
ListViewItem newItem = new ListViewItem();
newItem.SubItems.Add(r["ID"].ToString());
newItem.SubItems.Add(r["Subject"].ToString());
listViewInbox.Items.Add(newItem);
/**/
//////////
ListViewItem newItem = new ListViewItem(r["Subject"].ToString());
newItem.SubItems.Add(r["ID"].ToString());
listViewInbox.Items.Add(newItem);
/**/
//MessageBox.Show(r["ID"].ToString());
//listViewInbox.Items.Add(r["Subject"].ToString() + " - " + r["Sender"].ToString());
//listViewInbox.Items.Add(r["ID"].ToString());
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public static PcdbGameVersion GetGameVersion(SQLiteDatabase db)
{
return (PcdbGameVersion)Convert.ToInt32(db.Execute("SELECT TYPEID FROM DBTYPE").Rows[0][0]);
}
private void listView1_AfterLabelEdit(object sender, LabelEditEventArgs e)
{
if (e.Label == null)
return;
ListViewItem item = listView1.Items[e.Item];
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("label", e.Label.ToString());
try
{
db.Update("history", data, String.Format("history.id = {0}", item.SubItems[3].Text));
// reload
loadItems();
}
catch (Exception crap)
{
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", crap.Message, -1, "Fade", "Up", "", "", "error");
commonFunctions.writeLog(crap.Message, crap.StackTrace);
}
}
private void loadItems(string where="")
{
listView1.Items.Clear();
toolStripButton1.Enabled = false;
toolStripButton2.Enabled = false;
toolStripStatusLabel2.Text = "";
webBrowser1.Navigate("");
try
{
SQLiteDatabase db = new SQLiteDatabase();
String query;
if (where!="")
query = "select * from `history` WHERE "+where+" ORDER BY `date` DESC;";
else query = "select * from `history` ORDER BY `date` DESC;";
DataTable history = db.GetDataTable(query);
foreach (DataRow r in history.Rows)
{
ListViewItem item = new ListViewItem();
item.Text = r["label"].ToString();
item.SubItems.Add(r["link"].ToString());
item.SubItems.Add(r["date"].ToString());
item.SubItems.Add(r["id"].ToString());
listView1.Items.Add(item);
}
toolStripStatusLabel1.Text = history.Rows.Count.ToString() + " item(s) in history";
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", error, -1, "Fade", "Up", "", "", "error");
toolStripStatusLabel1.Text = "0 item(s) in history";
commonFunctions.writeLog(fail.Message, fail.StackTrace);
}
}
private void toolStripButton4_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to clear your history?", "fallyGrab", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
SQLiteDatabase db = new SQLiteDatabase();
db.Delete("history", "1=1");
// reload
loadItems();
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", error, -1, "Fade", "Up", "", "", "error");
commonFunctions.writeLog(fail.Message, fail.StackTrace);
}
}
private void editToolStripMenuItem_Click(object sender, EventArgs e)
{
// check if there is a selection
if (listView1.SelectedItems.Count > 0)
{
ListViewItem item = listView1.SelectedItems[0];
string x = Interaction.InputBox("Type a label for the screenshot", "Label", item.Text);
if (x.Trim() != "")
{
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<String, String>();
data.Add("label", x);
try
{
db.Update("history", data, String.Format("history.id = {0}", item.SubItems[3].Text));
// reload
loadItems();
}
catch (Exception crap)
{
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", crap.Message, -1, "Fade", "Up", "", "", "error");
commonFunctions.writeLog(crap.Message, crap.StackTrace);
}
}
}
}
