public IEnumerable <ServiceSubcategoryData> GetServiceSubcategory()
{
List <ServiceSubcategoryData> serviceSubcategories = new List <ServiceSubcategoryData>();
string sqlSelect = "exec sel_service_subcategory";
using (SqlDataReader reader = Utilities.ExecQuery(sqlSelect))
{
while (reader.Read())
{
string updateFirstName = string.Empty;
string updateLastName = string.Empty;
ServiceSubcategoryData newServiceSubcategory = new ServiceSubcategoryData
{
ID = (int)reader["ID"],
SubCategoryName = reader["subcategory_name"].ToString(),
CategoryID = (int)reader["service_categoryID"],
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"]
};
serviceSubcategories.Add(newServiceSubcategory);
}
}
return(serviceSubcategories);
}
public ActionResult <ServiceSubcategoryData> GetServiceSubcategory(int id)
{
ServiceSubcategoryData data = GetServiceSubcategory().FirstOrDefault((p) => p.ID == id);
if (data == null)
{
return(NotFound());
}
return(Ok(data));
}
public ActionResult <ServiceSubcategoryData> PostServiceSubcategory([FromBody] ServiceSubcategoryData 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_subcategory @subcategory_name, @service_categoryID, @description, @active";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdInsert = new SqlCommand(sqlInsert, fcnDBConnection))
{
cmdInsert.Parameters.AddWithValue("@subcategory_name", data.SubCategoryName);
cmdInsert.Parameters.AddWithValue("@service_categoryID", data.CategoryID);
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 = GetServiceSubcategory().FirstOrDefault((p) => p.ID == id);
return(CreatedAtAction("GetServiceSubcategory", new { id }, data));
}
public IEnumerable <ServiceSubcategoryData> GetActiveServiceSubcategories(int id)
{
//Get connection string - to be replaced with different credentials later
string fcnConnectionString = Utilities.GetDBConnectionString();
List <ServiceSubcategoryData> serviceSubcategories = new List <ServiceSubcategoryData>();
string sqlSelect = "exec FCN..sel_active_service_subcategories @categoryID";
using (SqlConnection fcnDBConnection = new SqlConnection(fcnConnectionString))
{
fcnDBConnection.Open();
using (SqlCommand cmdInsert = new SqlCommand(sqlSelect, fcnDBConnection))
{
cmdInsert.Parameters.AddWithValue("@subcategory_name", id);
using (SqlDataReader reader = cmdInsert.ExecuteReader())
{
while (reader.Read())
{
string updateFirstName = string.Empty;
string updateLastName = string.Empty;
ServiceSubcategoryData newSubCategory = new ServiceSubcategoryData
{
ID = (int)reader["ID"],
SubCategoryName = reader["subcategory_name"].ToString(),
CategoryID = (int)reader["service_categoryID"],
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"]
};
serviceSubcategories.Add(newSubCategory);
}
}
}
}
return(serviceSubcategories);
}
public ActionResult <ServiceSubcategoryData> PutServiceSubcategory(int id, [FromBody] ServiceSubcategoryData 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_subcategory @ID, @subcategory_name, @service_categoryID, @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("@subcategory_name", data.SubCategoryName);
cmdUpdate.Parameters.AddWithValue("@service_categoryID", data.CategoryID);
cmdUpdate.Parameters.AddWithValue("@description", data.Description);
cmdUpdate.Parameters.AddWithValue("@active", isActive);
cmdUpdate.ExecuteNonQuery();
}
}
data = GetServiceSubcategory().FirstOrDefault((p) => p.ID == id);
return(Ok(data));
}