public bool GetSchool()
{
bool bReturn = false;
sqlite_cmd = new SQLiteCommand("Select * from ProgramConfig where id = 1", sqlite_conn);
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
Console.WriteLine(sqlite_datareader["sLatitude"].ToString());
school = new School(sqlite_datareader["sPassword"].ToString(), sqlite_datareader["sEmail"].ToString(), sqlite_datareader["sSchoolName"].ToString(),
sqlite_datareader["sAddress"].ToString(), sqlite_datareader["sCity"].ToString(), sqlite_datareader["sState"].ToString(), sqlite_datareader["sZip"].ToString(),
sqlite_datareader["sLatitude"].ToString(), sqlite_datareader["sLongitude"].ToString(), sqlite_datareader["sImageFile"].ToString());
try
{
string sExistingDatabaseVersion = sqlite_datareader["sDatabaseVersion"].ToString();
if(OldDBVersion(sExistingDatabaseVersion,DatabaseVersion))
{
UpgradeDB(sExistingDatabaseVersion, DatabaseVersion);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("No database version available. Need to upgrade to DB Version 1.0 after backing things up.");
}
bReturn = true;
}
return bReturn;
}
public void arrayExecute(GameObject[] spawnArray, string type)
{
reader=command.ExecuteReader();
int sentinal=0;
while(reader.Read())
{
Debug.Log(reader[0]);
spawnArray[sentinal] = new GameObject(reader[0].ToString());
spawnArray[sentinal].AddComponent<FoodStats>();
Debug.Log(spawnArray[sentinal].name.ToString() + " spawned");
spawnArray[sentinal].GetComponent<FoodStats>().calories = int.Parse(reader[1].ToString()); //int.Parse(reader[4].ToString());
if (spawnArray[sentinal].GetComponent<FoodStats>().calories != null)
{
Debug.Log(spawnArray[sentinal].name.ToString() + " calories calculated" + " " + spawnArray[sentinal].GetComponent<FoodStats>().calories.ToString());
}
else
{
Debug.Log("Skipped");
}
spawnArray[sentinal].GetComponent<FoodStats>().setFoodType(type);
Debug.Log(spawnArray[sentinal].name.ToString() + " type set");
sentinal++;
}
reader.Close();
}
public List<Drop> GetDrops(int mobId)
{
// create a new SQL command:
sqlite_cmd = sqlite_conn.CreateCommand();
// But how do we read something out of our table ?
// First lets build a SQL-Query again:
sqlite_cmd.CommandText = "SELECT * FROM droplist where mobId = " + mobId;
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
List<Drop> droplist = new List<Drop>(10);
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
int itemId = Convert.ToInt32(sqlite_datareader["itemId"]);
int min = Convert.ToInt32(sqlite_datareader["min"]);
int max = Convert.ToInt32(sqlite_datareader["max"]);
int category = Convert.ToInt32(sqlite_datareader["category"]);
int chance = Convert.ToInt32(sqlite_datareader["chance"]);
droplist.Add(new Drop(mobId, itemId, min, max, category, chance));
}
return droplist;
}
void LoadUserSelect()
{
comboUser.Items.Clear();
SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People", sqlite_conn);
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
//Console.WriteLine(String.Format("{0}, {1}", sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
comboUser.Items.Add(String.Format("{0}: {1}, {2}", sqlite_datareader["id"], sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
}
}
public void Execute()
{
reader=command.ExecuteReader();
while(reader.Read())
{
Debug.Log(reader[0]);
//mystring = reader[0].ToString();
mynum= int.Parse(reader[0].ToString());
Debug.Log(mynum + "edit");
/*
for(int counter=0; counter<4; counter++)
{
Debug.Log(reader[counter]);
}
*/
}
reader.Close();
}
/// <summary>
/// return if exist config in SQLite
/// </summary>
/// <returns></returns>
public List<UiFontConf> EnumUiFont()
{
List<UiFontConf> t = new List<UiFontConf>();
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"select * from uifontconf top1";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
UiFontConf f = new UiFontConf()
{
Fontname = DataReader["fontname"].ToString(),
FontSize = Convert.ToInt32(DataReader["fontsize"].ToString()),
Isbold =Convert.ToBoolean(DataReader["isbold"].ToString()),
Isitalic = Convert.ToBoolean(DataReader["isitalic"].ToString()),
ColorName = DataReader["fontcolor"].ToString()
};
t.Add(f);
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
return null;
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return t;
}
/// <summary>
/// metodo cria ou acessa banco de dados sqlite carregar configs
/// </summary>
public void CreateOrAccessDataBase()
{
try
{
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (!ExistDataBase())
{
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3;New=True;Compress=True");
Connection.Open();
string[] config = new string[] {string.Empty, string.Empty, string.Empty};
// create table(s)
// config => contais configurations
config[0] = @"create table config (port interger(4)" +
@",server varchar(30) primary key" +
@",language varchar(20))";
// channels => contais channels
config[1] = @"create table channels (channel varchar(30) primary key)";
// user(s) => my login accont user(s) used login auth SSL
config[2] = @"create table users (nick varchar(20) primary key" +
@",password varchar(50))";
foreach (string i in config)
{
SQLiteCommand c = new SQLiteCommand(i, Connection);
c.ExecuteNonQuery();
}
IsDatabase = true;
}
else
{
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
Connection.Open();
IsDatabase = true;
// select database to list all channels and servers
C = Connection.CreateCommand();
C.CommandText = @"SELECT channel FROM channels";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
MainWindow.Configuration.Channels.Add(new TemplateChannels
{
Channels = DataReader["channel"].ToString()
});
}
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.ToString());
IsDatabase = false;
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
/// <summary>
/// check user exist not INSERT
/// </summary>
/// <param name="user">user</param>
/// <returns></returns>
public bool CheckUserExist(string user)
{
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT user FROM myusers WHERE user ='******'";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
return true;
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
return false;
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return false;
}
/// <summary>
/// retorna enum games in database
/// </summary>
/// <returns></returns>
public List<Games> EnumGamesSaved()
{
List<Games> t = new List<Games>();
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"select * from games";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
if (DataReader["IsEnabled"].ToString() == @"true")
{
Enabled = true;
}
else
{
Enabled = false;
}
Games g = new Games()
{
Name = DataReader["Name"].ToString(),
//DisplayMessageCount = Convert.ToInt32(DataReader["DisplayMessaCount"].ToString()),
IsEnabled = Enabled
};
t.Add(g);
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return t;
}
/// <summary>
/// return last channel connected saved in database
/// </summary>
/// <returns></returns>
public string GetLastChannelUsedDataBase()
{
string temp = string.Empty;
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT channel FROM users TOP1";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
temp = DataReader["channel"].ToString();
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.ToString());
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return temp;
}
/// <summary>
/// list all users in database
/// </summary>
/// <returns></returns>
public List<TemplateMyUsersDataBase> GetListMyUsersDataBase()
{
List<TemplateMyUsersDataBase> myUsersDataBases = new List<TemplateMyUsersDataBase>();
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT user , password , owner FROM myusers";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
TemplateMyUsersDataBase t = new TemplateMyUsersDataBase()
{
User = DataReader["user"].ToString(),
Password = DataReader["password"].ToString(),
Owner = DataReader["owner"].ToString()
};
myUsersDataBases.Add(t);
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return myUsersDataBases;
}
internal void AttachDataReader( SQLiteDataReader reader )
{
mServingDataReader = true;
if( mpConn != null )
mpConn.AttachDataReader(reader);
}
/// <summary>
/// list top 1 user => used for Shamia connect
/// </summary>
/// <returns></returns>
public List<TemplateUserDataBase> EnumUserDataBase()
{
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
List<TemplateUserDataBase> t = new List<TemplateUserDataBase>();
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT *FROM users TOP1";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
TemplateUserDataBase temp = new TemplateUserDataBase()
{
Nick = DataReader["nick"].ToString(),
Password = DataReader["password"].ToString(),
Auth = DataReader["auth"].ToString(),
Owner = DataReader["owner"].ToString(),
Port = Convert.ToInt32(DataReader["port"].ToString())
};
t.Add(temp);
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.ToString());
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return t;
}
/// <summary>
/// retorna o usuario para ser editado ou deletado
/// </summary>
/// <returns></returns>
/// <param name="user">user nick</param>
internal TemplateMyUsersDataBase GetUserEdit(string user)
{
TemplateMyUsersDataBase t = new TemplateMyUsersDataBase();
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT user, password , owner FROM myusers WHERE user ='******'";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
t.User = DataReader["user"].ToString();
t.Password = DataReader["password"].ToString();
t.Owner = DataReader["owner"].ToString();
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
return null;
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return t;
}
internal void DetachDataReader(SQLiteDataReader pReader)
{
--mDataReaderCount;
if( mDataReaderCount == 0 && mState == ConnectionState.Executing )
mState = ConnectionState.Open;
if( (pReader.mCmdBehavior & CommandBehavior.CloseConnection) != 0 )
{
if( mDataReaderCount == 0 )
Close();
else
throw new InvalidOperationException();
}
}
internal void DetachDataReader( SQLiteDataReader reader )
{
if( mpConn != null )
mpConn.DetachDataReader(reader);
UnCompile();
mServingDataReader = false;
}
// afficher tout les voiture
public List<Voiture> getLseVoitures()
{
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
List<Voiture> voitures = new List<Voiture>();
string req = "select * from Automobile where AutoMoto='True';";
// Lets insert something into our new table:
sqlite_cmd.CommandText = req;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
Voiture v = new Voiture();
v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
v.Coulour = sqlite_datareader["Coulour"].ToString();
v.Marque = sqlite_datareader["Marque"].ToString();
v.TypeV = sqlite_datareader["TypeV"].ToString();
voitures.Add(v);
}
sqlite_conn.Close();
return voitures;
}
public Moto getUnMoto(string immatriculation)
{
Moto m = new Moto();
try {
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
string req = "select * from Automobile where Immatriculation='" + immatriculation + "' and AutoMoto='False';";
// Lets insert something into our new table:
sqlite_cmd.CommandText = req;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
m.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
m.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
m.Cylindre = Int32.Parse(sqlite_datareader["Cylindre"].ToString());
m.VitesseMax = Int32.Parse(sqlite_datareader["VitesseMax"].ToString());
}
sqlite_conn.Close();
}
catch (Exception)
{
Console.WriteLine("Erreur dans la requette");
}
return m;
}
public Voiture getUneVoiture(string immatriculation)
{
Voiture v = new Voiture();
try
{
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
string req = "select * from Automobile where Immatriculation='" + immatriculation + "' and AutoMoto='True';";
sqlite_cmd.CommandText = req;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
v.Coulour = sqlite_datareader["Coulour"].ToString();
v.Marque = sqlite_datareader["Marque"].ToString();
v.TypeV = sqlite_datareader["TypeV"].ToString();
}
sqlite_conn.Close();
}
catch (Exception)
{
Console.WriteLine("Erreur dans la requette");
}
return v;
}
//afficher les motos
public List<Moto> getLesMotos()
{
List<Moto> motos = new List<Moto>();
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
string req = "select * from Automobile where AutoMoto='False';";
// Lets insert something into our new table:
sqlite_cmd.CommandText = req;
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
Moto v = new Moto();
v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
v.Cylindre = Int32.Parse(sqlite_datareader["Cylindre"].ToString());
v.VitesseMax = Int32.Parse(sqlite_datareader["VitesseMax"].ToString());
motos.Add(v);
}
sqlite_conn.Close();
return motos;
}
internal void AttachDataReader(SQLiteDataReader pReader)
{
++mDataReaderCount;
mState = ConnectionState.Executing;
}
/// <summary>
/// retorna bandeira de idioma usado
/// </summary>
/// <returns></returns>
public string GetLang()
{
Clanguage._ListLangs();
string file = AppDomain.CurrentDomain.BaseDirectory +
"Database\\Shamia.db";
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
";New=False;Compress=True");
try
{
Connection.Open();
C = Connection.CreateCommand();
C.CommandText = @"SELECT language FROM config LIMIT 1";
DataReader = C.ExecuteReader();
while (DataReader.Read())
{
foreach (TemplateLang i in Clanguage.Langs)
{
if (i.Lang == DataReader["language"].ToString())
{
return i.Flag;
}
}
//return DataReader["language"].ToString();
}
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.ToString());
return string.Empty;
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
return string.Empty;
}
private void comboUserSelect_SelectedIndexChanged(object sender, EventArgs e)
{
SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
string[] SelectedUser = comboUserSelect.SelectedItem.ToString().Split(':');
int iUserID = Int32.Parse(SelectedUser[0]);
sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
txtID.Text = String.Format("{0}", sqlite_datareader["id"]);
txtEditLName.Text = String.Format("{0}",sqlite_datareader["stxtLname"]);
txtEditFName.Text = String.Format("{0}", sqlite_datareader["stxtFname"]);
txtEditAddress.Text = String.Format("{0}", sqlite_datareader["stxtAddress"]);
txtEditCity.Text = String.Format("{0}", sqlite_datareader["stxtCity"]);
txtEditState.Text = String.Format("{0}", sqlite_datareader["stxtState"]);
txtEditZip.Text = String.Format("{0}", sqlite_datareader["stxtZip"]);
}
LoadEContacts();
LoadGuardians();
}
private bool ProcessRegistration(int iUserID)
{
SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
bool bReturn = false;
DateTime dtToday = DateTime.Now;
sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
string fName = "";
string lName = "";
bool bFound = false;
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
lName = String.Format("{0}", sqlite_datareader["stxtLname"]);
fName = String.Format("{0}", sqlite_datareader["stxtFname"]);
bFound = true;
}
if (bFound)
{
DialogResult result1 = MessageBox.Show(String.Format("Hello {0} {1}, If you are checking in for class click yes.", fName, lName), "Confirm registration", MessageBoxButtons.YesNo);
if (result1 == DialogResult.Yes)
{
sqlite_cmd = new SQLiteCommand("INSERT INTO Attendence (id,PersonID,Date,ClassID) VALUES (@id,@UserID,@today,@ClassID)", sqlite_conn);
sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = null;
sqlite_cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = iUserID;
sqlite_cmd.Parameters.Add("@today", SqlDbType.Text).Value = dtToday.ToLongDateString();
sqlite_cmd.Parameters.Add("@ClassID", SqlDbType.Int).Value = 0;
try
{
sqlite_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
bReturn = true;
MessageBox.Show("You are registered for class.");
}
}
else
{
MessageBox.Show("ID was not found, please confirm your ID with the admins.");
}
return bReturn;
}
