public IList<ItemStatusEntity> GetAllItemStatuss() { List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>(); try { _conn = new SqlConnection(ConnString); _conn.Open(); string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus;"; var cmd = new SqlCommand(sql, _conn); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { var itemStatus = new ItemStatusEntity() { itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")), status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")), updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")), updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK")) }; itemStatusList.Add(itemStatus); } } finally { _conn?.Close(); } return itemStatusList; }
internal static ItemStatusEntity ItemStatus(ItemStatus ent) { ItemStatusEntity data = new ItemStatusEntity(); data.itemStatusId = ent.Id; data.status = ent.Status; data.updateTimestamp = ent.UpdateTimestamp; data.updatePersonFK = ent.UpdatePersonFK; return data; }
internal static ItemStatus ItemStatus(ItemStatusEntity data) { ItemStatus ent = new ItemStatus(); ent.Id = data.itemStatusId; ent.Status = data.status; ent.UpdateTimestamp = data.updateTimestamp; ent.UpdatePersonFK = data.updatePersonFK; return ent; }
public ItemStatusEntity GetItemStatusById(int id) { List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>(); try { _conn = new SqlConnection(ConnString); _conn.Open(); string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus WHERE itemStatusId = @id;"; var cmd = new SqlCommand(sql, _conn); var paramQuery = new SqlParameter { ParameterName = "@id", Value = "%" + id + "%" }; cmd.Parameters.Add(paramQuery); var rdr = cmd.ExecuteReader(); while (rdr.Read()) { var itemStatus = new ItemStatusEntity() { itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")), status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")), updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")), updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK")) }; itemStatusList.Add(itemStatus); } } finally { _conn?.Close(); } return itemStatusList.FirstOrDefault(); }
private void CheckItemStatusForRequiredValues(ItemStatusEntity status, RepositoryUtils.RepositoryAction action) { List<string> missingFields = new List<string>(); //if (String.IsNullOrWhiteSpace(p.userName)) missingFields.Add("User Name"); //if (String.IsNullOrWhiteSpace(p.emailAddress)) missingFields.Add("Email Address"); //if (String.IsNullOrWhiteSpace(p.firstName)) missingFields.Add("First Name"); //if (String.IsNullOrWhiteSpace(p.lastName)) missingFields.Add("Last Name"); //if (String.IsNullOrWhiteSpace(p.passwordHash)) missingFields.Add("Password"); if (missingFields.Count > 0) { throw new Exception(String.Format("Cannot {0} ItemStatus: Missing Fields {1}", action.ToString(), String.Join(", ", missingFields.ToArray()))); } }
public void Update(int id, ItemStatusEntity itemStatus) { CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Update); var contactToUpdate = GetItemStatusById(id); if (contactToUpdate == null) { throw new Exception("ItemStatus does not exist in database"); } try { _conn = new SqlConnection(ConnString); var cmd = _conn.CreateCommand(); cmd.CommandText = @"UPDATE person SET [status]=@status, [updateTimestamp]=getdate(), [updatePersonFK]=@updatePersonFK WHERE personId=@Id"; cmd.Parameters.Add("@Id", SqlDbType.Int); cmd.Parameters["@Id"].Value = id; cmd.Parameters.Add("@status", SqlDbType.VarChar); cmd.Parameters["@status"].Value = itemStatus.status; cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int); cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK; _conn.Open(); var number = cmd.ExecuteNonQuery(); if (number != 1) { throw new Exception($"No Contacts were updated with Id: {id}"); } } finally { _conn?.Close(); } }
public long Insert(ItemStatusEntity itemStatus) { CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Insert); try { var contactExists = GetItemStatusByStatusName(itemStatus.status); if (contactExists != null) { throw new Exception($"Entity {itemStatus.status} already exists in database!"); } _conn = new SqlConnection(ConnString); var cmd = _conn.CreateCommand(); cmd.CommandText = @"INSERT INTO[dbo].[itemStatus] (status, updateTimestamp, updatePersonFK) VALUES(@status, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)"; cmd.Parameters.Add("@status", SqlDbType.VarChar); cmd.Parameters["@status"].Value = itemStatus.status; cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int); cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK; _conn.Open(); try { return int.Parse(cmd.ExecuteScalar().ToString()); } catch (Exception) { throw new Exception($"ItemStatus {itemStatus.status} not inserted in database!"); } } finally { _conn?.Close(); } }