public IEnumerable <DataTransferObject.Organization.IncomeGroupView> QueryIncomeGroup(IEnumerable <Guid> groupId)
{
var result = new List <DataTransferObject.Organization.IncomeGroupView>();
string sql = string.Format(@"SELECT
Id,Name,[Description],COUNT(TRel.Company) AS UserCount
FROM dbo.T_IncomeGroup TIncome LEFT JOIN dbo.T_IncomeGroupRelation TRel
ON TIncome.Id = TRel.IncomeGroup
WHERE TIncome.Id IN ({0})
group by Id,Name,[Description]" , groupId.Join(",", item => "'" + item.ToString() + "'"));
using (var dbOperator = new DbOperator(Provider, ConnectionString))
{
using (var reader = dbOperator.ExecuteReader(sql))
{
while (reader.Read())
{
DataTransferObject.Organization.IncomeGroupView groupView = new DataTransferObject.Organization.IncomeGroupView();
groupView.IncomeGroupId = reader.GetGuid(0);
groupView.Name = reader.GetString(1);
groupView.Description = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
groupView.UserCount = reader.GetInt32(3);
result.Add(groupView);
}
}
}
return(result);
}
public DataTransferObject.Organization.IncomeGroupView QueryIncomeGroup(Guid groupId)
{
DataTransferObject.Organization.IncomeGroupView groupView = null;
string sql = @"SELECT
Id,Name,[Description],COUNT(TRel.Company) AS UserCount
FROM dbo.T_IncomeGroup TIncome LEFT JOIN dbo.T_IncomeGroupRelation TRel
ON TIncome.Id = TRel.IncomeGroup
WHERE TIncome.Id = @i_Owner
group by Id,Name,[Description]" ;
using (var dbOperator = new DbOperator(Provider, ConnectionString))
{
dbOperator.AddParameter("i_Owner", groupId);
using (var reader = dbOperator.ExecuteReader(sql))
{
while (reader.Read())
{
groupView = new DataTransferObject.Organization.IncomeGroupView();
groupView.IncomeGroupId = reader.GetGuid(0);
groupView.Name = reader.GetString(1);
groupView.Description = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
groupView.UserCount = reader.GetInt32(3);
}
}
}
return(groupView);
}
