public IHttpActionResult Get(string columnName, string value)
{
SearchContainer searchContainer;
try
{
// Construct a search container object
searchContainer = new SearchContainer { ColumnName = columnName, SearchValue = value };
// Get customers
IEnumerable<Customer> customers = customerService.SearchFor(searchContainer);
if (customers == null)
{
return NotFound();
}
return Ok(customers);
}
catch
{
return InternalServerError();
}
}
public IEnumerable<Customer> SearchFor(SearchContainer searchContainer)
{
// Create connection object
using (this.CreateConnection())
{
try
{
List<Customer> customersReturnList;
SqlCommand cmd;
// Create list object
customersReturnList = new List<Customer>(50);
// Connect to database and execute given stored procedure
cmd = this.Setup("appSchema.usp_CustomerList");
// Add variable for stored procedure
switch (searchContainer.ColumnName)
{
case "CustomerId":
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = searchContainer.SearchValue;
break;
case "Name":
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = searchContainer.SearchValue;
break;
case "Address":
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 40).Value = searchContainer.SearchValue;
break;
case "PostNumber":
cmd.Parameters.Add("@PostNumber", SqlDbType.VarChar, 6).Value = searchContainer.SearchValue;
break;
case "City":
cmd.Parameters.Add("@City", SqlDbType.VarChar, 30).Value = searchContainer.SearchValue;
break;
case "EmailAddress":
cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 50).Value = searchContainer.SearchValue;
break;
case "PhoneNumber":
cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 20).Value = searchContainer.SearchValue;
break;
case "CellPhoneNumber":
cmd.Parameters.Add("@CellPhoneNumber", SqlDbType.VarChar, 20).Value = searchContainer.SearchValue;
break;
case "ParentCustomerName":
cmd.Parameters.Add("@ParentCustomerName", SqlDbType.VarChar, 50).Value = searchContainer.SearchValue;
break;
case "Notes":
cmd.Parameters.Add("@Notes", SqlDbType.VarChar, 200).Value = searchContainer.SearchValue;
break;
}
// Get all data from stored procedure
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Get all data rows
while (reader.Read())
{
// Create new Customer object from database values and add to list
customersReturnList.Add(new Customer
{
CustomerId = reader.GetSafeInt32(reader.GetOrdinal("CustomerId")),
Name = reader.GetSafeString(reader.GetOrdinal("Name")),
Address = reader.GetSafeString(reader.GetOrdinal("Address")),
PostNumber = reader.GetSafeString(reader.GetOrdinal("PostNumber")),
City = reader.GetSafeString(reader.GetOrdinal("City")),
EmailAddress = reader.GetSafeString(reader.GetOrdinal("EmailAddress")),
PhoneNumber = reader.GetSafeString(reader.GetOrdinal("PhoneNumber")),
CellPhoneNumber = reader.GetSafeString(reader.GetOrdinal("CellPhoneNumber")),
ParentCustomerId = reader.GetSafeInt32(reader.GetOrdinal("ParentCustomerId")),
ParentCustomerName = reader.GetSafeString(reader.GetOrdinal("ParentCustomerName")),
ImageSrc = reader.GetSafeString(reader.GetOrdinal("ImageSrc")),
Notes = reader.GetSafeString(reader.GetOrdinal("Notes")),
TotalBookings = reader.GetSafeInt32(reader.GetOrdinal("TotalBookings")),
TotalBookingValue = reader.GetSafeDecimal(reader.GetOrdinal("TotalBookingValue")),
ChildCustomers = reader.GetSafeInt32(reader.GetOrdinal("ChildCustomers"))
});
}
}
// Remove unused list rows, free memory.
customersReturnList.TrimExcess();
// Return list
return customersReturnList;
}
catch
{
throw new ApplicationException(DAL_ERROR_MSG);
}
}
}
public IEnumerable<Location> SearchFor(SearchContainer searchContainer)
{
// Create connection object
using (this.CreateConnection())
{
try
{
List<Location> locationsReturnList;
SqlCommand cmd;
// Create list object
locationsReturnList = new List<Location>(50);
// Connect to database and execute given stored procedure
cmd = this.Setup("appSchema.usp_LocationList");
// Add variable for stored procedure
switch (searchContainer.ColumnName)
{
case "Name":
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = searchContainer.SearchValue;
break;
case "MaxPeople":
cmd.Parameters.Add("@MaxPeople", SqlDbType.SmallInt).Value = Int16.Parse(searchContainer.SearchValue);
break;
case "BookingPricePerHour":
cmd.Parameters.Add("@BookingPricePerHour", SqlDbType.Decimal).Value = decimal.Parse(searchContainer.SearchValue);
break;
}
// Get all data from stored procedure
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Get all data rows
while (reader.Read())
{
// Create new Location object from database values and add to list
locationsReturnList.Add(new Location
{
LocationId = reader.GetSafeInt32(reader.GetOrdinal("LocationId")),
Name = reader.GetSafeString(reader.GetOrdinal("Name")),
MaxPeople = reader.GetSafeInt16(reader.GetOrdinal("MaxPeople")),
GPSLongitude = reader.GetSafeDecimal(reader.GetOrdinal("GPSLongitude")),
GPSLatitude = reader.GetSafeDecimal(reader.GetOrdinal("GPSLatitude")),
ImageSrc = reader.GetSafeString(reader.GetOrdinal("ImageSrc")),
BookingPricePerHour = reader.GetSafeDecimal(reader.GetOrdinal("BookingPricePerHour")),
TotalBookings = reader.GetSafeInt32(reader.GetOrdinal("TotalBookings")),
TotalBookingValue = reader.GetSafeDecimal(reader.GetOrdinal("TotalBookingValue")),
MinutesMarginAfterBooking = reader.GetSafeInt16(reader.GetOrdinal("MinutesMarginAfterBooking"))
});
}
}
// Remove unused list rows, free memory.
locationsReturnList.TrimExcess();
// Return list
return locationsReturnList;
}
catch
{
throw new ApplicationException(DAL_ERROR_MSG);
}
}
}
public IEnumerable<Customer> SearchFor(SearchContainer searchContainer)
{
return CustomerDAL.SearchFor(searchContainer);
}
public IEnumerable<Location> SearchFor(SearchContainer searchContainer)
{
return LocationDAL.SearchFor(searchContainer);
}
public IEnumerable<Booking> SearchFor(SearchContainer searchContainer)
{
// Create connection object
using (this.CreateConnection())
{
try
{
List<Booking> bookingsReturnList;
SqlCommand cmd;
// Create list object
bookingsReturnList = new List<Booking>(50);
// Connect to database and execute given stored procedure
cmd = this.Setup("appSchema.usp_BookingList");
// Add variable for stored procedure
switch(searchContainer.ColumnName)
{
case "Name":
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = searchContainer.SearchValue;
break;
case "NumberOfPeople":
cmd.Parameters.Add("@NumberOfPeople", SqlDbType.SmallInt).Value = Int16.Parse(searchContainer.SearchValue);
break;
case "Notes":
cmd.Parameters.Add("@Notes", SqlDbType.VarChar, 200).Value = searchContainer.SearchValue;
break;
case "CalculatedBookingPrice":
cmd.Parameters.Add("@CalculatedBookingPrice", SqlDbType.Decimal).Value = decimal.Parse(searchContainer.SearchValue);
break;
}
// Get all data from stored procedure
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Get all data rows
while (reader.Read())
{
// Create new Booking object from database values and add to list
bookingsReturnList.Add(new Booking
{
BookingId = reader.GetSafeInt32(reader.GetOrdinal("BookingId")),
Name = reader.GetSafeString(reader.GetOrdinal("Name")),
CustomerId = reader.GetSafeInt32(reader.GetOrdinal("CustomerId")),
CustomerName = reader.GetSafeString(reader.GetOrdinal("CustomerName")),
Provisional = reader.GetBoolean(reader.GetOrdinal("Provisional")),
NumberOfPeople = reader.GetSafeInt16(reader.GetOrdinal("NumberOfPeople")),
Discount = reader.GetSafeDecimal(reader.GetOrdinal("Discount")),
CalculatedBookingPrice = reader.GetSafeDecimal(reader.GetOrdinal("CalculatedBookingPrice")),
Notes = reader.GetSafeString(reader.GetOrdinal("Notes")),
//CreatedByUserId = reader.GetSafeInt32(reader.GetOrdinal("CreatedByUserId")),
//ModifiedByUserId = reader.GetSafeInt32(reader.GetOrdinal("ModifiedByUserId")),
//ResponsibleUserId = reader.GetSafeInt32(reader.GetOrdinal("ResponsibleUserId"))
});
}
}
// Remove unused list rows, free memory.
bookingsReturnList.TrimExcess();
// Return list
return bookingsReturnList;
}
catch
{
throw new ApplicationException(DAL_ERROR_MSG);
}
}
}
