public DbCommandData(DbContext context, System.Data.IDbCommand innerCommand)
{
Context = context;
InnerCommand = innerCommand;
InnerCommand.CommandType = (System.Data.CommandType)DbCommandTypes.Text;
Sql = new StringBuilder();
}
public static bool UpdateOrderInfo(SubmitOrderEntity entity)
{
bool result = false;
try
{
using (var context =
new DbContext().ConnectionStringName("CrmRstV1", new SqlServerProvider()))
{
var rowsAffected = context.StoredProcedure("sp_UpdateOrderInfo")
.Parameter("ContactName", entity.ContactName)
.Parameter("ContactPhone", entity.ContactPhone)
.Parameter("DiningDate", entity.DiningDate)
.Parameter("OrderId", entity.OrderId)
.Parameter("PersonCount", entity.PersonCount)
.Parameter("sex", entity.sex).Execute();
result = rowsAffected > 0;
}
}
catch (Exception ex)
{
//TODO
Logger.Log(ex);
}
return result;
}
// PUT: api/Customer/5
public int Put([FromBody] dynamic customer)
{
IDbContext Context = new DbContext().ConnectionStringName("Chinook", new SqlServerProvider());
int rowsAffected = Context.Sql(@"UPDATE Customer SET
FirstName = @0,
LastName = @1,
Address = @2,
City = @3,
Country = @4,
PostalCode = @5,
Phone = @6,
Email = @7
WHERE CustomerId = @8;")
.Parameters(
(string)customer.FirstName,
(string)customer.LastName,
(string)customer.Address,
(string)customer.City,
(string)customer.Country,
(string)customer.PostalCode,
(string)customer.Phone,
(string)customer.Email,
(int)customer.CustomerId)
.Execute();
return (int)customer.CustomerId;
}
/// <summary>
/// 获取今天所有特价菜。
/// </summary>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="sortExpression"></param>
/// <returns></returns>
public static List<SpecialsEntity> GetTodayByRestaurantId(Guid restaurantId)
{
List<SpecialsEntity> result = null;
try
{
using (var context =
new DbContext().ConnectionStringName("CrmRstV1", new SqlServerProvider()))
{
int dayOfWeek = (int) DateTime.Now.DayOfWeek;
if (dayOfWeek == 0)
{
dayOfWeek = 7;
}
var select = context.StoredProcedure("sp_GetTodayByRestaurantId")
.Parameter("RestaurantId", restaurantId)
.Parameter("WeekDateUse", dayOfWeek);
result = select.QueryMany<SpecialsEntity>();
}
}
catch (Exception exception)
{
Logger.Log(exception);
//TODO
}
if (result == null)
{
result=new List<SpecialsEntity>();
}
return result;
}
public DbCommand(
DbContext dbContext,
System.Data.IDbCommand innerCommand)
{
Data = new DbCommandData(dbContext, innerCommand);
Data.ExecuteQueryHandler = new ExecuteQueryHandler(this);
}
/// <summary>
/// 根据餐馆Id 得到特价菜数量。
/// </summary>
/// <param name="restaurantId"></param>
/// <returns></returns>
public static int CountAll(Guid restaurantId)
{
using (var context = new DbContext().ConnectionStringName("CrmRstV1", new SqlServerProvider()))
{
return context.Sql(" SELECT COUNT(*) FROM Specials ")
.QuerySingle<int>();
}
}
protected override void SelectFetchEntities(int entityCount)
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
var results = context.Sql("SELECT * FROM Entity WHERE Id <= @0", entityCount).QueryMany<Entity>();
context.Commit();
}
}
protected override void ResetDatabase()
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
context.Sql("DELETE FROM Entity").Execute();
context.Sql("DELETE FROM sqlite_sequence WHERE name = @0", "Entity").Execute();
context.Commit();
}
}
protected override void DeleteEntities(List<Entity> entities)
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
// Seriously, I have to specify the table every time and call execute via some bizarre api
entities.ForEach(entity => context.Delete("Entity", entity).Where(x => x.Id).Execute());
context.Commit();
}
}
protected override void InsertEntities(List<Entity> entities)
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
// Ugh, spcifying the table again having to ignore the id and assign the result, this makes me sad :(
entities.ForEach(entity =>
{
entity.Id = context.Insert("Entity", entity).AutoMap(x => x.Id).ExecuteReturnLastId<int>();
});
context.Commit();
}
}
protected override void SelectSingleEntity(int entityCount)
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
do
{
// So much for a select by ID method!
var entity = context.Sql("SELECT * FROM Entity WHERE Id = @0", entityCount).QuerySingle<Entity>();
entityCount--;
}
while (entityCount > 0);
context.Commit();
}
}
// POST: api/Customer
public int Post([FromBody] dynamic customer)
{
IDbContext Context = new DbContext().ConnectionStringName("Chinook", new SqlServerProvider());
int customerId = Context.Sql(@"INSERT INTO Customer(FirstName, LastName, Address, City, Country,
PostalCode, Phone, Email) VALUES(@0, @1, @2, @3, @4, @5, @6, @7);")
.Parameters(
(string)customer.FirstName,
(string)customer.LastName,
(string)customer.Address,
(string)customer.City,
(string)customer.Country,
(string)customer.PostalCode,
(string)customer.Phone,
(string)customer.Email)
.ExecuteReturnLastId<int>();
return customerId;
}
public static List<RestaurantImage> GetRestaurantImages(Guid restaurantId)
{
List<RestaurantImage> result = null;
try
{
using (var context =
new DbContext().ConnectionStringName("CrmRstV1", new SqlServerProvider()))
{
var select = context.StoredProcedure("SP_GetRestaurantImages")
.Parameter("RestaurantId", restaurantId);
result = select.QueryMany<RestaurantImage>();
}
}
catch (Exception)
{
//TODO
}
if (result == null)
{
result = new List<RestaurantImage>();
}
return result;
}
protected override void UpdateEntities(List<Entity> entities)
{
using (var context = new DbContext().ConnectionStringName("SQLiteTest", new SqliteProvider()))
{
context.UseTransaction(true);
// And again...
entities.ForEach(entity => context.Update("Entity", entity).AutoMap(x => x.Id).Where(x => x.Id).Execute());
context.Commit();
}
}
// GET: api/Customer/5
public dynamic Get(int id)
{
IDbContext Context = new DbContext().ConnectionStringName("Chinook", new SqlServerProvider());
dynamic customer = Context.Sql("SELECT * FROM Customer WHERE CustomerId = @0", id).QuerySingle<dynamic>();
return customer;
}
// GET: api/Customer
public IEnumerable<dynamic> Get()
{
IDbContext Context = new DbContext().ConnectionStringName("Chinook", new SqlServerProvider());
IEnumerable<dynamic> customers = Context.Sql("SELECT * FROM Customer").QueryMany<dynamic>();
return customers;
}
// DELETE: api/Customer/5
public void Delete(int id)
{
IDbContext Context = new DbContext().ConnectionStringName("Chinook", new SqlServerProvider());
int rowsAffected = Context.Sql("DELETE FROM Customer WHERE CustomerId = @0", id).Execute();
}
