/** public void createUc() - create user controls (display reminders) *
*/
public void createUc()
{
int i = 0;
UserControl1[] uc = new UserControl1[200];
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand get = new SQLiteCommand("SELECT * FROM reminder LIMIT 0 , 30", new_con);
String header;
SQLiteDataReader reader;
reader = get.ExecuteReader();
while (reader.Read())
{
//data[i,0] = new ArrayList();
string[] str = new string[3];
str[0]=reader[0].ToString();
str[1]=DateTime.Parse(reader[1].ToString()).ToShortDateString();
str[2]=DateTime.Parse(reader[5].ToString()).ToShortTimeString();
reminderList.Add(str);
header = String.Format("{1,-20} {0,5}", reader[2].ToString(), DateTime.Parse(reader[1].ToString()).ToShortDateString());
uc[i] = new UserControl1();
uc[i].setContent(Convert.ToInt16(reader[0].ToString()), reader[3].ToString(), reader[4].ToString(), DateTime.Parse(reader[5].ToString()).ToShortTimeString() , header);
WrapPanel1.Children.Add(uc[i]);
i++;
}
reminderlistarray = reminderList.ToArray();
}
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;
}
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;
}
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"]));
}
}
/// <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 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;
}
public string BuildTotalAttendenceReport()
{
string rString = String.Format("Name and number of classes attended.\r\n");
SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT PersonID, COUNT(*) from Attendence GROUP BY PersonID ORDER BY PersonID", sqlite_conn);
try
{
sqlite_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
SQLiteDataReader 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
{
rString += String.Format("{0}, {1}\r\n", GetUserName(Int32.Parse(String.Format("{0}", sqlite_datareader[0])), 3), sqlite_datareader[1]);
//comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
}
return rString;
}
/** private void MenuViewContacts_Click(object sender, System.Windows.RoutedEventArgs e)
* view Contacts
*/
public void createUserControlContact()
{
int i = 0;
UserControlExpanderContact[] uc = new UserControlExpanderContact[200];
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand get = new SQLiteCommand("SELECT * FROM contact_detail ORDER BY firstname ASC LIMIT 0 , 30", new_con);
SQLiteDataReader reader;
reader = get.ExecuteReader();
String header;
while (reader.Read())
{
header = reader[1].ToString() + "\t\t\t" + reader[2].ToString();
uc[i] = new UserControlExpanderContact();
uc[i].setContent(Convert.ToInt32(reader[0].ToString()), reader[2].ToString(), reader[3].ToString(), reader[5].ToString(), reader[4].ToString(), header);
WrapPanel1.Children.Add(uc[i]);
i++;
}
new_con.Close();
}
/// <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>
/// 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;
}
// 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;
}
public string BuildEODAttendenceReport(int iDayOffSet)
{
string rString = String.Format("Name and number of classes attended for {0}.\r\n", DateTime.Today.AddDays(iDayOffSet).ToShortDateString());
SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT PersonID, COUNT(*) from Attendence Where Date = @Date GROUP BY PersonID ORDER BY PersonID",sqlite_conn);
sqlite_cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.AddDays(iDayOffSet).ToLongDateString();
try
{
sqlite_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
SQLiteDataReader 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
{
rString += String.Format("{0}, {1}\r\n", GetUserName(Int32.Parse(String.Format("{0}",sqlite_datareader[0])),3), sqlite_datareader[1]);
//comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
}
return rString;
}
public string GetUserName(int id,int format)
{
string rString = "";
SQLiteCommand sqlite_cmd2 = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
sqlite_cmd2.Parameters.Add("@id", SqlDbType.Int).Value = id;
try
{
sqlite_cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
// Now the SQLiteCommand object can give us a DataReader-Object:
SQLiteDataReader sqlite_datareader = sqlite_cmd2.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"]));
// 1 = First Name
// 2 = Last Name
// 3 = First Last
// 4 = Last, First
switch (format)
{
case 1:
{
rString = String.Format("{0}",sqlite_datareader["stxtfname"]);
break;
}
case 2:
{
rString = String.Format("{1}",sqlite_datareader["stxtLname"]);
break;
}
case 3:
{
rString = String.Format("{0} {1}",sqlite_datareader["stxtFname"], sqlite_datareader["stxtLname"]);
break;
}
case 4:
{
rString = String.Format("{0} {1}",sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]);
break;
}
default:
{
break;
}
}
}
return rString;
}
/// <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;
}
/// <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;
}
/** private void ButtonOk_Click(object sender, RoutedEventArgs e)
* add new reminder
*/
private void ButtonOk_Click(object sender, RoutedEventArgs e)
{
int index=0,n=0;
int[] array = new int[10000];
string date = ReplaceApostrophe(DatePicker1.SelectedDate.Value.ToShortDateString());
string name = ReplaceApostrophe(ReminderName.Text);
string note = ReplaceApostrophe(Note.Text);
string snooze = ReplaceApostrophe(NumericUpDown1.Value.ToString());
string time = ReplaceApostrophe(TimePicker.SelectedTime.Value.ToString());
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand new_cnt = new SQLiteCommand("SELECT rid FROM reminder", new_con);
SQLiteDataReader reader;
reader = new_cnt.ExecuteReader();
while (reader.Read())
{
array[index] = Convert.ToInt32(reader[0].ToString());
index++;
}
if (!(index == 0))
{
n=index;
insertionSort(array, n);
index = 0;
while (index<n)
{
if (!(array[index] == index))
break;
index++;
}
}
SQLiteCommand get=new SQLiteCommand("INSERT INTO reminder VALUES ('"+ index +"', '" + date + "', '" + name + "', '" + note + "', '" + snooze + "', '" + time + "')", new_con);
get.ExecuteNonQuery();
this.Close();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdate();
}
/// <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>
/// Create and prepare a SQLiteCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid SQLiteConnection, on which to execute this command</param>
/// <param name="transaction">A valid SQLiteTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of SQLiteParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SQLiteHelper</param>
/// <returns>SQLiteDataReader containing the results of the command</returns>
private static SQLiteDataReader ExecuteReader(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, SQLiteConnectionOwnership connectionOwnership)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create a reader
SQLiteDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SQLiteConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the SQLiteParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SQLiteReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach(SQLiteParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if( mustCloseConnection )
connection.Close();
throw;
}
}
/// <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;
}
private void ButtonOk_Click(object sender, RoutedEventArgs e)
{
int index = 0, n = 0;
int[] array = new int[10000];
string firstname = ReplaceApostrophe(TextBoxFirstName.Text);
string lastname = ReplaceApostrophe(TextBoxLastName.Text);
string mail = ReplaceApostrophe(TextBoxMail.Text);
string address = ReplaceApostrophe(TextBoxAddress.Text);
try
{
int number= Convert.ToInt32(TextBoxPhone.Text);
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand new_cnt = new SQLiteCommand("SELECT cid FROM contact_detail", new_con);
SQLiteDataReader reader;
reader = new_cnt.ExecuteReader();
while (reader.Read())
{
array[index] = Convert.ToInt32(reader[0].ToString());
index++;
}
if (!(index == 0))
{
n = index;
insertionSort(array, n);
index = 0;
while (index < n)
{
if (!(array[index] == index))
break;
index++;
}
}
SQLiteCommand get = new SQLiteCommand("INSERT INTO contact_detail VALUES ('" + index + "', '" + firstname + "', '" + lastname + "', '" + Convert.ToInt32(number) + "', '" + address + "', '" + mail + "','','')", new_con);
get.ExecuteNonQuery();
new_con.Close();
this.Close();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdateContact();
}
catch
{
MessageBox.Show("Phone number is invalid!");
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand addAr = new SQLiteCommand("Select * from contact_detail where cid='" + cid + "'", new_con);
SQLiteDataReader reader;
reader = addAr.ExecuteReader();
while (reader.Read())
{
TextBoxFirstName.Text = reader[1].ToString();
TextBoxLastName.Text = reader[2].ToString();
TextBoxPhone.Text = reader[3].ToString();
TextBoxMail.Text = reader[5].ToString();
TextBoxAddress.Text = reader[4].ToString();
}
new_con.Close();
}
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 void Window_Loaded(object sender, RoutedEventArgs e)
{
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
SQLiteCommand addAr = new SQLiteCommand("Select * from reminder where rid='" + Convert.ToInt32(cnt) + "'", new_con);
SQLiteDataReader reader;
reader = addAr.ExecuteReader();
while (reader.Read())
{
ReminderName.Text = reader[2].ToString();
DatePicker1.Text = reader[1].ToString();
TimePicker.DateTimeText = Convert.ToDateTime(reader[5].ToString()).ToShortTimeString();
Note.Text = reader[3].ToString();
NumericUpDown1.Value = Convert.ToDouble(reader[4].ToString());
}
new_con.Close();
}
/// <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;
}
void LoadGuardians()
{
comboGuardians.Items.Clear();
SQLiteCommand sqlite_cmd3 = new SQLiteCommand("SELECT * FROM Guardians Where iStudent=@iStudent", sqlite_conn);
sqlite_cmd3.Parameters.Add("@iStudent", SqlDbType.Int).Value = Int32.Parse(txtID.Text);
try
{
sqlite_cmd3.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
// Now the SQLiteCommand object can give us a DataReader-Object:
SQLiteDataReader sqlite_datareader3 = sqlite_cmd3.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader3.Read()) // Read() returns true if there is still a result line to read
{
SQLiteCommand sqlite_cmd2 = new SQLiteCommand("SELECT * FROM People Where id = @id", sqlite_conn);
string[] SelectedUser = comboUserSelect.SelectedItem.ToString().Split(':');
int iUserID = Int32.Parse(SelectedUser[0]);
sqlite_cmd2.Parameters.Add("@id", SqlDbType.Int).Value = sqlite_datareader3["iPerson"];
try
{
sqlite_cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
// Now the SQLiteCommand object can give us a DataReader-Object:
SQLiteDataReader sqlite_datareader2 = sqlite_cmd2.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader2.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"]));
comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
}
}
}
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;
}
