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(); }