コード例 #1
0
        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;
            }
        }
コード例 #2
0
        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;
            });
        }
コード例 #3
0
        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;
        }