/// <summary>
/// 给阅读消息历史并且没有答过题的人重新推送习题
/// </summary>
/// <param name="businessPersonnelVguid"></param>
/// <param name="wechatMainVguid"></param>
/// <returns></returns>
public bool ReWechatPushExercise(string businessPersonnelVguid, string wechatMainVguid)
{
Guid mainVguid = Guid.Parse(wechatMainVguid);
Guid personnelVguid = Guid.Parse(businessPersonnelVguid);
bool result = false;
using (SqlSugarClient db = SugarDao_MsSql.GetInstance())
{
try
{
db.BeginTran();
var wechatMainModel = db.Queryable <Business_WeChatPush_Information>().Where(i => i.VGUID == mainVguid).SingleOrDefault();
var currentUser = db.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == personnelVguid).Select(i => i.UserID).SingleOrDefault();
DateTime?dateTime = null;
var wechatMain = new Business_WeChatPush_Information()
{
PushType = 1,
Title = wechatMainModel.Title,
MessageType = 4,
Timed = false,
TimedSendTime = null,
Important = wechatMainModel.PeriodOfValidity == null,
PeriodOfValidity = wechatMainModel.PeriodOfValidity == null ? dateTime : DateTime.Now.AddMonths(1),
Message = "从消息历史获取",
PushPeople = wechatMainModel.PushPeople,
Status = 3,
CreatedDate = DateTime.Now,
CreatedUser = wechatMainModel.CreatedUser,
VGUID = Guid.NewGuid(),
CoverImg = wechatMainModel.CoverImg,
CoverDescption = wechatMainModel.CoverDescption,
ExercisesVGUID = wechatMainModel.ExercisesVGUID,
History = "0",
Department_VGUID = Guid.Empty
};
var wechatDetail = new Business_WeChatPushDetail_Information()
{
Type = "1",
PushObject = currentUser,
CreatedUser = wechatMainModel.CreatedUser,
CreatedDate = DateTime.Now,
Vguid = Guid.NewGuid(),
Business_WeChatPushVguid = wechatMain.VGUID,
ISRead = "0",
};
string logdata = Extend.ModelToJson(wechatMain);
_ll.SaveLog(10, 34, currentUser, "从消息历史获取习题", logdata);
db.Insert(wechatDetail, false);
db.Insert(wechatMain, false);
db.CommitTran();
result = true;
}
catch (Exception ex)
{
db.RollbackTran();
LogHelper.WriteLog(ex.ToString());
}
}
return(result);
}
/// <summary>
/// 添加一条记录
/// </summary>
/// <param name="bookid"></param>
/// <param name="booktitle"></param>
public static void AddBook(string bookid, string booktitle)
{
var ret = db.Insert(new t_book()
{
bookid = bookid, booktitle = booktitle
});
}
protected void Page_Load(object sender, EventArgs e)
{
//设置序列
OracleConfig.SequenceMapping = new List <PubModel.SequenceModel>()
{
new PubModel.SequenceModel()
{
ColumnName = "ID", TableName = "STUDENT", Value = "SEQ_D"
},
new PubModel.SequenceModel()
{
ColumnName = "ID", TableName = "SCHOOL", Value = "SEQ_D"
}
};
using (SqlSugarClient db = SugarDaoSerNum.GetInstance())//开启数据库连接
{
var obj = Convert.ToInt32(db.Insert <STUDENT>(new STUDENT()
{
}));
var name = db.Queryable <STUDENT>().Single(it => it.ID == obj).NAME;
var obj2 = Convert.ToInt32(db.Insert <SCHOOL>(new SCHOOL()
{
}));
var name2 = db.Queryable <SCHOOL>().Single(it => it.ID == obj2).NAME;
}
}
public Business_ScanData_Information Save(Guid vguid)
{
Business_ScanData_Information ScanData = new Business_ScanData_Information();
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
var list = _dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == vguid).SingleOrDefault();
var Organization = _dbMsSql.Queryable <Master_Organization>().Where(i => i.Vguid == list.OwnedFleet).SingleOrDefault();
if (list != null)
{
ScanData.MachineCode = "";
ScanData.SystemID = "";
ScanData.ID = list.IDNumber;
ScanData.Age = list.Age;
ScanData.Sex = list.Sex;
ScanData.JobNumber = list.JobNumber;
ScanData.ServiceNumber = list.ServiceNumber;
ScanData.OwnedFleet = Organization.OrganizationName.ToString();
ScanData.UserVguid = vguid;
ScanData.Vguid = Guid.NewGuid();
ScanData.LicensePlate = list.LicensePlate;
ScanData.Name = list.Name;
ScanData.ScanUser = list.UserID;
ScanData.PhoneNumber = list.PhoneNumber;
ScanData.ScanDate = DateTime.Now;
ScanData.CreatedDate = DateTime.Now;
ScanData.CreatedUser = list.Name;
bool result = false;
result = _dbMsSql.Insert(ScanData, false) != DBNull.Value;
}
if (list != null)
{
var history = new Business_ScanHistory_Information()
{
MachineCode = "",
SystemID = "",
ScanUser = list.UserID,
Data = "",
User = "",
ScanTime = DateTime.Now,
CreatedUser = list.Name,
CreatedDate = DateTime.Now,
Vguid = Guid.NewGuid()
};
_dbMsSql.Insert(history, false);
}
//存入操作日志表
string logData = JsonHelper.ModelToJson(ScanData);
_ll.SaveLog(1, 2, list.Name, ScanData.Name, logData);
return(ScanData);
}
}
public bool Create(DiscountCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
R_Discount model = new R_Discount()
{
R_Restaurant_Id = req.Restaurant,
R_Area_Id = req.Area,
R_Market_Id = req.Market,
IsEnable = req.IsEnable,
Name = req.Name,
StartDate = req.StartDate,
EndDate = req.EndDate,
R_Company_Id = req.CompanyId
};
var newId = db.Insert(model);
if (req.CyxmZkCp != null)
{
var list = req.CyxmZkCp.Where(p => p.Id == 0).ToList();
list.ForEach(p => p.R_Discount_Id = Convert.ToInt32(newId));
//插入之前先判断是否存在，只插入不存在的
foreach (var item in list)
{
//查询是否存在
List <R_DiscountCategory> data = db.Queryable <R_DiscountCategory>()
.Where(p => p.R_Discount_Id == Convert.ToInt32(newId))
.Where(p => p.R_Category_Id == item.R_Category_Id)
.ToList();
//插入不存在的
if (data.Count <= 0)
{
db.Insert(item);
}
}
//db.InsertRange<R_DiscountCategory>(list);
}
db.CommitTran();
}
catch (Exception)
{
db.RollbackTran();
}
return(result);
}
}
/// <summary>
/// 保存 page Content
/// </summary>
/// <param name="sp"></param>
/// <param name="userName"></param>
public void SavePageContent(SysBest_PageContent sp, string userName)
{
var id = sp.Id;
try
{
_db.BeginTran();
Expression <Func <SysBest_PageContent, bool> > whereExpress = it => it.AddressId == sp.AddressId && it.LanguageId == sp.LanguageId;
var isAny = _db.Queryable <SysBest_PageContent>().Any(whereExpress);
if (isAny)
{
var data = _db.Queryable <SysBest_PageContent>().Single(whereExpress);
data.Html = sp.Html;
data.layout = sp.layout;
data.ModifiedBy = userName;
data.ModifiedTime = DateTime.Now;
_db.Update(data);
id = data.Id;
}
else
{
sp.Sort = 0;
sp.Founder = userName;
sp.CreateTime = DateTime.Now;
id = _db.Insert(sp).TryToInt();
}
//表单中的所有控件html id
List <string> formHtmlIdList = GetHtmlIdListByPageContent(sp);
//数据库中存在的html
var dbIdList = _db.Queryable <SysBest_Element>().Where(it => it.AddressId == sp.AddressId && it.LanguageId == sp.LanguageId).ToList().Select(it => it.Html_Id).ToList();
//在数据中，但是不在表单里面的需要删除
var delHtmlIdList = dbIdList.Where(it => formHtmlIdList.Any(htmlId => htmlId == it).IsFalse()).ToList();//不存在content里面的元素需要删除
foreach (var it in delHtmlIdList)
{
_db.Delete <SysBest_Element>(m => m.Html_Id == it);
}
//插入备份
InsertElementItemBak(sp.AddressId, sp.LanguageId, _db);
//处理其它页面控件的HTMLID
distinctHtmlIdList(sp, formHtmlIdList);
_db.CommitTran();
}
catch (Exception ex)
{
_db.RollbackTran();
throw ex;
}
}
/// <summary>
/// 保存问卷信息(主信息、详细信息)
/// </summary>
/// <param name="questionMainModel"></param>
/// <param name="isEdit"></param>
/// <returns></returns>
public bool SaveQuestionMain(Business_Questionnaire questionMainModel, bool isEdit, List <Business_QuestionnaireDetail> questionDetailList)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
try
{
_dbMsSql.BeginTran();
if (isEdit)
{
//定义匿名类型model
var model = new
{
QuestionnaireName = questionMainModel.QuestionnaireName,
Description = questionMainModel.Description,
Remarks = questionMainModel.Remarks,
EffectiveDate = questionMainModel.EffectiveDate,
Status = questionMainModel.Status,
ChangeUser = questionMainModel.ChangeUser,
ChangeDate = questionMainModel.ChangeDate
};
result = _dbMsSql.Update <Business_Questionnaire>(model, i => i.Vguid == questionMainModel.Vguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(questionMainModel);
_ll.SaveLog(4, 54, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "主信息", logData);
}
else
{
result = _dbMsSql.Insert(questionMainModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(questionMainModel);
_ll.SaveLog(1, 53, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "主信息", logData);
}
_dbMsSql.Delete <Business_QuestionnaireDetail>(i => i.QuestionnaireVguid == questionMainModel.Vguid);
foreach (var item in questionDetailList)
{
_dbMsSql.Insert(item, false);
//存入操作日志表
string logData = JsonHelper.ModelToJson(item);
_ll.SaveLog(4, 55, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "问卷详细信息", logData);
}
_dbMsSql.CommitTran();
}
catch (Exception ex)
{
_dbMsSql.RollbackTran();
Common.LogHelper.LogHelper.WriteLog(ex.Message + "/n" + ex + "/n" + ex.StackTrace);
}
return(result);
}
}
//更新余额(返款),更新返款状态
private void UpdateBalance(decimal cost, int UserId, string F_Id)
{
OC_RechargeRecord RechargeModel = new OC_RechargeRecord();
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
RechargeModel.F_Id = Guid.NewGuid().ToString();
Models.OC_UserInfo usermodel = db.Queryable <Models.OC_UserInfo>().Single(t => t.F_UserId == UserId);
usermodel.F_Balance = usermodel.F_Balance + cost; //返款
db.Update(usermodel); //更新
db.Update <Sev_FinalSendDetail>(new { F_IsCashBack = true }, it => it.F_Id == F_Id); //详单状态变成已返款
RechargeModel.F_Account = usermodel.F_Account;
RechargeModel.F_PayTime = RechargeModel.F_CreatorTime = DateTime.Now;
RechargeModel.F_CreatorUserId = "9f2ec079-7d0f-4fe2-90ab-8b09a8302aba";//admin
RechargeModel.F_DeleteMark = false;
RechargeModel.F_EnabledMark = true;
RechargeModel.F_ManagerId = usermodel.F_ManagerId.ObjToInt();
RechargeModel.F_OperatorId = 0; //代表系统
RechargeModel.F_PayMode = 4; //支付方式，4，返款
RechargeModel.F_Price = RechargeModel.F_TrueCash = RechargeModel.F_ShowCash = cost; //价格，实际金额，显示金额
RechargeModel.F_RechargeOver = 1;
RechargeModel.F_RechargeStar = 1;
RechargeModel.F_ShowDescription = "自动返款";
RechargeModel.F_State = "已付款";
RechargeModel.F_TrueDescription = "自动返款";
RechargeModel.F_UserId = usermodel.F_UserId.ToString();;
db.Insert(RechargeModel);
}
}
public bool Create(ExtendCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
R_ProjectExtend model = new R_ProjectExtend()
{
Name = req.Name,
Description = req.Description,
CyxmKzType = req.CyxmKzType,
Price = req.Price,
Unit = req.Unit,
R_ProjectExtendType_Id = req.ExtendType,
R_Company_Id = req.R_Company_Id
};
if (db.Insert(model) == null)
{
result = false;
}
return(result);
}
}
public Business_RideCheckFeedback AddBusiness_RideCheckFeedback(string user)
{
Business_RideCheckFeedback rideCheckFeedback = new Business_RideCheckFeedback();
rideCheckFeedback.FeedbackCode = DateTime.Now.ToString("yyyyMMddHHmmss");
rideCheckFeedback.ChangeUser = rideCheckFeedback.CreateUser = user;
rideCheckFeedback.ChangeDate = rideCheckFeedback.CreateDate = DateTime.Now;
rideCheckFeedback.VGUID = Guid.NewGuid();
rideCheckFeedback.Status = "1";
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
_dbMsSql.DisableInsertColumns = new string[] { "RideCheckFeedback_Items", "RideCheckFeedback_Attachments" };
_dbMsSql.Insert(rideCheckFeedback);
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 1, DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm"), "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 2, "", "", "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 3, "", "", "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 4, "", "", "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 5, "", "", "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 6, "", "", "", "", "", "", "");
SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 7, "", "", "", "", "", "", "");
rideCheckFeedback.RideCheckFeedback_Items = _dbMsSql.Queryable <Business_RideCheckFeedback_Item>().Where(c => c.RideCheckFeedbackVGUID == rideCheckFeedback.VGUID).OrderBy(c => c.FeedbackNumber).ToList();
}
return(rideCheckFeedback);
}
protected void Page_Load(object sender, EventArgs e)
{
//设置序列,程序起动时设置一次便可
OracleConfig.SequenceMapping = new List <PubModel.SequenceModel>()
{
new PubModel.SequenceModel()
{
ColumnName = "ID", TableName = "STUDENT", Value = "SEQ_D"
}
};
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
STUDENT s = new STUDENT()
{
NAME = "张" + new Random().Next(1, int.MaxValue)
};
db.Insert(s); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)
List <STUDENT> list = new List <STUDENT>()
{
new STUDENT()
{
NAME = "张" + new Random().Next(1, int.MaxValue)
},
new STUDENT()
{
NAME = "张" + new Random().Next(1, int.MaxValue)
}
};
db.InsertRange(list); //批量插入
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="entity">实体对象</param>
/// <param name="isIdentity">是否包含主键</param>
/// <returns></returns>
public object Insert(T entity, bool isIdentity = true)
{
using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance())
{
return(dbClient.Insert <T>(entity, isIdentity));
}
}
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 bool Add(Sys_Role obj, List <Sys_RoleAuthorize> list, bool isadd)
{
using (SqlSugarClient db = SqlSugarDao.GetInstance())//开启数据库连接
{
bool i = false;
db.CommandTimeOut = 30000;//设置超时时间
try
{
db.BeginTran();//开启事务
if (!isadd)
{
db.Delete <Sys_RoleAuthorize>("F_ObjectId=@F_id", new { F_id = obj.F_Id });
string[] notstr = { "F_CreatorUserId", "F_CreatorTime", "F_Category" };
db.AddDisableUpdateColumns(notstr);//添加禁止更新列
db.Update(obj);
}
else
{
db.Insert(obj);
}
db.InsertRange(list);
i = true;
db.CommitTran();//提交事务
}
catch (Exception ex)
{
db.RollbackTran();//回滚事务
i = false;
}
return(i);
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
Student s = new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
};
db.Insert(s); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)
List <Student> list = new List <Student>()
{
new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
},
new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
}
};
db.InsertRange(list); //批量插入
}
}
/// <summary>
/// 保存部门信息
/// </summary>
/// <param name="organizationModel"></param>
/// <param name="isEdit"></param>
/// <returns></returns>
public bool Save(Master_Organization organizationModel, bool isEdit)
{
using (SqlSugarClient dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
bool result = false;
if (isEdit)//编辑
{
var model = new
{
OrganizationName = organizationModel.OrganizationName,
//OrganizationCode = organizationModel.OrganizationCode,
Description = organizationModel.Description,
ChangeDate = organizationModel.ChangeDate,
ChangeUser = organizationModel.ChangeUser
};
result = dbMsSql.Update <Master_Organization>(model, i => i.Vguid == organizationModel.Vguid);
//存入操作日志表
string logData = JsonHelper.ModelToJson(organizationModel);
_ll.SaveLog(4, 3, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData);
}
else//新增
{
result = dbMsSql.Insert(organizationModel, false) != DBNull.Value;
//存入操作日志表
string logData = JsonHelper.ModelToJson(organizationModel);
_ll.SaveLog(1, 2, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData);
}
return(result);
}
}
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 AddHomecomingSurvey(Business_HomecomingSurvey hs)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
_dbMsSql.Insert(hs);
}
}
public User Add(User user)
{
int id = Convert.ToInt32(_sqlClient.Insert(user));
user.ID = id;
return(user);
}
public void Init()
{
var mstStr = "server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
var conList = new List <string>()
{
"server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest"
};
SqlSugarClient db = new SqlSugarClient(mstStr, conList);
var l1 = db.Queryable <Student>().ToList(); //走从
var d1 = db.Queryable <Student>().ToDataTable(); //走从
var t3 = db.Queryable <Student>().ToList(); //走从
db.Insert <Student>(new Student()
{
name = "主"
});
db.BeginTran();
var l2 = db.Queryable <Student>().ToList();//走主
db.CommitTran();
var l3 = db.Queryable <Student>().ToList();//走从
db.Dispose();
}
public void Init()
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
Student s = new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
};
db.Insert(s); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)
List <Student> list = new List <Student>()
{
new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
},
new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
}
};
db.InsertRange(list); //批量插入
}
}
public bool Create(TableCreateDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
R_Table model = new R_Table()
{
Name = req.Name,
Describe = req.Description,
R_Restaurant_Id = req.RestaurantId,
ServerRate = req.ServerRate,
R_Area_Id = req.RestaurantArea,
CythStatus = CythStatus.空置,
SeatNum = req.SeatNum,
IsVirtual = req.IsVirtual,
Sorted = req.Sorted
};
var insert = db.Insert(model);
if (req.Box > 0)
{
db.Insert(new R_BoxTable()
{
R_Table_Id = Convert.ToInt32(insert),
R_Box_Id = req.Box
});
}
db.CommitTran();
}
catch (Exception)
{
result = false;
db.RollbackTran();
throw;
}
return(result);
}
}
public void AddTest(PerformanceTest pt, SqlSugarClient db)
{
//sqlSuagr
pt.Execute(i =>
{
db.Insert <Models.InsertTest>(GetData());
}, m => { }, "添加记录");
}
public void Init()
{
Console.WriteLine("启动SerialNumber.Init");
using (SqlSugarClient db = SugarFactory.GetInstance())//开启数据库连接
{
var dientityValue = db.Insert <StudentEntity>(new StudentEntity()
{
});
var name = db.Queryable <StudentEntity>().Single(it => it.id == dientityValue.ObjToInt()).name;
Console.WriteLine(name);
var dientityValue2 = db.Insert <School>(new School()
{
});
var name2 = db.Queryable <School>().Single(it => it.id == dientityValue2.ObjToInt()).name;
Console.WriteLine(name2);;
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDaoSerNum.GetInstance()) //开启数据库连接
{
var obj = Convert.ToInt32(db.Insert <Student>(new Student()
{
}));
var name = db.Queryable <Student>().Single(it => it.id == obj).name;
var obj2 = Convert.ToInt32(db.Insert <School>(new School()
{
}));
var name2 = db.Queryable <School>().Single(it => it.id == obj2).name;
}
}
public void AddTest(PerformanceTest pt, SqlSugarClient db)
{
//sqlSuagr
pt.Execute(i =>
{
db.Insert<Models.InsertTest>(GetData());
}, m => { }, "添加记录");
}
public int Add(Job_Config record)
{
object newRec = _sqlClient.Insert(record);
if (newRec != null)
{
return(Convert.ToInt32(((Job_Config)newRec).ID));
}
return(-1);
}
public bool UpdateWeixinPrint(WeixinPrintDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
bool result = true;
try
{
db.BeginTran();
R_WeixinPrint model = Mapper.Map <WeixinPrintDTO, R_WeixinPrint>(req);
if (model.Id <= 0)
{
var insertId = Convert.ToInt32(db.Insert <R_WeixinPrint>(model));
if (req.PrintAreas != null && req.PrintAreas.Any())
{
List <R_WeixinPrintArea> list = new List <R_WeixinPrintArea>();
foreach (var item in req.PrintAreas)
{
list.Add(new R_WeixinPrintArea()
{
R_Area_Id = item.R_Area_Id,
R_WeixinPrint_Id = insertId
});
}
db.InsertRange <R_WeixinPrintArea>(list);
}
}
else
{
db.Update <R_WeixinPrint>(model);
db.Delete <R_WeixinPrintArea>(p => p.R_WeixinPrint_Id == model.Id);
if (req.PrintAreas != null && req.PrintAreas.Any())
{
List <R_WeixinPrintArea> list = new List <R_WeixinPrintArea>();
foreach (var item in req.PrintAreas)
{
list.Add(new R_WeixinPrintArea()
{
R_Area_Id = item.R_Area_Id,
R_WeixinPrint_Id = model.Id
});
}
db.InsertRange <R_WeixinPrintArea>(list);
}
}
db.CommitTran();
}
catch (Exception ex)
{
result = false;
db.RollbackTran();
throw ex;
}
return(result);
}
}
public JsonResult AddTreeMenuDetail(SysBest_MenuAddress entity)
{
entity.CreateTime = DateTime.Now;
entity.Founder = "聪哥";
var model = new ActionResultModel <string>();
_db.Insert(entity);
model.isSuccess = true;
model.responseInfo = model.isSuccess.IIF("新增菜单详情成功!", "新增菜单详情失败!");
return(Json(model));
}
[ValidateInput(false)]//敏感值验证
public JsonResult SaveCleaningCompany(Business_CleaningCompany cleaning, bool isEdit)
{
var model = new ActionResultModel <string>();
model.isSuccess = false;
using (SqlSugarClient _db = SugarDao_MsSql.GetInstance())
{
bool isExistCompanyName = _db.Queryable <Business_CleaningCompany>().Any(x => x.CompanyName == cleaning.CompanyName);
if (!isEdit && isExistCompanyName)
{
model.isSuccess = false;
model.respnseInfo = "2";
}
else
{
//保存时生成最新的二维码
if (cleaning.Vguid == Guid.Empty || cleaning.Vguid == null)
{
cleaning.Vguid = Guid.NewGuid();
}
cleaning.QRCode = getWeChatQRCode(cleaning);
if (isEdit)
{
cleaning.ChangeDate = DateTime.Now;
cleaning.ChangeUser = CurrentUser.GetCurrentUser().LoginName;
var data = new
{
CompanyName = cleaning.CompanyName,
Address = cleaning.Address,
Location = cleaning.Location,
TXLocation = cleaning.TXLocation,
ContactNumber = cleaning.ContactNumber,
ContactPerson = cleaning.ContactPerson,
QRCode = cleaning.QRCode,
Radius = cleaning.Radius,
ChangeUser = cleaning.ChangeUser,
ChangeDate = cleaning.ChangeDate
};
model.isSuccess = _db.Update <Business_CleaningCompany>(data, i => i.Vguid == cleaning.Vguid);
}
else
{
cleaning.CreatedUser = CurrentUser.GetCurrentUser().LoginName;
cleaning.CreatedDate = DateTime.Now;
cleaning.ChangeDate = DateTime.Now;
cleaning.ChangeUser = CurrentUser.GetCurrentUser().LoginName;
model.isSuccess = _db.Insert(cleaning, false) != DBNull.Value;
}
model.isSuccess = true;
model.respnseInfo = model.isSuccess == true ? cleaning.QRCode + "," + cleaning.Vguid : "0";
}
}
return(Json(model, JsonRequestBehavior.AllowGet));
}
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.Insert(GetItem);
}
});
}
public bool Save(list list)
{
var isAdd = list.id == 0;
if (isAdd)
{
return(db.Insert(list) != DBNull.Value);
}
else
{
return(db.Update <list>(list, it => it.id == list.id));
}
}
public void Init()
{
SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest", "server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest");
var l1= db.Queryable<Student>().ToList();//走从
var d1= db.Queryable<Student>().ToDataTable();//走从
var t3 = db.Queryable<Student>().ToList();//走从
db.Insert<Student>(new Student() { name="主" });
db.BeginTran();
var l2 = db.Queryable<Student>().ToList();//走主
db.CommitTran();
var l3 = db.Queryable<Student>().ToList();//走从
db.Dispose();
}
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.Insert(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()))
{
var sqlconn = db.GetConnection();
TuncateTable(db);//清空
//sqlSuagr
pt.Execute(i =>
{
db.Insert<Models.InsertTest>(GetData());
}, m => { }, "sqlSuagr");
TuncateTable(db);
//ef4.0+Sql05
using (TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities())
{
pt.Execute(i =>
{
ef.InsertTests.AddObject(GetEFData());
ef.SaveChanges();
}, m => { }, "ef4.0+Sql05");
}
TuncateTable(db);
//dapper
pt.Execute(i =>
{
sqlconn.Execute("insert into InsertTest (v1,v2,v3,int1,d1,txt ) values(@v1,@v2,@v3,@int1,@d1,@txt);select @@identity;", GetData());
}, m => { }, "dapper");
TuncateTable(db);
//ado.net
pt.Execute(i =>
{
db.GetScalar("insert into InsertTest (v1,v2,v3,int1,d1,txt ) values(@v1,@v2,@v3,@int1,@d1,@txt);select @@identity;", new SqlParameter[]{
new SqlParameter("@d1", DateTime.Now),
new SqlParameter("@int1",11),
new SqlParameter("@txt","哈另一个txt"),
new SqlParameter("@v1","v1sdfasdas"),
new SqlParameter("@v2","v2sdfasfas"),
new SqlParameter("@v3","v3adfasdf"),
new SqlParameter("@id",1)
});
}, m => { }, "ado.net");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
