internal int Insert(SQLiteConnection con, CountEntry en, SQLiteTransaction trans)
{
//en.Sync = CountEntry.SyncStatus.NEW;
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = @"INSERT INTO CNT
(ITMREF, LABEL, CREATED, VALUE, STU, LOCATION, DELETED, SYNC, HASH, SYNC_CODE, DELETE_KEY )
VALUES
(@ITMREF, @LABEL, @CREATED, @VALUE, @STU, @LOCATION, @DELETED, @SYNC, @HASH, @SYNC_CODE, @DELETE_KEY)";
cmd.Parameters.AddWithValue("@ITMREF", en.Itmref);
cmd.Parameters.AddWithValue("@LABEL", en.LabelNumber);
cmd.Parameters.AddWithValue("@CREATED", en.Time);
cmd.Parameters.AddWithValue("@VALUE", en.Value);
cmd.Parameters.AddWithValue("@STU", en.Unit);
cmd.Parameters.AddWithValue("@LOCATION", en.Location);
cmd.Parameters.AddWithValue("@DELETED", en.IsDeleted ? 1 : 0);
cmd.Parameters.AddWithValue("@SYNC", en.Sync);
cmd.Parameters.AddWithValue("@HASH", en.Hash);
cmd.Parameters.AddWithValue("@SYNC_CODE", en.SyncCode);
cmd.Parameters.AddWithValue("@DELETE_KEY", en.DeleteKey);
return(cmd.ExecuteNonQuery());
}
}
internal int Update(SQLiteConnection con, CountEntry en)
{
//en.Sync = CountEntry.SyncStatus.NEW;
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = @"UPDATE CNT SET
VALUE = @VALUE, LOCATION = @LOCATION,
DELETED = @DELETED, SYNC=@SYNC, HASH=@HASH, SYNC_CODE=@SYNC_CODE, DELETE_KEY=@DELETE_KEY
WHERE CNT.ITMREF=@WITMREF AND LABEL=@WLABEL AND HASH=@HASH";
cmd.Parameters.AddWithValue("@VALUE", en.Value);
cmd.Parameters.AddWithValue("@LOCATION", en.Location);
cmd.Parameters.AddWithValue("@DELETED", en.IsDeleted);
cmd.Parameters.AddWithValue("@SYNC", en.Sync);
cmd.Parameters.AddWithValue("@HASH", en.Hash);
cmd.Parameters.AddWithValue("@SYNC_CODE", en.SyncCode);
cmd.Parameters.AddWithValue("@DELETE_KEY", en.DeleteKey);
cmd.Parameters.AddWithValue("@WITMREF", en.Itmref);
cmd.Parameters.AddWithValue("@WLABEL", en.LabelNumber);
cmd.Parameters.AddWithValue("@HASH", en.Hash);
return(cmd.ExecuteNonQuery());
}
}
public override bool Equals(object obj)
{
CountEntry other = obj as CountEntry;
if (other != null)
{
if (!Itmref.Equals(other.Itmref))
{
return(false);
}
if (!LabelNumber.Equals(other.LabelNumber))
{
return(false);
}
if (!Value.Equals(other.Value))
{
return(false);
}
if (!Unit.Equals(other.Unit))
{
return(false);
}
if (!Time.Equals(other.Time))
{
return(false);
}
if (!IsDeleted.Equals(other.IsDeleted))
{
return(false);
}
if (!Hash.Equals(other.Hash))
{
return(false);
}
if (!Sync.Equals(other.Sync))
{
return(false);
}
if (SyncCode != other.SyncCode)
{
return(false);
}
if (!Location.Equals(other.Location))
{
return(false);
}
return(true);
}
return(base.Equals(obj));
}
private CountEntry ParseCountEntry(SQLiteDataReader rd)
{
CountEntry entry = new CountEntry();
entry.Itmref = rd.GetString(0);
entry.LabelNumber = rd.GetInt32(1);
entry.Time = rd.GetDateTime(2);
entry.Value = (double)rd.GetDecimal(3);
entry.Unit = rd.GetString(4);
entry.IsDeleted = rd.GetByte(5) != 0;
entry.Sync = (CountEntry.SyncStatus)rd.GetInt32(6);
entry.Hash = rd.GetString(7);
entry.SyncCode = rd.GetInt32(8);
entry.Location = rd.IsDBNull(9) ? string.Empty : rd.GetString(9);
return(entry);
}
public List <CountEntry> GetDirtyCountEntries(SQLiteConnection con)
{
List <CountEntry> entries = new List <CountEntry>(10);
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT ITMREF, LABEL, CREATED, VALUE, STU, DELETED, SYNC, HASH, SYNC_CODE, LOCATION FROM CNT WHERE SYNC = 0";
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
CountEntry entry = ParseCountEntry(rd);
entries.Add(entry);
}
}
}
return(entries);
}
public CountEntry GetCountEntryByHash(SQLiteConnection con, string hash)
{
CountEntry entry = null;
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT ITMREF, LABEL, CREATED, VALUE, STU, DELETED, SYNC, HASH, SYNC_CODE, LOCATION FROM CNT WHERE HASH=@h ";
cmd.Parameters.AddWithValue("@h", hash);
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
entry = ParseCountEntry(rd);
}
}
}
return(entry);
}
public List <CountEntry> GetCountEntries(SQLiteConnection con, string itmref)
{
List <CountEntry> entries = new List <CountEntry>(10);
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT ITMREF, LABEL, CREATED, VALUE, STU, DELETED, SYNC, HASH, SYNC_CODE, LOCATION FROM CNT WHERE DELETED = 0 AND ITMREF=@itmref ";
cmd.Parameters.AddWithValue("@itmref", itmref);
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
CountEntry entry = ParseCountEntry(rd);
entries.Add(entry);
}
}
}
return(entries);
}
internal int Insert(SQLiteConnection con, CountEntry en)
{
return(Insert(con, en, null));
}