internal static IEnumerable<SecureSettingsCategory> GetAllCategories(PetaPocoUnitOfWork unitOfWork)
{
var sql = @"
SELECT *
FROM G42SecureSettingsCategories
ORDER BY name
";
return unitOfWork.Database.Fetch<SecureSettingsCategory>(sql);
}
internal static void SaveCategory(PetaPocoUnitOfWork unitOfWork, SecureSettingsCategory model)
{
if (!string.IsNullOrEmpty(model.Name))
{
try
{
unitOfWork.Database.Save(model);
}
catch (Exception ex)
{
LogHelper.Error<SecureSettingsCategory>(ex.Message, ex);
}
}
}
internal static void RemoveCategory(PetaPocoUnitOfWork unitOfWork, int id)
{
try
{
if (GetAllSettings().Any(x => x.CategoryId == id))
{
LogHelper.Info<SecureSettingsCategory>("Cannot remove category as there are keys using it.");
}
unitOfWork.Database.Execute("DELETE FROM G42SecureSettingsCategories WHERE id = @0", id);
}
catch (Exception ex)
{
LogHelper.Error<SecureSettingsCategory>(ex.Message, ex);
}
}
internal static void AddCategory(PetaPocoUnitOfWork unitOfWork, string name)
{
if (!string.IsNullOrEmpty(name))
{
try
{
LogHelper.Info<SecureSettingsCategory>("Adding new category...");
unitOfWork.Database.Save(new SecureSettingsCategory()
{
Name = name
});
}
catch (Exception ex)
{
LogHelper.Error<SecureSettingsCategory>(ex.Message, ex);
}
}
}
internal static void CreateCategoriesTable(PetaPocoUnitOfWork unitOfWork)
{
try
{
if (!unitOfWork.Database.TableExist("G42SecureSettingsCategories"))
{
LogHelper.Info<SecureSettingsCategory>("Creating table...");
unitOfWork.Database.Execute(@"
CREATE TABLE [dbo].[G42SecureSettingsCategories](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_G42SecureSettingsCategories] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [AK_G42SecureSettingsCategories] UNIQUE(name)
)
");
unitOfWork.Database.Execute(@"
INSERT
INTO [dbo].[G42SecureSettingsCategories]
(name)
VALUES
('General')
");
}
else
{
LogHelper.Info<SecureSetting>("Table exists already, skipping...");
}
}
catch (Exception ex)
{
LogHelper.Error<SecureSettingsCategory>(ex.Message, ex);
}
}
/// <summary>
/// Creates the table if not exists.
/// </summary>
internal static void CreateSettingsTable(PetaPocoUnitOfWork unitOfWork)
{
try
{
if (!unitOfWork.Database.TableExist("G42SecureSettings"))
{
LogHelper.Info<SecureSetting>("Creating table...");
unitOfWork.Database.Execute(@"
CREATE TABLE [dbo].[G42SecureSettings](
[id] [int] IDENTITY(1,1) NOT NULL,
[key] [nvarchar](255) NOT NULL,
[value] [ntext] NOT NULL,
[categoryId] [int] NOT NULL,
[sortOrder] [int] NOT NULL,
[updatedOn] [datetime] NOT NULL,
CONSTRAINT [PK_G42SecureSettings] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
");
}
else
{
LogHelper.Info<SecureSetting>("Table exists already, skipping...");
}
}
catch (Exception ex)
{
LogHelper.Error<Exception>(ex.Message, ex);
}
}
internal static void SaveSortOrder(PetaPocoUnitOfWork unitOfWork, SortedKeys sortedKeys)
{
try
{
foreach (var key in sortedKeys.Keys)
{
var sql = @"
UPDATE G42SecureSettings
SET
sortOrder = @0,
categoryId = @1
WHERE id = @2
";
unitOfWork.Database.Execute(sql, key.SortOrder, key.CategoryId, key.Id);
}
SecureSettingsService.Clear();
}
catch (Exception ex)
{
LogHelper.Error<Exception>(ex.Message, ex);
}
}
internal static void AddKey(PetaPocoUnitOfWork unitOfWork, SecureSetting model)
{
if (!string.IsNullOrEmpty(model.Key) && !string.IsNullOrEmpty(model.Value) && model.CategoryId > 0)
{
var setting = GetSetting(unitOfWork, model.Key);
if (setting == null)
{
LogHelper.Info<SecureSetting>("Adding new key...");
try
{
unitOfWork.Database.Save(new SecureSetting()
{
Key = model.Key,
Value = SecurityHelper.Encrypt(model.Value, SecurityHelper.GetKey()),
CategoryId = model.CategoryId,
UpdatedOn = DateTime.UtcNow
});
SecureSettingsService.Clear();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
LogHelper.Error<Exception>(ex.Message, ex);
}
}
else
{
LogHelper.Info<SecureSetting>("Key exists already=>" + model.Key);
}
}
}
/// <summary>
/// Removes the specified key by identifier.
/// </summary>
/// <param name="id">The identifier.</param>
internal static void RemoveKey(PetaPocoUnitOfWork unitOfWork, int id)
{
try
{
unitOfWork.Database.Execute("DELETE FROM G42SecureSettings WHERE id = @0", id);
SecureSettingsService.Clear();
}
catch (Exception ex)
{
LogHelper.Error<Exception>(ex.Message, ex);
}
}
/// <summary>
/// Saves the specified key in the DB.
/// </summary>
/// <param name="key">The key.</param>
/// <param name="value">The value.</param>
internal static void SaveKey(PetaPocoUnitOfWork unitOfWork, string key, string value)
{
try
{
var setting = GetSetting(unitOfWork, key);
setting.UpdatedOn = DateTime.UtcNow;
setting.Value = SecurityHelper.Encrypt(value, SecurityHelper.GetKey());
unitOfWork.Database.Save(setting);
SecureSettingsService.Clear();
}
catch (Exception ex)
{
LogHelper.Error<Exception>(ex.Message, ex);
}
}
/// <summary>
/// Gets all keys from the DB.
/// </summary>
/// <returns></returns>
internal static IEnumerable<SecureSetting> GetAllSettingsFromDb(PetaPocoUnitOfWork unitOfWork)
{
var sql = @"
SELECT a.id, a.[key], a.value, a.updatedOn, a.categoryId, a.sortOrder, c.name AS categoryName
FROM G42SecureSettings a
INNER JOIN G42SecureSettingsCategories c ON c.id = a.categoryId
ORDER BY categoryName, a.sortOrder
";
try
{
return unitOfWork.Database.Fetch<SecureSetting>(sql);
}
catch (Exception ex)
{
LogHelper.Error<SecureSetting>(ex.Message, ex);
throw;
}
}
internal static IEnumerable<object> GetAllSettingsGroupedByCategory(PetaPocoUnitOfWork unitOfWork)
{
var list = new List<object>();
foreach (var category in GetAllCategories(unitOfWork))
{
var settings = new List<SecureSetting>();
settings.AddRange(SecureSettingsService.Instance.Items.Where(x => x.CategoryId == category.Id));
list.Add(settings);
}
return list;
}
/// <summary>
/// Gets the specified key.
/// </summary>
/// <param name="key">The key.</param>
/// <returns></returns>
internal static SecureSetting GetSetting(PetaPocoUnitOfWork unitOfWork, string key)
{
return GetAllSettings().FirstOrDefault(x => x.Key == key);
}