/// <summary>
/// Creates a new connection to the icontent.cache file.
/// </summary>
/// <param name="filename">The file to connect to.</param>
public IGADatabaseConnector(String filename)
{
try {
sqlite = new SqliteConnection("URI=file:" + filename + ",version=3");
sqlite.Open();
SqliteCommand query = new SqliteCommand("SELECT [appId] FROM [contentlist] LIMIT 1", sqlite);
Object result = query.ExecuteScalar();
sqlite.Close();
if (result == null)
{
this._appID = 0;
}
else
{
this._appID = (int)result;
}
if (this._appID > 0) {
this._appSupported = Common.AppInfos.ContainsKey(this._appID);
} else {
this._appSupported = false;
}
if (this._appSupported)
{
_appInfo = Common.AppInfos[this._appID];
}
} catch (Exception) {
throw new DatabaseConnectionFailureException();
}
}
public void Initialise(string connectionString)
{
m_connectionString = connectionString;
m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
m_connection.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_connection, assem, "EstateStore");
m.Update();
m_connection.Close();
m_connection.Open();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
// Use this for initialization
void Start()
{
//E:\Etude\Projet\Projet_4e_semestre\GITHUB\New_Unity_Project\Assets
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
const string sql = "select nomcarte from carte";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
string NomCarte = reader.GetString(0);
Debug.Log("Carte: " + NomCarte + "\n");
}
reader.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
// Use this for initialization
void Start () {
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
const string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.IDCARTE = '1' AND atk.IDATK='1'";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
IDCARTE = reader.GetInt32(0);
}
reader.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
public static IDataReader ExecuteSQL(string db, string sql)
{
var connection = new SqliteConnection(db);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteReader();
connection.Close();
return result;
}
public new void ExecuteNonQuery(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public new object ExecuteScalar(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
object retVal = cmd.ExecuteScalar();
conn.Close();
return retVal;
}
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "RegionStore");
int version = m.Version;
if (version <= 14)
{
if (version == 0)
{
//read rex tables and add to rex database
m_log.Info("[regionstore] converting rex tables to rexobjectproperties");
if (!ConvertLegacyRexDataToModreX())
{
conn.Close();
return false;
}
m_log.Info("[RegionStore] Update region migrations");
//Add new field to Land table
SqliteCommand addAuthbyerIDCmd = new SqliteCommand(addAuthbyerID, conn);
addAuthbyerIDCmd.ExecuteNonQuery();
//Change migration to version 1
m.Version = 1;
}
//Run migrations up to 9
//Note: this run migrations only to point nine since only those files exist in application resources.
m.Update();
//Skip over 10. Change version to 10
//This skips adding of the ClickAction since that already exists in 0.4 database
//m.Version = 10;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[RegionStore] Migration failed. Reason: {0}", e);
return false;
}
}
//Diese Methode Schreibt ein Gegebenes Auto in die Datenbank.
// Das Auto wird als Parameter gegeben
public void addauto(Autos autodaten)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection .Open();
sql = "INSERT INTO AUTOS (KENNZEICHEN, STATUS) Values ('"+ autodaten.getKennzeichen ()+"','"+autodaten.getStatus ()+"')";
_command.CommandText = sql;
_command.ExecuteNonQuery();
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
//Debug.Log (autodaten.getKennzeichen ());
}
public ArrayList Select(string quer)
{
Resultd = new ArrayList();
string connectionString = "URI=file:" + Application.dataPath + "/Qjournaldb.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = quer;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
Resultd.Add(reader.GetString(2));
//Debug.Log(reader.GetString(2));
}
// clean up
reader.Dispose();
dbcmd.Dispose();
dbcon.Close();
return Resultd;
}
// Hier wird überprüft ob eine bestimmte Tabelle in der Datenbank existieren. Die Anzahl der Funde wird Zurückgegeben. 1=Existiert; 0=Gibt es nicht
public int abfrageexisttabelle(string Tabellenname)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT count(name) as Count FROM sqlite_master WHERE type='table' AND name='"+Tabellenname+"'";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
_connection .Close();
_connection.Dispose ();
_connection = null;
_command.Dispose ();
_command = null;
int anzahl = System.Convert.ToInt32 (_reader ["Count"]);
_reader.Close();
_reader.Dispose ();
_reader = null;
return anzahl;
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "UserStore");
if (m.Version == 0)
{
m.Version = 1;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[UserStore] Migration failed. Reason: {0}", e);
return false;
}
}
/// <summary>Creates the database if necessary and returns the current
/// iteration.</summary>
protected int InitializeDatabase()
{
IDbConnection dbcon = new SqliteConnection(_db_con);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
string sql = "CREATE TABLE crawl (iter INTEGER, Address TEXT, Hits INTEGER, " +
"Rtt REAL, Hops REAL)";
dbcmd.CommandText = sql;
try {
dbcmd.ExecuteNonQuery();
} catch {
}
sql = "CREATE TABLE iterations (iter INTEGER, time INTEGER)";
dbcmd.CommandText = sql;
try {
dbcmd.ExecuteNonQuery();
} catch {
}
// Set _current_iter to how many iterations there exist already
sql = "SELECT iter FROM iterations";
dbcmd.CommandText = sql;
int current_iter = dbcmd.ExecuteNonQuery();
dbcmd.Dispose();
dbcon.Close();
return current_iter;
}
public void init(int idcarte, string numserie, int idatk, int idatk2)
{
this.idcarte = idcarte;
this.numserie = numserie;
xdeck = this.transform.position.x;
ydeck = this.transform.position.y;
zdeck = this.transform.position.z;
ObservJ1 = false;
ObservJ2 = false;
mettredsdeck = false;
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk + "";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
NomCarte = reader.GetString(0);
puisscrea = reader.GetInt32(1);
vitcrea = reader.GetInt32(2);
typecrea = reader.GetString(3);
pvcrea = reader.GetInt32(4);
nomatk = reader.GetString(5);
dommatk = reader.GetInt32(6);
vitatk = reader.GetInt32(7);
}
reader.Dispose();
string sql2 = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk2 + "";
dbcmd.CommandText = sql2;
IDataReader reader2 = dbcmd.ExecuteReader();
while (reader2.Read())
{
nomatk2 = reader2.GetString(5);
dommatk2 = reader2.GetInt32(6);
vitatk2 = reader2.GetInt32(7);
}
reader2.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
/// <summary>
/// ////////////////////////////////////////Les fonctions liées aux gui
/// </summary>
void OnGUI()
{
//Debug.Log(idcarte);
if (ObservJ1 == true)
{
if (GUI.Button(new Rect(710, 50, 200, 100), "Mettre dans le Deck"))
{
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql5 = "INSERT INTO j_deck_carte (IDDECK,IDCARTE) VALUES ('1'," + this.idcarte + ")";
dbcmd.CommandText = sql5;
IDataReader reader5 = dbcmd.ExecuteReader();
while (reader5.Read())
{
}
reader5.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
if (GUI.Button(new Rect(710, 270, 200, 100), "Enlever du Deck"))
{
idcartebddJ1 = 0;
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql6 = "DELETE FROM j_deck_carte WHERE IDDECK='1' AND IDCARTE='" + this.idcarte + "'";
dbcmd.CommandText = sql6;
IDataReader reader6 = dbcmd.ExecuteReader();
while (reader6.Read())
{
}
reader6.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
if (GUI.Button(new Rect(710, 490, 200, 100), "Reposer"))
{
this.gameObject.transform.position = new Vector3(xdeck, ydeck, zdeck);
ObservJ1 = false;
}
}
if (ObservJ2 == true)
{
if (GUI.Button(new Rect(710, 50, 200, 100), "Mettre dans le Deck"))
{
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql7 = "INSERT INTO j_deck_carte (IDDECK,IDCARTE) VALUES ('2','" + this.idcarte + "')";
dbcmd.CommandText = sql7;
IDataReader reader7 = dbcmd.ExecuteReader();
while (reader7.Read())
{
}
reader7.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
if (GUI.Button(new Rect(710, 270, 200, 100), "Enlever du Deck"))
{
idcartebddJ2 = 0;
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql8 = "DELETE FROM j_deck_carte WHERE IDDECK='2' AND IDCARTE='" + this.idcarte + "'";
dbcmd.CommandText = sql8;
IDataReader reader8 = dbcmd.ExecuteReader();
while (reader8.Read())
{
}
reader8.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
if (GUI.Button(new Rect(710, 490, 200, 100), "Reposer"))
{
this.gameObject.transform.position = new Vector3(xdeck, ydeck, zdeck);
ObservJ2 = false;
}
}
}
/// <summary>
/// ////////////////////////////////////////voir si la carte est dans le 2e deck
/// </summary>
public void voirsidsdeck2(int idcarte)
{
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql4 = "SELECT DISTINCT j_deck_carte.idcarte,j_deck_carte.IDDECK, nomcarte FROM JOUEUR INNER JOIN DECK ON joueur.IDJ = deck.IDJ INNER JOIN j_deck_carte ON j_deck_carte.IDDECK = deck.IDDECK INNER JOIN carte ON carte.IDCARTE = j_deck_carte.IDCARTE INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK where j_deck_carte.iddeck = '2' and j_deck_carte.IDCARTE =" + this.idcarte + "";
dbcmd.CommandText = sql4;
IDataReader reader4 = dbcmd.ExecuteReader();
while (reader4.Read())
{
this.idcartebddJ2 = reader4.GetInt32(0);
}
reader4.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_assetConnectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "AssetStore");
if (m.Version == 0)
{
//fetch all assets with mediaurl and construct RexAssetData objects
List<RexAssetData> rexAssets = new List<RexAssetData>();
using (SqliteCommand cmd = new SqliteCommand(assetSelect, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (((String)reader["MediaURL"]) != "")
{
UUID id = new UUID((String) reader["UUID"]);
string mediaUrl = (String)reader["MediaURL"];
byte refreshRate = 0;
object refRate = reader["RefreshRate"];
if (refRate is byte)
{
refreshRate = (byte)refRate;
}
RexAssetData data = new RexAssetData(id, mediaUrl,refreshRate);
rexAssets.Add(data);
}
}
}
}
conn.Close();
//Now add them to ModreX database
NHibernateRexAssetData rexAssetManager = new NHibernateRexAssetData();
rexAssetManager.Initialise(m_rexConnectionString);
foreach (RexAssetData data in rexAssets)
{
rexAssetManager.StoreObject(data);
}
//finally remove realXtend properties and update version number
conn.Open();
//TODO: remove realXtend properties
// this is not done yet because SQLite is missing drop column feature
m.Version = 1;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[AssetStore] Migration failed. Reason: {0}", e);
return false;
}
}
// Hier wird die Tabelle TypPunkte mit den nötigen Werten gefüllt.
// Es wurde nur am anfang benötigt um Änderungen bei jedem Start zu übernehmen
// Wird zurzeit nicht mehr genutzt
public void fillTypPunkt()
{
Debug.Log ("fillTypPunkte");
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection.Open();
sql = " Delete From TYPPUNKTE";
_command.CommandText = sql;
_command.ExecuteNonQuery ();
_command.Dispose ();
_command = null;
_connection.Close ();
_connection.Dispose ();
_connection = null;
TypPunkte typen = new TypPunkte ();
typen.setID ("1"); typen.setTypbezeichnung ("Abzweigung"); this.addTypforPunkt (typen);
typen.setID ("2"); typen.setTypbezeichnung ("ParkPlatzFront"); this.addTypforPunkt (typen);
typen.setID ("3"); typen.setTypbezeichnung ("Parkplatz"); this.addTypforPunkt (typen);
typen.setID ("4"); typen.setTypbezeichnung ("Start"); this.addTypforPunkt (typen);
}
// Hier wird eine gegebene Drone in die Tabelle DRONEN eingefüllt
public void addDrone(Drone drone)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection .Open();
sql = "INSERT INTO DRONEN (DRONENNAME,AKTUELLERKNOTEN,HOMEPUNKTID, STATUS,CARTOSHOW) Values ('"+ drone.getName()+"','2','2','0','ef')";
_command.CommandText = sql;
_command.ExecuteNonQuery();
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
sql = null;
}
// Hier wird Ein Auto aus der Datenbank genommen mit einem als Parameter gegebenem Kennzeichen
// Die Rückgabe ist eine Object der Klasse Autos
public Autos getAutoViaKennzeichen(String Kennzeichen)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT * FROM AUTOS WHERE KENNZEICHEN='"+Kennzeichen+"' ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
Autos auto = new Autos ();
_reader.Read ();
auto.setKennzeichen (System.Convert.ToString(_reader ["KENNZEICHEN"]));
auto.setStatus (System.Convert.ToString(_reader ["STATUS"]));
_command.Dispose ();
_command = null;
_connection.Close ();
_connection.Dispose ();
_connection = null;
_reader.Close ();
_reader.Dispose ();
_reader = null;
return auto;
}
// Hier wird nur der Erste freie Parkplatz zurückgeschickt
public Parkplatz getfreeParkplatzlimit1()
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT * FROM PARKPLATZ WHERE FREI=1 LIMIT 1 ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
Parkplatz platz=new Parkplatz();
platz.setPARKPLATZNUMMER(System.Convert.ToString(_reader["PARKPLATZNUMMER"]));
platz.setFREI(System.Convert.ToString(_reader["FREI"]));
platz.setKENNZEICHEN(System.Convert.ToString(_reader["KENNZEICHENFAHRZEUG"]));
platz.setROUTENID(System.Convert.ToString(_reader["ROUTENID"] ));
platz.setXKOORD(System.Convert.ToString(_reader["XKOORD"] ));
platz.setZKOORD(System.Convert.ToString(_reader["ZKOORD"]));
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
_reader.Close();
_reader.Dispose ();
_reader = null;
return platz;
}
// Hier wird ein zufälliges PArkendes Auto zurückgesendet
public Autos getrandomparkingcar()
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT Count(KENNZEICHEN) as COUNT FROM AUTOS WHERE STATUS='2' ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
int zufall = UnityEngine.Random.Range (1, System.Convert.ToInt32(_reader ["COUNT"]));
_reader.Close ();
sql = "SELECT * FROM AUTOS WHERE STATUS = '2' LIMIT "+ zufall;
_command.CommandText = sql;
_reader = _command.ExecuteReader();
// Hier wird ein zufälliges Auto gesucht
for (int i=0; i<zufall; i++) {
_reader.Read();
}
Autos car = new Autos ();
//Debug.Log (_reader ["KENNZEICHEN"]);
car.setKennzeichen (System.Convert.ToString (_reader ["KENNZEICHEN"]));
car.setStatus("2");
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
_reader.Close ();
_reader.Dispose ();
_reader = null;
return car;
}
//Hier wird die Datenbank wieder auf Anfang gesetzt. Alle Änderungen beim Letzten Ausführen des Programms werden gelöst
public void allesaufanfang()
{
Debug.Log ("delete Car");
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection.Open();
sql = " Delete From AUTOS ";
_command.CommandText = sql;
_command.ExecuteNonQuery ();
sql=" UPDATE PARKPLATZ SET KENNZEICHENFAHRZEUG='', FREI='1' WHERE FREI='0'";
_command.CommandText = sql;
_command.ExecuteNonQuery ();
sql=" UPDATE DRONEN SET STATUS='0' WHERE STATUS > 0 ";
_command.CommandText = sql;
_command.ExecuteNonQuery ();
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
}
// Diese Wurde als Test benutzt um zu gucken ob eine Änderung an der Tabelle vorgenommen worden ist.
// Problem war Kennzeichen wurden nicht beim einfahren eines Neuen Autos nich zu einem Parkplatz zugewiesen
// Wird nicht mehr Verwendet
public void getParkplatzViaKennzeichencount(String Kennzeichen)
{
IDbConnection _connection = new SqliteConnection (_strDBName);
IDbCommand _command = _connection .CreateCommand ();
string sql;
IDataReader _reader;
_connection .Open ();
sql = "SELECT * FROM PARKPLATZ WHERE FREI='0' ";
_command.CommandText = sql;
_reader = _command.ExecuteReader ();
while (_reader.Read ()){
//Debug.Log ("ParkplatzNummer " + _reader["PARKPLATZNUMMER"]+" Kennzeichen "+_reader["KENNZEICHENFAHRZEUG"]);
}
_command.Dispose ();
_command = null;
_connection.Close ();
_connection.Dispose ();
_connection = null;
_reader.Close ();
_reader.Dispose ();
_reader = null;
}
// Hier wird nach dem Parkplatz gesucht der zu einem Als Parameter gegbenem PKW-Kennzeichen gehört.
// Die Rückgabe ist von der Klasse PArkplatz
public Parkplatz getParkplatzViaKennzeichen(String Kennzeichen)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT * FROM PARKPLATZ WHERE KENNZEICHENFAHRZEUG='"+Kennzeichen+"' ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
Parkplatz parkplatz = new Parkplatz ();
_reader.Read ();
// Hier werden die Gefundenen werte in das objekt PArkplatz geschrieben
parkplatz.setFREI(System.Convert.ToString(_reader["FREI"]));
parkplatz.setPARKPLATZNUMMER(System.Convert.ToString(_reader["PARKPLATZNUMMER"]));
parkplatz.setROUTENID(System.Convert.ToString(_reader["ROUTENID"]));
parkplatz.setKENNZEICHEN(System.Convert.ToString(_reader["KENNZEICHENFAHRZEUG"]));
parkplatz.setXKOORD(System.Convert.ToString(_reader["XKOORD"]));
parkplatz.setZKOORD(System.Convert.ToString(_reader["ZKOORD"]));
_command.Dispose ();
_command = null;
_connection.Close ();
_connection.Dispose ();
_connection = null;
_reader.Close ();
_reader.Dispose ();
_reader = null;
return parkplatz;
}
//Hier wird geguckt ob überhaupt ein aktives auto existiert
// ein Boolwert wird zurückgeschickt
public bool getifactiveCarExists()
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT COUNT(KENNZEICHEN) AS Count FROM AUTOS WHERE STATUS='1' Limit 1 ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
// Wenn ein oder mehr Active Autos existieren wirt ein True gegeben sonst ein false
if (0 < System.Convert.ToInt32 (_reader ["Count"]))
{ _command.Dispose();
_command=null;
_connection .Close();
_connection.Dispose();
_connection = null;
_reader.Close();
_reader.Dispose();
_reader=null;
return true;
}
else{
_command.Dispose();
_command=null;
_connection .Close();
_connection.Dispose();
_connection = null;
_reader.Close();
_reader.Dispose();
_reader=null;
return false;
}
}
static void Test(bool v3, string encoding) {
if (!v3)
Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
else
Console.WriteLine("Testing Version 3");
System.IO.File.Delete("SqliteTest.db");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
if (v3)
connectionString += ",Version=3";
if (encoding != null)
connectionString += ",encoding=" + encoding;
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );
dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );
dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );
Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read())
for (int i = 0; i < reader.FieldCount; i++)
Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
Console.WriteLine("read and display data using DataAdapter/DataSet...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(" " + myRow[myColumn]);
}
}
}
/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
DataTable dt = new DataTable();
adapter.Fill(dt);
DataView dv = new DataView(dt);
foreach (DataRowView myRow in dv) {
foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
Console.WriteLine(" " + myRow[myColumn.ColumnName]);
}
}*/
try {
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Console.WriteLine("Should not reach here.");
} catch (Exception e) {
Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
/*try {
dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
} catch (Exception e) {
Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
}*/
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
/// <summary>The test is finished, write the results to the DB</summary>
protected void Finished()
{
IDbConnection dbcon = new SqliteConnection(_db_con);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
string sql = "INSERT INTO iterations (iter, time) VALUES ( " +
_current_iter + ", datetime())";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();
foreach(ResultState rs in _results.Values) {
int hits = 0;
double rtt = 0;
double hops = 0;
for(int i = 0; i < 3; i++) {
if(rs.RTTs[i] == TimeSpan.MinValue) {
continue;
}
hits++;
rtt += rs.RTTs[i].TotalSeconds;
hops += rs.Hops[i];
}
if(hits > 0) {
rtt /= hits;
hops /= hits;
} else {
rtt = -1;
hops = -1;
}
sql = "INSERT INTO crawl (iter, Address, Hits, Rtt, Hops) " +
" VALUES ( " + _current_iter + ", \"" + GetAddress(rs.Target) + "\", " +
hits + ", " + rtt + ", " + hops + ")";
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery();
}
dbcmd.Dispose();
dbcon.Close();
_results = null;
_outstanding = null;
_current_iter++;
Interlocked.Exchange(ref _running, 0);
}
static void Main(string[] args)
{
Console.WriteLine("If this test works, you should get:");
Console.WriteLine("Data 1: 5");
Console.WriteLine("Data 2: Mono");
Console.WriteLine("create SqliteConnection...");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
Console.WriteLine("setting ConnectionString using: " +
connectionString);
dbcon.ConnectionString = connectionString;
Console.WriteLine("open the connection...");
dbcon.Open();
Console.WriteLine("create SqliteCommand to CREATE TABLE MONO_TEST");
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT )";
Console.WriteLine("execute command...");
dbcmd.ExecuteNonQuery();
Console.WriteLine("set and execute command to INSERT INTO MONO_TEST");
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC )"+
"VALUES(5,'Mono')";
dbcmd.ExecuteNonQuery();
Console.WriteLine("set command to SELECT FROM MONO_TEST");
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
Console.WriteLine("execute reader...");
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read()) {
Console.WriteLine("Data 1: " + reader[0].ToString());
Console.WriteLine("Data 2: " + reader[1].ToString());
}
Console.WriteLine("read and display data using DataAdapter...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(myRow[myColumn]);
}
}
}
Console.WriteLine("clean up...");
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
Console.WriteLine("Done.");
}
//Hier wird angegeben ob es Mindestens Ein Freies Parkplatz gibt
public int getanzahlfreeparkplaetzelimit1()
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT count(PARKPLATZNUMMER) as Count FROM PARKPLATZ WHERE FREI='1' ";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
_connection .Close();
_connection.Dispose ();
_connection = null;
_command.Dispose ();
_command = null;
int wert = System.Convert.ToInt32(_reader ["Count"]);
_reader.Close();
_reader.Dispose ();
_reader = null;
return wert;
}
