private async void ajouterFiliere(object sender, RoutedEventArgs e)
{
string filiere = filiereLabel.Text;
string msg = "Bien Ajoutée";
// check already exist
string query = @"SELECT * FROM Filieres WHERE nom_filiere='" + filiere + "'";
ISQLiteStatement stmt = con.Prepare(query);
if (stmt.Step() == SQLiteResult.ROW)
{
msg = "Cette Filiere déjà existe";
}
else
{
// add it in db
query = @"INSERT INTO Filieres(nom_filiere) VALUES('" + filiere + "')";
stmt = con.Prepare(query);
stmt.Step();
// add it in table
ListFilieres.Clear();
win_loaded(sender, e);
}
// show reaction
MessageDialog dialog = new MessageDialog(msg);
await dialog.ShowAsync();
}
private void UpdateNote(long noteId, string title, ObservableCollection <TodoNote.TodoEntry> TodoEntries)
{
string timeStamp = TimeUtil.GetStringTimestamp();
ISQLiteStatement statement = dbConn.Prepare(UPDATE_TODO_NOTE);
statement.Bind(1, title);
statement.Bind(2, timeStamp);
statement.Bind(3, null);
statement.Bind(4, noteId);
statement.Step();
TodoNote todoNote = (TodoNote)GetNoteById(noteId);
// Delete all todo note contents
foreach (TodoNote.TodoEntry entry in ((TodoNote)GetNoteById(noteId)).TodoEntries)
{
statement.Reset();
statement.ClearBindings();
statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT);
statement.Bind(1, entry.Id);
statement.Step();
}
// Add all todo note new contents
foreach (TodoNote.TodoEntry entry in TodoEntries)
{
statement.Reset();
statement.ClearBindings();
statement = dbConn.Prepare(INSERT_TODO_NOTE_CONTENT);
statement.Bind(1, entry.Content);
statement.Bind(2, ConvertBoolToInt(entry.IsDone));
statement.Bind(3, noteId);
statement.Bind(4, timeStamp);
statement.Step();
}
}
public void AddNote(string title, ObservableCollection <TodoNote.TodoEntry> entries, string schedulingId, DateTimeOffset dateTime)
{
long notificationId = NotificationHelper.AddNotification(schedulingId, dateTime);
ISQLiteStatement statement = dbConn.Prepare(INSERT_TODO_NOTE);
statement.Bind(1, title);
statement.Bind(2, TimeUtil.GetStringTimestamp());
statement.Bind(3, notificationId);
statement.Step();
foreach (TodoNote.TodoEntry entry in entries)
{
statement = dbConn.Prepare(INSERT_TODO_NOTE_CONTENT);
statement.Bind(1, entry.Content);
int done = ConvertBoolToInt(entry.IsDone);
statement.Bind(2, done);
ISQLiteStatement stat = dbConn.Prepare(SELECT_LAST_NOTE_INSERT_ID);
stat.Step();
long noteID = (long)stat[0];
statement.Bind(3, noteID);
statement.Bind(4, TimeUtil.GetStringTimestamp());
statement.Step();
statement.Reset();
statement.ClearBindings();
}
}
private void bLogin_Click(object sender, RoutedEventArgs e)
{
switch (radiosel)
{
case "Doctor":
String sSQL = @"SELECT [DoctorId] from iatroi where [DoctorId] = " + tbLogin.Text;
ISQLiteStatement dbstate = dbcoonection.Prepare(sSQL);
while (dbstate.Step() == SQLiteResult.ROW)
{
var itemId = tbLogin.Text;
this.Frame.Navigate(typeof(DoctorPage), itemId);
}
break;
case "Drugstore":
sSQL = @"SELECT [Store_Id] from dragstores where [Store_Id] = " + tbLogin.Text;
dbstate = dbcoonection.Prepare(sSQL);
while (dbstate.Step() == SQLiteResult.ROW)
{
var itemId = tbLogin.Text;
this.Frame.Navigate(typeof(DrugstorePage), itemId);
}
break;
}
}
public void DeleteEntry(long entryId)
{
ISQLiteStatement statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT);
statement.Bind(1, entryId);
statement.Step();
}
public static void DeleteNotificationById(long id)
{
ISQLiteStatement statement = dbConn.Prepare(DELETE_SIMPLE_NOTE_NOTIFICATION);
statement.Bind(1, id);
statement.Step();
}
private static long GetLastInsertedNotificationId()
{
ISQLiteStatement statement = dbConn.Prepare(SELECT_LAST_NOTIFICATION_INSERT_ID);
statement.Step();
return((long)statement[0]);
}
private void AddData_Click(object sender, RoutedEventArgs e)
{
/*string name = nameContact.Text;*/
message.Text = "";
string phone = phoneNumber.Text;
string name = nameContact.Text;
string select = @"Select * from Contact where phoneNumber = '" + phone + "';";
ISQLiteStatement dbState = (SQLiteStatement)conn.Prepare(select);
while (dbState.Step() == SQLiteResult.ROW)
{
string nameContact = dbState["name"] as string;
string phoneNumber = dbState["phoneNumber"] as string;
items.Add(nameContact + " " + phoneNumber);
listPhone.Add(phoneNumber);
}
foreach (string s in listPhone)
{
if (s != phone)
{
string sql = @"Insert into Contact (name, phoneNumber) values ('" + name + "', '" + phone + "' ); ";
SQLiteStatement statment = (SQLiteStatement)conn.Prepare(sql);
statment.Step();
}
else
{
message.Text = "Số điện thoại đã tồn tại";
}
}
}
private IList <JObject> ExecuteQuery(TableDefinition table, string sql, IDictionary <string, object> parameters)
{
table = table ?? new TableDefinition();
parameters = parameters ?? new Dictionary <string, object>();
var rows = new List <JObject>();
using (ISQLiteStatement statement = this.connection.Prepare(sql))
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
statement.Bind(parameter.Key, parameter.Value);
}
SQLiteResult result;
while ((result = statement.Step()) == SQLiteResult.ROW)
{
var row = ReadRow(table, statement);
rows.Add(row);
}
ValidateResult(result);
}
return(rows);
}
public ISQLiteStatement Query(string table, string[] columns, string orderBy, string limit, string whereClause,
params string[] args)
{
if (String.IsNullOrWhiteSpace(table) || columns == null || columns.Length == 0)
{
throw new InvalidOperationException("Must specify a table and columns to query");
}
if (String.IsNullOrWhiteSpace(whereClause))
{
whereClause = String.Empty;
}
else
{
whereClause = "WHERE " + whereClause;
}
string sql = String.Format(QueryStatement,
String.Join(", ", columns),
table,
whereClause,
orderBy,
limit,
String.Empty);
ISQLiteStatement stmt = _sqlConnection.Prepare(sql);
if (args != null)
{
for (int i = 0; i < args.Length; i++)
{
stmt.Bind(i + 1, args[i]);
}
}
SQLiteResult result = stmt.Step();
return(stmt);
}
public static void InsertRow(SQLiteConnection connection, DATA_INFO db)
{
string _HandleP = string.Join(",", db.HandleP);
string _SaddleP = string.Join(",", db.SaddleP);
string _PedalLeftP = string.Join(",", db.PedalLeftP);
string _PedalLeftAcc = string.Join(",", db.PedalLeftAcc);
string _PeadlLeftGyro = string.Join(",", db.PeadlLeftGyro);
string _PeadlRightP = string.Join(",", db.PeadlRightP);
string _PedalRightAcc = string.Join(",", db.PedalRightAcc);
string _PeadlRightGyro = string.Join(",", db.PeadlRightGyro);
string _CrankAcc = string.Join(",", db.CrankAcc);
string _CrankGyro = string.Join(",", db.CrankGyro);
string sql = string.Format("INSERT INTO [itec] (" +
"[ID], [EventType], [EventTime], [Cadence], [Power], [Resistance], " +
"[HandleP], [SaddleP], [PedalLeftP], [PedalLeftAcc], [PeadlLeftGyro], " +
"[PeadlRightP], [PedalRightAcc], [PeadlRightGyro], [CrankAcc], " +
"[CrankGyro]) " +
"VALUES (" +
"'{0}','{1}','{2}',{3},{4},{5}," +
"'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}'," +
"'{14}','{15}')",
db.ID, db.EventType, db.EventTime, db.Cadence, db.Power, db.Resistance,
_HandleP, _SaddleP, _PedalLeftP, _PedalLeftAcc, _PeadlLeftGyro,
_PeadlRightP, _PedalRightAcc, _PeadlRightGyro,
_CrankAcc, _CrankGyro
);
using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
{
sqliteStatement.Step();
}
}
public List <GV.DATA_INFO_STRING> GetDataRecord(SQLiteConnection conn, string sql)
{
List <GV.DATA_INFO_STRING> entries = new List <GV.DATA_INFO_STRING>();
using (ISQLiteStatement dbState = GV.connection.Prepare(sql))
{
while (dbState.Step() == SQLiteResult.ROW)
{
entries.Add(
new GV.DATA_INFO_STRING
{
Key = dbState.GetInteger(0),
ID = dbState.GetText(1),
EventType = dbState.GetText(2),
EventTime = dbState.GetText(3),
Cadence = dbState.GetText(4),
Power = dbState.GetText(5),
Resistance = dbState.GetText(6),
HandleP = dbState.GetText(7),
SaddleP = dbState.GetText(8),
PedalLeftP = dbState.GetText(9),
PedalLeftAcc = dbState.GetText(10),
PeadlLeftGyro = dbState.GetText(11),
PeadlRightP = dbState.GetText(12),
PedalRightAcc = dbState.GetText(13),
PeadlRightGyro = dbState.GetText(14),
CrankAcc = dbState.GetText(15),
CrankGyro = dbState.GetText(16)
});
}
}
return(entries.ToList <GV.DATA_INFO_STRING>());
}
public AddMoiRekviziti()
{
this.InitializeComponent();
SQLitePCL.SQLiteConnection dbConnection = new SQLiteConnection("sdelkidatabase.db");
try {
// ПОЛУЧЕНИЕ ДАННЫХ ИЗ БД ДЛЯ RADIOBUTTON PRINTING
string readSQL1 = "SELECT Printing FROM PRINTINGSETTINGS WHERE ID=1;";
ISQLiteStatement dbState1 = dbConnection.Prepare(readSQL1);
while (dbState1.Step() == SQLiteResult.ROW)
{
string PrintingStatusString = dbState1["Printing"] as string;
if (PrintingStatusString == "HTML")
{
TextBlockMoiPechatPodpisPDF.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ImageMoiPechatPodpis.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ButtonPechatPodpis.Visibility = Windows.UI.Xaml.Visibility.Collapsed; TextBlockMojLogoPDF.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ImageLogo.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ButtonLogo.Visibility = Windows.UI.Xaml.Visibility.Collapsed;
}
if (PrintingStatusString == "PDF")
{
TextBlockMoiPechatPodpis5.Visibility = Windows.UI.Xaml.Visibility.Collapsed; TextBoxMoiPechatPodpis5.Visibility = Windows.UI.Xaml.Visibility.Collapsed; TextBlockMojLogo5.Visibility = Windows.UI.Xaml.Visibility.Collapsed; TextBoxMojLogo5.Visibility = Windows.UI.Xaml.Visibility.Collapsed;
}
}
}
//КОНЕЦ ПОЛУЧЕНИЕ ДАННЫХ ИЗ БД ДЛЯ RADIOBUTTON PRINTING }
catch { TextBlockMoiPechatPodpisPDF.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ImageMoiPechatPodpis.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ButtonPechatPodpis.Visibility = Windows.UI.Xaml.Visibility.Collapsed; TextBlockMojLogoPDF.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ImageLogo.Visibility = Windows.UI.Xaml.Visibility.Collapsed; ButtonLogo.Visibility = Windows.UI.Xaml.Visibility.Collapsed; }
}
private async void deleteFiliereAction(object sender, RoutedEventArgs e)
{
ContentDialog res = new ContentDialog
{
Title = "Suppression d'une Filiere !",
Content = "Voulez-vous vraiment supprimer cette filière ?",
PrimaryButtonText = "Supprimer",
CloseButtonText = "Annuler"
};
ContentDialogResult result = await res.ShowAsync();
if (result == ContentDialogResult.Primary)
{
// delete the student
if (ListFilieres.Contains(selectedFiliere))
{
// delete in db
string query = @"Delete from Filieres WHERE id_filiere='" + selectedFiliere.Id_filiere.ToString() + "'";
ISQLiteStatement stmt = con.Prepare(query);
stmt.Step();
// show tooltip
MessageDialog dialog = new MessageDialog("Supprimée");
await dialog.ShowAsync();
// delete in local table
ListFilieres.Remove(selectedFiliere);
// clear the textbox
filiereLabel.Text = "";
}
}
res.Hide();
}
/// <summary>
/// Run a query given by its query spec, only returning results from the selected page.
/// </summary>
/// <param name="querySpec"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public JArray Query(QuerySpec querySpec, int pageIndex)
{
lock (smartlock)
{
QuerySpec.SmartQueryType qt = querySpec.QueryType;
var results = new JArray();
string sql = ConvertSmartSql(querySpec.SmartSql);
int offsetRows = querySpec.PageSize * pageIndex;
int numberRows = querySpec.PageSize;
string limit = offsetRows + "," + numberRows;
using (ISQLiteStatement statement = DBHelper.GetInstance(DatabasePath)
.LimitRawQuery(sql, limit, querySpec.getArgs()))
{
if (statement.DataCount > 0)
{
do
{
if (qt == QuerySpec.SmartQueryType.Smart)
{
results.Add(GetDataFromRow(statement));
}
else
{
results.Add(JObject.Parse(GetObject(statement, 0).ToString()));
}
} while (statement.Step() == SQLiteResult.ROW);
}
statement.ResetAndClearBindings();
}
return(results);
}
}
public bool Delete(string table, Dictionary <string, object> contentValues)
{
if (String.IsNullOrWhiteSpace(table) || contentValues == null || contentValues.Keys.Count == 0)
{
throw new InvalidOperationException("Must specify a table and provide content to delete");
}
string values = String.Join(" = ?, ", contentValues.Keys);
if (contentValues.Keys.Count > 0)
{
values += " = ?";
}
string sql = String.Format(DeleteStatement,
table,
values);
using (ISQLiteStatement stmt = _sqlConnection.Prepare(sql))
{
int place = 1;
foreach (object next in contentValues.Values)
{
stmt.Bind(place, next);
place++;
}
return(stmt.Step() == SQLiteResult.DONE);
}
}
public bool RollbackTransaction()
{
using (ISQLiteStatement stmt = _sqlConnection.Prepare("Rollback Transaction"))
{
return(SQLiteResult.DONE == stmt.Step());
}
}
public JArray Retrieve(string soupName, params long[] soupEntryIds)
{
lock (smartlock)
{
DBHelper db = Database;
string soupTableName = db.GetSoupTableName(soupName);
var result = new JArray();
if (String.IsNullOrWhiteSpace(soupTableName))
{
throw new SmartStoreException("Soup: " + soupName + " does not exist");
}
using (ISQLiteStatement statement = db.Query(soupTableName, new[] { SoupCol }, String.Empty, String.Empty,
GetSoupEntryIdsPredicate(soupEntryIds)))
{
if (statement.DataCount > 0)
{
do
{
string raw = statement.GetText(statement.ColumnIndex(SoupCol));
result.Add(JObject.Parse(raw));
} while (statement.Step() == SQLiteResult.ROW);
}
}
return(result);
}
}
private async void bAdd_Click(object sender, RoutedEventArgs e)
{
if (tbAMKA.Text == "")
{
MessageDialog msgbox = new MessageDialog("Παρακαλώ συμπληρώστε το Α.Μ.Κ.Α. του πελάτη");
await msgbox.ShowAsync();
}
else if (tbQuantity.Text == "")
{
MessageDialog msgbox = new MessageDialog("Παρακαλώ συμπληρώστε το ποσότητα του φαρμάκου");
await msgbox.ShowAsync();
}
else
{
string sSQL = @"insert into preskr ([amka] ,[drag],[quant])
values ('" + tbAMKA.Text + "','" + tbSelDrug.Text + "','" + tbQuantity.Text + "')";
dbcoonection.Prepare(sSQL).Step();
{
var Items = new List <string>();
String sSQL1 = @"SELECT [amka],[Drag],[Quant] from preskr where [amka] like '" + tbAMKA.Text + "%'";
ISQLiteStatement dbstate = dbcoonection.Prepare(sSQL1);
while (dbstate.Step() == SQLiteResult.ROW)
{
Items.Add(dbstate["Drag"] + " " + dbstate["Quant"]);
}
listDrugsPriscription.ItemsSource = Items;
}
tbSuccess.Text = "Καταχώρηση επιτυχής!";
}
}
public static myObjectType SearchRecords <myObjectType>(String FieldName, String myObjectPath, String FieldToSearch, String SearchString)
{
String myObjectString = String.Empty;
String sSql = String.Format(@"SELECT * FROM {0};", TableName);
ISQLiteStatement cnStatement = dbcon.Prepare(sSql);
while (cnStatement.Step() == SQLiteResult.ROW)
{
myObjectString = cnStatement[FieldName].ToString();
myObjectType myObject = (myObjectType)Activator.CreateInstance(Type.GetType(myObjectPath));
myObject = ConvertStringToObject <myObjectType>(myObjectString, myObjectPath);
PropertyInfo prop = typeof(myObjectType).GetProperty(FieldToSearch);
if (prop.GetValue(myObject).ToString() == SearchString)
{
return(myObject);
}
}
return((myObjectType)Activator.CreateInstance(Type.GetType(myObjectPath)));
}
private async void loginButton_Click(object sender, RoutedEventArgs e)
{
string query = @"Select * from Users where login='******' and password='******'";
ISQLiteStatement stmt = con.Prepare(query);
int nbr = 0;
while (stmt.Step() == SQLiteResult.ROW)
{
nbr++;
}
if (nbr <= 0)
{
var messageDialog = new MessageDialog("Le nom d'utilisateur ou mot de passe incorrect");
await messageDialog.ShowAsync();
}
else
{
if (remember_me.IsChecked == true)
{
localSettings.Values["remember"] = DateTime.Today.ToString();
}
Frame.Navigate(typeof(Main));
}
}
private void win_loaded(object sender, RoutedEventArgs e)
{
String query = @"Select * from Filieres";
ISQLiteStatement stmt = con.Prepare(query);
ISQLiteStatement stmtForTotal;
ISQLiteStatement stmtNbAbsence;
while (stmt.Step() == SQLiteResult.ROW)
{
query = @"Select id_etudiant from Etudiants WHERE id_filiere='" + stmt["id_filiere"].ToString() + "'";
stmtForTotal = con.Prepare(query);
int nb_etudiant = 0;
int nb_absence = 0;
while (stmtForTotal.Step() == SQLiteResult.ROW)
{
nb_etudiant++;
query = @"Select id_absence from Absences WHERE id_etudiant='" + stmtForTotal["id_etudiant"].ToString() + "'";
stmtNbAbsence = con.Prepare(query);
while (stmtNbAbsence.Step() == SQLiteResult.ROW)
{
nb_absence++;
}
}
Filiere fil = new Filiere(int.Parse(stmt["id_filiere"].ToString()), stmt["nom_filiere"].ToString(), nb_etudiant, nb_absence);
ListFilieres.Add(fil);
}
}
public static void CreateTable(SQLiteConnection connection)
{
//創建表
string sql = string.Format(" CREATE TABLE [{0}] ( " +
" [Key] INTEGER PRIMARY KEY AUTOINCREMENT," +
" [ID] nvarchar(32)," +
" [EventType] nvarchar(254)," +
" [EventTime] nvarchar(254), " +
" [Cadence] nvarchar(254), " +
" [Power] nvarchar(254)," +
" [Resistance] nvarchar(254)," +
" [HandleP] nvarchar(254)," +
" [SaddleP] nvarchar(254)," +
" [PedalLeftP] nvarchar(254)," +
" [PedalLeftAcc] nvarchar(254)," +
" [PeadlLeftGyro] nvarchar(254)," +
" [PeadlRightP] nvarchar(254)," +
" [PedalRightAcc] nvarchar(254)," +
" [PeadlRightGyro] nvarchar(254)," +
" [CrankAcc] nvarchar(254)," +
" [CrankGyro] nvarchar(254));", _tableName);
using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
{
sqliteStatement.Step();
}
}
public Boolean ValidarJogo(string ID)
{
string Select = @"SELECT * FROM JOGOS WHERE ID = '" + ID + "'";
ISQLiteStatement Query = SqlConexao.Prepare(Select);
return(Query.Step() == SQLiteResult.ROW);
}
private void bDelete_Click(object sender, RoutedEventArgs e)
{
String sSQL = @"delete from preskr where [amka] =" + tbAMKA.Text;
ISQLiteStatement dbstate = dbcoonection.Prepare(sSQL);
dbstate.Step();
tbSuccess.Text = "Διαγραφή επιτυχής!";
}
public bool MoveToNext()
{
stepResult = statement.Step();
currentRow++;
return(stepResult == SQLiteResult.ROW);
}
public Cursor (ISQLiteStatement statement)
{
this.statement = statement;
currentRow = -1;
hasRows = statement.Step () == SQLiteResult.ROW;
statement.Reset ();
}
private void bSuccess_Click(object sender, RoutedEventArgs e)
{
String sSQL = @"delete from preskr where [amka] like '" + tbAMKA.Text + "%'";
ISQLiteStatement dbstate = dbcoonection.Prepare(sSQL);
dbstate.Step();
tbSucess.Text = "Η συνταγή εκτελέστηκε με επιτυχία!";
}
public void makeDB()
{
SQLitePCL.SQLiteConnection dbConnect = new SQLiteConnection("Karty.db");
MySQLiteHelper mySQLiteHelper = new MySQLiteHelper();
ISQLiteStatement iSQLiteStatement = dbConnect.Prepare(mySQLiteHelper.getCreat_KP_TALE());
iSQLiteStatement.Step();
}
public void DeleteNote(long id)
{
ISQLiteStatement statement = dbConn.Prepare(DELETE_TODO_NOTE_CONTENT);
TodoNote todoNote = (TodoNote)GetNoteById(id);
foreach (TodoNote.TodoEntry entry in todoNote.TodoEntries)
{
statement.Bind(1, entry.Id);
statement.Step();
statement.Reset();
statement.ClearBindings();
}
statement = dbConn.Prepare(DELETE_TODO_NOTE);
statement.Bind(1, id);
statement.Step();
DeleteNotificationByNoteId(id);
}
public Cursor(ISQLiteStatement statement)
{
this.statement = statement;
currentRow = -1;
hasRows = statement.Step() == SQLiteResult.ROW;
statement.Reset();
}
protected override void OnNavigatedTo(NavigationEventArgs ne)
{
id = ne.Parameter.ToString();
string CategoryName = @"SELECT * FROM categories WHERE id=" + id;
var title = objConn.Prepare(CategoryName);
title.Step();
CBTitle.Text = title[1] + " - Quiz {}";
string QuestionPhrase = @"SELECT * FROM questions WHERE categoryid=" + id;
question = objConn.Prepare(QuestionPhrase);
while (question.Step() == SQLiteResult.ROW)
{
questiontotal++;
}
question.Reset();
ChangeQuestion();
}
public SQLiteResult Execute(string sql, out ISQLiteStatement statement)
{
statement = _sqlConnection.Prepare(sql);
if (statement != null)
{
return statement.Step();
}
return SQLiteResult.ERROR;
}
public bool Execute(ISQLiteStatement statement)
{
if (_isFinished)
{
throw new Exception("Cannot execute statement on finished transaction. Start a new transaction.");
}
if (_mustRollback)
{
throw new Exception("Cannot execute statement on failed transaction. Rollback this transaction and start a new transaction.");
}
var result = statement.Step();
if (!result.IsSuccess())
{
System.Diagnostics.Debug.WriteLine("Failed to execute statement.");
_mustRollback = true;
return false;
}
return true;
}
