/// <summary> /// 导入数据到目标表 /// </summary> /// <param name="tableName">目标表名</param> /// <param name="filePath">数据文件流路径,支持xls、xlsx、csv文件</param> /// <param name="columnCont">字段数量</param> /// <param name="databaseSessionKey">数据库连接Key</param> /// <returns>成功行数</returns> public static int Import(string tableName, string filePath, int columnCont = 0, string databaseSessionKey = null) { using (var session = DatabaseSession.OpenSession(databaseSessionKey)) { return(Import(session, tableName, filePath, columnCont)); } }
public byte[] ExportUserBytes(UserSearchInput input) { using (var session = DatabaseSession.OpenSession()) { var sql = @"SELECT A.""Id"" 标识, A.""UserName"" 工号, ""Name"" 姓名, C.GROUP_NAME 地区, B.NAME 部门, CASE WHEN A.""IsDeleted"" = 1 THEN '注销' WHEN A.""IsActive"" = 0 THEN '锁定' ELSE '正常' END 状态, a.""CreationTime"" 创建时间, a.""LastLoginTime"" 最后登录时间 FROM ""AbpDepartment"" B, ""AbpUsers"" A LEFT JOIN ""AbpDistrict"" C ON A.""GroupId"" = C.ID WHERE ""TenantId"" = {0} AND A.""DepartmentId"" = B.ID".FormatWith(AbpSession.TenantId); const string whereString = ""; var orderString = input.Order != null ? @" ORDER BY A.""{0}"" {1}".FormatWith(input.Order.Name, input.Order.Type) : ""; var reader = session.ExecuteReader(new DataCommandDefinition(sql + whereString + orderString)); return(_exportProvider.ExportBig(reader, ExportFileType.Excel)); } }
//根据code判断功能权限接口 public bool Checkin(string code) { long Id = CurrentUser.Id; using (var session = DatabaseSession.OpenSession()) { var sql = @"SELECT DISTINCT E.CODE FROM ""AbpUsers"" A INNER JOIN ""AbpUserRoles"" B ON A.""Id"" = B.""UserId"" INNER JOIN ""AbpRoles"" C ON B.""RoleId"" = C.""Id"" LEFT JOIN ""AbpFunctionRole"" D ON D.""ROLE_ID"" = C.""Id"" INNER JOIN ""AbpFunction"" E ON D.""FUNCTION_ID"" = E.""ID"" WHERE A.""Id""= {0}"; var resultL = session.Query <string>(sql.FormatWith(Id)); if (resultL.ToList().Contains(code)) { return(true); } } return(false); }
public List <FuncModel> GetAllFuncs() { var restult = new List <FuncModel>(); long Id = CurrentUser.Id; using (var session = DatabaseSession.OpenSession()) { var sql = @"SELECT DISTINCT E.Id,E.CODE,E.NAME FROM ""AbpFunction"" E WHERE E.IS_DELETED=0"; var resultL = session.Query <FuncModel>(sql.FormatWith(Id)); var FunCs = resultL as FuncModel[] ?? resultL.ToArray(); return(FunCs.ToList()); } }
public List <string> GetFuncs(string roleId) { var restult = new List <string>(); using (var session = DatabaseSession.OpenSession()) { var sql = @"SELECT DISTINCT E.Id FROM ""AbpRoles"" C LEFT JOIN ""AbpFunctionRole"" D ON D.""ROLE_ID"" = C.""Id"" INNER JOIN ""AbpFunction"" E ON D.""FUNCTION_ID"" = E.""ID"" WHERE C.""Id""= {0}"; var resultL = session.Query <string>(sql.FormatWith(roleId)); var FunCs = resultL as string[] ?? resultL.ToArray(); return(FunCs.ToList()); } }
/// <summary> /// 记录生成任务 /// </summary> /// <param name="strColumnHeader"></param> /// <param name="strExecSql"></param> /// <param name="strExt"></param> public void WriteTaskRecord(string strColumnHeader, string strExecSql, string strExt) { string strSql = "insert into EM_DownloadFile (ext,createDate,countNum,sql,ColumnHeader,state)"; strSql += "values('" + strExt + "',to_date('" + DateTime.Now.ToString() + "', 'YYYY-MM-DD HH24:MI:SS')" + ",0,'" + strExecSql + "','" + strColumnHeader + "',0)"; using (var session = DatabaseSession.OpenSession()) { try { int intExe = session.Execute(strSql, null, null, null, null); if (intExe <= 0) { // Logger.Debug("str:" + strSql + ",保存失败!");//日志文件 } } catch (Exception ex) { //Logger.Debug("str:" + strSql + ",保存失败!error:" + ex);//日志文件 } } }
/// <summary> /// 获得DataReader /// </summary> /// <param name="dbId"></param> /// <param name="sql"></param> /// <returns></returns> public static IDataReader ExecuteDataReader(string sql) { IDataReader dr = null; var _session = DatabaseSession.OpenSession(); if (_session != null) { try { dr = _session.ExecuteReader(sql); return(dr); } catch (Exception ex) { _session.Closed(); _session.Dispose(); throw new Exception("sql执行失败:" + ex.Message); } } return(dr); }
/// <summary> /// 执行sql语句,返回 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> public static int Execute(string sql, object param = null, System.Data.IDbTransaction transaction = null, int?commandTimeout = null, System.Data.CommandType?commandType = null) { int result = 0;//默认为0 var _dbSession = DatabaseSession.OpenSession(); DataTable table = new DataTable(); try { result = _dbSession.Execute(sql, param, transaction, commandTimeout, commandType); _dbSession.Closed(); } catch (Exception ex) { _dbSession.Closed(); throw new Exception("sql执行失败:" + ex.Message); } finally { _dbSession.Closed(); } return(result); }
/// <summary> /// 执行一个sql语句得到一个table /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> public static DataTable ExecuteGetTable(string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null) { var _dbSession = DatabaseSession.OpenSession(); DataTable table = new DataTable(); try { var dr = _dbSession.ExecuteReader(sql, param, transaction, commandTimeout, commandType); table.Load(dr); _dbSession.Closed(); } catch (Exception ex) { _dbSession.Closed(); throw new Exception("sql执行失败:" + ex.Message); } finally { _dbSession.Closed(); } return(table); }
/// <summary> /// 记录下载文件至数据库 /// </summary> /// <param name="strTitle"></param> /// <param name="strMapPath"></param> /// <param name="strPath"></param> public void WriteFilesDataBase(string strTitle, string strMapPath, string strPath) { System.IO.FileInfo FileObj = new FileInfo(strMapPath); long lngSize = FileObj.Length; //长度 string strExt = FileObj.Extension; //扩展后缀 string strSql = "insert into EM_DownloadFile (title,sizeNum,ext,createDate,countNum,constituteDate,urlPath,state)"; strSql += "values('" + strTitle + "'," + lngSize + ",'" + strExt + "',to_date('" + DateTime.Now.ToString() + "', 'YYYY-MM-DD HH24:MI:SS')" + ",0,to_date('" + DateTime.Now.ToString() + "', 'YYYY-MM-DD HH24:MI:SS'),'" + strPath + "',1)"; using (var session = DatabaseSession.OpenSession()) { try { int intExe = session.Execute(strSql, null, null, null, null); if (intExe <= 0) { // Logger.Debug("str:" + strSql + ",保存失败!");//日志文件 } } catch (Exception ex) { // Logger.Debug("str:" + strSql + ",保存失败!error:" + ex);//日志文件 } } }