public static String OrganizationList() { OrganizationListViewData view = new OrganizationListViewData(); view.List = new List <Organization>(); List <TreeNode> list; view.PageData = new PaginationData(); view.PageData.PageIndex = 1; view.PageData.PageSize = 10; view.PageData.PagintionJsFunction = "window.System.OrganizationList.search()"; view.PageData.OrderBy = "Code"; string sql = @"SELECT Name AS Text, CAST(UnitGuid AS NVARCHAR(36)) AS ID, CAST(ParentGuid AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ' + Icon + '''></i>' AS Icon FROM dbo.Organization ORDER BY Code"; using (DbConnectionScope dcs = new DbConnectionScope()) { list = DbHelper.Db(sql).ToList <TreeNode>(); view.treeList = TreeNodeList.ToTreeNodeJson(list); } view.treeTitle = "组织架构"; TreeNode tr = list.FirstOrDefault(); if (tr == null) { tr = new TreeNode(); } sql = @"SELECT A.*, B.Name AS ParentName FROM dbo.Organization AS A LEFT JOIN dbo.Organization AS B ON A.ParentGuid = B.UnitGuid WHERE A.ParentGuid = @ParentGuid"; view.List = new Pagintion(sql, new { ParentGuid = tr.ID }, view.PageData).ToList <Organization>(); return(PageViewHelper.LoadPage("/System/OrganizationList.aspx", view)); }
public String RoleList() { RoleListViewData view = new RoleListViewData(); view.PageData.PagintionJsFunction = "window.System.RoleTab.searchRole()"; view.PageData.OrderBy = "Code"; string sql = @"SELECT A.*, CASE WHEN B.OrganizationName IS NOT NULL THEN STUFF(B.OrganizationName, 1, 1, '') ELSE '' END AS OrganizationName, CASE WHEN C.UserName IS NOT NULL THEN STUFF(C.UserName, 1, 1, '') ELSE '' END AS UserName FROM dbo.RoleInfo AS A OUTER APPLY (SELECT ';' + IB.Name FROM dbo.RoleToBusiness AS IA INNER JOIN dbo.Organization AS IB ON IA.BusinessGuid = IB.UnitGuid WHERE IA.RoleGuid = A.RoleGuid AND IB.Name > '' ORDER BY IB.Code FOR XML PATH('')) AS B(OrganizationName) OUTER APPLY (SELECT ';' + IB.UserName FROM dbo.RoleToBusiness AS IA INNER JOIN dbo.UserInfo AS IB ON IA.BusinessGuid = IB.UserGuid WHERE IA.RoleGuid = A.RoleGuid AND IB.UserName > '' ORDER BY IB.UserCode FOR XML PATH('')) AS C(UserName)"; using (DbConnectionScope dcs = new DbConnectionScope()) { view.List = new Pagintion(sql, null, view.PageData).ToList <RoleInfo>(); } return(PageViewHelper.LoadPage("/System/RoleList.aspx", view)); }
public DomainEFContext(DbConnectionScope dbConnectionScope, bool contextOwnsConnectionScope = false) : base(dbConnectionScope.Connection, contextOwnsConnection: false) { Debug.WriteLine($"{ClassName} - Constructor({nameof(dbConnectionScope)}, {nameof(contextOwnsConnectionScope)}: {contextOwnsConnectionScope})"); _dbConnectionScope = dbConnectionScope; _contextOwnsConnectionScope = contextOwnsConnectionScope; }
public DbScope(TransactionScopeOption option, IsolationLevel isolationLevel, TimeSpan timeOut) { _disposed = true; if (option != TransactionScopeOption.Suppress ) { TransactionOptions tranOp = new TransactionOptions() { IsolationLevel = isolationLevel, Timeout = timeOut }; _tranScope = new TransactionScope(option, tranOp, TransactionScopeAsyncFlowOption.Enabled); _connScope = new DbConnectionScope(option == TransactionScopeOption.RequiresNew? DbConnectionScopeOption.RequiresNew: DbConnectionScopeOption.Required); } else { _tranScope = new TransactionScope(TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled); _connScope = new DbConnectionScope(DbConnectionScopeOption.RequiresNew); } _disposed = false; }
public String SystemTree() { TreeViewData view = new TreeViewData(); view.Title = "系统"; string sql = @"SELECT Name AS Text, CAST(SystemGuid AS NVARCHAR(36)) AS ID, CAST(NULL AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ''></i>' AS Icon FROM dbo.SystemInfo ORDER BY Code"; view.List = "[]"; using (DbConnectionScope dcs = new DbConnectionScope()) { view.List = TreeNodeList.ToTreeNodeJson(DbHelper.Db(sql).ToList <TreeNode>()); } return(PageViewHelper.LoadPage("/System/CommonPage/SingleSelectedTree.aspx", view)); }
public String DepartmentTree() { TreeViewData view = new TreeViewData(); view.Title = "部门"; string sql = @"SELECT Name AS Text, CAST(UnitGuid AS NVARCHAR(36)) AS ID, CAST(ParentGuid AS NVARCHAR(36)) AS ParentID, '<i class=''ace-icon ' + Icon + '''></i>' AS Icon FROM dbo.Organization --WHERE ParentGuid IS NOT NULL ORDER BY Code"; view.List = "[]"; using (DbConnectionScope dcs = new DbConnectionScope()) { view.List = TreeNodeList.ToTreeNodeJson(DbHelper.Db(sql).ToList <TreeNode>()); } return(PageViewHelper.LoadPage("/System/CommonPage/SingleSelectedTree.aspx", view)); }
public string Insert(Customer entity, string stepList) { entity.CustomerID = (entity.CustomerID == Guid.Empty ? Guid.NewGuid() : entity.CustomerID); List <ProcessStep> list = JsonHelper.JsonTo <List <ProcessStep> >(stepList); using (DbConnectionScope sc = new DbConnectionScope()) { foreach (ProcessStep item in list) { item.ID = Guid.NewGuid(); item.CustomerID = entity.CustomerID; item.CreatedOn = DateTime.Now; item.CreatedBy = UserInfoBLL.GetCurrentUserGuid(); item.SetDefaultValue("FinishDate"); item.Insert(); } return(bll.Insert(entity).ToString()); } }
/// <summary> /// 获取提醒记录数 /// </summary> /// <returns></returns> public static List <Customer> GetAlertCustomer() { string sql = @"SELECT A.*, B.Name AS CompanyName, C.Name AS BusinessTypeName, SUM(BidCost) OVER() AS TotalBidCost, SUM(SaleAmount) OVER() AS TotalSaleAmount, SUM(FactCost) OVER() AS TotalFactCost, SUM(Profit) OVER() AS TotalProfit, D.StepName, D.EstimateDate FROM dbo.Customer AS A LEFT JOIN dbo.Company AS B ON A.CompanyID = B.CompanyID LEFT JOIN dbo.BusinessType AS C ON A.BusinessType = C.TypeID OUTER APPLY (SELECT TOP 1 IA.ID AS StepID, IA.StepName, IA.EstimateDate FROM dbo.ProcessStep AS IA WHERE A.CustomerID = IA.CustomerID AND IA.EstimateDate > GETDATE() AND IA.EstimateDate < GETDATE() + @AlertDays AND FinishDate IS NULL ORDER BY IA.EstimateDate, IA.CreatedOn) AS D WHERE D.StepID IS NOT NULL"; using (DbConnectionScope cs = new DbConnectionScope()) { return(DbHelper.Db(sql, new { AlertDays = Convert.ToInt32(ConfigurationManager.AppSettings["AlertDays"]) }).ToList <Customer>()); } }
public string Update(Customer entity, string stepList) { List <ProcessStep> list = JsonHelper.JsonTo <List <ProcessStep> >(stepList); using (DbConnectionScope sc = new DbConnectionScope()) { List <string> guids = new List <string>() { Guid.Empty.ToString() }; guids.AddRange(list.Select(item => item.ID.ToString())); string sql = @"DELETE FROM dbo.ProcessStep WHERE CustomerID = @CustomerID AND ID NOT IN('" + string.Join("', '", guids.ToArray()) + "');"; DbHelper.Db(sql, new { CustomerID = entity.CustomerID }); foreach (ProcessStep item in list) { item.CustomerID = entity.CustomerID; if (item.ID == Guid.Empty) { item.ID = Guid.NewGuid(); item.CreatedOn = DateTime.Now; item.CreatedBy = UserInfoBLL.GetCurrentUserGuid(); item.SetDefaultValue("FinishDate"); item.Insert(); } else { item.ModifiedOn = DateTime.Now; item.ModifiedBy = UserInfoBLL.GetCurrentUserGuid(); item.SetDefaultValue("FinishDate"); item.Update(); } } entity.ModifiedOn = DateTime.Now; entity.ModifiedBy = UserInfoBLL.GetCurrentUserGuid(); return(bll.Update(entity).ToString()); } }
public async Task <SqlConnection> GetConnectionByNameAsync(string connectionName) { return(await DbConnectionScope.GetOpenConnectionAsync <SqlConnection>(_dbFactory.Value, connectionName)); }
public async Task <SqlConnection> GetConnectionByDbNameAsync(string dbname) { return(await DbConnectionScope.GetOpenConnectionAsync <SqlConnection>(_dbNameFactory.Value, dbname).ConfigureAwait(false)); }