public static List <FoeClientCatalogItem> GetAll()
{
List <FoeClientCatalogItem> catalog = new List <FoeClientCatalogItem>();
// Open data and prepare command
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from catalog order by name";
cmd.Prepare();
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Get catalog item from database
FoeClientCatalogItem item = new FoeClientCatalogItem();
item.Code = reader.GetString(reader.GetOrdinal("code"));
string isSubscribed = reader.GetString(reader.GetOrdinal("issubscribed"));
item.IsSubscribed = ((isSubscribed == "T") ? true : false);
item.ContentType = reader.GetString(reader.GetOrdinal("contenttype"));
item.Name = reader.GetString(reader.GetOrdinal("name"));
item.Description = reader.GetString(reader.GetOrdinal("description"));
item.DtLastUpdated = reader.GetDateTime(reader.GetOrdinal("dtlastupdated"));
catalog.Add(item);
}
reader.Close();
conn.Close();
return(catalog);
}
public static void Add(FoeClientCatalogItem item)
{
// First delete existing item if it exists
Delete(item.Code);
// Connect to DB
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText =
"insert into catalog (code, issubscribed, name, description, contenttype, dtlastupdated)" +
"values (@code, @issubscribed, @name, @description, @contenttype, @dtlastupdated)";
cmd.Parameters.Add("@name", System.Data.DbType.String, 128);
cmd.Parameters.Add("@description", System.Data.DbType.String, 512);
cmd.Parameters.Add("@contenttype", System.Data.DbType.String, 32);
cmd.Parameters.Add("@issubscribed", System.Data.DbType.String, 1);
cmd.Parameters.Add("@code", System.Data.DbType.String, 10);
cmd.Parameters.Add("@dtlastupdated", System.Data.DbType.Time);
cmd.Prepare();
cmd.Parameters["@name"].Value = item.Name;
cmd.Parameters["@description"].Value = item.Description;
cmd.Parameters["@contenttype"].Value = item.ContentType;
cmd.Parameters["@issubscribed"].Value = (item.IsSubscribed ? "T" : "F");
cmd.Parameters["@code"].Value = item.Code;
cmd.Parameters["@dtlastupdated"].Value = item.DtLastUpdated;
cmd.ExecuteNonQuery();
conn.Close();
}
public static FoeClientCatalogItem Get(string catalogCode)
{
FoeClientCatalogItem item = null;
// Connect to DB
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from catalog where code=@code";
cmd.Parameters.Add("@code", System.Data.DbType.String, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = catalogCode;
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
// Get catalog item from database
item = new FoeClientCatalogItem();
item.Code = reader.GetString(reader.GetOrdinal("code"));
item.IsSubscribed = ((reader.GetString(reader.GetOrdinal("issubscribed")) == "T") ? true : false);
item.ContentType = reader.GetString(reader.GetOrdinal("contenttype"));
item.Name = reader.GetString(reader.GetOrdinal("name"));
item.Description = reader.GetString(reader.GetOrdinal("description"));
item.DtLastUpdated = reader.GetDateTime(reader.GetOrdinal("dtlastupdated"));
}
reader.Close();
conn.Close();
return(item);
}
/// <summary>
/// Get registry key.
/// </summary>
/// <param name="registryName">Registry key name.</param>
/// <returns>FoeClientRegistryEntry object. Returns null if key is not available.</returns>
public static FoeClientRegistryEntry GetEntry(string registryName)
{
FoeClientRegistryEntry entry = null;
SQLiteConnection conn = FoeClientDb.Open();
// Connect to database and prepare command
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from registry where name=@name";
cmd.Parameters.Add("@name", System.Data.DbType.String, 128);
cmd.Prepare();
cmd.Parameters["@name"].Value = registryName;
// read registry entry
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
entry = new FoeClientRegistryEntry();
entry.Name = registryName;
entry.Value = reader.GetString(reader.GetOrdinal("value"));
}
reader.Close();
conn.Close();
return(entry);
}
public static FoeClientRequestItem Get(string requestId)
{
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from requests where id=@id";
cmd.Parameters.Add("@id", System.Data.DbType.String, 128);
cmd.Prepare();
cmd.Parameters["@id"].Value = requestId;
// Read request
FoeClientRequestItem req = null;
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
req = new FoeClientRequestItem();
req.Id = requestId;
req.Type = reader.GetString(reader.GetOrdinal("type"));
req.DtRequested = reader.GetDateTime(reader.GetOrdinal("dtrequested"));
}
reader.Close();
conn.Close();
return(req);
}
public static void DeleteAll()
{
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from catalog";
cmd.Prepare();
// execute query
cmd.ExecuteNonQuery();
conn.Close();
}
public static void Delete(string catalogCode)
{
// Connect to DB
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from catalog where code=@code";
cmd.Parameters.Add("@code", System.Data.DbType.String, 10);
cmd.Prepare();
cmd.Parameters["@code"].Value = catalogCode;
cmd.ExecuteNonQuery();
conn.Close();
}
public static void DeleteEntry(string registryName)
{
SQLiteConnection conn = FoeClientDb.Open();
// first delete existing registry key
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from registry where name=@name";
cmd.Parameters.Add("@name", System.Data.DbType.String, 128);
cmd.Prepare();
cmd.Parameters["@name"].Value = @registryName;
cmd.ExecuteNonQuery();
}
public static void SetSubscription(string catalogCode, bool isSubscribe)
{
// Connect to DB
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "update catalog set issubscribed=@issubscribed where code=@code";
cmd.Parameters.Add("@issubscribed", System.Data.DbType.String, 1);
cmd.Parameters.Add("@code", System.Data.DbType.String, 10);
cmd.Prepare();
cmd.Parameters["@issubscribed"].Value = (isSubscribe ? "T" : "F");
cmd.Parameters["@code"].Value = catalogCode;
cmd.ExecuteNonQuery();
conn.Close();
}
public static void DeleteOldRequest(TimeSpan span)
{
DateTime timeline = DateTime.Now.Subtract(span);
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from requests where dtrequested<=@timeline";
cmd.Parameters.Add("@timeline", System.Data.DbType.Time);
cmd.Prepare();
cmd.Parameters["@timeline"].Value = timeline;
// execute query
cmd.ExecuteNonQuery();
conn.Close();
}
public static void Add(FoeClientRequestItem req)
{
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into requests(id, type, dtrequested) values (@id, @type, @dtrequested)";
cmd.Parameters.Add("@id", System.Data.DbType.String, 128);
cmd.Parameters.Add("@type", System.Data.DbType.String, 10);
cmd.Parameters.Add("@dtrequested", System.Data.DbType.DateTime);
cmd.Prepare();
// execute query
cmd.Parameters["@id"].Value = req.Id;
cmd.Parameters["@type"].Value = req.Type;
cmd.Parameters["@dtrequested"].Value = req.DtRequested;
cmd.ExecuteNonQuery();
conn.Close();
}
public static void SetEntry(string registryName, string registryValue)
{
// first delete existing registry key
DeleteEntry(registryName);
// Insert new key
SQLiteConnection conn = FoeClientDb.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd = conn.CreateCommand();
cmd.CommandText = "insert into registry (name, value) values (@name, @value)";
cmd.Parameters.Add("@name", System.Data.DbType.String, 128);
cmd.Parameters.Add("@value", System.Data.DbType.String, 512);
cmd.Prepare();
cmd.Parameters["@name"].Value = registryName;
cmd.Parameters["@value"].Value = registryValue;
cmd.ExecuteNonQuery();
conn.Close();
}