查询:
static IQueryable<Outlet> Query(string Region, string DC, string CustomerCode, string KA, string OutletCode, string NestleOutletCode)
{
var query = (from o in DB.Current.Outlet
where
(string.IsNullOrEmpty(Region) || o.Region.Contains(Region)) &&
(string.IsNullOrEmpty(DC) || o.DC.Contains(DC)) &&
(string.IsNullOrEmpty(CustomerCode) || o.CustomerCode.Contains(CustomerCode)) &&
(string.IsNullOrEmpty(KA) || o.KA.Contains(KA)) &&
(string.IsNullOrEmpty(OutletCode) || o.OutletCode.Contains(OutletCode)) &&
(string.IsNullOrEmpty(NestleOutletCode) || o.NestleOutletCode.Contains(NestleOutletCode))
select o);
return query;
}
删除1
public static bool DelAll(string Region, string DC, string CustomerCode, string KA, string OutletCode, string NestleOutletCode)
{
var delsql = new StringBuilder();
using (var db = new NestleImportEntities())
{
delsql.Append($" DELETE FROM Outlet where 1=1 ");
if (Region != null && Region != "") {
delsql.Append("and Region like '%").Append(Region).Append($"%' ");
}
if (DC != null && DC != "") {
delsql.Append("and DC like '%").Append(DC).Append($"%' ");
}
if (CustomerCode != null && CustomerCode != "") {
delsql.Append("and CustomerCode like '%").Append(CustomerCode).Append($"%' ");
}
if (KA != null && KA != "") {
delsql.Append("and KA like '%").Append(KA).Append($"%' ");
}
if (OutletCode != null && OutletCode != "") {
delsql.Append("and OutletCode like '%").Append(OutletCode).Append($"%' ");
}
if (NestleOutletCode != null && NestleOutletCode != "") {
delsql.Append("and NestleOutletCode like '%").Append(NestleOutletCode).Append($"%' ");
}
db.Database.ExecuteSqlCommand(delsql.ToString());
}
return true;
}
删除2
public static bool Del(int ID)
{
Outlet outlet = DB.Current.Outlet.FirstOrDefault(o => o.ID == ID);
DB.Current.Database.CommandTimeout = 120;
DB.Current.Outlet.Remove(outlet);
DB.Current.SaveChanges();
return true;
}
插入更新
private void UpdateOrInsertGroup(List<ExcelData> allList)
{
using (var db = new NestleImportEntities())
{
db.Database.CommandTimeout = 420;
var wheresql = new StringBuilder();
wheresql.Append($" 1 = 1 ");
foreach (var f in UpdateWhereField)
{
wheresql.Append($"and t.[{f}] = s.[{f}] ");
}
try
{
var mergeSql = new StringBuilder();
mergeSql.Append($"MERGE INTO Product_Detail AS t USING ( ");
mergeSql.Append($" SELECT DISTINCT ");
mergeSql.Append($" YearMonth, ");
mergeSql.Append($" SKU_Code, ");
mergeSql.Append($" Unit_Quantity, ");
mergeSql.Append($" RRP_EA, ");
mergeSql.Append($" RRP_Cases, ");
mergeSql.Append($" SKU_Group, ");
mergeSql.Append($" Key_SKU, ");
mergeSql.Append($" insertUser, ");
mergeSql.Append($" CreateTime, ");
mergeSql.Append($" UpdateTime, ");
mergeSql.Append($" batchNo ");
mergeSql.Append($" FROM ");
mergeSql.Append($" Product_Detail_temp WHERE insertUser = '").Append(loginedUser.User.ID).Append($"'");
mergeSql.Append($") AS s ON ").Append(wheresql);
mergeSql.Append($"WHEN matched THEN ");
mergeSql.Append($" UPDATE ");
mergeSql.Append($"SET t.YearMonth = s.YearMonth, ");
mergeSql.Append($"t.SKU_Code = s.SKU_Code, ");
mergeSql.Append($"t.Unit_Quantity = t.Unit_Quantity, ");
mergeSql.Append($"t.RRP_EA = t.RRP_EA, ");
mergeSql.Append($"t.RRP_Cases = t.RRP_Cases, ");
mergeSql.Append($"t.SKU_Group = s.SKU_Group, ");
mergeSql.Append($"t.Key_SKU = s.Key_SKU, ");
mergeSql.Append($"t.insertUser = s.insertUser, ");
mergeSql.Append($"t.CreateTime = t.CreateTime, ");
mergeSql.Append($"t.UpdateTime = s.UpdateTime ");
mergeSql.Append($"WHEN NOT matched THEN ");
mergeSql.Append($" INSERT ( ");
mergeSql.Append($" YearMonth, ");
mergeSql.Append($"SKU_Code, ");
mergeSql.Append($"Unit_Quantity, ");
mergeSql.Append($"RRP_EA, ");
mergeSql.Append($"RRP_Cases, ");
mergeSql.Append($"SKU_Group, ");
mergeSql.Append($"Key_SKU, ");
mergeSql.Append($"insertUser, ");
mergeSql.Append($"CreateTime, ");
mergeSql.Append($"UpdateTime ");
mergeSql.Append($" ) ");
mergeSql.Append($"VALUES ");
mergeSql.Append($" ( ");
mergeSql.Append($" s.YearMonth, ");
mergeSql.Append($" s.SKU_Code, ");
mergeSql.Append($" s.Unit_Quantity, ");
mergeSql.Append($" s.RRP_EA, ");
mergeSql.Append($" s.RRP_Cases, ");
mergeSql.Append($" s.SKU_Group, ");
mergeSql.Append($" s.Key_SKU, ");
mergeSql.Append($" s.insertUser, ");
mergeSql.Append($" s.CreateTime, ");
mergeSql.Append($" s.UpdateTime ");
mergeSql.Append($" ) ; ");
Logger.Current.Info("merg》》》》》:" + mergeSql.ToString());
db.Database.ExecuteSqlCommand(mergeSql.ToString());
}
catch (Exception ex)
{
Logger.Current.Info("上传失败!!!!!!!" + ex.Message);
throw new Exception("发送信息异常,原因:" + ex.Message + ex.InnerException);
}
finally
{
//释放资源
//scope.Dispose();
}
///
}
}