public List<PersonInfoDto> GetAllPersons()
{
string sql = @"SELECT p.[BusinessEntityID]
,[PersonType]
,[Title]
,[FirstName]
,[LastName]
,e.[BirthDate] AS [EmployeeBrithDate]
,e.[Gender] AS [EmployeeGender]
FROM [AdventureWorks2014].[Person].[Person] p
LEFT JOIN [AdventureWorks2014].[HumanResources].[Employee] e
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY p.[ModifiedDate];";
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
List<PersonInfoDto> results = new List<PersonInfoDto>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PersonInfoDto personInfo = new PersonInfoDto();
personInfo.BusinessEntityID = Convert.ToInt32(reader["BusinessEntityID"]);
personInfo.EmployeeBrithDate = this.MapToDateTime(reader, "EmployeeBrithDate");
personInfo.EmployeeGender = this.MapToString(reader, "EmployeeGender");
personInfo.FirstName = this.MapToString(reader, "FirstName");
personInfo.LastName = this.MapToString(reader, "LastName");
personInfo.PersonType = this.MapToString(reader, "PersonType");
personInfo.Title = this.MapToString(reader, "Title");
results.Add(personInfo);
}
}
return results;
}
}
private void MapperConfigure(MapperInstance cfd)
{
cfd.AddMap<Person, PersonInfoDto>(src =>
{
PersonInfoDto dst = new PersonInfoDto();
dst.InjectFrom(src);
dst.EmployeeBrithDate = src.Employee?.BirthDate;
return dst;
});
cfd.AddMap<EmailAddress, EmailDto>(src =>
{
EmailDto dst = new EmailDto();
dst.InjectFrom(src);
dst.EmailAddress = src.EmailAddress1;
return dst;
});
cfd.AddMap<ShipMethod, ShipMethodDto>(src =>
{
ShipMethodDto dst = new ShipMethodDto();
dst.InjectFrom(src);
return dst;
});
cfd.AddMap<ProductListPriceHistory, ProductListPriceHistoryDto>(src =>
{
ProductListPriceHistoryDto dst = new ProductListPriceHistoryDto();
dst.InjectFrom(src);
return dst;
});
cfd.AddMap<Product, ProductDto>(src =>
{
ProductDto dst = new ProductDto();
dst.InjectFrom(src);
dst.ProductListPriceHistories = new List<ProductListPriceHistoryDto>();
foreach (ProductListPriceHistory item in src.ProductListPriceHistories)
{
ProductListPriceHistoryDto itemDto = new ProductListPriceHistoryDto();
itemDto.InjectFrom(item);
dst.ProductListPriceHistories.Add(itemDto);
}
return dst;
});
cfd.AddMap<ProductModel, ProductModelDto>(src =>
{
ProductModelDto dst = new ProductModelDto();
dst.InjectFrom(src);
return dst;
});
cfd.AddMap<Product, Product2Dto>(src =>
{
Product2Dto dst = new Product2Dto();
dst.InjectFrom(src);
dst.ProductModel = new ProductModelDto();
dst.ProductModel.InjectFrom(src.ProductModel);
dst.ProductModelID = src.ProductModel?.ProductModelID;
return dst;
});
}
public List<PersonInfoDto> GetPagedPersons(int skip, int take)
{
string sql = @"SELECT p.[BusinessEntityID]
,[PersonType]
,[Title]
,[FirstName]
,[LastName]
,e.[BirthDate] AS [EmployeeBrithDate]
,e.[Gender] AS [EmployeeGender]
FROM [AdventureWorks2014].[Person].[Person] p
LEFT JOIN [AdventureWorks2014].[HumanResources].[Employee] e
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY p.[ModifiedDate]
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY;";
SqlCommand cmd = this.connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@Skip", skip);
cmd.Parameters.AddWithValue("@Take", take);
List<PersonInfoDto> results = new List<PersonInfoDto>(take);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PersonInfoDto personInfo = new PersonInfoDto();
personInfo.BusinessEntityID = Convert.ToInt32(reader["BusinessEntityID"]);
personInfo.EmployeeBrithDate = this.MapToDateTime(reader, "EmployeeBrithDate");
personInfo.EmployeeGender = this.MapToString(reader, "EmployeeGender");
personInfo.FirstName = this.MapToString(reader, "FirstName");
personInfo.LastName = this.MapToString(reader, "LastName");
personInfo.PersonType = this.MapToString(reader, "PersonType");
personInfo.Title = this.MapToString(reader, "Title");
results.Add(personInfo);
}
}
return results;
}