public void Create (USER User)
{
if (User == null)
throw new ArgumentNullException("User");
if (User.GUID_RECORD == Guid.Empty)
{
User.GUID_RECORD = Guid.NewGuid();
}
if (string.IsNullOrEmpty(User.KEY))
throw new ArgumentNullException("KEY");
if (string.IsNullOrEmpty(User.KEY_IDENTITY) && !string.IsNullOrEmpty(User.PROVIDER))
{
throw new ArgumentNullException("KEY_IDENTITY");
}
if (!string.IsNullOrEmpty(User.KEY_IDENTITY) && string.IsNullOrEmpty(User.PROVIDER))
{
User.PROVIDER = "PASSWORD";
}
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
/// Check GUID
command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER] where [GUID_RECORD] = '{0}'", User.GUID_RECORD);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
/// Check key
command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER] where [KEY] = '{0}'", User.KEY);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
command.CommandText = @"insert into [SYSTEM.SECURITY.USER] (
[GUID_RECORD],
[KEY],
[LAST_LOGIN],
[LOGIN_ATTEMPT_COUNT],
[BATCH_GUID],
[HIDDEN],
[DELETED])
values (@GUID_RECORD, @KEY, @LAST_LOGIN, @LOGIN_ATTEMPT_COUNT, @BATCH_GUID, @HIDDEN, @DELETED)";
command.Parameters.AddWithValue("@GUID_RECORD", User.GUID_RECORD);
command.Parameters.AddWithValue("@KEY", User.KEY);
command.Parameters.AddWithValue("@LAST_LOGIN", DateTime.Today);
command.Parameters.AddWithValue("@LOGIN_ATTEMPT_COUNT", 0);
command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
command.Parameters.AddWithValue("@HIDDEN", 0);
command.Parameters.AddWithValue("@DELETED", 0);
command.ExecuteNonQuery();
/// INSERT USER_IDENTITY
if (!string.IsNullOrEmpty(User.KEY_IDENTITY) && !string.IsNullOrEmpty(User.PROVIDER))
{
command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER_IDENTITY] where [USER_GUID] = '{0}' AND PROVIDER = '{1}'", User.GUID_RECORD, User.PROVIDER);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
command.CommandText = @"insert into [SYSTEM.SECURITY.USER_IDENTITY] (
[USER_GUID],
[PROVIDER],
[KEY],
[BATCH_GUID],
[HIDDEN],
[DELETED])
values (@GUID_RECORD, @USER_GUID, @PROVIDER, @KEY, @BATCH_GUID, @HIDDEN, @DELETED)";
command.Parameters.AddWithValue("@USER_GUID", User.GUID_RECORD);
command.Parameters.AddWithValue("@PROVIDER", User.PROVIDER);
command.Parameters.AddWithValue("@KEY", User.KEY_IDENTITY); /// Добавить преобразование в пароль
command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
command.Parameters.AddWithValue("@HIDDEN", 0);
command.Parameters.AddWithValue("@DELETED", 0);
command.ExecuteNonQuery();
}
}
}
}
public void Update (USER User)
{
if (User == null)
throw new ArgumentNullException("User");
if (User.GUID_RECORD == Guid.Empty)
throw new ArgumentNullException("GUID_RECORD");
if (string.IsNullOrEmpty(User.KEY))
throw new ArgumentNullException("Key");
var record = GetById(User.GUID_RECORD);
var alterColumns = new List<string>();
var alterValues = new List<SqlParameter>();
if (!String.Equals(record.KEY, User.KEY))
{
alterColumns.Add("[KEY] = @KEY");
alterValues.Add(new SqlParameter("@KEY", User.KEY));
/// add check constraint
}
if (!String.Equals(record.DELETED, User.DELETED))
{
alterColumns.Add("[DELETED] = @DELETED");
alterValues.Add(new SqlParameter("@DELETED", User.DELETED));
}
if (!Nullable<Guid>.Equals(record.HIDDEN, User.HIDDEN))
{
alterColumns.Add("[HIDDEN] = @HIDDEN");
alterValues.Add(new SqlParameter("@HIDDEN", User.HIDDEN));
if (record.HIDDEN == true && User.HIDDEN == false)
{
alterColumns.Add("[LOGIN_ATTEMPT_COUNT] = @LOGIN_ATTEMPT_COUNT");
alterValues.Add(new SqlParameter("@LOGIN_ATTEMPT_COUNT", 0));
}
}
if (alterColumns.Any())
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = String.Format(
"update [SYSTEM.SECURITY.USER] set {0} where [GUID_RECORD] = @GUID_RECORD",
String.Join(", ", alterColumns));
command.Parameters.AddWithValue("@GUID_RECORD", User.GUID_RECORD);
command.Parameters.AddRange(alterValues.ToArray());
command.ExecuteNonQuery();
}
}
}
}