protected void Page_Load(object sender, EventArgs e)
{
string connStr = "server=.;uid=sa;password=sasa;database=nfd";
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
db.BeginTran();
db.Sqlable.IsNoLock = true;
try
{
var sql = db.Sqlable.MappingTable<CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*");
var dt = db.GetDataTable(sql);
var id = db.Insert(new test() { name = "哈哈" + DateTime.Now });
var del = db.Delete<test>(21);
var update = db.Update<test>(new { name = "5555" + DateTime.Now }, new { id=1 });
}
catch (Exception)
{
db.RollbackTran();
}
}
;
//var xx = SqlTool.CreateMappingTable(20);
Console.Read();
}
public void Init()
{
Console.WriteLine("启动EnumDemo.Init");
using (SqlSugarClient db = SugarDao.GetInstance())
{
var stuList = db.Queryable <Student>().ToList();
db.Insert <Student>(new Student()
{
sch_id = SchoolEnum.北大青鸟
});
db.Update <Student>(new Student()
{
sch_id = SchoolEnum.it清华, id = 11
});
var stuList2 = db.Queryable <Student>().Where(it => it.sch_id == SchoolEnum.全智).ToList();
}
}
/// <summary>
/// 更新是否查看推送
/// </summary>
/// <param name="pushVguid"></param>
/// <param name="userID"></param>
/// <returns></returns>
public bool UpdateIsRead(string pushVguid, string userID)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
bool isRead = false;
try
{
Guid pushVGUID = Guid.Parse(pushVguid);
isRead = dbMsSql.Update <Business_WeChatPushDetail_Information>(new { ISRead = "1" }, i => i.Business_WeChatPushVguid == pushVGUID && i.PushObject == userID);
}
catch (Exception exp)
{
LogHelper.WriteLog("更新是否查看推送状态：" + pushVguid + "/" + exp.ToString());
}
return(isRead);
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="vguid"></param>
/// <param name="newPwd"></param>
/// <param name="oldPwd"></param>
/// <param name="name"></param>
/// <param name="msg"></param>
/// <returns></returns>
public bool ChangePassWord(Guid vguid, string newPwd, string oldPwd, string name, out string msg)
{
msg = "";
bool result = false;
using (SqlSugarClient sqlSugar = SugarDao_MsSql.GetInstance())
{
var list = sqlSugar.Queryable <Sys_User>().Where(i => i.Vguid == vguid).ToList();
if (list[0].Password != oldPwd)
{
msg = "旧密码不正确！";
return(false);
}
result = sqlSugar.Update <Sys_User>(new { Password = newPwd, ChangeDate = DateTime.Now, ChangeUser = name }, i => i.Vguid == vguid);
_logLogic.SaveLog(4, 0, Common.CurrentUser.GetCurrentUser().LoginName, "更新密码", oldPwd + "->" + newPwd);
return(result);
}
}
public bool Update(BoxCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_Box model = new R_Box()
{
Name = req.Name,
Description = req.Description,
Id = req.Id,
R_Restaurant_Id = req.Restaurant,
R_Area_Id = req.RestaurantArea
};
result = db.Update(model);
return(result);
}
}
public bool Update(MarketCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_Market model = new R_Market()
{
Name = req.Name,
Description = req.Description,
Id = req.Id,
R_Restaurant_Id = req.Restaurant,
StartTime = req.StartTime,
EndTime = req.EndTime
};
result = db.Update(model);
return(result);
}
}
public bool Update(ExtendCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_ProjectExtend model = new R_ProjectExtend()
{
Name = req.Name,
Description = req.Description,
Id = req.Id,
CyxmKzType = req.CyxmKzType,
Price = req.Price,
Unit = req.Unit,
R_ProjectExtendType_Id = req.ExtendType
};
result = db.Update(model);
return(result);
}
}
public bool Update(PrinterDTO dto)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = false;
result = db.Update <Printer>(
new
{
Name = dto.Name,
Code = dto.Code,
Remark = dto.Remark,
PcName = dto.PcName,
PrintPort = dto.PrintPort,
IpAddress = dto.IpAddress,
IsDelete = dto.IsDelete
}, x => x.Id == dto.Id);
return(result);
}
}
/// <summary>
/// 上传用户头像
/// </summary>
/// <param name="url"></param>
/// <param name="exerciseVguid"></param>
/// <param name="personVguid"></param>
/// <returns></returns>
public bool UpLoadImg(string url, string exerciseVguid, string personVguid)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
Guid mainExerciseVguid = Guid.Parse(exerciseVguid);
Guid personalVguid = Guid.Parse(personVguid);
Business_ExercisesAnswer_Information exerciseAnswerModel = new Business_ExercisesAnswer_Information();
exerciseAnswerModel = dbMsSql.Queryable <Business_ExercisesAnswer_Information>().Where(i => i.BusinessExercisesVguid == mainExerciseVguid && i.BusinessPersonnelVguid == personalVguid).SingleOrDefault();
if (exerciseAnswerModel != null)
{
if (string.IsNullOrEmpty(exerciseAnswerModel.PicturePath))
{
result = dbMsSql.Update <Business_ExercisesAnswer_Information>(new { PicturePath = url }, i => i.BusinessExercisesVguid == mainExerciseVguid && i.BusinessPersonnelVguid == personalVguid);
}
}
else //如果Business_ExercisesAnswer_Information没有数据要新增一条
{
Business_ExercisesAnswer_Information mainExercise = new Business_ExercisesAnswer_Information();
mainExercise.Vguid = Guid.NewGuid();
mainExercise.BusinessExercisesVguid = mainExerciseVguid;
mainExercise.BusinessPersonnelVguid = personalVguid;
mainExercise.ChangeDate = DateTime.Now;
mainExercise.CreatedDate = DateTime.Now;
mainExercise.PicturePath = url;
mainExercise.SolveNumber = 0;
result = dbMsSql.Insert <Business_ExercisesAnswer_Information>(mainExercise, false) != DBNull.Value;
}
//存入操作日志表
string logData = JsonHelper.ModelToJson <Business_ExercisesAnswer_Information>(exerciseAnswerModel);
_ll.SaveLog(13, 34, personVguid, "用户图片", logData);
}
catch (Exception ex)
{
LogHelper.WriteLog(ex.Message);
_ll.SaveLog(5, 34, personVguid, "用户图片", ex.Message);
}
return(result);
}
}
/// <summary>
/// 更新用户是否阅读推送状态
/// </summary>
/// <param name="userID">用户微信UserID</param>
/// <param name="pushVguid">推送内容Vguid</param>
/// <returns></returns>
public bool UpdateIsRead(string userID, Guid pushVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
_dbMsSql.BeginTran();
result = _dbMsSql.Update <Business_WeChatPushDetail_Information>(new { ISRead = "1" }, i => i.PushObject == userID && i.Business_WeChatPushVguid == pushVguid);
_dbMsSql.CommitTran();
}
catch (Exception exp)
{
_dbMsSql.RollbackTran();
LogHelper.WriteLog(exp.Message + "/n" + exp.ToString() + "/n" + exp.StackTrace);
}
return(result);
}
}
public bool Unlock(CheckOutBillDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
var tableCount = db.Queryable <R_OrderTable>()
.Where(p => req.OrderTableIds.Contains(p.R_Table_Id) && p.R_Order_Id == req.OrderId && p.IsCheckOut == false).Count();
if (tableCount == req.OrderTableIds.Count())
{
result = db.Update <R_OrderTable>(new { IsLock = false },
p => p.R_Order_Id == req.OrderId && req.OrderTableIds.Contains(p.R_Table_Id));
}
else
{
result = false;
}
return(result);
}
}
//进行标记啊啥的,不需要拆66字了
private void GetFinalList(List <Sev_SendDateDetail> HttpSendList)
{
try
{
for (int x = 0; x < HttpSendList.Count(); x++)
{
Sev_FinalSendDetail senditem = new Sev_FinalSendDetail();
senditem.F_Id = Guid.NewGuid().ToString();
senditem.F_SendId = HttpSendList[x].F_Id;
senditem.F_Level = HttpSendList[x].F_Level;
senditem.F_DealState = 0; //默认未发送
senditem.F_Report = "0"; //默认未收到回复报告
senditem.F_CreateTime = DateTime.Now;
senditem.F_SendTime = HttpSendList[x].F_SendTime;
senditem.F_Reissue = 0; //初始化补发次数为0；
senditem.F_Response = 0; //默认未收到应答
senditem.F_SmsContent = HttpSendList[x].F_SmsContent; //内容直接复制拆大段表的内容，不需要拆分
senditem.F_CreatorUserId = HttpSendList[x].F_CreatorUserId;
senditem.F_IsCashBack = false;
//decimal SmsCount = Math.Ceiling(Convert.ToDecimal(item.F_SmsContent.Length) / 66);//向上取整获取发送条数
//for (int i = 0; i < SmsCount; i++)
//{
// senditem.F_Id = Guid.NewGuid().ToString();
// senditem.F_SmsOrderNo = i + 1;
// if ((i + 1) * 66 < item.F_SmsContent.Length)
// senditem.F_SmsContent = item.F_SmsContent.Substring(i * 66, 66);//进行66个字的拆分
// else
// senditem.F_SmsContent = item.F_SmsContent.Substring(i * 66, item.F_SmsContent.Length-66);//避免超过长度
// senditem.F_SmsCount = senditem.F_SmsContent.Length;
// FinalList.Add(senditem);//加入队列
//}
using (SqlSugarClient db = new SqlSugarClient(connStr)) //开启数据库连接
{
db.Insert(senditem); //单个插入，客户要求
db.Update <Sev_SendDateDetail>(new { F_DealState = 1 }, it => it.F_Id == HttpSendList[x].F_Id); //更新状态为已拆分状态
}
}
}
catch (Exception ex)
{ LogHelper.WriteLog("发送任务出现异常：" + ex); }
}
/// <summary>
/// 删除用户信息
/// </summary>
/// <param name="vguid"></param>
/// <returns></returns>
public bool DeleteUserInfo(string vguid)
{
using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance())
{
bool result = false;
Guid Vguid = Guid.Parse(vguid);
Business_Personnel_Information userInfo = dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == Vguid).SingleOrDefault();
try
{
dbMsSql.BeginTran();
string weChatJson = JsonHelper.ModelToJson(userInfo);
//从微信中删除
string accessToken = Common.WeChatPush.WeChatTools.GetAccessoken();
string focusResult = Common.WeChatPush.WeChatTools.DisableWeChatData(accessToken, userInfo.UserID);//禁用微信通讯录人员
U_FocusResult resultMsg = new U_FocusResult();
resultMsg = JsonHelper.JsonToModel <U_FocusResult>(focusResult);
result = dbMsSql.Update <Business_Personnel_Information>(new { ApprovalStatus = 4 }, i => i.Vguid == Vguid);
if (resultMsg.errcode == 0)//删除成功
{
result = true;
}
else
{
LogHelper.WriteLog("人员:" + userInfo.UserID + "离职失败：错误码为-" + resultMsg.errcode + ",错误消息为_" + resultMsg.errmsg);
result = false;
}
//存入操作日志表
_ll.SaveLog(2, 5, CurrentUser.GetCurrentUser().LoginName, userInfo.Name, weChatJson);
dbMsSql.CommitTran();
return(result);
}
catch (Exception ex)
{
dbMsSql.RollbackTran();
LogHelper.WriteLog("人员:" + userInfo.UserID + "离职异常：异常信息为_" + ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace);
}
return(result);
}
}
/// <summary>
/// 保存角色信息
/// </summary>
/// <param name="roleTypeModel"></param>
/// <param name="permissionList"></param>
/// <param name="isEdit"></param>
/// <returns></returns>
public bool SaveRole(Sys_Role roleTypeModel, List <Sys_Role_Module> permissionList, bool isEdit)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
_dbMsSql.BeginTran();
if (isEdit)//编辑
{
var model = new
{
ChangeDate = roleTypeModel.ChangeDate,
ChangeUser = roleTypeModel.ChangeUser,
Role = roleTypeModel.Role,
Description = roleTypeModel.Description
};
string logData = JsonHelper.ModelToJson <Sys_Role>(roleTypeModel);
_logLogic.SaveLog(4, 32, Common.CurrentUser.GetCurrentUser().LoginName, roleTypeModel.Role, logData);
result = _dbMsSql.Update <Sys_Role>(model, i => i.Vguid == roleTypeModel.Vguid);
}
else//新增
{
string logData = JsonHelper.ModelToJson <Sys_Role>(roleTypeModel);
_logLogic.SaveLog(1, 31, Common.CurrentUser.GetCurrentUser().LoginName, roleTypeModel.Role, logData);
result = _dbMsSql.Insert <Sys_Role>(roleTypeModel, false) != DBNull.Value;
}
_dbMsSql.Delete <Sys_Role_Module>(i => i.RoleVGUID == roleTypeModel.Vguid);
_dbMsSql.InsertRange(permissionList, false);
_dbMsSql.CommitTran();
}
catch (Exception ex)
{
_dbMsSql.RollbackTran();
Common.LogHelper.LogHelper.WriteLog(ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace);
}
finally
{
}
return(result);
}
}
internal void SaveAvatar(SqlSugarClient db, UserInfo user)
{
//将远程图片下载到本地
if (user.Avatar.IsValuable() && user.Avatar.Contains("http://"))
{
string path = "/_theme/img/avatar{0}.jpg".ToFormat(user.Id);
string savePath = FileSugar.GetMapPath("~" + path);
WebClient my = new WebClient();
byte[] mybyte;
mybyte = my.DownloadData(user.Avatar.Replace(@"/30", @"/100"));
using (MemoryStream ms = new MemoryStream(mybyte))
{
System.Drawing.Image img;
img = System.Drawing.Image.FromStream(ms);
img.Save(savePath, ImageFormat.Jpeg);//保存
}
user.Avatar = path;
db.Update <UserInfo>(new { Avatar = user.Avatar }, it => it.Id == user.Id);
}
}
public JsonResult Edit(GridTable gt)
{
using (SqlSugarClient db = SugarDao.GetInstance())
{
ActionResultModel <string> model = new ActionResultModel <string>();
string message = string.Empty;
var isValid = ValidationSugar.PostValidation("validate_key_grid_index", out message);
if (isValid)//后台验证数据完整性
{
model.isSuccess = db.Update <GridTable>(gt, it => it.id == gt.id);
model.responseInfo = model.isSuccess ? "编辑成功" : "编辑失败";
}
else
{
model.isSuccess = false;
model.responseInfo = message;
}
return(Json(model));
}
}
public bool Update(CategoryCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_Category model = new R_Category()
{
Id = req.Id,
Name = req.Name,
Description = req.Description,
DiscountRate = req.DiscountRate,
PId = req.Pid,
IsDiscount = req.IsDiscount,
Sorted = req.Sorted
};
result = db.Update(model);
return(result);
}
}
private void UpdateMyRights(SqlSugarClient _db, Business_EquityAllocation equity)
{
if (equity.ValidType == "周期")
{
getPeriodDate(equity, equity.Period);
}
var myRights = _db.Queryable <Business_MyRights>().Where(x => x.VGUID == equity.VGUID).FirstOrDefault();
var data = new
{
RightsName = equity.RightsName,
Description = equity.Description,
Type = equity.Type,
StartValidity = equity.StartValidity,
EndValidity = equity.EndValidity,
ChangeUser = equity.ChangeUser,
ChangeDate = equity.ChangeDate
};
_db.Update <Business_MyRights>(data, i => i.VGUID == equity.VGUID);
}
public void UpdateBusiness_VehicleRepairComplaints(Business_VehicleRepairComplaints vehicleRepairComplaints)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
if (vehicleRepairComplaints.VGUID == Guid.Empty)
{
vehicleRepairComplaints.VGUID = Guid.NewGuid();
vehicleRepairComplaints.CreateDate = DateTime.Now;
vehicleRepairComplaints.ChangeDate = DateTime.Now;
vehicleRepairComplaints.ChangeUser = vehicleRepairComplaints.CreateUser;
_dbMsSql.Insert(vehicleRepairComplaints);
}
else
{
vehicleRepairComplaints.ReflectDate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
vehicleRepairComplaints.ChangeDate = DateTime.Now;
vehicleRepairComplaints.ChangeUser = vehicleRepairComplaints.CreateUser;
_dbMsSql.Update(vehicleRepairComplaints);
}
}
}
public bool Delete(int id)
{
using (var db = new SqlSugarClient(Connection))
{
bool res = false;
try
{
var model = db.Queryable <R_PayMethod>().InSingle(id);
if (model.IsSystem)
{
throw new Exception("该支付项为系统配置，不能删除");
}
res = db.Update <R_PayMethod>(new { IsDelete = true }, p => p.Id == id || p.Pid == id);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(res);
}
}
public WarmUp()
{
Console.WriteLine("开启预热");
//预热处理
for (int i = 0; i < 2; i++)
{
using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
{
var list = conn.QueryFirst <Test>("select top 1 * from Test");
conn.Update(new Test());
}
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.Queryable <Test>().Where(it => 1 == 2).ToList();
conn.Update(new Test());
}
}
Console.WriteLine("预热完毕");
Console.WriteLine("----------------比赛开始-------------------");
}
public WarmUp()
{
Console.WriteLine("开启预热");
//预热处理
for (int i = 0; i < 2; i++)
{
using (SqlConnection conn = new SqlConnection(PubConst.connectionString))
{
var list = conn.QueryFirst<Test>("select top 1 * from Test");
conn.Update(new Test());
}
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.Queryable<Test>().Where(it => 1 == 2).ToList();
conn.Update(new Test());
}
}
Console.WriteLine("预热完毕");
Console.WriteLine("----------------比赛开始-------------------");
}
public int Update(PackageCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
int result = 0;
var baseModel = db.Queryable <R_Package>()
.First(p => p.Id == req.Id);
R_Package model = new R_Package()
{
Id = req.Id,
Name = req.Name,
IsOnSale = req.IsOnSale,
Property = req.IsDiscount + req.IsChangePrice + req.IsGive,
Describe = req.Describe,
CostPrice = baseModel.CostPrice,
Price = baseModel.Price,
R_Category_Id = req.R_Category_Id
};
result = db.Update(model) ? req.Id : 0;
return(result);
}
}
/// <summary>
/// 逻辑删除
/// </summary>
/// <param name="id">包厢id</param>
/// <returns></returns>
public bool IsDelete(int id)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
//包厢
db.Update <R_Box>(new { IsDelete = true }, x => x.Id == id);
//删除包厢台号关系表
db.Delete <R_BoxTable>(p => p.R_Box_Id == id);
db.CommitTran();
}
catch (Exception)
{
db.RollbackTran();
result = false;
}
return(result);
}
}
public bool SaveBusiness_RideCheckFeedbackItem(string user, Guid rideCheckFeedbackVguid, int feedbackNumber, string answer1, string answer2, string answer3, string answer4, string answer5, string answer6, string answer7)
{
Business_RideCheckFeedback_Item item = new Business_RideCheckFeedback_Item();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
item = _dbMsSql.Queryable <Business_RideCheckFeedback_Item>().Where(c => c.RideCheckFeedbackVGUID == rideCheckFeedbackVguid && c.FeedbackNumber == feedbackNumber).FirstOrDefault();
if (item == null)
{
item = new Business_RideCheckFeedback_Item();
item.VGUID = Guid.NewGuid();
item.RideCheckFeedbackVGUID = rideCheckFeedbackVguid;
item.ChangeUser = item.CreateUser = user;
item.ChangeDate = item.CreateDate = DateTime.Now;
item.FeedbackNumber = feedbackNumber;
item.FeedbackAnswer1 = answer1;
item.FeedbackAnswer2 = answer2;
item.FeedbackAnswer3 = answer3;
item.FeedbackAnswer4 = answer4;
item.FeedbackAnswer5 = answer5;
item.FeedbackAnswer6 = answer6;
item.FeedbackAnswer7 = answer7;
_dbMsSql.Insert(item);
}
else
{
item.FeedbackAnswer1 = answer1;
item.FeedbackAnswer2 = answer2;
item.FeedbackAnswer3 = answer3;
item.FeedbackAnswer4 = answer4;
item.FeedbackAnswer5 = answer5;
item.FeedbackAnswer6 = answer6;
item.FeedbackAnswer7 = answer7;
_dbMsSql.Update(item);
}
}
return(true);
}
/// <summary>
/// 保存配置文件内容
/// </summary>
/// <param name="configModel">配置表实体</param>
/// <returns></returns>
public bool SaveConfig(Master_Configuration configModel)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
result = _dbMsSql.Update <Master_Configuration>(new { ConfigValue = configModel.ConfigValue }, i => i.ID == configModel.ID);
//写入日志
Master_Configuration configuration = new Master_Configuration();
configuration = _dbMsSql.Queryable <Master_Configuration>().Where(i => i.ID == configModel.ID).SingleOrDefault();
string logData = Common.JsonHelper.ModelToJson(configuration);
_logLogic.SaveLog(4, 35, Common.CurrentUser.GetCurrentUser().LoginName, configuration.ConfigDescription, logData);
}
catch (Exception exp)
{
Common.LogHelper.LogHelper.WriteLog(exp.Message + "/n" + exp.ToString() + "/n" + exp.StackTrace);
}
return(result);
}
}
public bool Update(StallsCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_Stall model = new R_Stall()
{
Id = req.Id,
Name = req.Name,
Description = req.Description
};
//result = db.Update(model);
result = db.Update <R_Stall>(
new
{
Name = req.Name,
Description = req.Description,
Print_Id = req.Print_Id,
R_Company_Id = req.R_Company_Id,
}, x => x.Id == req.Id);
return(result);
}
}
/// <summary>
/// 逻辑删除
/// </summary>
/// <param name="id">台号id</param>
/// <returns></returns>
public bool IsDelete(int id, out string msg)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
msg = "";
//先查询是否此台号还存在未结账的订单
var orderTableIds = db.Queryable <R_OrderTable>()
.JoinTable <R_Order>((s1, s2) => s1.R_Order_Id == s2.Id)
.Where(" s1.R_Table_Id = " + id + " and s2.CyddStatus not in (6,7) and s2.IsDelete=0").ToList();
if (orderTableIds != null && orderTableIds.Count > 0)
{
result = false;
msg = "此台号还存在未结账的订单,删除失败！";
}
else
{
//删除台号
result = db.Update <R_Table>(new { IsDelete = true }, x => x.Id == id);
}
return(result);
}
}
public bool Edit(List <CustomConfigDTO> req)
{
using (var db = new SqlSugarClient(Connection))
{
var res = true;
try
{
if (req.Any())
{
foreach (var item in req)
{
db.Update <R_CustomConfig>(new { Sorted = item.Sorted, ModuleName = item.ModuleName, FunctionName = item.FunctionName, Colour = item.Colour }, p => p.Id == item.Id);
}
}
}
catch (Exception e)
{
res = false;
throw e;
}
return(res);
}
}
public bool Edit(OrderDetailCauseDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = false;
try
{
var model = Mapper.Map <OrderDetailCauseDTO, R_OrderDetailCause>(req);
if (req.Id > 0)
{
result = db.Update(model);
}
else
{
result = db.Insert(model) != null ? true : false;
}
}
catch (Exception e)
{
throw e;
}
return(result);
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
string aaa = null;
var xx = db.Queryable <School>().Where(it => true).ToList();
var xx2 = db.Queryable <Student>().Where(it => it.isOk == false).ToList();
var sl2 = db.Sqlable().Form <Student>("s").SelectToList <Student>("id");
var sl = db.Sqlable().Form <Student>("s").SelectToList <Student>("*");
db.Queryable <Student>().In("id", "1", "2", "3").ToList();
db.Queryable <Student>().In("id", new string[] { "1", "2", "3" }).ToList();
db.Queryable <Student>().In("id", new List <string> {
"1", "2", "3"
}).ToList();
var array = new string[] { "1", "2", "3" };
db.Queryable <Student>().Where(it => array.Contains(it.name));
db.Delete <Student, int>(1, 2);
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable <Student>().ToList();
var stud = new Student()
{
id = db.GetInt("select top 1 id from Student")
};
//查询单条
var single = db.Queryable <Student>().Single(c => c.id == stud.id);
//取10-20条
var page1 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable <Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable <Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable <Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable <Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable <Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable <Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable <Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable <Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable <Student>().Any(c => c.id == 1);
int maxId = db.Queryable <Student>().Max <Student, int>("id");
int minId = db.Queryable <Student>().Where(c => c.id > 0).Min <Student, int>("id");
//---------Sqlable,创建多表查询---------//
//多表查询
List <School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList <School>("st.*", new { id = 1 });
//多表分页
List <School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList <School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery <Student>("select * from Student");
//获取id
var id = db.SqlQuery <int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List <School> sList = new List <School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update <School>(new { name = "蓝翔2" }, it => it.id == id);
db.Update <School, int>(new { name = "蓝翔2" }, 1, 3, 12);
db.Update <School, string>(new { name = "蓝翔2" }, new string[] { "1", "2" });
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update(new School {
id = id, name = "蓝翔2"
});
db.Update <School>(new School {
id = id, name = "蓝翔2"
}, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete <School, int>(10);//注意主键必需为实体类的第一个属性
db.Delete <School>(it => it.id > 100);
db.Delete <School, string>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList <Student>(sql);
db.GetSingle <Student>(sql + " where id=" + stud.id);
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
public bool Update(TableCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
R_Table model = db.Queryable <R_Table>().First(p => p.Id == req.Id);
model.Name = req.Name;
model.Describe = req.Description;
model.R_Restaurant_Id = req.RestaurantId;
model.ServerRate = req.ServerRate;
model.R_Area_Id = req.RestaurantArea;
model.SeatNum = req.SeatNum;
model.IsVirtual = req.IsVirtual;
model.Sorted = req.Sorted;
result = db.Update(model);
var cybxth = db.Queryable <R_BoxTable>()
.Where(p => p.R_Table_Id == model.Id)
.FirstOrDefault();
if (req.Box > 0)
{
if (cybxth != null)
{
if (cybxth.R_Box_Id != req.Box)
{
cybxth.R_Box_Id = req.Box;
db.Update <R_BoxTable>(cybxth);
}
}
else
{
db.Insert <R_BoxTable>(new R_BoxTable()
{
R_Table_Id = model.Id,
R_Box_Id = req.Box
});
}
}
else
{
if (cybxth != null)
{
db.Delete <R_BoxTable>(p => p.Id == cybxth.Id);
}
}
db.CommitTran();
}
catch (Exception)
{
db.RollbackTran();
result = false;
throw;
}
return(result);
}
}
private static void SqlSugar(int eachCount)
{
GC.Collect();//回收资源
System.Threading.Thread.Sleep(2000);//休息2秒
PerHelper.Execute(eachCount, "SqlSugar", () =>
{
using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))
{
var list = conn.Update(GetItem);
}
});
}
protected void Page_Load(object sender, EventArgs e)
{
//连接字符串
string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = 1 };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id=1");
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);//设置循环次数
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ef
using (WebTest.TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities())
{
pt.Execute(i =>
{
var datas = ef.InsertTests.Where(c => c.id == 1 || c.id > 1000);
foreach (var r in datas)
{
r.v1 = "new11";
r.v2 = "newv22";
r.v3 = "new33";
}
ef.SaveChanges();
}, m => { }, "EF4.0+sql05 Linq语法");
}
//ado.net
pt.Execute(i =>
{
db.ExecuteCommand(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id =@id1) Or (id >@id1000))",new SqlParameter[]{
new SqlParameter("@v1","new11"),
new SqlParameter("@v2","new22"),
new SqlParameter("@v3","new33"),
new SqlParameter("@id1","1"),
new SqlParameter("@id1000","1000")
});
}, m => { },"ado.net");
//dapper
var sqlConn=db.GetConnection();
pt.Execute(i =>
{
sqlConn.Execute(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id ='1') Or (id >'1000'))",
new { v1 = "newv11", v2 = "newv22", v3 = "newv33",id1=1,id1000=1000 } );
}, m => { }, "dapper");
//sqlSugar
pt.Execute(i =>
{
//更新10000次
db.Update<Models.InsertTest>(new { v1 = "newv11", v2 = "newv22", v3 = "newv33" }, it => it.id == 1 || it.id > 1000);
}, m => { }, "sqlSugar");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
