/// <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));
}
/// <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));
}
/// <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));
}
/// <summary>
/// 获取用户组信息。
/// </summary>
/// <param name="userGroup_GUID">用户组GUID。</param>
/// <param name="userGroupName">用户组名。</param>
/// <returns>用户和用户组信息数据集。</returns>
public DataSet SelectUserGroupData(string userGroup_GUID = null, string userGroupName = null)
{
DatabaseUtil.SelectBuilder s = DatabaseUtil.SelectBuilder.GetInstance();
s.ConnectionString = DALArgs.GetInstance().CurrentConnectString;
s.From.Append(@" from [UserGroup_Type] ");
s.Select.Append(@"select[GUID],[UserGroupName]");
s.Where.Append(@"where 1=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(userGroupName))
{
s.Where.Append(@" and Lower([UserGroupName]) like @UserGroupName escape '\' ");
s.Parameters.Add(new SqlParameter("@UserGroupName", string.Format("%{0}%",
DatabaseUtil.FormatSqlParameterValue(userGroupName).
ToLower())));
}
return(DALArgs.GetInstance().CurrentDatabaseUtil.ExecuteDataAdapter(s));
}
/// <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));
}