private static void InitConnection()
{
Console.WriteLine(Application.UserAppDataPath);
string dataPath = Application.UserAppDataPath + "\\" + "data";
if (Directory.Exists(dataPath) && File.Exists(dataPath + "\\database.db"))
{
sqlConnection =
new SQLiteConnection(
"Data Source=" + dataPath + "\\database.db" +
";Version=3;New=False;Compress=True;UTF8Encoding=True;");
sqlConnection.Open();
sqlCommand = sqlConnection.CreateCommand();
}
else
{
if (!Directory.Exists(dataPath))
{
Directory.CreateDirectory(dataPath);
ClearFileUnderPath(dataPath);
}
sqlConnection =
new SQLiteConnection(
"Data Source=" + dataPath + "\\database.db" +
";Version=3;New=True;Compress=True;UTF8Encoding=True;");
sqlConnection.Open();
ClearFileUnderPath(dataPath);
sqlCommand = sqlConnection.CreateCommand();
sqlCommand.CommandText = "create table movie(fileLocation nvarchar primary key)";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "create table music(fileLocation nvarchar primary key)";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "create table file(fileLocation nvarchar primary key)";
sqlCommand.ExecuteNonQuery();
}
//string sql = "select count(*) as c from sqlite_master where type ='table' and name ='movie'";
//sqlCommand.CommandText =
// "create table movie(fileLocation nvarchar primary key)";
//sqlCommand.ExecuteNonQuery();
//sqlCommand.CommandText = "create table music(fileLocation nvarchar primary key)";
//sqlCommand.ExecuteNonQuery();
//sqlCommand.CommandText = "create table file(fileLocation nvarchar primary key)";
//sqlCommand.ExecuteNonQuery();
}
// create a new database connection:
//ajouter voiture
public bool ajouterVoiture(Voiture v)
{
try
{
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
string req = "Insert Into Automobile(Annee, Immatriculation, Coulour, Marque, TypeV, AutoMoto) Values (" + v.Annee + ", '" + v.Immatriculation + "', '" + v.Coulour + "', '" + v.Marque + "', '" + v.TypeV + "', 'True');";
// Lets insert something into our new table:
sqlite_cmd.CommandText = req;
sqlite_cmd.ExecuteNonQuery();
sqlite_conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
// Let the SQLiteCommand object know our SQL-Query:
sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;Compress=True;");
sqlite_conn.Open();
//UPDATE People set stxtFname=@stxtFname,stxtLname = @stxtLName,
SQLiteCommand sqlite_cmd = new SQLiteCommand(@"Update ProgramConfig Set sPassword = @password, sEmail = @email, sSchoolName = @name,
sAddress = @address, sCity = @city, sState = @state, sZip = @zip, sLatitude = @latitude,
sLongitude = @longitude, sImageFile = @ImageFile Where id = 1", sqlite_conn);
sqlite_cmd.Parameters.Add("@password", SqlDbType.Text).Value = parentForm.school.password;
sqlite_cmd.Parameters.Add("@email", SqlDbType.Text).Value = parentForm.school.email;
sqlite_cmd.Parameters.Add("@name", SqlDbType.Text).Value = parentForm.school.name;
sqlite_cmd.Parameters.Add("@address", SqlDbType.Text).Value = parentForm.school.address;
sqlite_cmd.Parameters.Add("@city", SqlDbType.Text).Value = parentForm.school.city;
sqlite_cmd.Parameters.Add("@state", SqlDbType.Text).Value = parentForm.school.state;
sqlite_cmd.Parameters.Add("@zip", SqlDbType.Text).Value = parentForm.school.zip;
sqlite_cmd.Parameters.Add("@latitude", SqlDbType.Text).Value = parentForm.school.latitude;
sqlite_cmd.Parameters.Add("@longitude", SqlDbType.Text).Value = parentForm.school.longitude;
sqlite_cmd.Parameters.Add("@ImageFile", SqlDbType.Text).Value = parentForm.school.ImageFile;
sqlite_cmd.CommandType = CommandType.Text;
try
{
int i = sqlite_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
parentForm.RefreshLogo(parentForm.school.ImageFile);
this.Close();
}
public void ExecuteQuery(string txtQuery)
{
SetConnection();
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = txtQuery;
sql_cmd.ExecuteNonQuery();
sql_con.Close();
}
static private void ExecuteQuery(string txtQuery)
{
lock(sql_con)
{
SetConnection();
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = txtQuery;
sql_cmd.ExecuteNonQuery();
sql_con.Close();
}
}
public DataTable Insert(string queryString)
{
try
{
lock(DataBase._conn)
{
SQLiteCommand Cmd = new SQLiteCommand();
Cmd = DataBase._conn.CreateCommand();
Cmd.CommandText = queryString;
Cmd.CommandType=CommandType.Text ;
Cmd.ExecuteNonQuery();
}
return null;
}catch(Exception e){
Debug.WriteLine("DataBase Insert Problem: "+ e.Message);
return null;
}
}
private void btnAddGuardian_Click(object sender, EventArgs e)
{
SearchForUser GetContact = new SearchForUser(GUARDIAN_ASSOCIATION_REQUEST, this, sqlite_conn);
GetContact.ShowDialog();
Console.Write("{0}{1}", iUser, sRelationship);
SQLiteCommand sqlite_cmd = new SQLiteCommand("INSERT INTO Guardians (id,iStudent,iPerson,sRelationship) VALUES (@id,@iStudent,@iPerson,@sRelationship)", sqlite_conn);
sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = null;
sqlite_cmd.Parameters.Add("@iStudent", SqlDbType.Int).Value = Int32.Parse(txtID.Text);
sqlite_cmd.Parameters.Add("@iPerson", SqlDbType.Int).Value = iUser;
sqlite_cmd.Parameters.Add("@sRelationship", SqlDbType.Text).Value = sRelationship;
try
{
sqlite_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
LoadGuardians();
}
/** private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
* delete reminder
*/
private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
{
SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_con.Open();
if (delete_status == 1)
{
SQLiteCommand get = new SQLiteCommand("DELETE FROM reminder WHERE rid ='" + Convert.ToInt32(id) + "'", new_con);
get.ExecuteNonQuery();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdate();
}
else
{
SQLiteCommand get = new SQLiteCommand("DELETE FROM contact_detail WHERE cid ='" + Convert.ToInt32(id) + "'", new_con);
get.ExecuteNonQuery();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdateContact();
}
new_con.Close();
this.Close();
}
//ajouter moto
public bool ajouterMoto(Moto v)
{
try
{
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
string req = "Insert Into Automobile(Annee, Immatriculation, Cylindre , VitesseMax, AutoMoto) Values (" + v.Annee + ", '" + v.Immatriculation + "', " + v.Cylindre + ", " + v.VitesseMax + ", 'False');";
// Lets insert something into our new table:
sqlite_cmd.CommandText = req;
sqlite_cmd.ExecuteNonQuery();
sqlite_conn.Close();
return true;
}
catch (Exception)
{
return false;
}
}
/** private void ButtonOk_Click(object sender, RoutedEventArgs e)
* update reminder
*/
private void ButtonOk_Click(object sender, RoutedEventArgs e)
{
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_up = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_up.Open();
SQLiteCommand get = new SQLiteCommand("Update reminder set date='" + date + "',name='" + name + "', note='" + note + "',snooze='" + snooze + "',time='" + time + "' where rid='" + Convert.ToInt32(cnt) + "'", new_up);
get.ExecuteNonQuery();
new_up.Close();
this.Close();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdate();
}
/// <summary>
/// Execute a SQLiteCommand (that returns no resultset) against the specified SQLiteConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.Text, "Update TableTransaction set OrderAmount = 500 where ProdId=?", new SQLiteParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SQLiteConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SQLiteParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
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!");
}
}
/// <summary>
/// update user
/// </summary>
/// <param name="user"></param>
/// <param name="password"></param>
/// <param name="owner"></param>
/// <param name="oldnick"></param>
internal void UpdateUserIn(string user, string password, string owner ,string oldnick)
{
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 = "UPDATE myusers SET user ='******',"+
"password='******'," +
"owner='" + owner + "'" +
" WHERE user='******'";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
/** 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();
}
private void ButtonOk_Click(object sender, RoutedEventArgs e)
{
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_up = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
new_up.Open();
SQLiteCommand up1 = new SQLiteCommand("Update contact_detail set firstname='" + firstname + "',lastname='" + lastname + "', number='" + Convert.ToInt32(number) + "',address='" + address + "',email='" + mail + "' where cid='" + cid + "'", new_up);
up1.ExecuteNonQuery();
new_up.Close();
this.Close();
MainWindowStart mainWindow = MainWindowStart.Instance;
mainWindow.mainWindowUpdateContact();
}
catch
{
MessageBox.Show("Phone number is invalid!");
}
}
private void btnDeletePerson_Click(object sender, EventArgs e)
{
int iUserID = Int32.Parse(txtID.Text);
SQLiteCommand deleteSQL = new SQLiteCommand("Delete from People WHERE id=@id", sqlite_conn);
deleteSQL.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
try
{
deleteSQL.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
LoadUserSelect();
}
/// <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(); }
}
}
public void UpdateUiConf(string fontname, int fontsize, bool isbold, bool isitalic, string color)
{
// delete saved
DeleteUiConf();
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 = @"INSERT INTO uifontconf VALUES('" + fontname + "','"+
fontsize + "','" + isbold + "','" + isitalic + "','" + color +"')";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
/// <summary>
/// adciona jogo
/// </summary>
/// <param name="game">jogo</param>
public void AddGame(string game)
{
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 = @"INSERT INTO Games VALUES('" + game + "','false')";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
/// <summary>
/// creat user
/// </summary>
/// <param name="user"> user name</param>
/// <param name="password">password</param>
/// <param name="owner"></param>
public void CreatUser(string user, string password,string owner)
{
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 = @"INSERT INTO myusers (user,password,owner) VALUES ('" + user + "','" + password + "','" + owner +"')" ;
C.ExecuteNonQuery();
}
catch (Exception ex)
{
// chave duplicada
if (ex.HResult == -2146233088)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
((MainWindow)Application.Current.MainWindow).ShowMessageAsync(((MainWindow)Application.Current.MainWindow).Resources.MergedDictionaries[0]["ErroKeyDuplicateTitle"].ToString(),
((MainWindow)Application.Current.MainWindow).Resources.MergedDictionaries[0]["ErroKeyDuplicate"].ToString());
}
else
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
private void ExecuteQuery(string txtQuery)
{
try
{
SetConnection();
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = txtQuery;
sql_cmd.ExecuteNonQuery();
sql_con.Close();
}
catch(Exception e)
{}
}
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 SaveConfigToFile(string id, string val)
{
try
{
//save the current configuration
SetConnection();
sql_con.Open();
sql_cmd = new SQLiteCommand();
sql_cmd.Connection = sql_con;
sql_cmd.CommandText = "replace into data(id,value,grp,tbl) values('"+id+"','" + val + "','"+this.sql_grp+"','" + this.sql_tbl + "')";
sql_cmd.ExecuteNonQuery();
sql_con.Close();
return true;
}
catch (System.Exception e)
{
MsgDlg.Show(e.Message);
return false;
}
}
private void btnAddPerson_Click(object sender, EventArgs e)
{
SQLiteCommand insertSQL = new SQLiteCommand(@"INSERT INTO People (
id,stxtFname,stxtLname,stxtPhone,
stxtAddress,stxtCity,stxtState,stxtZip) VALUES
(@id,@stxtFname,@stxtLname,@stxtPhone,@stxtAddress,@stxtCity,
@stxtState, @stxtZip)", sqlite_conn);
//SQLiteCommand test = new SQLiteCommand("")
insertSQL.Parameters.Add("@id", SqlDbType.Int).Value = null;
insertSQL.Parameters.Add("@stxtFname", SqlDbType.Text).Value = txtEditFName.Text;
insertSQL.Parameters.Add("@stxtLname", SqlDbType.Text).Value = txtEditLName.Text;
insertSQL.Parameters.Add("@stxtPhone", SqlDbType.Text).Value = txtEditPhone.Text;
insertSQL.Parameters.Add("@stxtAddress", SqlDbType.Text).Value = txtEditAddress.Text;
insertSQL.Parameters.Add("@stxtCity", SqlDbType.Text).Value = txtEditCity.Text;
insertSQL.Parameters.Add("@stxtState", SqlDbType.Text).Value = txtEditState.Text;
insertSQL.Parameters.Add("@stxtZip", SqlDbType.Text).Value = txtEditZip.Text;
insertSQL.CommandType = CommandType.Text;
try
{
insertSQL.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
LoadUserSelect();
}
private void btnEditPerson_Click(object sender, EventArgs e)
{
SQLiteCommand updateSQL = new SQLiteCommand("UPDATE People set stxtFname=@stxtFname,stxtLname = @stxtLName, stxtPhone=@stxtPhone, stxtAddress=@stxtAddress,stxtCity=@stxtCity,stxtState=@stxtState,stxtZip=@stxtZip Where id=@id", sqlite_conn);
//,stxtLname,stxtPhone,stxtAddress,stxtCity,stxtState,stxtZip Where id = $) VALUES (@id,@stxtFname,@stxtLname,@stxtPhone,@stxtAddress,@stxtCity,@stxtState, @stxtZip)", sqlite_conn);
int iUserID = Int32.Parse(txtID.Text);
updateSQL.Parameters.Add("@id", SqlDbType.Int).Value = iUserID;
updateSQL.Parameters.Add("@stxtFname", SqlDbType.Text).Value = txtEditFName.Text;
updateSQL.Parameters.Add("@stxtLname", SqlDbType.Text).Value = txtEditLName.Text;
updateSQL.Parameters.Add("@stxtPhone", SqlDbType.Text).Value = txtEditPhone.Text;
updateSQL.Parameters.Add("@stxtAddress", SqlDbType.Text).Value = txtEditAddress.Text;
updateSQL.Parameters.Add("@stxtCity", SqlDbType.Text).Value = txtEditCity.Text;
updateSQL.Parameters.Add("@stxtState", SqlDbType.Text).Value = txtEditState.Text;
updateSQL.Parameters.Add("@stxtZip", SqlDbType.Text).Value = txtEditZip.Text;
updateSQL.CommandType = CommandType.Text;
try
{
updateSQL.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
LoadUserSelect();
}
/// <summary>
/// Execute a SQLiteCommand (that returns no resultset) against the specified SQLiteTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SQLiteTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SQLiteTransaction transaction, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// Create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (SQLiteConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SQLiteParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// update table config channel used and por
/// </summary>
internal void UpdateServerUsedAndPort()
{
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 = "UPDATE config SET port='" + MainWindow.Configuration.StConfiguration.Port + "'," +
"server='" + MainWindow.Configuration.StConfiguration.Server + "'";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
internal void DelMnuUserItem(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 = "DELETE from myusers WHERE user='******'";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
/// <summary>
/// metodo atualiza table users
/// </summary>
/// <param name="user"></param>
public void UpdateSaved()
{
// delete users and new insert
DeleteAllUser();
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 = "INSERT INTO users VALUES('" + MainWindow.Configuration.StConfiguration.Nick + "','" +
MainWindow.Configuration.StConfiguration.Password +"','"+
MainWindow.Configuration.StConfiguration.AuthSsl + "','" +
MainWindow.Configuration.StConfiguration.Owner + "','" +
MainWindow.Configuration.StConfiguration.Port + "','"+
MainWindow.Configuration.StConfiguration.Channel +"')";
C.ExecuteNonQuery();
}
catch (Exception ex)
{
MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
}
finally
{
if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
}
}
public void RemoveConfig(string oldcfg)
{
//SetConnection();
sql_con.Open();
sql_cmd = new SQLiteCommand("delete from data where grp='"+ oldcfg +"' and tbl='" + this.sql_tbl+ "'");
sql_cmd.Connection = sql_con;
sql_cmd.ExecuteNonQuery();
sql_con.Close();
curr_conf.Remove(oldcfg);
}
