public IEnumerable <VIPManageView> Query()
{
string sql = "SELECT [T_VIPManagement].[Id],[T_Membership].[UserName],[T_Company].[AbbreviateName],[T_Company].[Type],[T_Contact].[Name]," +
"[T_VIPManagement].[IsVip] FROM [T_VIPManagement] RIGHT JOIN [T_Company] ON [T_Company].[Id]=[T_VIPManagement].[Company] " +
"INNER JOIN [T_User] ON [T_Company].[Id] =[T_User].[Owner] AND [T_User].[IsAdmin]=1 " +
"INNER JOIN [T_Membership] ON [T_User].[Id]=[T_Membership].[User] " +
"INNER JOIN [T_Contact] ON [T_Company].[Contact]=[T_Contact].[Id]";
var result = new List <VIPManageView>();
using (var dbOperator = new DbOperator(Provider, ConnectionString)) {
using (var reader = dbOperator.ExecuteReader(sql)) {
while (reader.Read())
{
var view = new VIPManageView(reader.GetGuid(0));
view.UserName = reader.GetString(1);
view.AbbreviateName = reader.GetString(2);
view.Type = (CompanyType)reader.GetInt32(3);
view.Contact = reader.GetString(4);
view.IsVip = reader.GetBoolean(5);
result.Add(view);
}
}
}
return(result);
}
public IEnumerable <VIPManageView> Query(VIPManageQueryCondition condition)
{
var result = new List <VIPManageView>();
string sql = "SELECT [T_VIPManagement].[Id],[T_Membership].[UserName],[T_Company].[AbbreviateName],[T_Company].[Type],[T_Contact].[Name]," +
"[T_VIPManagement].[IsVip] FROM [T_VIPManagement] RIGHT JOIN [T_Company] ON [T_Company].[Id]=[T_VIPManagement].[Company] " +
"INNER JOIN [T_User] ON [T_Company].[Id] =[T_User].[Owner] AND [T_User].[IsAdmin]=1 " +
"INNER JOIN [T_Membership] ON [T_User].[Id]=[T_Membership].[User] " +
"INNER JOIN [T_Contact] ON [T_Company].[Contact]=[T_Contact].[Id]";
StringBuilder strWhere = new StringBuilder();
using (var dbOperator = new DbOperator(Provider, ConnectionString)) {
if (!string.IsNullOrWhiteSpace(condition.UserName))
{
strWhere.Append("[T_Membership].[UserName]=@UserName AND");
dbOperator.AddParameter("UserName", condition.UserName);
}
if (!string.IsNullOrWhiteSpace(condition.AbbreviateName))
{
strWhere.Append("[T_Company].[AbbreviateName]=@AbbreviateName AND");
dbOperator.AddParameter("AbbreviateName", condition.AbbreviateName);
}
if (condition.Type.HasValue)
{
strWhere.Append("[T_Company].[Type]=@Type");
dbOperator.AddParameter("Type", condition.Type);
}
if (condition.IsVip.HasValue)
{
strWhere.Append("[T_VIPManagement].[IsVip]=@IsVip");
dbOperator.AddParameter("IsVip", condition.IsVip.Value);
}
if (strWhere.Length > 0)
{
strWhere = strWhere.Remove(strWhere.Length - 4, 4);
sql += " WHERE" + strWhere;
}
using (var reader = dbOperator.ExecuteReader(sql)) {
while (reader.Read())
{
var view = new VIPManageView(reader.GetGuid(0));
view.UserName = reader.GetString(1);
view.AbbreviateName = reader.GetString(2);
view.Type = (CompanyType)reader.GetInt32(3);
view.Contact = reader.GetString(4);
view.IsVip = reader.GetBoolean(5);
result.Add(view);
}
}
}
return(result);
}
