public bool GetDataSetsGreaterThanDateTimeByIdenSensorState(DateTime dateTime, ObservableCollection <FingerEvent> m_DataSets, int SensorState) // get all datas > dateTime
{
// TimeSpan sp = -TimeSpan.FromDays(14);
// dateTime.AddTicks(sp.Ticks);
using (SqliteConnection db = new SqliteConnection(m_DBConnectName))
{
try
{
m_DataSets.Clear();
SqliteDataReader query;
db.Open();
SqliteCommand selectCommand = new SqliteCommand("SELECT * from FingerIdEventsTbl WHERE EventTime >= @EventTime AND SensorState = @SensorState Order By EventTime DESC ;", db);
selectCommand.Parameters.AddWithValue("@EventTime", dateTime.Ticks);
selectCommand.Parameters.AddWithValue("@SensorState", SensorState);
query = selectCommand.ExecuteReader();
while (query.Read())
{
FingerEvent fingerSet = new FingerEvent();
SetQueryToEventData(query, fingerSet);
m_DataSets.Add(fingerSet);
}
db.Close();
return(m_DataSets.Count > 0);
}
catch (SqliteException ex)
{
//Handle error
db.Close();
return(false);
}
}
}
private void SetQueryToEventData(SqliteDataReader query, FingerEvent fingerSet) // get all datas > dateTime
{
if (query.FieldCount > 0)
{
fingerSet.EventID = query.GetInt32(0);
}
if (query.FieldCount > 1)
{
fingerSet.FingerID = query.GetInt32(1);
}
if (query.FieldCount > 2)
{
byte[] buffer = new byte[32];
query.GetBytes(2, 0, buffer, 0, buffer.Length);
fingerSet.SensorId = buffer;
}
if (query.FieldCount > 3)
{
fingerSet.SecondName = query.GetString(3);
}
if (query.FieldCount > 4)
{
fingerSet.FirstName = query.GetString(4);
}
if (query.FieldCount > 5)
{
fingerSet.EventType = query.GetString(5);
}
if (query.FieldCount > 6)
{
fingerSet.MatchScore = query.GetInt32(6);
}
if (query.FieldCount > 7)
{
fingerSet.SensorState = query.GetInt32(7);
}
if (query.FieldCount > 8)
{
fingerSet.SensorTxtState = query.GetString(8);
}
if (query.FieldCount > 9)
{
long timeTicks;
timeTicks = query.GetInt64(9);
fingerSet.EventTime = new DateTime(timeTicks);
}
}
public bool InsertFingerEvent(FingerEvent dataSet)
{
using (SqliteConnection db = new SqliteConnection(m_DBConnectName))
{
try
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT OR IGNORE INTO FingerIdEventsTbl VALUES (NULL, @FingerID, @SensorId, @SecondName, @FirstName, @EventType, @MatchScore, @SensorState, @SensorTxtState, @EventTime);";
insertCommand.Parameters.AddWithValue("@FingerID", dataSet.FingerID);
insertCommand.Parameters.AddWithValue("@SensorId", dataSet.SensorId);
insertCommand.Parameters.AddWithValue("@SecondName", dataSet.SecondName);
insertCommand.Parameters.AddWithValue("@FirstName", dataSet.FirstName);
insertCommand.Parameters.AddWithValue("@EventType", dataSet.EventType);
insertCommand.Parameters.AddWithValue("@MatchScore", dataSet.MatchScore);
insertCommand.Parameters.AddWithValue("@SensorState", dataSet.SensorState);
insertCommand.Parameters.AddWithValue("@SensorTxtState", dataSet.SensorTxtState);
dataSet.EventTime = DateTime.Now;
insertCommand.Parameters.AddWithValue("@EventTime", dataSet.EventTime.Ticks);
insertCommand.ExecuteReader();
db.Close();
return(true);
}
catch (SqliteException ex)
{
db.Close();
//Handle error
return(false);
}
}
}
public bool GetDataSetsGreaterThanDateTimeByNotIdenSensorState(DateTime dateTime, ObservableCollection <FingerEvent> m_DataSets, int SensorState) // get all datas > dateTime
{
// TimeSpan sp = -TimeSpan.FromDays(14);
// dateTime.AddTicks(sp.Ticks);
using (SqliteConnection db = new SqliteConnection(m_DBConnectName))
{
try
{
m_DataSets.Clear();
SqliteDataReader query;
db.Open();
SqliteCommand selectCommand = new SqliteCommand("SELECT * from FingerIdEventsTbl WHERE EventTime >= @EventTime AND SensorState != @SensorState Order By EventTime DESC ;", db);
selectCommand.Parameters.AddWithValue("@EventTime", dateTime.Ticks);
selectCommand.Parameters.AddWithValue("@SensorState", SensorState);
query = selectCommand.ExecuteReader();
while (query.Read())
{
FingerEvent fingerSet = new FingerEvent();
SetQueryToEventData(query, fingerSet);
/*
* if (query.FieldCount > 0)
* fingerSet.EventID = query.GetInt32(0);
*
*
* if (query.FieldCount > 1)
* fingerSet.FingerID = query.GetInt32(1);
*
* if (query.FieldCount > 2)
* {
* byte[] buffer = new byte[32];
* query.GetBytes(2, 0, buffer, 0, buffer.Length);
* fingerSet.SensorId = buffer;
* }
*
* if (query.FieldCount > 3)
* {
* fingerSet.SecondName = query.GetString(3);
* }
*
* if (query.FieldCount > 4)
* fingerSet.FirstName = query.GetString(4);
*
* if (query.FieldCount > 5)
* fingerSet.EventType = query.GetString(5);
*
* if (query.FieldCount > 6)
* {
* fingerSet.MatchScore = query.GetInt32(6);
*
* }
* if (query.FieldCount > 7)
* {
* fingerSet.SensorState = query.GetInt32(7);
* }
* if (query.FieldCount > 8)
* {
* fingerSet.SensorTxtState = query.GetString(8);
* }
*
* if (query.FieldCount > 9)
* {
* long timeTicks;
* timeTicks = query.GetInt64(9);
* fingerSet.EventTime = new DateTime(timeTicks);
* }
*/
m_DataSets.Add(fingerSet);
}
db.Close();
return(m_DataSets.Count > 0);
}
catch (SqliteException)
{
//Handle error
db.Close();
return(false);
}
}
}
