public IEnumerable <ServiceCategoryData> GetActiveServiceCategories()
{
List <ServiceCategoryData> serviceCategories = new List <ServiceCategoryData>();
string sqlSelect = "exec FCN..sel_active_service_categories";
using (SqlDataReader reader = Utilities.ExecQuery(sqlSelect))
{
while (reader.Read())
{
string updateFirstName = string.Empty;
string updateLastName = string.Empty;
ServiceCategoryData newActiveCategory = new ServiceCategoryData
{
ID = (int)reader["ID"],
CategoryName = reader["category_name"].ToString(),
Description = reader["description"].ToString(),
Active = reader["active"].ToString() == "1" ? true : false,
CreatedDate = (DateTime)reader["created_date"],
UpdatedDate = (DateTime)reader["updated_date"]
};
serviceCategories.Add(newActiveCategory);
}
}
return(serviceCategories);
}
public ActionResult <ServiceCategoryData> GetServiceCategory(int id)
{
ServiceCategoryData data = GetServiceCategory().FirstOrDefault((p) => p.ID == id);
if (data == null)
{
return(NotFound());
}
return(Ok(data));
}
public ActionResult <ServiceCategoryData> PostServiceCategory([FromBody] ServiceCategoryData data)
{
int id = 0;
int isActive = (data.Active.ToString() == "True" ? 1 : 0);
//Get connection string - to be replaced with different credentials later
string fcnConnectionString = Utilities.GetDBConnectionString();
string sqlInsert = "exec FCN..ins_service_category @category_name, @description, @active";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdInsert = new SqlCommand(sqlInsert, fcnDBConnection))
{
cmdInsert.Parameters.AddWithValue("@category_name", data.CategoryName);
cmdInsert.Parameters.AddWithValue("@description", data.Description);
cmdInsert.Parameters.AddWithValue("@active", isActive);
using (SqlDataReader reader = cmdInsert.ExecuteReader())
{
while (reader.Read())
{
if (reader["ID"] != DBNull.Value)
{
id = (int)reader["ID"];
}
if (id == 0)
{
return(NotFound());
}
}
}
}
}
data = GetServiceCategory().FirstOrDefault((p) => p.ID == id);
return(CreatedAtAction("GetServiceCategory", new { id }, data));
}
public ActionResult <ServiceCategoryData> PutServiceCategory(int id, [FromBody] ServiceCategoryData data)
{
int isActive = (data.Active.ToString() == "True" ? 1 : 0);
//Get connection string - to be replaced with different credentials later
string fcnConnectionString = Utilities.GetDBConnectionString();
string sqlUpdate = "exec FCN..upd_service_category @ID, @category_name, @description, @active";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, fcnDBConnection))
{
cmdUpdate.Parameters.AddWithValue("@ID", id);
cmdUpdate.Parameters.AddWithValue("@category_name", data.CategoryName);
cmdUpdate.Parameters.AddWithValue("@description", data.Description);
cmdUpdate.Parameters.AddWithValue("@active", isActive);
cmdUpdate.ExecuteNonQuery();
}
}
data = GetServiceCategory().FirstOrDefault((p) => p.ID == id);
return(Ok(data));
}