/// <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);//日志文件
}
}
}
