public SQLiteCommand CreateDeleteCommand(SQLiteConnection connection, Setting setting)
{
/*
DELETE FROM [dbo].[Setting] WHERE [Name] LIKE 'baz%' AND [Environment] = 'boz'
*/
var sql = new StringBuilder();
var dbProviderFactory = DbProviderFactories.GetFactory(connection);
using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
{
var quote = new Func<string, string>(identifier => commandBuilder.QuoteIdentifier(identifier));
var table = $"{quote(TableConfiguration.TableName)}";
sql.Append($"DELETE FROM {table}").AppendLine();
sql.Append(setting.Attributes.Keys.Aggregate(
$"WHERE ([{nameof(Setting.Name)}] = @{nameof(Setting.Name)} OR [{nameof(Setting.Name)}] LIKE @{nameof(Setting.Name)} || '[%]')",
(result, key) => $"{result} AND {quote(key)} = @{key} ")
);
}
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql.ToString();
// --- add parameters & values
AddParameter(command, nameof(Setting.Name), setting.Name.WeakFullName);
AddParameters(command, setting.Attributes);
return command;
}
public override IEnumerable<Setting> GetSettings(Setting setting)
{
var registryPath = new RegistryUrn(setting.Name);
var subKeyName = Path.Combine(_baseSubKeyName, registryPath.Namespace);
using (var subKey = _baseKey.OpenSubKey(subKeyName, false))
{
if (subKey == null)
{
throw new OpenOrCreateSubKeyException(_baseKey.Name, _baseSubKeyName, subKeyName);
}
var settings =
subKey.GetValueNames()
.Where(valueName => RegistryUrn.Parse(valueName).IsLike(registryPath.WeakFullName))
.Select(valueName => new Setting
{
Name = SettingUrn.Parse(valueName),
Value = subKey.GetValue(valueName)
})
.ToList();
return settings;
}
}
public List<Setting> GetSettings(SettingPath path, IReadOnlyDictionary<string, object> namespaces)
{
var attributes = namespaces.Aggregate(
$"@{nameof(Setting.Name)}='{path}'",
(result, next) => $"{result} and @{next.Key}='{next.Value}'");
var xPath = $"//{RootElementName}/{SettingElementName}[{attributes}]";
var xSettings = XConfig.XPathSelectElements(xPath);
var elements = xSettings.Select(x =>
{
// set default key and value
var element = new Setting
{
Name = x.Attribute(nameof(Setting.Name)).Value,
Value = x.Value
};
// set other keys
foreach (var settingNamespace in namespaces)
{
element[settingNamespace.Key] = x.Attribute(settingNamespace.Key).Value;
}
return element;
}).ToList();
return elements;
}
public override IEnumerable<Setting> GetSettings(Setting setting)
{
var connectionStringSettings =
_connectionStringsSection.ConnectionStrings.Cast<ConnectionStringSettings>()
.Where(x => SettingUrn.Parse(x.Name).IsLike(setting.Name) && !string.IsNullOrEmpty(x.ConnectionString))
.Select(x => new Setting
{
Name = SettingUrn.Parse(x.Name),
Value = x.ConnectionString
})
.ToList();
return connectionStringSettings;
}
public override IEnumerable<Setting> GetSettings(Setting setting)
{
var keys =
_appSettingsSection.Settings.AllKeys
.Where(k => SettingUrn.Parse(k).IsLike(setting.Name))
.ToArray();
var settings = keys.Select(key => new Setting
{
Name = SettingUrn.Parse(key),
Value = _appSettingsSection.Settings[key].Value
})
.ToList();
return settings;
}
public void ctor_ObjectInitializer()
{
var setting = new Setting
{
Name = SettingUrn.Parse("foo.bar[baz]"),
Value = "waldo",
Attributes = new Dictionary<string, object> { ["Environment"] = "qux" },
["Config"] = "corge"
};
setting.Name.Verify().IsTrue(x => x == SettingUrn.Parse("foo.bar[baz]"));
setting.Attributes.Count.Verify().IsEqual(2);
setting.Attributes["Environment"].Verify().IsTrue(x => x.Equals("qux"));
setting.Attributes["Config"].Verify().IsTrue(x => x.Equals("corge"));
(setting["Environment"] as string).Verify().IsNotNullOrEmpty().IsEqual("qux");
(setting["Config"] as string).Verify().IsNotNullOrEmpty().IsEqual("corge");
setting.Value.Verify().IsNotNull().IsTrue(x => x.Equals("waldo"));
setting.WeakId.Verify().IsNotNullOrEmpty().IsEqual("foo.bar/corge/qux");
setting.StrongId.Verify().IsNotNullOrEmpty().IsEqual("foo.bar[baz]/corge/qux");
//setting.NamespaceEquals("environment", "qux").Verify().IsTrue();
//setting.IsLike(new Setting(
// new SettingUrn("foo.bar"),
// new Dictionary<string, object> { ["Environment"] = "qux" })
//{ Config = "corge" }
//).Verify().IsTrue();
//setting.IsLike(new Setting(
// new SettingUrn("foo.baar"),
// new Dictionary<string, object> { ["Environment"] = "qux" })
//{ Config = "corge" }
//).Verify().IsFalse();
}
public SQLiteCommand CreateInsertCommand(SQLiteConnection connection, Setting setting)
{
/*
INSERT OR REPLACE INTO Setting([Name], [Value])
VALUES('{setting.Name.FullNameEx}', '{setting.Value}')
*/
// --- build sql
var sql = new StringBuilder();
var dbProviderFactory = DbProviderFactories.GetFactory(connection);
using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
{
var quote = new Func<string, string>(identifier => commandBuilder.QuoteIdentifier(identifier));
var table = $"{quote(TableConfiguration.TableName)}";
var columns = setting.Attributes.Keys.Select(columnName => quote(columnName)).Aggregate(
$"[{nameof(Setting.Name)}], [{nameof(Setting.Value)}]",
(result, next) => $"{result}, {next}"
);
sql.Append($"INSERT OR REPLACE INTO {table}({columns})").AppendLine();
var parameterNames = setting.Attributes.Keys.Aggregate(
$"@{nameof(Setting.Name)}, @{nameof(Setting.Value)}",
(result, next) => $"{result}, @{next}"
);
sql.Append($"VALUES ({parameterNames})").AppendLine();
}
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql.ToString();
// --- add parameters
AddParameter(command, nameof(Setting.Name), setting.Name.StrongFullName);
AddParameter(command, nameof(Setting.Value), setting.Value);
AddParameters(command, setting.Attributes);
return command;
}
public SqlCommand CreateInsertCommand(SqlConnection connection, Setting setting)
{
/*
UPDATE [Setting]
SET [Value] = 'Hallo update!'
WHERE [Name]='baz' AND [Environment] = 'boz'
IF @@ROWCOUNT = 0
INSERT INTO [Setting]([Name], [Value], [Environment])
VALUES ('baz', 'Hallo insert!', 'boz')
*/
var sql = new StringBuilder();
var dbProviderFactory = DbProviderFactories.GetFactory(connection);
using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
{
var quote = new Func<string, string>(identifier => commandBuilder.QuoteIdentifier(identifier));
var table = $"{quote(TableConfiguration.SchemaName)}.{quote(TableConfiguration.TableName)}";
sql.Append($"UPDATE {table}").AppendLine();
sql.Append($"SET [{nameof(Setting.Value)}] = @{nameof(Setting.Value)}").AppendLine();
sql.Append(setting.Attributes.Keys.Aggregate(
$"WHERE ([{nameof(Setting.Name)}] = @{nameof(Setting.Name)} OR [{nameof(Setting.Name)}] LIKE @{nameof(Setting.Name)} + N'[[]%]')",
(result, next) => $"{result} AND {quote(next)} = @{next} ")
).AppendLine();
sql.Append($"IF @@ROWCOUNT = 0").AppendLine();
var columns = setting.Attributes.Keys.Select(columnName => quote(columnName)).Aggregate(
$"[{nameof(Setting.Name)}], [{nameof(Setting.Value)}]",
(result, next) => $"{result}, {next}"
);
sql.Append($"INSERT INTO {table}({columns})").AppendLine();
var parameterNames = setting.Attributes.Keys.Aggregate(
$"@{nameof(Setting.Name)}, @{nameof(Setting.Value)}",
(result, next) => $"{result}, @{next}"
);
sql.Append($"VALUES ({parameterNames})");
}
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql.ToString();
// --- add parameters
AddParameter(command, nameof(Setting.Name), setting.Name.StrongFullName);
AddParameter(command, nameof(Setting.Value), setting.Value);
AddParameters(command, setting.Attributes);
return command;
}
public SqlCommand CreateSelectCommand(SqlConnection connection, Setting setting)
{
var sql = new StringBuilder();
var dbProviderFactory = DbProviderFactories.GetFactory(connection);
using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
{
var quote = new Func<string, string>(identifier => commandBuilder.QuoteIdentifier(identifier));
var table = $"{quote(TableConfiguration.SchemaName)}.{quote(TableConfiguration.TableName)}";
sql.Append($"SELECT *").AppendLine();
sql.Append($"FROM {table}").AppendLine();
sql.Append(setting.Attributes.Aggregate(
$"WHERE ([{nameof(Setting.Name)}] = @{nameof(Setting.Name)} OR [{nameof(Setting.Name)}] LIKE @{nameof(Setting.Name)} + N'[[]%]')",
(result, next) => $"{result} AND {quote(next.Key)} = @{next.Key}")
);
}
var command = connection.CreateCommand();
command.CommandText = sql.ToString();
// --- add parameters & values
AddParameter(command, nameof(Setting.Name), setting.Name.WeakFullName);
AddParameters(command, setting.Attributes);
return command;
}
