public void Update(PERSON Person)
{
if (Person == null)
throw new ArgumentNullException("category");
if (Person.GUID_RECORD == Guid.Empty)
throw new ArgumentNullException("GUID_RECORD");
if (string.IsNullOrEmpty(Person.FIRSTNAME))
throw new ArgumentNullException("Firstname");
if (string.IsNullOrEmpty(Person.LASTNAME))
throw new ArgumentNullException("Lastname");
var record = GetById(Person.GUID_RECORD);
var alterColumns = new List<string>();
var alterValues = new List<SqlParameter>();
if (!String.Equals(Person.LASTNAME, record.LASTNAME))
{
alterColumns.Add("[LASTNAME] = @LASTNAME");
alterValues.Add(new SqlParameter("@LASTNAME", Person.LASTNAME));
}
if (!String.Equals(Person.FIRSTNAME, record.FIRSTNAME))
{
alterColumns.Add("[FIRSTNAME] = @FIRSTNAME");
alterValues.Add(new SqlParameter("@FIRSTNAME", Person.FIRSTNAME));
}
Person.MIDNAME = string.IsNullOrEmpty(Person.MIDNAME) ? string.Empty : Person.MIDNAME;
if (!String.Equals(Person.MIDNAME.ToString(), record.MIDNAME))
{
alterColumns.Add("[MIDNAME] = @MIDNAME");
alterValues.Add(new SqlParameter("@MIDNAME", Person.MIDNAME));
}
if (!String.Equals(Person.BIRTHDATE, record.BIRTHDATE))
{
alterColumns.Add("[BIRTHDATE] = @BIRTHDATE");
alterValues.Add(new SqlParameter("@BIRTHDATE", Person.BIRTHDATE));
}
if (!String.Equals(Person.USER_GUID, record.USER_GUID))
{
alterColumns.Add("[USER_GUID] = @USER_GUID");
alterValues.Add(new SqlParameter("@USER_GUID", Person.USER_GUID));
}
if (!String.Equals(Person.HIDDEN, record.HIDDEN))
{
using (var connection = new SqlConnection(_connectionString))
{
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
/// Checking user
command.CommandText = string.Format("select 1 from [SYSTEM.SECURITY.USER] where [GUID_RECORD] = '{0}'", Person.USER_GUID);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
throw new Exception("user is");
}
else
{
/// Check for Person user
command.CommandText = string.Format("select 1 from [BUSINESS.PERSON] where [USER_GUID] = '{0}'", Person.USER_GUID);
using (var readerUser = command.ExecuteReader())
{
if (reader.HasRows)
{
throw new Exception("Recording for the user already exists");
}
else
{
alterColumns.Add("[HIDDEN] = @HIDDEN");
alterValues.Add(new SqlParameter("@HIDDEN", Person.HIDDEN));
}
}
}
}
}
connection.Close();
}
catch (Exception e)
{ }
finally
{
connection.Close();
}
}
}
if (alterColumns.Any())
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = String.Format(
"update [BUSINESS.PERSON] set {0} where [GUID_RECORD] = @GUID_RECORD",
String.Join(", ", alterColumns));
command.Parameters.AddWithValue("@GUID_RECORD", Person.GUID_RECORD);
command.Parameters.AddRange(alterValues.ToArray());
command.ExecuteNonQuery();
}
}
}
}
public void Create(PERSON Person)
{
if (Person == null)
throw new ArgumentNullException("category");
if (Person.GUID_RECORD == Guid.Empty)
{
Person.GUID_RECORD = Guid.NewGuid();
}
if (string.IsNullOrEmpty(Person.FIRSTNAME))
{
throw new ArgumentNullException("Firstname");
}
if (string.IsNullOrEmpty(Person.LASTNAME))
{
throw new ArgumentNullException("Lastname");
}
using (var connection = new SqlConnection(_connectionString))
{
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
/// Check id
command.CommandText = string.Format("select 1 from [BUSINESS.PERSON] where [GUID_RECORD] = '{0}'", Person.GUID_RECORD);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
command.CommandText = @"insert into [BUSINESS.PERSON] (
[GUID_RECORD],
[FIRSTNAME],
[LASTNAME],
[MIDNAME],
[BIRTHDATE],
[USER_GUID],
[BATCH_GUID],
[HIDDEN],
[DELETED])
values (@GUID_RECORD, @FIRSTNAME, @LASTNAME, @MIDNAME, @BIRTHDATE, @USER_GUID, @BATCH_GUID, @HIDDEN, @DELETED)";
command.Parameters.AddWithValue("@GUID_RECORD", Person.GUID_RECORD);
command.Parameters.AddWithValue("@FIRSTNAME", Person.FIRSTNAME);
command.Parameters.AddWithValue("@LASTNAME", Person.LASTNAME);
command.Parameters.AddWithValue("@MIDNAME", string.IsNullOrEmpty(Person.MIDNAME)? string.Empty : Person.MIDNAME );
command.Parameters.AddWithValue("@BIRTHDATE", Person.BIRTHDATE.HasValue? Person.BIRTHDATE.Value : (object)DBNull.Value );
command.Parameters.AddWithValue("@USER_GUID", DBNull.Value);
command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
command.Parameters.AddWithValue("@HIDDEN", 0);
command.Parameters.AddWithValue("@DELETED", 0);
command.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception e)
{
throw new Exception("Exception");
}
finally
{
connection.Close();
}
}
}
