protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School","sc","sc.id","s.sch_id",JoinType.INNER) .Join("subject","sb","sb.sid","s.id",JoinType.LEFT).SelectToList<Models.Student>("*"); }, m => { }, "sqlSugar SQL语法糖"); //sqlSugar pt.Execute(i => { db.SqlQuery<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "sqlSugar 纯SQL写法"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.ExecuteStoreQuery<WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "EF4.0+sql05 纯SQL写法"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
private void InsertMyRights(SqlSugarClient _db, Business_EquityAllocation equity, string PushObject) { List <Business_MyRights> myRightsList = new List <Business_MyRights>(); string[] userID = null; var userData = _db.SqlQuery <Business_Personnel_Information>(@"select * from Business_Personnel_Information where DepartmenManager in ('1','2','3')").ToList(); if (PushObject.Contains("-")) { //部门 Guid pushObject = Guid.Parse(PushObject); userData = userData.Where(x => x.OwnedFleet == pushObject).ToList(); } else if (PushObject.Contains("|") || !PushObject.Contains("-")) { //一个或多人司机 List <Business_Personnel_Information> newDataLsit = new List <Business_Personnel_Information>(); userID = PushObject.Split(new char[1] { '|' }); foreach (var item in userID) { Business_Personnel_Information newData = new Business_Personnel_Information(); newData = userData.Where(x => x.UserID == item).FirstOrDefault(); newDataLsit.Add(newData); } userData = newDataLsit; } if (equity.ValidType == "周期") { getPeriodDate(equity, equity.Period); } foreach (var item in userData) { Business_MyRights myRights = new Business_MyRights(); myRights.RightsName = equity.RightsName; myRights.Description = equity.Description; myRights.Type = equity.Type; myRights.StartValidity = equity.StartValidity; myRights.EndValidity = equity.EndValidity; myRights.UsageTime = null; myRights.UserVGUID = item.Vguid.ToString(); myRights.Status = "草稿"; myRights.CreatedUser = CurrentUser.GetCurrentUser().LoginName; myRights.CreatedDate = DateTime.Now; myRights.ChangeDate = DateTime.Now; myRights.ChangeUser = CurrentUser.GetCurrentUser().LoginName; myRights.EquityVGUID = equity.VGUID.ToString(); myRights.VGUID = Guid.NewGuid(); myRightsList.Add(myRights); } _db.InsertRange(myRightsList, false); }
public Business_CleaningCompany GetCleaningCompanyByVguid(string vguid) { Business_CleaningCompany cleaningCompany = new Business_CleaningCompany(); using (SqlSugarClient _db = SugarDao_MsSql.GetInstance()) { cleaningCompany = _db.SqlQuery <Business_CleaningCompany>(@"select * from Business_CleaningCompany where Vguid=@VGUID", new { VGUID = vguid }).ToList().FirstOrDefault(); } return(cleaningCompany); }
public JsonResult GetPointsListBySearch(string fleet, string name, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var ownedFleet = cm.MotorcadeName; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); List <DriverScore> driverScoreList = new List <DriverScore>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization in (" + fleetAll + @") and vdv.status='1' and qs.score is not null and qs.score != 0 order by DriverId asc").ToList(); } else { driverScoreList = _dbMsSql.SqlQuery <DriverScore>(@"select DriverId,Name,convert(int,score) as Score from [DZ_DW].[dbo].[Visionet_DriverInfo_View] vdv left join tb_query_score qs on qs.id_no=vdv.IdCard where vdv.Organization=@OwnedCompany and vdv.MotorcadeName in (" + fleet + @") and vdv.status='1' and qs.score is not null and qs.score != 0 order by DriverId asc", new { OwnedCompany = ownedCompany }).ToList(); } if (name != "" && name != null) { driverScoreList = driverScoreList.Where(x => x.Name.Contains(name)).ToList(); } } return(Json(driverScoreList, JsonRequestBehavior.AllowGet)); }
public JsonResult GetCompanyLocation(string vguid) { Business_CleaningCompany cleaning = new Business_CleaningCompany(); using (SqlSugarClient _db = SugarDao_MsSql.GetInstance()) { cleaning = _db.SqlQuery <Business_CleaningCompany>(@"select * from Business_CleaningCompany where Vguid=@VGUID", new { VGUID = vguid }).ToList().FirstOrDefault(); //cleaning = _db.Queryable<Business_CleaningCompany>().Where(x => x.Vguid == vguid).FirstOrDefault(); } return(Json(cleaning, JsonRequestBehavior.AllowGet)); }
//***********************************************************************获取,修改数据*********************************************************************************************** //取得发送表数据,未审核(F_DealState=0),未发送,未处理,发送时间已到,根据优先级排序(大的在前) private List <SMC_SendSms> GetSMCSendSmsList() { List <SMC_SendSms> list_smc_sendsms = new List <SMC_SendSms>(); using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接 { string sql = "select F_Id,F_MobileList,F_MobileCount,F_IsTimer,F_RootId,F_SendSign,F_CreatorUserId,F_SmsContent from SMC_SendSms" + " where F_DealState=0 and F_SendState=0 and F_OperateState=0 ORDER BY F_Priority DESC"; list_smc_sendsms = db.SqlQuery <SMC_SendSms>(sql);//F_CreatorUserId是用户的F_Id } return(list_smc_sendsms); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER) .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).SelectToList <Models.Student>("*"); }, m => { }, "sqlSugar SQL语法糖"); //sqlSugar pt.Execute(i => { db.SqlQuery <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "sqlSugar 纯SQL写法"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.ExecuteStoreQuery <WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "EF4.0+sql05 纯SQL写法"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
//*************************************************************************数据库操作方法*************************************************************************************** //获取Http发送方式的发送数据,Sev_SendDateDetail的处理状态(F_DealState)为0,可拆分.发送协议(F_ProtocolType)为1,http发送方式,根据优先级(F_Level)排序 private List <Sev_SendDateDetail> GetHttpSendList() { List <Sev_SendDateDetail> httpsendlist = new List <Sev_SendDateDetail>(); using (SqlSugarClient db = new SqlSugarClient(connStr)) { string sql = "select F_Id,SMC_F_Id,F_SmsContent,F_Level,F_SendTime from Sev_SendDateDetail" //要批量更新状态,只能全部取过来,不然会更新出bug + " where F_DealState=0 and F_ProtocolType=1 ORDER BY F_Level DESC"; httpsendlist = db.SqlQuery <Sev_SendDateDetail>(sql); //F_CreatorUserId是用户的F_Id } return(httpsendlist); }
public DriverInfo getDriverInfo(Business_Personnel_Information personInfoModel) { DriverInfo pi = new DriverInfo(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { pi = _dbMsSql.SqlQuery <DriverInfo>(@"select DriverId,OrganizationId from [DZ_DW].[dbo].[Visionet_DriverInfo_View] where IdCard=@IDNumber and status='1'" , new { IDNumber = personInfoModel.IDNumber }).ToList().FirstOrDefault(); } return(pi); }
//获取审核条数 private int ReviewedNum(string F_Id) { List <Models.OC_UserInfo> list = new List <Models.OC_UserInfo>(); using (SqlSugarClient db = new SqlSugarClient(connStr)) //开启数据库链接 { string sql = "select F_Reviewed from OC_UserInfo where F_UserFid = @F_Id"; //获取F_Reviewed list = db.SqlQuery <Models.OC_UserInfo>(sql, new { F_Id = F_Id }); } try { return(list[0].F_Reviewed.ObjToInt()); } catch { return(0); } }
public Personnel_Info getPersonnelInfo(Business_Personnel_Information personInfoModel) { Personnel_Info pi = new Personnel_Info(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { pi = _dbMsSql.SqlQuery <Personnel_Info>(@"select Name,IdCard,CabLicense,CabVMLicense,MotorcadeName,Organization from [DZ_DW].[dbo].[Visionet_DriverInfo_View] where IdCard=@IDNumber and status='1'" , new { IDNumber = personInfoModel.IDNumber }).ToList().FirstOrDefault(); } return(pi); }
//获取Http发送方式的扣量数据,Sev_SendDateDetail的处理状态(F_DealState)为3,可拆分.发送协议(F_ProtocolType)为1,http发送方式 private List <Sev_SendDateDetail> GetBuckleList() { List <Sev_SendDateDetail> buckleList = new List <Sev_SendDateDetail>(); using (SqlSugarClient db = new SqlSugarClient(connStr)) { string sql = "select * from Sev_SendDateDetail" //数据全取,为了之后好批量更新 + " where F_DealState=3 and F_ProtocolType=1 and (( F_SendTime+'00:30:00')< GETDATE())"; //推迟30分钟模拟 buckleList = db.SqlQuery <Sev_SendDateDetail>(sql); //F_CreatorUserId是用户的F_Id } return(buckleList); }
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.SqlQuery <Test>("select * from(select *,row_number() over(order by id) as r from test ) t where t.r between @b and @e ", new { b = 1000, e = 1010 }); } }); }
public JsonResult GetOperationInfo(string fleet, string date, string carID, string code) { var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code]; var ownedCompany = cm.Organization; var fleetAll = PartnerHomePageController.getSqlInValue(cm.MotorcadeName, code); //ownedCompany = "第一服务中心"; List <Visionet_CabInfo> carList = new List <Visionet_CabInfo>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization in (" + fleet + @") and OperationStatus=0 order by Motorcade asc").ToList(); } else { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @") and OperationStatus=0 order by Motorcade asc", new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList(); } if (carID != null && carID != "") { carList = _dbMsSql.SqlQuery <Visionet_CabInfo>(@"select CabLicense,Motorcade from [DZ_DW].[dbo].Visionet_CabInfo_View where Organization=@OwnedCompany and Motorcade in (" + fleet + @") and OperationStatus=0 and CabLicense like '%" + carID + "%' order by Motorcade asc", new { OwnedCompany = ownedCompany, OwnedFleet = fleet }).ToList(); } } return(Json(carList, JsonRequestBehavior.AllowGet)); }
public static string getAllOwnedCompany(string description, string level) { var str = ""; var fleetList = new List <string>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (description != "" && level == "12" && description != "全部" && description != "R") { //查所配置公司备注 var description2 = getSqlInValue(description, ""); fleetList = _dbMsSql.SqlQuery <string>(@"select distinct Remark from DZ_Organization where OrganizationName in (" + description2 + ") and status=0").ToList(); } else if (description == "全部" && level == "12") { //查全公司名称 fleetList = _dbMsSql.SqlQuery <string>(@"select distinct OrganizationName from DZ_Organization where status=0").ToList(); } else if (description == "R" && level == "12") { //查全公司备注 fleetList = _dbMsSql.SqlQuery <string>(@"select distinct Remark from DZ_Organization where status=0").ToList(); } else if (description != "" && level == "11") { //查全车队名称 fleetList = _dbMsSql.SqlQuery <string>(@"select CarTeamName from DZ_Organization where OrganizationName=@OrgName and status=0", new { OrgName = description }).ToList(); } if (fleetList.Count > 0) { foreach (var item in fleetList) { str += item + ","; } str = str.Substring(0, str.Length - 1); } } return(str); }
public OrderPayRecordDTO GetModelByPayId(int id) { using (var db = new SqlSugarClient(Connection)) { var model = db.SqlQuery <OrderPayRecordDTO>($@" SELECT P.*, P.R_Market_Id AS MarketId, ISNULL(C.czdmmc00, '') AS CreateUserName FROM R_OrderPayRecord P LEFT JOIN dbo.czdm C ON C.Id = P.CreateUser WHERE P.Id = " + id).FirstOrDefault(); return(model); } }
/// <summary> /// 部门树形结构 /// </summary> /// <returns></returns> public List <Master_Organization> GetOrganizationTreeList() { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { var currentDepartment = CurrentUser.GetCurrentUser().Department; var mainDepVguid = dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault(); var organizationList = dbMsSql.SqlQuery <Master_Organization>("exec usp_OrganizationDetail @orgVguid", new { orgVguid = currentDepartment ?? mainDepVguid.ToString() }); return(organizationList); } }
/// <summary> /// 获取问卷答题状况详情(饼图) /// </summary> /// <param name="vguid"></param> /// <param name="departmentVguid">部门vguid</param> /// <returns></returns> public List <U_QuestionSsetsRate> GetQuestionRateDetail(string vguid, string departmentVguid) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { Guid exerciseVguid = Guid.Parse(vguid); List <U_QuestionSsetsRate> questionRateEntity = new List <U_QuestionSsetsRate>(); string sql = string.Format(@"EXEC usp_QuestionSsetsRate @ExercisesVGUID,@DepartmentVGUID"); DataTable dt = new DataTable(); string departVguid = string.Empty; if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin") { departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid; } else { departVguid = CurrentUser.GetCurrentUser().Department; if (!string.IsNullOrEmpty(departmentVguid)) { Guid dep = Guid.Parse(departVguid); var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 Guid depVguid = Guid.Parse(departmentVguid); departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid; } } //dt = _dbMsSql.GetDataTable(sql, new //{ // ExercisesVGUID = exerciseVguid, // DepartmentVGUID = departVguid //}); //exerciseRateEntity = GetExerciseRateList(dt); questionRateEntity = _dbMsSql.SqlQuery <U_QuestionSsetsRate>(sql, new { ExercisesVGUID = exerciseVguid, DepartmentVGUID = departVguid }); //存入操作日志表 //string logData = JsonHelper.ModelToJson<JsonResultModel<U_ScoreReport>>(jsonResult); //_ll.SaveLog(3, 8, "习题列表", logData); return(questionRateEntity); } }
public JsonResult GetCarViolationInfo(string fleet, string code) { //电子警察违章 var cm = CacheManager <Personnel_Info> .GetInstance()[PubGet.GetUserKey + code + "K"]; var orgName = cm.Organization; var fleetAll = getSqlInValue(cm.MotorcadeName, code); List <Electronic_police> resultInfo = new List <Electronic_police>(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { if (fleet == "0") { fleet = fleetAll; } else { fleet = "'" + fleet + "'"; } if (cm.DepartmenManager == "12") { resultInfo = _dbMsSql.SqlQuery <Electronic_police>(@"select plate_no,peccancy_date,score,amercement,area,act from tb_electronic_police ep left join [DZ_DW].[dbo].[Visionet_CabInfo_View] vcv on ep.plate_no=vcv.CabLicense where vcv.Organization in (" + fleet + @") and ep.status_cd='0' order by peccancy_date desc", new { OrgName = orgName }).ToList(); } else { resultInfo = _dbMsSql.SqlQuery <Electronic_police>(@"select plate_no,peccancy_date,score,amercement,area,act from tb_electronic_police ep left join [DZ_DW].[dbo].[Visionet_CabInfo_View] vcv on ep.plate_no=vcv.CabLicense where vcv.Organization=@OrgName and vcv.Motorcade in (" + fleet + @") and ep.status_cd='0' order by peccancy_date desc", new { OrgName = orgName }).ToList(); } } return(Json(resultInfo, JsonRequestBehavior.AllowGet)); }
public JsonResult GetEquityAllocationByVguid(string vguid) { Business_EquityAllocation equityAllocation = new Business_EquityAllocation(); using (SqlSugarClient _db = SugarDao_MsSql.GetInstance()) { if (vguid != "" && vguid != null) { equityAllocation = _db.SqlQuery <Business_EquityAllocation>(@"select * from Business_EquityAllocation where VGUID=@VGUID", new { VGUID = vguid }).ToList().FirstOrDefault(); } } return(Json(equityAllocation, JsonRequestBehavior.AllowGet)); }
public List <Master_Organization> GetUserOrganizationModel() { using (SqlSugarClient dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { string sql = string.Format(@";with cte as ( select * from Master_Organization where Vguid = '{0}' union all select air.* from Master_Organization as air inner join cte on air.ParentVguid = cte.Vguid )select * from cte", CurrentUser.GetCurrentUser().Department); var organizationModel = dbMsSql.SqlQuery <Master_Organization>(sql); return(organizationModel); } }
/// <summary> /// 通过查询条件获取推送信息列表(已推送) /// </summary> /// <param name="searchParam"></param> /// <param name="para"></param> /// <returns></returns> public JsonResultModel <V_Business_WeChatPushMain_Information> GetWeChatPushListBySearch(SearchWeChatPushList searchParam, GridParams para) { using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { JsonResultModel <V_Business_WeChatPushMain_Information> jsonResult = new JsonResultModel <V_Business_WeChatPushMain_Information>(); //只查询三个月内的数据 DateTime endDate = DateTime.Now.AddMonths(-3); var query = _dbMsSql.Queryable <V_Business_WeChatPushMain_Information>().Where(i => i.Status == 4 && i.PushDate > endDate && i.Title != "营收短信" && i.Title != "营收微信"); if (CurrentUser.GetCurrentUser().LoginName.ToLower() != "sysadmin") { var mainDep = _dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == null).Select(i => i.Vguid).SingleOrDefault(); //大众交通集团 var listChildDep = _dbMsSql.Queryable <Master_Organization>().Where(i => i.ParentVguid == mainDep).Select(i => i.Vguid).ToList(); //大众出租租赁 listChildDep.Add(mainDep); Guid currentDep = Guid.Parse(CurrentUser.GetCurrentUser().Department); //首先查询登录人的部门 if (!listChildDep.Contains(currentDep)) { var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + currentDep + "')"); //找到该部门以及其所有子部门 var list = _dbMsSql.Queryable <Sys_User>().In(i => i.Department, listDep).Select(i => i.LoginName).ToList(); query.Where(i => list.Contains(i.PushPeople)); //找到该部门中人员所推送的消息 } } if (!string.IsNullOrEmpty(searchParam.Title)) { query.Where(c => c.Title.Contains(searchParam.Title));//标题 } if (!string.IsNullOrEmpty(searchParam.PushType.ToString())) { query.Where(c => c.PushType == searchParam.PushType);//推送类型 } if (!string.IsNullOrEmpty(searchParam.Important.ToString())) { query.Where(c => c.Important == searchParam.Important);//是否重要 } if (!string.IsNullOrEmpty(searchParam.PeriodOfValidity.ToString())) { DateTime pushDate = DateTime.Parse(searchParam.PushDate.ToString().Replace("0:00:00", "23:59:59")); query.Where(c => c.PushDate < pushDate);//有效时间 } query.OrderBy(para.sortdatafield + " " + para.sortorder); int pageCount = 0; jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize, ref pageCount); jsonResult.TotalRows = pageCount; string logData = JsonHelper.ModelToJson(jsonResult); _ll.SaveLog(3, 20, CurrentUser.GetCurrentUser().LoginName, "已推送列表", logData); return(jsonResult); } }
/// <summary> /// 导出 /// </summary> /// <param name="exerciseVguid">习题Vguid</param> /// <param name="exportType">导出类型</param> /// <param name="departmentVguid">部门vguid</param> public void Export(string exerciseVguid, string exportType, string departmentVguid) { using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { string sql = string.Format(@"EXEC usp_ExercisesAnswerUser @ExercisesVGUID,@Type,@DepartmentVGUID"); DataTable dt = new DataTable(); string departVguid = string.Empty; if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin") { departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid; } else { departVguid = CurrentUser.GetCurrentUser().Department; if (!string.IsNullOrEmpty(departmentVguid)) { Guid dep = Guid.Parse(departVguid); var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门 Guid depVguid = Guid.Parse(departmentVguid); departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid; } } dt = _dbMsSql.GetDataTable(sql, new { ExercisesVGUID = exerciseVguid, Type = exportType, DepartmentVGUID = departVguid }); dt.TableName = "table"; DataView dv = dt.DefaultView; dv.Sort = "Department"; dt = dv.ToTable(); //因为导出模板不一样(exportType为3/4/5的是一个模板,其他的是一个模板) if (exportType == "3" || exportType == "4" || exportType == "5") { string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("ScoreTemplate"); Common.ExportExcel.ExportExcels("ScoreTemplate.xlsx", amountFileName, dt); _logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "ScoreTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt)); } else { string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PersonReportTemplate"); Common.ExportExcel.ExportExcels("ReprotPersonTemplate.xlsx", amountFileName, dt); _logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "PersonReportTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt)); } } }
//获取用户的自动免审模板 private List <Models.OC_AutoExamineTmpl> GetAutoExamineTmpl(int RootId) { List <Models.OC_AutoExamineTmpl> list = new List <Models.OC_AutoExamineTmpl>(); try { using (SqlSugarClient db = new SqlSugarClient(connStr)) { string sql = "select F_Analysis from OC_AutoReviewTemplete where F_RootId = @RootId "; list = db.SqlQuery <Models.OC_AutoExamineTmpl>(sql, new { RootId = RootId }); } } catch { list = null; }//避免空值报错 return(list); }
//获取用户的人工免审模板 private List <Models.OC_ManualExamineTmpl> GetManualExamineTmpl(int RootId) { List <Models.OC_ManualExamineTmpl> list = new List <Models.OC_ManualExamineTmpl>(); try { using (SqlSugarClient db = new SqlSugarClient(connStr)) { string sql = "select F_RegexContent,F_Action,F_ChannelId from OC_ManualExamineTmplete where F_RootId = @RootId"; list = db.SqlQuery <Models.OC_ManualExamineTmpl>(sql, new { RootId = RootId }); } } catch { list = null; }//避免空值报错 return(list); }
public JsonResult SaveCleaningInfo(string companyVGUID, string location, string type, string description, string code) { var model = new ActionResultModel <string>(); var cm = CacheManager <Business_Personnel_Information> .GetInstance()[PubGet.GetUserKey + code]; var cabLicense = ""; var cabOrgName = ""; Master_Organization organizationDetail = new Master_Organization(); organizationDetail = _ol.GetOrganizationDetail(cm.OwnedFleet.ToString()); var manOrgName = organizationDetail.OrganizationName; using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance2()) { var manData = _dbMsSql.SqlQuery <Personnel_Info>(@"select Organization,CabLicense from [DZ_DW].[dbo].[Visionet_DriverInfo_View] where IdCard=@ID and status='1'", new { ID = cm.IDNumber }).FirstOrDefault(); if (manData != null) { cabLicense = manData.CabLicense; cabOrgName = manData.Organization; } } using (SqlSugarClient _db = SugarDao_MsSql.GetInstance()) { Business_SecondaryCleaning cleaning = new Business_SecondaryCleaning(); cleaning.Vguid = Guid.NewGuid(); cleaning.Type = type; cleaning.Description = description; cleaning.Location = location; cleaning.Personnel = cm.UserID; cleaning.OperationDate = DateTime.Now; cleaning.CompanyVguid = companyVGUID; cleaning.CouponType = description; cleaning.CabLicense = cabLicense; cleaning.CabOrgName = cabOrgName; cleaning.ManOrgName = manOrgName; cleaning.CreatedUser = cm.Name; cleaning.CreatedDate = DateTime.Now; cleaning.ChangeDate = DateTime.Now; cleaning.ChangeUser = cm.Name; model.isSuccess = _db.Insert(cleaning, false) != DBNull.Value; model.respnseInfo = model.isSuccess == true?cleaning.CreatedDate.TryToString() : "0"; //同时修改权益表中优惠券状态 UpdateMyRights(_db, description, cm.Vguid); } return(Json(model, JsonRequestBehavior.AllowGet)); }
//取得发送表数据,已审核(F_DealState=9),未发送,未处理 private List <SMC_SendSms> GetOperateSmsList() { List <SMC_SendSms> list_smc_operasms = new List <SMC_SendSms>(); using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接 { string sql = "select F_Id,F_IsTimer from SMC_SendSms" + " where F_DealState=9 and F_SendState=0 and F_OperateState=0 and F_IsTimer='true'"; list_smc_operasms = db.SqlQuery <SMC_SendSms>(sql);//F_CreatorUserId是用户的F_Id } try { return(list_smc_operasms); } catch { return(list_smc_operasms = null); } }
/// <summary> /// 获取所有数据库表名 /// </summary> /// <param name="db"></param> /// <returns></returns> public List <string> GetTableNames(SqlSugarClient db) { var isLog = db.IsEnableLogEvent; db.IsEnableLogEvent = false; string sql = SqlSugarTool.GetCreateClassSql(null); var tableNameList = db.SqlQuery <string>(sql).ToList(); for (int i = 0; i < tableNameList.Count; i++) { var tableName = tableNameList[i]; tableNameList[i] = db.GetClassTypeByTableName(tableName); } db.IsEnableLogEvent = isLog; return(tableNameList); }
/// <summary> /// 推送接收者树形结构 /// </summary> /// <returns></returns> public List <V_Business_Personnel_Information> GetOrganizationTreeList() { using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { List <V_Business_Personnel_Information> organizationTreeList = new List <V_Business_Personnel_Information>(); if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin") { organizationTreeList = _dbMsSql.Queryable <V_Business_Personnel_Information>().OrderBy(it => it.UserID).ToList(); } else { organizationTreeList = _dbMsSql.SqlQuery <V_Business_Personnel_Information>(" exec usp_getOrganization @vguid", new { vguid = CurrentUser.GetCurrentUser().Department }); } return(organizationTreeList); } }
public List <SchemeDiscountDetailDTO> GetSchemeDetailListById(int discountId) { using (var db = new SqlSugarClient(Connection)) { string detailSql = "SELECT DC.*, D.Id AS SchemeId, D.[Name] AS SchemeName, " + "D.R_Area_Id AS AreaId, D.R_Market_Id AS MarketId, " + "C.Id AS CategoryId, C.Name AS CategoryName " + "FROM dbo.R_DiscountCategory DC " + "INNER JOIN dbo.R_Discount D ON D.Id = DC.R_Discount_Id " + "INNER JOIN dbo.R_Category C ON C.Id = DC.R_Category_Id " + "WHERE DC.R_Discount_Id = @DiscountId"; var detailList = db.SqlQuery <SchemeDiscountDetailDTO>(detailSql, new { DiscountId = discountId }); return(detailList); } }
/// <summary> /// 事务执行sql语句或存储过程 /// </summary> /// <typeparam name="TResult"></typeparam> /// <param name="sql">sql语句</param> /// <param name="whereObj">命令参数对应匿名对象</param> /// <returns></returns> public List <TResult> QueryBySqlTransactions <TResult>(string sql, object whereObj = null) { using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance()) { try { dbClient.BeginTran(); return(dbClient.SqlQuery <TResult>(sql, whereObj)); } catch (Exception ex) { dbClient.RollbackTran(); throw ex; } } }
/// <summary> /// 获取表结构信息 /// </summary> /// <param name="db"></param> /// <param name="tableName"></param> /// <returns></returns> public List<PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName) { string sql = SqlSugarTool.GetTtableColumnsInfo(tableName); return db.SqlQuery<PubModel.DataTableMap>(sql); }
/// <summary> /// 获取所有需要生成多语言的视图名称 /// </summary> /// <param name="db"></param> /// <returns></returns> internal static List<string> GetLanguageViewNameList(SqlSugarClient db) { string key = "LanguageHelper.GetViewNameList"; var cm = CacheManager<List<string>>.GetInstance(); if (cm.ContainsKey(key)) { return cm[key]; } else { var list = db.SqlQuery<string>(@" select a.name from sys.objects a JOIN sys.sql_modules b on a.[object_id]=b.[object_id] where [type]='v' and b.[definition] like '%" + db.Language.ReplaceViewStringKey + @"%' and a.name not like '%"+PreSuffix+@"%' ").ToList(); cm.Add(key, list, cm.Day); return list; } }
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; } }//关闭数据库连接 }
/// <summary> /// 获取所有数据库表名 /// </summary> /// <param name="db"></param> /// <returns></returns> public List<string> GetTableNames(SqlSugarClient db) { var isLog = db.IsEnableLogEvent; db.IsEnableLogEvent = false; string sql = SqlSugarTool.GetCreateClassSql(null); var tableNameList = db.SqlQuery<string>(sql).ToList(); for (int i = 0; i < tableNameList.Count; i++) { var tableName = tableNameList[i]; tableNameList[i] = db.GetClassTypeByTableName(tableName); } db.IsEnableLogEvent = isLog; return tableNameList; }