/// <summary> /// 获取试室 /// </summary> /// <returns></returns> public List <TestRoom> GetTestRooms() { using (SqlSugarClient sugarClient = Educationcontext.GetClient()) { var result = sugarClient.SqlQueryable <TestRoom>("select * from TestRoom").ToList(); return(result); } }
/// <summary> /// 获取单位 /// </summary> /// <returns></returns> public List <Company> GetCompanies() { using (SqlSugarClient sugarClient = Educationcontext.GetClient()) { var result = sugarClient.SqlQueryable <Company>("select * from Company").ToList(); return(result); } }
/// <summary> /// 获取用户角色信息 /// </summary> /// <returns></returns> public List <UsersInfo> GetUsersList() { using (SqlSugarClient db = Educationcontext.GetClient()) { //三表联查 var result = db.SqlQueryable <UsersInfo>("select a.Id,a.UserName,a.`Password`,b.RoleName,c.UsersId,c.RolesId from Users a,Role b,UserandRole c where a.Id=c.UsersId and b.Id=c.RolesId"); return(result.ToList()); } }
/// <summary> /// 获取权限信息 /// </summary> /// <returns></returns> public List <Jurisdiction> GetJurisdictions() { using (SqlSugarClient db = Educationcontext.GetClient()) { var result = db.SqlQueryable <Jurisdiction>("select * from Jurisdiction where Level=0"); var list = result.ToList(); return(list); } }
public async Task <T> GetEntity(string sql) { T t = null; using (SqlSugarClient db = SqlClient.GetInstance()) { t = await db.SqlQueryable <T>(sql).FirstAsync(); } return(t); }
public async Task <List <T> > GetList(string sql, int firstRow, int pageSize, string orderStr) { List <T> list = null; using (SqlSugarClient db = SqlClient.GetInstance()) { list = await db.SqlQueryable <T>(sql).OrderBy(orderStr).ToPageListAsync(firstRow, pageSize); } return(list); }
public DataTable Query(SqlSugarClient db, string sql, KeyValue <int, string> query) { if (query.Value != "Full") { sql = $"select t.* from ({sql}) t where rownum <= {query.Key}"; } var result = db.SqlQueryable <dynamic>(sql).ToDataTable(); return(result); }
private static void Main(string[] args) { SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True", IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute }); List <GDDRU> gDDRUs = db.Queryable <GDDRU>().ToList(); List <GDFAN> gDFANs = db.Queryable <GDFAN>().ToList(); List <string> tableNameList = new List <string> { "fmu", "med", "chk", "dru", "lab", "ope" }; foreach (var gDFANItem in gDFANs) { if (tableNameList.Contains(gDFANItem.FTBL.ToLower())) { string sql = $"select * from {gDFANItem.FTBL} where conid={gDFANItem.FCONID} and cvocable='{gDFANItem.FSTR}'"; var list = db.SqlQueryable <dynamic>(sql).ToList(); foreach (var item in list) { //CVOCABLE CONID System.Console.WriteLine(item.CVOCABLE.ToString()); gDFANItem.FKEY_NO = item.KEY_NO; } db.Updateable(gDFANItem).WhereColumns(e => new { e.FCONID, e.FSTR, e.id, e.IFID }).ExecuteCommand(); } } //foreach (var gDDRUItem in gDDRUs) //{ // if (tableNameList.Contains(gDDRUItem.DRUTBL.ToLower())) // { // string sql = $"select * from {gDDRUItem.DRUTBL} where conid={gDDRUItem.DRUCONID} and cvocable='{gDDRUItem.DRUNAME}'"; // var list = db.SqlQueryable<dynamic>(sql).ToList(); // foreach (var item in list) // { // //CVOCABLE CONID // System.Console.WriteLine(item.CVOCABLE.ToString()); // gDDRUItem.DRUKey_No = item.KEY_NO; // } // db.Updateable(gDDRUItem).WhereColumns(e=>new {e.DRUCONID,e.DRUNAME,e.id,e.IFID }).ExecuteCommand(); // } //} }
/// <summary> /// 获取产品信息 /// </summary> /// <returns></returns> public static IEnumerable <Goods> GetMultiData() { IEnumerable <Goods> goods; SqlSugarClient db = DbConnect.Connect(); goods = db.SqlQueryable <dynamic>("select incode,fname," + "left(stype,3) as stype " + "from tbSpXinXi ") .Select <Goods>().OrderBy(" newid() ").ToPageList(1, 1000); return(goods); }
public List <Tables> GetAllTableName(SqlSugarClient db) { var sql = "select name as TABLE_NAME, type_desc as OWNER from sys.tables"; var tables = db.SqlQueryable <Tables>(sql).ToList(); var names = tables.GroupBy(c => c.OWNER).Select(c => new Tables { OWNER = c.Key, tables = c.Select(z => z.TABLE_NAME).OrderBy(z => z).ToList() }).ToList(); return(names); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### SqlQueryable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.Sqlite, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true }); int total = 0; var list = db.SqlQueryable <Order>("select * from `order`").ToPageList(1, 2, ref total); //by expression var list2 = db.SqlQueryable <Order>("select * from `order`").Where(it => it.Id == 1).ToPageList(1, 2); //by sql var list3 = db.SqlQueryable <Order>("select * from `order`").Where("id=@id", new { id = 1 }).ToPageList(1, 2); Console.WriteLine("#### SqlQueryable End ####"); }
/// <summary> /// 获取产品信息 /// </summary> /// <returns></returns> public static List <BGoods> GetBinaryData() { List <BGoods> goods; SqlSugarClient db = DbConnect.Connect(); goods = db.SqlQueryable <dynamic>("select fname," + "(case left(stype,1) when '1' then 1 " + "else 0 end) as stype from tbSpXinXi ") .Select <BGoods>().OrderBy(" newid() ") .ToPageList(1, 1300); return(goods); }
/// <summary> /// 根据准考证号显示考生信息 /// </summary> /// <param name="examNumber"></param> /// <returns></returns> public Candidateinherit GetCandidatesByExamNumber(string examNumber) { Candidateinherit candidate = null; try { SqlSugarClient sugarClient = Educationcontext.GetClient(); //var candidate = sugarClient.Queryable<Candidate>().First(s=>s.ExamNumber==examNumber); candidate = sugarClient.SqlQueryable <Candidateinherit>(" select * from ( select a.*,c.`Name` as CompanyName,b.`Name` as TestRoomName, d.`Name` as ExamRoomName from candidate a,testroom b, examroom c ,company d where a.TestRoomID=b.Id and a.CompanyID=d.Id and a.ExamRoomID=c.Id) info where ExamNumber =" + examNumber).Single(); return(candidate); } catch (Exception) { return(candidate); } }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Ado Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.Custom, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, AopEvents = new AopEvents { OnLogExecuting = (sql, p) => { Console.WriteLine(sql); Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value))); } } }); //sql var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <SugarParameter>() { new SugarParameter("@id", 1), new SugarParameter("@name", "2") }); //sql var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" }); //There are many methods to under db.ado var list = db.Ado.SqlQuery <Order>("select * from [order] "); var list3 = db.Ado.SqlQuery <int>(" delete from [order] where 2=15 "); var intValue = db.Ado.SqlQuerySingle <int>("select 1"); db.Ado.ExecuteCommand("delete from [order] where id>1000"); db.SqlQueryable <Custom>(@"select * from custom").ToList(); //db.Ado.xxx Console.WriteLine("#### Ado End ####"); }
/// <summary> /// 通过接口获取人员信息 /// </summary> /// <param name="applicantAccount"></param> /// <returns></returns> public JsonResult GetUserInfo(string applicantAccount) { var request = new HttpWebRequestHelp(); if (!string.IsNullOrEmpty(applicantAccount)) { var userinfo = userService.GetUseInfoByAccount(applicantAccount); var userExt = userService.QueryEntity <MD_UserExt>(s => s.UserSysId == userinfo.SysId); var db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = ConfigurationManager.ConnectionStrings["KStarConnection_Write"].ToString(),//主连接 DbType = DbType.SqlServer, IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute, SlaveConnectionConfigs = new List <SlaveConnectionConfig>() //从连接 { new SlaveConnectionConfig() { HitRate = 10, ConnectionString = ConfigurationManager.ConnectionStrings["KStarConnection_Read"].ToString() } } }); db.SqlQueryable <MD_Rank>(""); string companyCode = "BB"; string empNo = "C39456"; if (userExt != null) { companyCode = userExt.EHRCode; empNo = userExt.UUMUserID; } var result = request.HttpPost("http://58.250.19.210:8080/K2BPM/api/STest/SyncEmployees", string.Format("token={0}&&CompanyCode={1}&&EmpNo={2}", token, companyCode, empNo)); return(Json(new { UserInfo = userinfo, JobTitle = userExt.JobTitle, UserExt = result }, JsonRequestBehavior.AllowGet)); } else { return(Json(null, JsonRequestBehavior.AllowGet)); } }
/// <summary> /// 多条添加题 /// </summary> /// <param name="questionBanks"></param> /// <returns></returns> public int ADDList(List <QuestionBankinherit> questionBankinherits) { try { foreach (var item in questionBankinherits) { //添加到题库 QuestionBank questionBank = new QuestionBank(); questionBank.Subject = item.Subject; questionBank.Answer = item.Answer; questionBank.Photo = item.Photo; questionBank.TypeOfExam = item.TypeOfExam; questionBank.Enable = item.Enable; var resultquestionBank = QuestionBankDB.Insert(questionBank); if (resultquestionBank) { //获取最后一个Id SqlSugarClient sugarClient = Educationcontext.GetClient(); questionBank = sugarClient.SqlQueryable <QuestionBank>("select id from QuestionBank order by id DESC limit 1").First(); //添加到选项 Option option = new Option(); option.QuestionBankId = questionBank.Id; option.AnswerA = item.AnswerA; option.AnswerB = item.AnswerB; option.AnswerC = item.AnswerC; option.AnswerD = item.AnswerD; option.AnswerE = item.AnswerE; OptionDB.Insert(option); } } return(1); } catch (Exception ex) { //return Content(ex.Message); return(0); } }
public LayuiData GetServerList() { List <ServerInfo> data = _dbContext.SqlQueryable <ServerInfo>("select A.*,B.BusinessName from ServerInfo A left join BusinessCategory B on A.BusinessGuid = B.Guid").ToList(); return(LayuiData.CreateResult(data.Count, data)); }
public IEnumerable <dynamic> GetAllItemsAsDynamic() { return(db.SqlQueryable <dynamic>("select * from Posts").ToList()); }
/// <summary> /// 处理洗标数据 /// </summary> /// <returns></returns> public string HandlerData(string SCZSBH, Data.FirstServerDbcontext firstServerDbcontext, string BLLink) { try { var DATList = firstServerDbcontext.DAT_Production.Where(T => T.SCZSBH == SCZSBH).ToList(); foreach (var item in DATList) { string SCGGDH = item.SCGGDH; DataSet dszdynr = new DataSet(); var removeList = firstServerDbcontext.BLZDYNR.Where(T => T.scggdh == SCGGDH).ToList(); //如果数据库中有数据 则直接删除 if (removeList.Count > 0) { firstServerDbcontext.BLZDYNR.RemoveRange(); firstServerDbcontext.SaveChanges(); } int BLCodeCount = 0; GetBLCodeSql = string.Format(GetBLCodeSql, SCGGDH); ConnectionConfig LinkConfig = new ConnectionConfig(); LinkConfig.ConnectionString = BLLink; LinkConfig.IsAutoCloseConnection = true;//是否自动释放数据库,设为true我们不需要close或者Using的操作, LinkConfig.InitKeyType = InitKeyType.Attribute; LinkConfig.DbType = SqlSugar.DbType.Oracle; List <BLZDYNR> BLCODEList = new List <BLZDYNR>(); using (var BLDB = new SqlSugarClient(LinkConfig)) { BLCODEList = BLDB.SqlQueryable <BLZDYNR>(GetBLCodeSql).ToList(); } foreach (var GYItem in BLCODEList) { GYItem.scggdh = SCGGDH; } if (BLCODEList.Count > 0) { firstServerDbcontext.BLZDYNR.AddRange(BLCODEList); BLCodeCount += firstServerDbcontext.SaveChanges(); } Tuple <string, string> tuple = new Tuple <string, string>("获取BL工艺代码并处理数据", $"导入成功,共导入{BLCodeCount}行工艺代码!"); #region 处理数据 SqlParameter[] Param = { new SqlParameter("@ID", SqlDbType.VarChar), new SqlParameter("@cRESULT", SqlDbType.VarChar ) }; Param[0].Value = item.ID; Param[1].Direction = ParameterDirection.Output; ExecuteNonQueryAsync(firstServerDbcontext, "P_MarkPrint_ProductionNEW", Param); if (item.SCYSPD.Substring(0, 3) == "ASR") { var datobj = item; var xbobj = ASRChuLi(datobj, firstServerDbcontext); if (xbobj != null) { datobj.PrntXBModelCode = xbobj.XBMODEL; firstServerDbcontext.DAT_Production.Update(datobj); firstServerDbcontext.SaveChanges(); } } #endregion #region 判断是否是ASR订单 如果是 引入 D_ASRRool表的洗标分配逻辑 #endregion } return("success"); } catch (Exception ex) { //Core.XinXiJilu.JiLuRiZhi(SCZSBH, "获取BL工艺代码并处理数据", -1, ex.Message); return("error:" + ex.Message); } }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Ado Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, AopEvents = new AopEvents { OnLogExecuting = (sql, p) => { Console.WriteLine(sql); Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value))); } } }); //sql var dt = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new List <SugarParameter>() { new SugarParameter("@id", 1), new SugarParameter("@name", "2") }); //sql var dt2 = db.Ado.GetDataTable("select * from [order] where @id>0 or name=@name", new { id = 1, name = "2" }); //create sp db.Ado.ExecuteCommand(@" if object_id('up_user') is not null drop proc up_user;"); db.Ado.ExecuteCommand(@" create proc up_user @id int, @name varchar(10) ='' output as begin set @name='abc' select @id as id end "); //get output var dt3 = db.Ado.UseStoredProcedure().GetDataTable("up_user", new { name = "张三", id = 0 }); var IdP = new SugarParameter("@id", 1); var NameP = new SugarParameter("@name", null, true);//isOutput=true var dt4 = db.Ado.UseStoredProcedure().GetDataTable("up_user", IdP, NameP); var outputValue = NameP.Value; //There are many methods to under db.ado var list = db.Ado.SqlQuery <Order>("select * from [order] "); var intValue = db.Ado.SqlQuerySingle <int>("select 1"); db.Ado.ExecuteCommand("delete [order] where id>1000"); db.SqlQueryable <Custom>(@"select * from custom").ToList(); //db.Ado.xxx Console.WriteLine("#### Ado End ####"); }
/// <summary> /// 获取所有Entity(立即执行请使用ToList() /// </summary> /// <param name="CommandText">Sql语句</param> /// <param name="objParams">可变参数</param> /// <returns></returns> public virtual IEnumerable <T> GetEntities(string CommandText) { return(Db.SqlQueryable <T>("select * from " + typeof(T).Name + " where 1=1 and " + CommandText).ToList()); }
private excelInReturnInfo importExcels() { string pl_no = public_method.getRadNum("dr"); int isheet = 0; string msg = ""; string sql = ""; List <string> l_distinct_hrz = new List <string>(); string loginPersonId = public_method.getLoginInObject().personId.ToString(); //获取获取到当前登录人的personId excelInReturnInfo ri = new excelInReturnInfo(); DataTable dt_public_moduelDt = new DataTable(); sqlHelper sh = new sqlHelper(); SqlSugarClient db = sh.dbClient(); int iAllCount = 0; int iExportSuccessCount = 0;//成功导入的条数 string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"]; if (string.IsNullOrEmpty(defaultPsw)) { defaultPsw = "123456"; } defaultPsw = DES_En_De.UserMd5(defaultPsw); int count = 0; //授权的cout int departmentCount = 0; //当前公司的人员数量 string tempSql = "select (select listen_count from sys_listen) as listen_count,count(1) as deoartment_count from public_department"; DataTable table = db.SqlQueryable <object>(tempSql).ToDataTable(); count = int.Parse(DES_En_De.DesDecrypt(table.Rows[0]["listen_count"].ToString())); departmentCount = int.Parse(table.Rows[0]["deoartment_count"].ToString()); foreach (ISheet sheet in hssfworkbook) { if (isheet > 0) { msg = "请上传正确模板,必须是7列"; break; } isheet++; System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; if (row.RowNum == 0)//行数大于1行 { if (row.Cells.Count != 7) { msg = "请上传正确模板,必须是7列!"; break; } } else { //这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim() //这里进行导入 try { importExcelTempPojo tempPojo = new importExcelTempPojo(row); if (string.IsNullOrEmpty(tempPojo.department)) { msg = "部门名称必填!"; continue; } if (string.IsNullOrEmpty(tempPojo.leadingCadre)) { msg = "负责状态必填!"; continue; } if ((!"1".Equals(tempPojo.leadingCadre)) && (!"0".Equals(tempPojo.leadingCadre))) { msg = "负责状态不合法:请检查:1是负责人,0不是负责人!"; continue; } if (string.IsNullOrEmpty(tempPojo.loginAccount)) { msg = "登录帐号必填!"; continue; } if (string.IsNullOrEmpty(tempPojo.identifying)) { msg = "机器标识必填!"; continue; } if ((!"1".Equals(tempPojo.sex)) && (!"0".Equals(tempPojo.sex))) { msg = "性别不合法:请检查:1是男,0是女!"; continue; } string departId = ""; //当前的部门id string parentId = "0"; //父级的部门id默认为0 如果为空的情况下 sql = "select departmentId,isnull((select departmentId from public_department where departmentName='" + tempPojo.superiorDepartment + "' and flag =1),'0') as parentId from public_department where departmentName='" + tempPojo.department + "' and flag=1"; DataTable dt = db.Ado.GetDataTable(sql); if (dt != null && dt.Rows.Count > 0) { departId = dt.Rows[0]["departmentId"].ToString(); parentId = dt.Rows[0]["parentId"].ToString(); } //如果部门id不存在与对应的部门当中 public_department pdtt = new public_department(); pdtt.belongsId = int.Parse(parentId); pdtt.departmentName = tempPojo.department; pdtt.createDate = DateTime.Now; pdtt.updateDate = DateTime.Now; pdtt.updateUserId = int.Parse(loginPersonId); pdtt.flag = 1; pdtt.createUserId = int.Parse(loginPersonId); if (string.IsNullOrEmpty(departId)) { departId = db.Insertable(pdtt).IgnoreColumns(it => new { it.updateUserId, it.updateDate }).ExecuteReturnIdentity().ToString(); //执行插入并且返回对应的当前部门id } else { pdtt.belongsId = int.Parse(parentId); db.Updateable(pdtt).IgnoreColumns(it => new { it.createDate, it.createUserId, it.remarks }).Where(it => it.departmentId == int.Parse(departId)).ExecuteCommand(); } string findLeadingCadreSql = " select count(1) rownumber from tb_machine_user where account = '" + tempPojo.loginAccount + "'"; DataTable machineUserList = db.SqlQueryable <object>(findLeadingCadreSql).ToDataTable(); //如果当前人员存在的情况下 tb_Machine_user tempMachineUser = new tb_Machine_user(); tempMachineUser.updateDate = DateTime.Now; tempMachineUser.updateUserId = int.Parse(loginPersonId); tempMachineUser.createUserId = int.Parse(loginPersonId); tempMachineUser.createDate = DateTime.Now; tempMachineUser.userName = tempPojo.name; tempMachineUser.flag = 1; tempMachineUser.managerFlag = int.Parse(tempPojo.leadingCadre); tempMachineUser.belongsId = int.Parse(departId); tempMachineUser.cpuId = tempPojo.identifying; tempMachineUser.psw = defaultPsw; tempMachineUser.sex = int.Parse(tempPojo.sex); tempMachineUser.account = tempPojo.loginAccount; if (int.Parse(machineUserList.Rows[0]["rownumber"].ToString()) > 0) { db.Updateable(tempMachineUser).UpdateColumns(it => new { it.updateUserId, it.updateDate, it.userName, it.managerFlag, it.cpuId, it.belongsId }).Where(it => tempPojo.loginAccount.Equals(it.account)).ExecuteCommand(); iExportSuccessCount++; } else { db.Insertable(tempMachineUser).ExecuteCommand(); departmentCount++; iExportSuccessCount++; } } catch (Exception ex) { msg = "发生异常:" + ex.Message; } iAllCount++; } } } /** * 将所有的部门登记全部进行筛选然后将部门等级全部更改掉 * */ var departmentLeveDataTable = db.Ado.UseStoredProcedure().GetDataTable("eve_department_leve"); List <public_department> departmentLeveList = new List <public_department>(); for (int i = 0; i < departmentLeveDataTable.Rows.Count; i++) { public_department pd = new public_department(); pd.departmentId = int.Parse(departmentLeveDataTable.Rows[i]["departmentId"].ToString()); pd.level = int.Parse(departmentLeveDataTable.Rows[i]["departmentLevel"].ToString()); departmentLeveList.Add(pd); } db.Updateable(departmentLeveList).UpdateColumns(it => new { it.level }).ExecuteCommand(); if (iAllCount > 0) { if (iExportSuccessCount > 0) { ri.code = 100; ri.allCount = iAllCount; ri.msg = "共有[" + iAllCount + "]条数据,共影响[" + iExportSuccessCount + "]条数据!"; } else { ri.code = 0; ri.allCount = iAllCount; ri.msg = "共有[" + iAllCount + "]条,没有影响到数据!"; } } else { ri.code = -10; ri.allCount = iAllCount; ri.msg = "没有数据可以导入!"; } return(ri); }
/// <summary> /// 导出Excel. /// </summary> /// <param name="projectName">项目名称.</param> /// <returns>Excel.</returns> public ActionResult OutputResult(string projectName) { Project project = Db.Queryable <Project>().Where(it => it.Name == projectName).Single(); int projectId = project.Id; List <ContentDTO> list = Db.SqlQueryable <ContentDTO>("select a.*,b.name,b.method from content a,project b where a.projectId=b.id and a.projectId=" + projectId).ToList(); if (project.Status == "完成统计") { list.OrderBy(it => it.Result); } else { list.OrderBy(it => it.Id); } // 创建Excel对象 工作薄 HSSFWorkbook excelBook = new HSSFWorkbook(); // 创建Excel工作表 Sheet ISheet sheet = excelBook.CreateSheet("内容信息"); if (project.Method == "评分") { // 标题 IRow rowTitle = sheet.CreateRow(0); rowTitle.CreateCell(0).SetCellValue("项目名称"); rowTitle.CreateCell(1).SetCellValue("评审编号"); rowTitle.CreateCell(2).SetCellValue("评审内容"); rowTitle.CreateCell(3).SetCellValue("评审方式"); rowTitle.CreateCell(4).SetCellValue("分数"); rowTitle.CreateCell(5).SetCellValue("评审结果"); for (int i = 0; i < list.Count(); i++) { IRow row = sheet.CreateRow(i + 1); rowTitle.CreateCell(0).SetCellValue(list[i].ProjectName); rowTitle.CreateCell(1).SetCellValue(list[i].Number); rowTitle.CreateCell(2).SetCellValue(list[i].Name); rowTitle.CreateCell(3).SetCellValue(list[i].Method); rowTitle.CreateCell(4).SetCellValue(list[i].Score); rowTitle.CreateCell(5).SetCellValue(list[i].Result); } } else { // 标题 IRow rowTitle = sheet.CreateRow(0); rowTitle.CreateCell(0).SetCellValue("项目名称"); rowTitle.CreateCell(1).SetCellValue("评审编号"); rowTitle.CreateCell(2).SetCellValue("评审内容"); rowTitle.CreateCell(3).SetCellValue("评审方式"); rowTitle.CreateCell(4).SetCellValue("一等奖票数"); rowTitle.CreateCell(5).SetCellValue("二等奖票数"); rowTitle.CreateCell(6).SetCellValue("三等奖票数"); rowTitle.CreateCell(7).SetCellValue("弃权票数"); rowTitle.CreateCell(8).SetCellValue("分数"); rowTitle.CreateCell(9).SetCellValue("评审结果"); for (int i = 0; i < list.Count(); i++) { IRow row = sheet.CreateRow(i + 1); rowTitle.CreateCell(0).SetCellValue(list[i].ProjectName); rowTitle.CreateCell(1).SetCellValue(list[i].Number); rowTitle.CreateCell(2).SetCellValue(list[i].Name); rowTitle.CreateCell(3).SetCellValue(list[i].Method); rowTitle.CreateCell(4).SetCellValue(list[i].FirstPrizeNum); rowTitle.CreateCell(5).SetCellValue(list[i].SecondPrizeNum); rowTitle.CreateCell(6).SetCellValue(list[i].ThirdPrizeNum); rowTitle.CreateCell(7).SetCellValue(list[i].GiveupNum); rowTitle.CreateCell(8).SetCellValue(list[i].Score); rowTitle.CreateCell(9).SetCellValue(list[i].Result); } } string fileName = project.Name + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls"; MemoryStream stream = new MemoryStream(); excelBook.Write(stream); stream.Seek(0, SeekOrigin.Begin); return(File(stream, "application/vnd.ms-excel", fileName)); }
/// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="sql">sql</param> /// <returns>实体</returns> public List <T> QueryList <T>(string sql) where T : class, new() { return(DbContext.SqlQueryable <T>(sql).With(SqlWith.NoLock).ToList()); }
/// <summary> /// 执行查询sql语句 ,返回数据集 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public async Task <List <T> > GetListBySql(string sql) { return(await _dbBase.SqlQueryable <T>(sql).ToListAsync()); }
} //用来处理T表的常用操作 /// <summary> /// 获取所有 /// </summary> /// <returns></returns> public virtual List <T> GetList() {// entity.GetType().Name return(_db.SqlQueryable <T>($"select * from {new T().GetType().Name} where IsDelete=0").ToList()); }
/// <summary> /// 根据sql语句查询 /// </summary> /// <returns></returns> public virtual ISugarQueryable <T> GetSqlQueryable(string sql) { return(Db.SqlQueryable <T>(sql)); }
/// <summary> /// 分页操作 /// </summary> /// <param name="pagIndex">页面索引</param> /// <param name="pageSize">每页有多少条记录</param> /// <param name="total">默认 0</param> /// <returns></returns> List <UserList> IUserListDAL.Pagination(int pagIndex, int pageSize, int total) { var list = db.SqlQueryable <UserList>("select * from userlist").ToPageList(1, 2, ref total); return(list); }
/// <summary> /// 根据订单Aid获取订单 /// </summary> /// <param name="aid"></param> /// <returns></returns> public TB_hotelcashorder GetOrderByAid(int aid) { string sql = string.Format("select * from TB_hotelcashorder with(nolock) where aid={0}", aid); return(sqlSugarClient.SqlQueryable <TB_hotelcashorder>(sql).First()); }
/// <summary> /// 执行sql获取List /// </summary> /// <param name="Sql"></param> /// <returns></returns> public List <T> GetListBySql(string Sql) { return(Sqldb.SqlQueryable <T>(Sql).ToList()); }