public override IList <IDistrict> GetByShop(int shopId)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <District>("SELECT D.Id, D.name, D.Created, D.Updated FROM [District] D INNER JOIN [DistrictShop] ds ON D.Id = ds.DistrictId WHERE ds.ShopId = @shopId", new SqlParameter("shopId", shopId)).ToList <IDistrict>());
}
}
public override bool Update(int id, string name)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.ExecuteSqlCommand("UPDATE FROM [Shop] SET name = @name WHERE ID = @id", new SqlParameter("id", id), new SqlParameter("name", name)) > 0);
}
}
public override IList <IDistrict> GetAll()
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <District>("SELECT * FROM [District]").ToList <IDistrict>());
}
}
public override IList <IShop> GetByDistrict(int districtId)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <Shop>("SELECT S.Id, S.name, S.Created, S.Updated FROM [Shop] S INNER JOIN [DistrictShop] ds ON S.Id = ds.ShopId WHERE ds.DistrictId = @DistrictId", new SqlParameter("districtId", districtId)).ToList <IShop>());
}
}
public override int Insert(string name)
{
using (var db = new TestCompanyEntities())
{
return(Convert.ToInt32(db.Database.SqlQuery <decimal>("INSERT INTO [Shop] (Name) VALUES(@name);SELECT SCOPE_IDENTITY();", new SqlParameter("name", name)).FirstOrDefault()));
}
}
public override bool Delete(int id)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.ExecuteSqlCommand("DELETE FROM [Shop] WHERE ID = @id", new SqlParameter("id", id)) > 0);
}
}
public override IShop Get(int id)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <Shop>("SELECT FROM [Shop] WHERE ID = @id", new SqlParameter("id", id)).FirstOrDefault());
}
}
public override bool Update(int sellerId, int districtId, bool isPrimary)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.ExecuteSqlCommand("UpdateDistrictSeller @sellerId, @isPrimary, @districtId", new SqlParameter("districtId", districtId), new SqlParameter("sellerId", sellerId), new SqlParameter("isPrimary", isPrimary)) > 0);
}
}
public override bool Insert(int sellerId, int districtId, bool isPrimary)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.ExecuteSqlCommand("INSERT INTO [DistrictSeller] (DistrictId, SellerId, IsPrimary) VALUES(@DistrictId, @SellerId, @IsPrimary);", new SqlParameter("DistrictId", districtId), new SqlParameter("SellerId", sellerId), new SqlParameter("IsPrimary", isPrimary)) > 0);
}
}
public override bool Delete(int sellerId, int districtId)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.ExecuteSqlCommand("DeleteDistrictSeller @sellerId, @districtId", new SqlParameter("districtId", districtId), new SqlParameter("sellerId", sellerId)) > 0);
}
}
public override IList <ISeller> GetByNotInDistrict(int districtId)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <Seller>("SELECT S.Id, S.name, S.phone, s.email, S.Created, S.Updated, CASE WHEN ds.IsPrimary IS NULL THEN CAST(1 AS BIT) ELSE ds.IsPrimary END AS IsPrimary FROM [Seller] S LEFT JOIN [DistrictSeller] ds ON S.Id = ds.SellerId WHERE (ds.DistrictId <> @DistrictId OR ds.DistrictId IS NULL) AND S.ID NOT IN (SELECT S.Id FROM [Seller] S LEFT JOIN [DistrictSeller] ds ON S.Id = ds.SellerId WHERE ds.DistrictId = @DistrictId)", new SqlParameter("DistrictId", districtId)).ToList <ISeller>());
}
}
public override IList <ISeller> GetByDistrict(int districtId)
{
using (var db = new TestCompanyEntities())
{
return(db.Database.SqlQuery <Seller>("SELECT S.Id, S.name, S.phone, s.email, S.Created, S.Updated, ds.IsPrimary FROM [Seller] S INNER JOIN [DistrictSeller] ds ON S.Id = ds.SellerId WHERE ds.DistrictId = @DistrictId", new SqlParameter("DistrictId", districtId)).ToList <ISeller>());
}
}
