public HttpResponseMessage GetAccommodationPerHost(long id, [FromBody] SelectSQLString sqlString)
{
AccommodationPersistenceService accommodationPersistenceService = new AccommodationPersistenceService();
if (sqlString != null)
{
var accommodations = accommodationPersistenceService.GetAccommodationsSelectiveColumnsHost(sqlString.includeAmenities, sqlString.includeBedSize, sqlString.includePricePerNight, id);
HttpResponseMessage response;
if (accommodations == null)
{
response = Request.CreateResponse(HttpStatusCode.NotFound, "No accommodations found for cities from that host with id: " + id);
return(response);
}
response = Request.CreateResponse(HttpStatusCode.OK, accommodations);
return(response);
}
var selectedAccommodations = accommodationPersistenceService.GetAccommodationsHost(id);
HttpResponseMessage selectedResponse;
if (selectedAccommodations == null)
{
selectedResponse = Request.CreateResponse(HttpStatusCode.NotFound, "No accommodations found for cities from that host with id: " + id);
return(selectedResponse);
}
selectedResponse = Request.CreateResponse(HttpStatusCode.OK, selectedAccommodations);
return(selectedResponse);
}
public override bool OfCreateCommand(string ps_target_table, string ps_view_table, DbTransaction pTran,
bool pGenNonSelectCommand = true
)
{
if (ps_view_table.Trim().Length == 0)
{
ps_view_table = ps_target_table;
}
if (ps_target_table.Trim().Length > 0)
{
this.GenCommandSQLString = "SELECT * FROM " + ps_target_table + " ";
}
// 如果不是以 SELECT 開頭，則加上 SELECT 語句
if (ps_view_table.ToUpper().StartsWith("SELECT") == false && ps_view_table.Trim().Length > 0)
{
this.SelectSQLString = "SELECT * FROM " + ps_view_table + " ";
this.SelectCountSQLString = "SELECT Count(1) FROM " + ps_view_table + " ";
}
this.DataAdapter = new SqlDataAdapter();
// 查詢 Select Command
if (SelectSQLString.Trim().Length > 0)
{
DataAdapter.SelectCommand = new SqlCommand(SelectSQLString, (SqlConnection)DBConntion, (SqlTransaction)pTran);
}
// 更新 Command
if (this.ReadOnly == false && ps_target_table.Trim().Length > 0)
{
if (GenCommandSQLString.Length == 0 || GenCommandSQLString.Length == 0)
{
return(false);
}
SqlCommandBuilder lcommand_builder = new SqlCommandBuilder((SqlDataAdapter)DataAdapter);
lcommand_builder.ConflictOption = ConflictOption.OverwriteChanges;
lcommand_builder.RefreshSchema();
DataAdapter.SelectCommand = new SqlCommand(GenCommandSQLString, (SqlConnection)DBConntion, (SqlTransaction)pTran);
if (pGenNonSelectCommand)
{
DataAdapter.UpdateCommand = lcommand_builder.GetUpdateCommand();
DataAdapter.InsertCommand = lcommand_builder.GetInsertCommand();
DataAdapter.DeleteCommand = lcommand_builder.GetDeleteCommand();
}
// 用 select sql 再重新產生 SelectCommand
DataAdapter.SelectCommand = new SqlCommand(SelectSQLString, (SqlConnection)DBConntion, (SqlTransaction)pTran);
}
return(true);
}
public override DataTable OfSelect(DbTransaction ptran, string ps_where, params object[] p_params)
{
string ls_sql = "";
if (SelectSQLString.ToLower().Contains("where") || ps_where.Trim().ToLower().StartsWith("where"))
{
ls_sql = this.SelectSQLString + ps_where;
}
else
{
ls_sql = this.SelectSQLString + " WHERE 1=1 " + ps_where;
}
return(OfExecuteDatatable(ptran, ls_sql, p_params));
}
/// <summary>
///
/// </summary>
/// <param name="ps_where"></param>
/// <returns></returns>
public int of_select_count(DbTransaction ptran, string ps_where)
{
string ls_sql = "";
if (SelectSQLString.ToLower().Contains("where") || ps_where.Trim().ToLower().StartsWith("where"))
{
ls_sql = this.SelectCountSQLString + ps_where;
}
else
{
ls_sql = this.SelectCountSQLString + " WHERE " + ps_where;
}
int li_return = int.Parse(of_execute_scalar(ptran, ls_sql, null).ToString());
return(li_return);
}
