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(); } } }