예제 #1
0
        /// <summary>
        /// 根据操作类型插入或更新一条用户数据
        /// </summary>
        /// <param name="model"></param>
        /// <param name="type"></param>
        public void AddOrUpdateUser(UserDataModel model, string type)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            if ("update".Equals(type))
            {
                s.Header.Append(@"update [User_Data] set [Account] = @Account
      ,[UserName] = @UserName
      ,[Password] = @Password 
      ,[Ban] = @ban where GUID = @GUID");
            }
            else
            {
                s.Header.Append(
                    @"INSERT INTO [User_Data]
           ([GUID]
           ,[Account]
           ,[UserName]
           ,[Password]
           ,[Ban])
     VALUES (@GUID
           ,@Account
           ,@UserName
           ,@Password
           ,@Ban)");
            }

            s.Parameters.Add(new SqlParameter("@GUID", model.GUID));
            s.Parameters.Add(new SqlParameter("@Account", model.Account));
            s.Parameters.Add(new SqlParameter("@UserName", model.UserName));
            s.Parameters.Add(new SqlParameter("@Password", model.Password));
            s.Parameters.Add(new SqlParameter("@ban", model.Ban));
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #2
0
        /// <summary>
        /// 插入新的登录日志。
        /// </summary>
        /// <param name="account">登录用户账号。</param>
        /// <param name="clientFlag">客户端GUID。</param>
        /// <returns>新数据信息。</returns>
        public DataSet Insert_Login_Log(string account, string clientFlag)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"
insert into [Login_Log](
    [FK_User_Data], 
    [DateTimeLogin], 
    [DateTimeUpdate], 
    [ClientFlag], 
    [ClientPassword], 
    [Offline]
)
(
    select top 1 [GUID],
        @DateTimeUpdate, 
        @DateTimeUpdate, 
        @ClientFlag, 
        @ClientPassword, 
        0
    from [User_Data] where [Account] = @Account
)
select @ClientPassword as 'ClientPassword'
");
            s.Parameters.Add(new SqlParameter("@Account", account));
            s.Parameters.Add(new SqlParameter("@ClientFlag", clientFlag));
            s.Parameters.Add(new SqlParameter("@ClientPassword", Guid.NewGuid().ToString()));
            s.Parameters.Add(new SqlParameter("@DateTimeUpdate", DateTime.Now));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #3
0
        /// <summary>
        /// 把用户插入用户数组
        /// </summary>
        /// <param name="userGUID">用户GUID</param>
        /// <param name="userGroupGUID">用户组GUID</param>
        public void AddUserToUserGroup(string userGUID, string userGroupGUID, bool addOrDelete)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            if (addOrDelete)
            {
                s.Header.Append(@" IF NOT EXISTS(SELECT * FROM dbo.User_Data_UserGroup_Type_Link WHERE FK_User_Data=@FK_User_Data AND FK_UserGroup_Type=@FK_UserGroup_Type)
                               BEGIN
                                  insert into User_Data_UserGroup_Type_Link (FK_User_Data,FK_UserGroup_Type) values (@FK_User_Data,@FK_UserGroup_Type)
                               END");
                s.Parameters.Add(new SqlParameter("@FK_User_Data", userGUID));
                s.Parameters.Add(new SqlParameter("@FK_UserGroup_Type", userGroupGUID));
            }
            else
            {
                s.Header.Append(@" DELETE FROM User_Data_UserGroup_Type_Link WHERE 1=1");
                if (!String.IsNullOrEmpty(userGUID))
                {
                    s.Header.Append(@" and FK_User_Data=@FK_User_Data");
                    s.Parameters.Add(new SqlParameter("@FK_User_Data", userGUID));
                }
                if (!String.IsNullOrEmpty(userGroupGUID))
                {
                    s.Header.Append(@"  AND FK_UserGroup_Type=@FK_UserGroup_Type");
                    s.Parameters.Add(new SqlParameter("@FK_UserGroup_Type", userGroupGUID));
                }
            }
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #4
0
        /// <summary>
        /// 修改一条数据
        /// </summary>
        /// <param name="model"></param>
        public void UpdateUrl(UrlDataModel model)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@" Update [Url_Data]
           SET [Url] = @Url
              ,[ParentGUID]=@ParentGuid
              ,[UrlCode] = @UrlCode
              ,[UrlParams] = @UrlParams
              ,[UrlName] = @UrlName
              ,[UrlIndex] = @UrlIndex
              ,[UserAuthentication] = @UserAuthentication
              ,[Show] = @Show where [GUID]=@GUID");
            s.Parameters.Add(new SqlParameter("@GUID", model.GUID));
            s.Parameters.Add(new SqlParameter("@ParentGUID", model.ParentGUID));
            s.Parameters.Add(new SqlParameter("@Url", model.Url));
            s.Parameters.Add(new SqlParameter("@UrlCode", model.UrlCode));
            s.Parameters.Add(new SqlParameter("@UrlParams", model.UrlParams));
            s.Parameters.Add(new SqlParameter("@UrlName", model.UrlName));
            s.Parameters.Add(new SqlParameter("@UrlIndex", model.UrlIndex));
            s.Parameters.Add(new SqlParameter("@UserAuthentication", model.UserAuthentication));
            s.Parameters.Add(new SqlParameter("@Show", model.Show));
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #5
0
        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="model"></param>
        public void AddUrl(UrlDataModel model)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@" INSERT INTO [Url_Data]
           ([GUID]
           ,[ParentGUID]
           ,[Url]
           ,[UrlCode]
           ,[UrlParams]
           ,[UrlName]
           ,[UserAuthentication]
           ,[Show])
     VALUES
           (@GUID
           ,@ParentGUID
           ,@Url
           ,@UrlCode
           ,@UrlParams
           ,@UrlName
           ,@UserAuthentication
           ,@Show)");

            s.Parameters.Add(new SqlParameter("@GUID", model.GUID));
            s.Parameters.Add(new SqlParameter("@ParentGUID", model.ParentGUID));
            s.Parameters.Add(new SqlParameter("@Url", model.Url));
            s.Parameters.Add(new SqlParameter("@UrlCode", model.UrlCode));
            s.Parameters.Add(new SqlParameter("@UrlParams", model.UrlParams));
            s.Parameters.Add(new SqlParameter("@UrlName", model.UrlName));
            s.Parameters.Add(new SqlParameter("@UserAuthentication", model.UserAuthentication));
            s.Parameters.Add(new SqlParameter("@Show", model.Show));
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #6
0
        /// <summary>
        /// 更新用户日志在线标记。
        /// </summary>
        /// <param name="account">账号。</param>
        /// <param name="offlineFlag">更新后在线标记。</param>
        /// <param name="count">更新数量。</param>
        /// <param name="offline">在线状态。</param>
        /// <param name="clientPassword">客户端密码。</param>
        /// <param name="dateTimeUpdate_Start">最后在线时间范围起点。</param>
        /// <param name="dateTimeUpdate_End">最后在线时间范围终点。</param>
        /// <returns>更新结果反馈。</returns>
        public DataSet Update_Login_Log_Offline(string account, bool offlineFlag, int count = 1, int offline = -1, string clientPassword = null, DateTime dateTimeUpdate_Start = new DateTime(), DateTime dateTimeUpdate_End = new DateTime())
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            StringBuilder select = new StringBuilder();

            if (count > 1)
            {
                select.Append(string.Format(@"select top {0} ClientPassword ", count));
            }
            else
            {
                select.Append(@"select ClientPassword ");
            }
            StringBuilder from = new StringBuilder(@"
from [Login_Log] ");

            StringBuilder where = new StringBuilder(@"
where 1=1 ");

            if (offline > -1)
            {
                where.Append(@"
and Offline = @Offline ");
                s.Parameters.Add(new SqlParameter("@Offline", offline > 0 ? 1 : 0));
            }

            if (!string.IsNullOrEmpty(clientPassword))
            {
                where.Append(@"
and ClientPassword = @ClientPassword ");
                s.Parameters.Add(new SqlParameter("@ClientPassword", clientPassword));
            }

            if (dateTimeUpdate_Start > DateTime.MinValue)
            {
                where.Append(@"
and DateTimeUpdate >= @DateTimeUpdate_Start ");
                s.Parameters.Add(new SqlParameter("@DateTimeUpdate_Start", dateTimeUpdate_Start));
            }

            if (dateTimeUpdate_End > DateTime.MinValue)
            {
                where.Append(@"
and DateTimeUpdate <= @DateTimeUpdate_End ");
                s.Parameters.Add(new SqlParameter("@DateTimeUpdate_End", dateTimeUpdate_End));
            }

            s.Header.Append(string.Format(@"
update [Login_Log] set [Offline] = @OfflineFlag 
where Exists(select * from ({0} {1} {2}) as A where [Login_Log].[ClientPassword] = A.[ClientPassword])
    and Exists(select * from [User_Data] where [User_Data].[Account] = @Account and [User_Data].[GUID] = [Login_Log].[FK_User_Data])
select @@ROWCOUNT as TotalCount
", select, from, where));
            s.Parameters.Add(new SqlParameter("@Account", account));
            s.Parameters.Add(new SqlParameter("@OfflineFlag", offlineFlag ? 1 : 0));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #7
0
        /// <summary>
        /// 用户组与地址关联性查询。
        /// </summary>
        /// <param name="userGroup_GUID">用户组GUID。</param>
        /// <param name="url_GUID">地址GUID。</param>
        /// <param name="url">地址。</param>
        /// <param name="urlCode">地址编码。</param>
        /// <param name="forbidden"></param>
        /// <returns>用户组与地址关联性数据集。</returns>
        public DataSet Select_Url_Data_UserGroup_Type(string userGroup_GUID = null, string url_GUID = null, string url = null, string urlCode = null, bool?forbidden = null)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@"
from [UserGroup_Type] join [Url_Data_UserGroup_Type_Link] on [UserGroup_Type].[GUID] = [Url_Data_UserGroup_Type_Link].[FK_UserGroup_Type]
    inner join [Url_Data] on [Url_Data_UserGroup_Type_Link].[FK_Url_Data] = [Url_Data].[GUID]
");
            s.Select.Append(@"
select [UserGroup_Type].[UserGroupName]
      ,[Url_Data_UserGroup_Type_Link].[Forbidden]
      ,[Url_Data_UserGroup_Type_Link].[PriorityLevel]
      ,[Url_Data].[GUID] as [Url_GUID]
      ,[Url_Data].[ParentGUID]
      ,[Url_Data].[Url]
      ,[Url_Data].[UrlCode]
      ,[Url_Data].[UrlParams]
      ,[Url_Data].[UrlName]
      ,[Url_Data].[UrlIndex]
");
            s.Where.Append(@"
where 1=1 
    and [Url_Data].[Show] = 1");

            if (!string.IsNullOrEmpty(userGroup_GUID))
            {
                s.Where.Append(@"
    and [UserGroup_Type].[GUID] = @UserGroup_GUID ");
                s.Parameters.Add(new SqlParameter("@UserGroup_GUID", userGroup_GUID));
            }

            if (!string.IsNullOrEmpty(url_GUID))
            {
                s.Where.Append(@"
    and [Url_Data].[GUID] = @Url_GUID ");
                s.Parameters.Add(new SqlParameter("@Url_GUID", url_GUID));
            }

            if (!string.IsNullOrEmpty(url))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[Url]) = Lower(@Url) ");
                s.Parameters.Add(new SqlParameter("@Url", url));
            }

            if (!string.IsNullOrEmpty(urlCode))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[UrlCode]) = Lower(@UrlCode) ");
                s.Parameters.Add(new SqlParameter("@UrlCode", urlCode));
            }
            if (forbidden != null)
            {
                s.Where.Append(@"and Lower([Url_Data_UserGroup_Type_Link].[Forbidden]) = @forbidden ");
                s.Parameters.Add(new SqlParameter("@forbidden", forbidden));
            }
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #8
0
        /// <summary>
        /// 删除一条Module_Data数据
        /// </summary>
        public void DeleteUrl(string strWhere)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"delete from [Url_Data]" + strWhere);
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #9
0
        /// <summary>
        /// 清空表Module_Data
        /// </summary>
        public void DeleteAllUrl()
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"TRUNCATE TABLE[Url_Data_UserGroup_Type_Link] DELETE FROM [Url_Data]");
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #10
0
        /// <summary>
        /// 查询用户信息。
        /// </summary>
        /// <param name="guid">GUID。</param>
        /// <param name="account">账号。</param>
        /// <param name="userName">用户名。模糊查询。</param>
        /// <param name="password">密码。</param>
        /// <param name="ban">是否禁止。</param>
        /// <returns>用户信息查询结果数据集。</returns>
        public DataSet Select_User_Data(string guid = null, string account = null, string userName = null, string password = null, bool?ban = null)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;

            s.From.Append(@"
from [User_Data]
");
            s.Select.Append(@"
select [GUID]
      ,[Account]
      ,[UserName]
      ,[Password]
      ,[Ban]
");
            s.Where.Append(@"
where 1=1
");
            if (!string.IsNullOrEmpty(guid))
            {
                s.Where.Append(@"
    and GUID = @GUID ");
                s.Parameters.Add(new SqlParameter("@GUID", guid));
            }

            if (!string.IsNullOrEmpty(account))
            {
                s.Where.Append(@"
    and Account = @Account ");
                s.Parameters.Add(new SqlParameter("@Account", account));
            }

            if (!string.IsNullOrEmpty(userName))
            {
                s.Where.Append(@"
    and UserName like @UserName escape '\' ");
                s.Parameters.Add(new SqlParameter("@UserName", DatabaseUtil.FormatSqlParameterValue(userName)));
            }

            if (!string.IsNullOrEmpty(password))
            {
                s.Where.Append(@"
    and Password = @Password ");
                s.Parameters.Add(new SqlParameter("@Password", password));
            }

            if (ban != null)
            {
                s.Where.Append(@"
    and Ban = @Ban ");
                s.Parameters.Add(new SqlParameter("@Ban", ban));
            }

            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #11
0
        /// <summary>
        /// 更新用户的禁止状态
        /// </summary>
        /// <param name="userGUID">用户GUID</param>
        /// <param name="ban">当前状态</param>
        public void ChangeUserState(string userGUID, bool ban)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;

            s.Header.Append(@"update [User_Data] set [Ban] = @ban where GUID = @GUID");
            s.Parameters.Add(new SqlParameter("@ban", !ban));
            s.Parameters.Add(new SqlParameter("@GUID", userGUID));
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #12
0
        /// <summary>
        /// 更新用户日志更新日期。
        /// </summary>
        /// <param name="clientPassword">客户端密码。</param>
        /// <returns>更新结果。</returns>
        public DataSet Update_Login_Log_DateTimeUpdate(string clientPassword)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"
update [Login_Log] set [DateTimeUpdate] = GetDate() where ClientPassword = @ClientPassword
select [DateTimeUpdate] from [Login_Log] where ClientPassword = @ClientPassword
");
            s.Parameters.Add(new SqlParameter("@ClientPassword", clientPassword));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #13
0
        /// <summary>
        /// 获取用户和用户组信息。
        /// </summary>
        /// <param name="user_GUID">用户GUID。</param>
        /// <param name="userGroup_GUID">用户组GUID。</param>
        /// <param name="account">用户账号。</param>
        /// <param name="userName">用户名。</param>
        /// <param name="userGroupName">用户组名。</param>
        /// <returns>用户和用户组信息数据集。</returns>
        public DataSet Select_UserGroup_Type_User_Data(string user_GUID = null, string userGroup_GUID = null, string account = null, string userName = null, string userGroupName = null)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@"
from [User_Data] join [User_Data_UserGroup_Type_Link] on [User_Data].[GUID] = [User_Data_UserGroup_Type_Link].[FK_User_Data]
    join [UserGroup_Type] on [User_Data_UserGroup_Type_Link].[FK_UserGroup_Type] = [UserGroup_Type].[GUID] ");
            s.Select.Append(@"
select [UserGroup_Type].[GUID] as [UserGroup_GUID]
      ,[UserGroup_Type].[UserGroupName]
      ,[User_Data].[Account]
      ,[User_Data].[UserName] ");
            s.Where.Append(@"
where 1=1");

            if (!string.IsNullOrEmpty(user_GUID))
            {
                s.Where.Append(@"
    and [User_Data].[GUID] = @User_GUID ");
                s.Parameters.Add(new SqlParameter("@User_GUID", user_GUID));
            }

            if (!string.IsNullOrEmpty(userGroup_GUID))
            {
                s.Where.Append(@"
    and [UserGroup_Type].[GUID] = @UserGroup_GUID ");
                s.Parameters.Add(new SqlParameter("@UserGroup_GUID", userGroup_GUID));
            }

            if (!string.IsNullOrEmpty(account))
            {
                s.Where.Append(@"
    and [User_Data].[Account] = @Account ");
                s.Parameters.Add(new SqlParameter("@Account", account));
            }

            if (!string.IsNullOrEmpty(userName))
            {
                s.Where.Append(@"
    and Lower([User_Data].[UserName]) like @UserName escape '\' ");
                s.Parameters.Add(new SqlParameter("@UserName", string.Format("%{0}%", DatabaseUtil.FormatSqlParameterValue(userName).ToLower())));
            }

            if (!string.IsNullOrEmpty(userGroupName))
            {
                s.Where.Append(@"
    and Lower([UserGroup_Type].[UserGroupName]) like @UserGroupName escape '\' ");
                s.Parameters.Add(new SqlParameter("@UserGroupName", string.Format("%{0}%", DatabaseUtil.FormatSqlParameterValue(userGroupName).ToLower())));
            }

            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #14
0
        /// <summary>
        /// 查询用户日志。
        /// </summary>
        /// <param name="account">用户账号。</param>
        /// <param name="clientFlag">客户端标记。</param>
        /// <param name="clientPassword">客户端密码。</param>
        /// <param name="dateTimeUpdate_Start">最后在线时间范围起点。</param>
        /// <param name="offline">下线标记。</param>
        /// <returns>用户日志数据集。</returns>
        public DataSet Select_User_Data_Login_Log(string account, string clientFlag = null, string clientPassword = null, DateTime dateTimeUpdate_Start = new DateTime(), int offline = -1)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@"
from [User_Data] left join [Login_Log] on [User_Data].[GUID] = [Login_Log].[FK_User_Data]
");
            s.Select.Append(@"
select [User_Data].[Account]
      ,[User_Data].[UserName]
      ,[Login_Log].[DateTimeLogin]
      ,[Login_Log].[DateTimeUpdate]
      ,[Login_Log].[ClientFlag]
      ,[Login_Log].[ClientPassword]
      ,[Login_Log].[Offline]
");
            s.Where.Append(@"
where 1=1
    and [User_Data].[Account] = @Account ");
            s.Parameters.Add(new SqlParameter("@Account", account));

            if (!string.IsNullOrEmpty(clientFlag))
            {
                s.Where.Append(@"
    and [Login_Log].[ClientFlag] = @ClientFlag ");
                s.Parameters.Add(new SqlParameter("@ClientFlag", clientFlag));
            }

            if (!string.IsNullOrEmpty(clientPassword))
            {
                s.Where.Append(@"
    and [Login_Log].[ClientPassword] = @ClientPassword ");
                s.Parameters.Add(new SqlParameter("@ClientPassword", clientPassword));
            }

            if (dateTimeUpdate_Start > DateTime.MinValue)
            {
                s.Where.Append(@"
    and [Login_Log].[DateTimeUpdate] >= @DateTimeUpdate_Start ");
                s.Parameters.Add(new SqlParameter("@DateTimeUpdate_Start", dateTimeUpdate_Start));
            }

            if (offline > -1)
            {
                s.Where.Append(@"
    and [Login_Log].[Offline] = @Offline ");
                s.Parameters.Add(new SqlParameter("@Offline", offline > 0 ? 1 : 0));
            }

            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #15
0
        /// <summary>
        /// 根据用户编号删除一个用户
        /// </summary>
        /// <param name="userGUID">用户GUID</param>
        public void DeleteUserData(string userGUID)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@" DELETE FROM [User_Data] WHERE 1=1");
            if (!String.IsNullOrEmpty(userGUID))
            {
                s.Header.Append(@" and GUID=@userGUID");
                s.Parameters.Add(new SqlParameter("@userGUID", userGUID));
            }
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #16
0
        /// <summary>
        /// 更新用户表Ban数据。
        /// </summary>
        /// <param name="account">用户账号。</param>
        /// <param name="ban">禁止标记。</param>
        /// <returns>更新后结果。</returns>
        public DataSet Update_User_Data_Ban(string account, int ban)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"
update [User_Data] set [Ban] = @Ban where Account = @Account
select [Ban] from [User_Data] where Account = @Account
");
            s.Parameters.Add(new SqlParameter("@Account", account));
            s.Parameters.Add(new SqlParameter("@Ban", ban > 0 ? 1 : 0));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #17
0
        /// <summary>
        /// 获取最大UrlIndex值
        /// </summary>
        /// <returns></returns>
        public int GetMaxUrlIndex()
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"SELECT maxvalue=ISNULL(MAX([UrlIndex]),1)  FROM [Url_Data]");
            DataSet dataSet = DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);

            if (dataSet == null || dataSet.Tables.Count <= 0 || dataSet.Tables[0].Rows.Count <= 0)
            {
                return(0);
            }
            return((from DataRow row in dataSet.Tables[0].Rows select Convert.ToInt32(row["maxvalue"].ToString())).FirstOrDefault());
        }
예제 #18
0
        /// <summary>
        /// 获取所有子节点URL数据集
        /// </summary>
        /// <param name="parentGuid">父节点</param>
        /// <returns></returns>
        public DataSet GetSubUrlData(string parentGuid)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"WITH cte AS(
  SELECT *,0 AS LEVEL FROM [Url_Data] WHERE ParentGUID is null
  UNION ALL 
  SELECT a.*,b.LEVEL+1 AS LEVEL FROM [Url_Data] a,cte b WHERE a.ParentGUID=b.GUID
)
SELECT * FROM cte WHERE UserAuthentication=1");
            s.Parameters.Add(new SqlParameter("@parentGuid", parentGuid));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #19
0
        /// <summary>
        /// 获取地址和地址用户组关联信息
        /// </summary>
        /// <param name="userGroupGuid"></param>
        /// <param name="urlGuid"></param>
        /// <param name="parentGuid"></param>
        /// <param name="forbidden"></param>
        /// <returns></returns>
        public DataSet GetUrlAndUserGroupLink(string userGroupGuid, string urlGuid, string parentGuid, string forbidden)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"SELECT  a.GUID AS Url_Guid ,
        a.Url ,
        a.UrlCode ,
        a.UrlName ,
        a.UrlParams ,
        UserAuthentication ,
        a.Show ,
        b.Forbidden ,
        b.PriorityLevel ,
        b.FK_UserGroup_Type AS UserGroup_Guid
FROM    dbo.Url_Data a
        LEFT JOIN dbo.Url_Data_UserGroup_Type_Link b ON a.GUID = b.FK_Url_Data
                                                        AND b.FK_UserGroup_Type = @UserGroupGuid
");
            s.Header.Append(@" where a.UserAuthentication=1");
            if (parentGuid == null)
            {
                s.Header.Append(@" AND ParentGUID IS NULL");
                s.Parameters.Add(new SqlParameter("@parentGuid", parentGuid));
            }
            else if (!String.IsNullOrEmpty(parentGuid))
            {
                s.Header.Append(@" AND a.ParentGUID=@parentGuid");
                s.Parameters.Add(new SqlParameter("@parentGuid", parentGuid));
            }

            if (!String.IsNullOrEmpty(urlGuid))
            {
                s.Header.Append(@" AND a.GUID=@urlGuid");
                s.Parameters.Add(new SqlParameter("@urlGuid", urlGuid));
            }

            switch (forbidden)
            {
            case "-1": s.Header.Append(@" AND b.Forbidden IS NULL"); break;

            case "0": s.Header.Append(@" AND b.Forbidden=1"); break;

            case "1": s.Header.Append(@" AND b.Forbidden=0"); break;

            default: break;
            }
            s.Parameters.Add(new SqlParameter("@UserGroupGuid", userGroupGuid));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #20
0
        /// <summary>
        /// 导入表Module_Data
        /// </summary>
        public void ImportUrl(string values)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(@"INSERT INTO [Url_Data]
           ([GUID]
           ,[Url]
           ,[UrlCode]
           ,[UrlParams]
           ,[UrlName]
           ,[UrlIndex]
           ,[UserAuthentication]
           ,[Show]) " + values);
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #21
0
        /// <summary>
        /// 获取Module_Data数据
        /// </summary>
        public DataSet SelectUrlData(UrlDataModel model)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@" FROM [Url_Data] ");
            s.Select.Append(@" SELECT [GUID]
                                      ,[ParentGUID]
                                      ,[Url]
                                      ,[UrlCode]
                                      ,[UrlParams]
                                      ,[UrlName]
                                      ,[UserAuthentication]
                                      ,[Show]");
            s.Where.Append(@" where 1=1 ");
            if (!string.IsNullOrEmpty(model.GUID))
            {
                s.Where.Append(@" and [GUID]=@GUID ");
                s.Parameters.Add(new SqlParameter("@GUID", model.GUID));
            }
            if (!string.IsNullOrEmpty(model.UrlName))
            {
                s.Where.Append(@" and [UrlName] like @urlName escape '\' ");
                s.Parameters.Add(new SqlParameter("@urlName", string.Format("%{0}%", DatabaseUtil.FormatSqlParameterValue(model.UrlName.Trim()))));
            }
            if (!string.IsNullOrEmpty(model.Url))
            {
                s.Where.Append(@" and [Url] like @url escape '\' ");
                s.Parameters.Add(new SqlParameter("@url", string.Format("%{0}%", DatabaseUtil.FormatSqlParameterValue(model.Url.Trim()))));
            }
            if (model.UserAuthentication != null)
            {
                s.Where.Append(@" and [UserAuthentication]=@userAuthentication ");
                s.Parameters.Add(new SqlParameter("@userAuthentication", model.UserAuthentication));
            }
            if (model.Show != null)
            {
                s.Where.Append(@" and [Show]=@show ");
                s.Parameters.Add(new SqlParameter("@show", model.Show));
            }
            s.OrderBy.Append(@" order by [UrlIndex]");
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #22
0
        /// <summary>
        /// 查询用户组的所有用户。
        /// </summary>
        /// <param name="userGroup_GUID">用户组GUID。</param>
        /// <param name="userAccount">用户帐号</param>
        /// <param name="userName">用户名。</param>
        /// <param name="ban">是否被禁止</param>
        /// <param name="isBelongto">是否属于</param>
        /// <returns>用户和用户组信息数据集。</returns>
        public DataSet SelectGroupUsersData(string userGroup_GUID, string userAccount, string userName, bool?ban, bool isBelongto)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@" from [User_Data] ");
            s.Select.Append(@"select [User_Data].[GUID],[User_Data].[Account],[User_Data].[UserName],[User_Data].[Ban] ");

            s.Where.Append(@" where 1=1 ");
            if (!string.IsNullOrEmpty(userGroup_GUID))
            {
                s.Where.Append(isBelongto
                                   ? @" AND GUID IN (SELECT [FK_User_Data] FROM [User_Data_UserGroup_Type_Link] where FK_UserGroup_Type=@userGroup_GUID)"
                                   : @" AND GUID NOT IN (SELECT [FK_User_Data] FROM [User_Data_UserGroup_Type_Link] where FK_UserGroup_Type=@userGroup_GUID)");
                s.Parameters.Add(new SqlParameter("@userGroup_GUID", userGroup_GUID));
            }
            if (ban != null)
            {
                s.Where.Append(@" AND [User_Data].[Ban] =@ban ");
                s.Parameters.Add(new SqlParameter("@ban", ban));
            }


            if (!string.IsNullOrEmpty(userName))
            {
                s.Where.Append(@" AND [User_Data].[UserName] LIKE @userName escape '\' ");
                s.Parameters.Add(new SqlParameter("@userName", string.Format("%{0}%",
                                                                             DatabaseUtil.FormatSqlParameterValue(userName).
                                                                             ToLower())));
            }
            if (!string.IsNullOrEmpty(userAccount))
            {
                s.Where.Append(@" AND [User_Data].[Account] LIKE @userAccount escape '\' ");
                s.Parameters.Add(new SqlParameter("@userAccount", string.Format("%{0}%",
                                                                                DatabaseUtil.FormatSqlParameterValue(userAccount).
                                                                                ToLower())));
            }
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #23
0
        /// <summary>
        /// 保存权限分配信息
        /// </summary>
        /// <param name="urlGuid">地址</param>
        /// <param name="userGroupGuid">用户组</param>
        /// <param name="proirotyLevel">优先级</param>
        /// <param name="type">是否禁止-1:未分配0:禁止1:允许</param>
        public void SavePermission(string urlGuid, string userGroupGuid, int proirotyLevel, string type)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.Header.Append(
                @" IF @ForbiddenType='-1'
BEGIN
   DELETE FROM dbo.Url_Data_UserGroup_Type_Link WHERE FK_Url_Data=@UrlGuid AND FK_UserGroup_Type=@UserGroupGuid
END
ELSE
BEGIN
   IF EXISTS(SELECT * FROM dbo.Url_Data_UserGroup_Type_Link WHERE FK_Url_Data=@UrlGuid AND FK_UserGroup_Type=@UserGroupGuid)
   BEGIN
      UPDATE dbo.Url_Data_UserGroup_Type_Link SET Forbidden=@ForbiddenType,PriorityLevel=@ProirotyLevel WHERE FK_Url_Data=@UrlGuid AND FK_UserGroup_Type=@UserGroupGuid
   END
   ELSE
   BEGIN
      INSERT dbo.Url_Data_UserGroup_Type_Link
              ( FK_Url_Data ,
                FK_UserGroup_Type ,
                Forbidden ,
                PriorityLevel
              )
      VALUES  ( @UrlGuid ,
                @UserGroupGuid ,
                @ForbiddenType ,
                @ProirotyLevel
              )
   END
END");
            s.Parameters.Add(new SqlParameter("@UrlGuid", urlGuid));
            s.Parameters.Add(new SqlParameter("@UserGroupGuid", userGroupGuid));
            s.Parameters.Add(new SqlParameter("@ProirotyLevel", proirotyLevel));
            s.Parameters.Add(new SqlParameter("@ForbiddenType", type));
            DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s);
        }
예제 #24
0
        /// <summary>
        /// 保存用户组信息。
        /// </summary>
        /// <param name="userGroup_GUID">用户组GUID。</param>
        /// <param name="userGroupName">用户组名。</param>
        /// <param name="type">保存方式add:增加 update:更新 delete:删除</param>
        /// <returns>用户和用户组信息数据集。</returns>
        public DataSet SaveUserGroupData(string userGroup_GUID = null, string userGroupName = null, string type = "add")
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            if ("add".Equals(type.Trim()))
            {
                s.Header.Append(@"insert into [UserGroup_Type] ([GUID],[UserGroupName]) values (@GUID,@UserGroupName)");
            }
            else if ("update".Equals(type.Trim()))
            {
                s.Header.Append(@"update [UserGroup_Type] set [UserGroupName] = @UserGroupName where GUID = @GUID");
            }
            else
            {
                s.Header.Append(@" delete from [User_Data_UserGroup_Type_Link] where FK_UserGroup_Type = @GUID");
                s.Header.Append(@" delete from [Url_Data_UserGroup_Type_Link] where FK_UserGroup_Type = @GUID");
                s.Header.Append(@" delete from [UserGroup_Type] where GUID = @GUID");
            }
            s.Header.Append(@" select * from [UserGroup_Type]");
            s.Parameters.Add(new SqlParameter("@UserGroupName", userGroupName));
            s.Parameters.Add(new SqlParameter("@GUID", userGroup_GUID));
            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }
예제 #25
0
        /// <summary>
        /// 查询url信息。
        /// </summary>
        /// <param name="guid">urlId。</param>
        /// <param name="parentGuid"></param>
        /// <param name="url">url地址。</param>
        /// <param name="urlCode">url代码。</param>
        /// <param name="urlName">url名称。</param>
        /// <param name="userAuthentication">是否验证用户。</param>
        /// <param name="show">是否显示。</param>
        /// <returns>url信息数据集。</returns>
        public DataSet Select_Url_Data(string guid = null, string parentGuid = "", string url = null, string urlCode = null, string urlName = null, int userAuthentication = -1, int show = -1)
        {
            var s = DatabaseUtil.SelectBuilder.GetInstance();

            s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
            s.From.Append(@"
from [Url_Data] ");
            s.Select.Append(@"
select [GUID] as [Url_GUID]
      ,[ParentGUID]
      ,[Url]
      ,[UrlCode]
      ,[UrlParams]
      ,[UrlName]
      ,[UrlIndex]
      ,[UserAuthentication]
      ,[Show] ");
            s.Where.Append(@"
where 1=1");

            if (!string.IsNullOrEmpty(guid))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[GUID]) = @GUID ");
                s.Parameters.Add(new SqlParameter("@GUID", guid));
            }

            if (parentGuid == null)
            {
                s.Where.Append(@"
    and [Url_Data].[ParentGUID] is null ");
                s.Parameters.Add(new SqlParameter("@parentGuid", parentGuid));
            }
            else if (!String.IsNullOrEmpty(parentGuid))
            {
                s.Where.Append(@"
    and [Url_Data].[ParentGUID]= @parentGuid");
                s.Parameters.Add(new SqlParameter("@parentGuid", parentGuid));
            }

            if (!string.IsNullOrEmpty(url))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[Url]) = Lower(@Url) ");
                s.Parameters.Add(new SqlParameter("@Url", url));
            }

            if (!string.IsNullOrEmpty(urlCode))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[UrlCode]) = Lower(@UrlCode) ");
                s.Parameters.Add(new SqlParameter("@UrlCode", urlCode));
            }

            if (!string.IsNullOrEmpty(urlName))
            {
                s.Where.Append(@"
    and Lower([Url_Data].[UrlName]) like @UrlName escape '\' ");
                s.Parameters.Add(new SqlParameter("@UrlName", string.Format("%{0}%", DatabaseUtil.FormatSqlParameterValue(urlName).ToLower())));
            }

            if (userAuthentication > -1)
            {
                s.Where.Append(@"
    and [Url_Data].[UserAuthentication] = @UserAuthentication ");
                s.Parameters.Add(new SqlParameter("@UserAuthentication", userAuthentication > 0 ? 1 : 0));
            }

            if (show > -1)
            {
                s.Where.Append(@"
    and [Url_Data].[Show] = @Show ");
                s.Parameters.Add(new SqlParameter("@Show", show > 0 ? 1 : 0));
            }

            return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
        }