// use ordinals to speed up access to DataReader // links: // docLink: http://sql2x.org/documentationLink/327451c3-64a8-4de8-b359-76742d634497 // parameters: // reader: IDataReader from SQLClient public void Populate(IDataReader reader, ProductSearchByCategoryDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.ProductId)) { ProductId = reader.GetGuid(ordinals.ProductId); } if (!reader.IsDBNull(ordinals.ProductName)) { ProductName = reader.GetString(ordinals.ProductName); } if (!reader.IsDBNull(ordinals.Identifier)) { Identifier = reader.GetString(ordinals.Identifier); } if (!reader.IsDBNull(ordinals.Value)) { Value = reader.GetString(ordinals.Value); } if (!reader.IsDBNull(ordinals.ProductInfoValue)) { ProductInfoValue = reader.GetString(ordinals.ProductInfoValue); } if (!reader.IsDBNull(ordinals.ProductImageTypeRcd)) { ProductImageTypeRcd = reader.GetString(ordinals.ProductImageTypeRcd); } if (!reader.IsDBNull(ordinals.ProductImageTypeName)) { ProductImageTypeName = reader.GetString(ordinals.ProductImageTypeName); } if (!reader.IsDBNull(ordinals.Image)) { Image = (System.Byte[])reader[ordinals.Image]; } }
/// <summary>Search products by category</summary> /// <cardinality>Many</cardinality> public List <ProductSearchByCategoryData> ProductSearchByCategory( Guid productCategoryId, bool onParent ) { var ret = new List <ProductSearchByCategoryData>(); string sql = @" select p.product_id ,p.product_name ,( select top 1 pid.identifier from product_identifier as pid where pid.product_id = p.product_id order by pid.date_time ) as identifier ,( select top 1 pa.value from product_attribute as pa where pa.product_id = p.product_id order by pa.date_time ) as value ,( select top 1 pi.product_info_value from product_info as pi where pi.product_id = p.product_id order by pi.date_time ) as product_info_value ,( select top 1 pi.product_image_type_rcd from product_image as pi where pi.product_id = p.product_id order by pi.date_time ) as product_image_type_rcd ,( select top 1 pi.product_image_type_rcd from product_image as pi inner join product_image_type_ref as pit on pit.product_image_type_rcd = pi.product_image_type_rcd where pi.product_id = p.product_id order by pi.date_time ) as product_image_type_name ,( select top 1 pi.image from product_image as pi where pi.product_id = p.product_id order by pi.date_time ) as image from product as p inner join product_category_mapping as pcm on pcm.product_id = p.product_id inner join product_category as pc on pc.product_category_id = pcm.product_category_id --realwhere where pcm.product_category_id = @product_category_id and p.product_became_id is null "; if (onParent) { sql += " or pc.product_category_parent_id = @product_category_id\r\n"; } sql += " order by p.product_name, identifier\r\n"; using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) { conn.Open(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@product_category_id", SqlDbType.UniqueIdentifier).Value = productCategoryId; IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); var ordinals = new ProductSearchByCategoryDataOrdinals(reader); while (reader.Read()) { var data = new ProductSearchByCategoryData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }