public KeyVaultSecretData(KeyVaultSecret secret, string name, string description, KeyVaultSecretType type, DateTime created, long createdByUserId, DateTime?lastUpdate, long?lastUpdatedByUserId)
{
Secret = secret;
Name = name;
Description = description;
SecretType = type;
Created = created;
CreatedByUserId = createdByUserId;
LastUpdate = lastUpdate;
LastUpdatedByUserId = lastUpdatedByUserId;
}
public async ValueTask <bool> UpdateSecretData(long userId, long secretId, string name, KeyVaultSecretType type, byte[] value, byte[] iv)
{
using (var conn = new NpgsqlConnection(_connectionString)) {
await conn.OpenAsync().NoSync();
var transaction = await conn.BeginTransactionAsync().NoSync();
await using (transaction.NoSync()) {
var ts = DateTime.UtcNow;
bool updated;
using (var cmd = new NpgsqlCommand("UPDATE \"SecretData\" SET \"Type\" = @type, \"Value\" = @value, \"IV\" = @iv, \"LastUpdateDate\" = @lastUpdateData, \"LastUpdateUserId\" = @lastUpdateUserId WHERE \"SecretId\" = @secretId AND \"Name\" " + (string.IsNullOrEmpty(name) ? "IS NULL" : "= @name") + ";", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
if (!string.IsNullOrEmpty(name))
{
cmd.Parameters.AddWithValue("@name", name);
}
cmd.Parameters.AddWithValue("@type", type.ToString());
cmd.Parameters.AddWithValue("@value", value);
cmd.Parameters.AddWithValue("@iv", iv);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
updated = await cmd.ExecuteNonQueryAsync().NoSync() > 0;
}
if (updated)
{
using (var cmd = new NpgsqlCommand("UPDATE \"Secret\" SET \"LastUpdateDate\" = @lastUpdateData, \"LastUpdateUserId\" = @lastUpdateUserId WHERE \"id\" = @secretId;", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
}
await transaction.CommitAsync().NoSync();
}
return(updated);
}
}
}
public async ValueTask <bool> AddSecretData(long userId, long secretId, string name, string description, KeyVaultSecretType type, byte[] value, byte[] iv)
{
using (var conn = new NpgsqlConnection(_connectionString)) {
await conn.OpenAsync().NoSync();
var transaction = await conn.BeginTransactionAsync().NoSync();
await using (transaction.NoSync()) {
var ts = DateTime.UtcNow;
bool created;
using (var cmd = new NpgsqlCommand("INSERT INTO \"SecretData\" (\"SecretId\", \"Name\", \"Description\", \"Type\", \"Value\", \"IV\", \"CreateDate\", \"CreatorUserId\") VALUES (@secretId, @name, @description, @type, @value, @iv, @createDate, @creatorUserId);", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@name", string.IsNullOrEmpty(name) ? DBNull.Value : name);
cmd.Parameters.AddWithValue("@description", string.IsNullOrEmpty(description) ? DBNull.Value : description);
cmd.Parameters.AddWithValue("@type", type.ToString());
cmd.Parameters.AddWithValue("@value", value);
cmd.Parameters.AddWithValue("@iv", iv);
cmd.Parameters.AddWithValue("@createDate", ts);
cmd.Parameters.AddWithValue("@creatorUserId", userId);
created = await cmd.ExecuteNonQueryAsync().NoSync() > 0;
}
if (created)
{
using (var cmd = new NpgsqlCommand("UPDATE \"Secret\" SET \"LastUpdateDate\" = @lastUpdateData, \"LastUpdateUserId\" = @lastUpdateUserId WHERE \"id\" = @secretId;", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
}
await transaction.CommitAsync().NoSync();
}
return(created);
}
}
}
public async ValueTask <bool> UpdateSecretData(long userId, long secretId, string name, KeyVaultSecretType type, byte[] value, byte[] iv)
{
using (var conn = new SqliteConnection(_connectionString)) {
await conn.OpenAsync().NoSync();
var transaction = (SqliteTransaction)await conn.BeginTransactionAsync().NoSync();
await using (transaction.NoSync()) {
var ts = CommonUtility.GetTimestamp();
bool updated;
using (var cmd = new SqliteCommand("UPDATE [SecretData] SET [Type] = @type, [Value] = @value, [IV] = @iv, [LastUpdateDate] = @lastUpdateData, [LastUpdateUserId] = @lastUpdateUserId WHERE [SecretId] = @secretId AND [Name] " + (string.IsNullOrEmpty(name) ? "IS NULL" : "= @name") + ";", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
if (!string.IsNullOrEmpty(name))
{
cmd.Parameters.AddWithValue("@name", name);
}
cmd.Parameters.AddWithValue("@type", type.ToString());
cmd.Parameters.AddWithValue("@value", value);
cmd.Parameters.AddWithValue("@iv", iv);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
updated = await cmd.ExecuteNonQueryAsync().NoSync() > 0;
}
if (updated)
{
using (var cmd = new SqliteCommand("UPDATE [Secret] SET [LastUpdateDate] = @lastUpdateData, [LastUpdateUserId] = @lastUpdateUserId WHERE [id] = @secretId;", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
}
await transaction.CommitAsync().NoSync();
}
return(updated);
}
}
}
public async ValueTask <bool> AddSecretData(long userId, long secretId, string name, string description, KeyVaultSecretType type, byte[] value, byte[] iv)
{
using (var conn = new SqliteConnection(_connectionString)) {
await conn.OpenAsync().NoSync();
var transaction = (SqliteTransaction)await conn.BeginTransactionAsync().NoSync();
await using (transaction.NoSync()) {
var ts = CommonUtility.GetTimestamp();
bool created;
using (var cmd = new SqliteCommand("INSERT INTO [SecretData] ([SecretId], [Name], [Description], [Type], [Value], [IV], [CreateDate], [CreatorUserId]) VALUES (@secretId, @name, @description, @type, @value, @iv, @createDate, @creatorUserId);", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@name", string.IsNullOrEmpty(name) ? DBNull.Value : name);
cmd.Parameters.AddWithValue("@description", string.IsNullOrEmpty(description) ? DBNull.Value : description);
cmd.Parameters.AddWithValue("@type", type.ToString());
cmd.Parameters.AddWithValue("@value", value);
cmd.Parameters.AddWithValue("@iv", iv);
cmd.Parameters.AddWithValue("@createDate", ts);
cmd.Parameters.AddWithValue("@creatorUserId", userId);
created = await cmd.ExecuteNonQueryAsync().NoSync() > 0;
}
if (created)
{
using (var cmd = new SqliteCommand("UPDATE [Secret] SET [LastUpdateDate] = @lastUpdateData, [LastUpdateUserId] = @lastUpdateUserId WHERE [id] = @secretId;", conn, transaction)) {
cmd.Parameters.AddWithValue("@secretId", secretId);
cmd.Parameters.AddWithValue("@lastUpdateData", ts);
cmd.Parameters.AddWithValue("@lastUpdateUserId", userId);
}
await transaction.CommitAsync().NoSync();
}
return(created);
}
}
}
