public bool InsertDataSet(DBDataSet 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 FingerIdTbl VALUES (@FingerID, @SensorId, @SecondName, @FirstName, @FingerTemplate, @AccessRights, @MatchScore, @CreationTime, @Info);";
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("@FingerTemplate", dataSet.FingerTemplate);
insertCommand.Parameters.AddWithValue("@AccessRights", dataSet.AccessRights);
insertCommand.Parameters.AddWithValue("@MatchScore", dataSet.MatchScore);
dataSet.CreationTime = DateTime.Now;
insertCommand.Parameters.AddWithValue("@CreationTime", dataSet.CreationTime.Ticks);
insertCommand.Parameters.AddWithValue("@Info", dataSet.Info);
insertCommand.ExecuteReader();
db.Close();
return(true);
}
catch (SqliteException)
{
db.Close();
//Handle error
return(false);
}
}
}
public bool UpdateDataSet(DBDataSet 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 = "UPDATE FingerIdTbl SET SecondName = @SecondName, FirstName = @FirstName, SensorId = @SensorId, AccessRights = @AccessRights, MatchScore = @MatchScore, CreationTime = @CreationTime, Info = @Info WHERE FingerID = @FingerID;";
insertCommand.Parameters.AddWithValue("@FingerID", dataSet.FingerID);
insertCommand.Parameters.AddWithValue("@AccessRights", dataSet.AccessRights);
insertCommand.Parameters.AddWithValue("@FirstName", dataSet.FirstName);
insertCommand.Parameters.AddWithValue("@SecondName", dataSet.SecondName);
insertCommand.Parameters.AddWithValue("@MatchScore", dataSet.MatchScore);
insertCommand.Parameters.AddWithValue("@SensorId", dataSet.SensorId);
dataSet.CreationTime = DateTime.Now;
insertCommand.Parameters.AddWithValue("@CreationTime", dataSet.CreationTime.Ticks);
insertCommand.Parameters.AddWithValue("@Info", dataSet.Info);
insertCommand.ExecuteReader();
db.Close();
return(true);
}
catch (SqliteException e)
{
db.Close();
//Do nothing
return(false);
}
}
}
public bool GetDataSetByFingerId(int fingerId, DBDataSet fingerSet)
{
bool ret = false;
using (SqliteConnection db = new SqliteConnection(m_DBConnectName))
{
try
{
SqliteDataReader query;
db.Open();
SqliteCommand selectCommand = new SqliteCommand("SELECT * from FingerIdTbl WHERE FingerID = @FingerId", db);
selectCommand.Parameters.AddWithValue("@FingerId", fingerId);
query = selectCommand.ExecuteReader();
while (query.Read())
{
if (query.FieldCount > 0)
{
fingerSet.FingerID = query.GetInt32(0);
}
if (query.FieldCount > 1)
{
byte[] buffer = new byte[32];
query.GetBytes(1, 0, buffer, 0, buffer.Length);
fingerSet.SensorId = buffer;
}
if (query.FieldCount > 2)
{
fingerSet.SecondName = query.GetString(2);
}
if (query.FieldCount > 3)
{
fingerSet.FirstName = query.GetString(3);
}
if (query.FieldCount > 4)
{
byte[] buffer = new byte[512];
query.GetBytes(4, 0, buffer, 0, buffer.Length);
fingerSet.FingerTemplate = buffer;
}
if (query.FieldCount > 5)
{
fingerSet.AccessRights = (ulong)query.GetInt64(5);
}
if (query.FieldCount > 6)
{
fingerSet.MatchScore = query.GetInt32(6);
}
if (query.FieldCount > 7)
{
long timeTicks;
timeTicks = query.GetInt64(7);
fingerSet.CreationTime = new DateTime(timeTicks);
}
if (query.FieldCount > 8)
{
fingerSet.Info = query.GetString(8);
}
ret = true;
break;
}
db.Close();
return(true);
}
catch (SqliteException)
{
//Handle error
db.Close();
return(false);
}
}
}