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