// 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, ProductSearchByCategoryType2DataOrdinals 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.Gtin13)) { Gtin13 = reader.GetString(ordinals.Gtin13); } if (!reader.IsDBNull(ordinals.Hn)) { Hn = reader.GetString(ordinals.Hn); } if (!reader.IsDBNull(ordinals.Color)) { Color = reader.GetString(ordinals.Color); } }
/// <summary>Search products by category, result set 2</summary> /// <cardinality>Many</cardinality> public List <ProductSearchByCategoryType2Data> ProductSearchByCategoryType2( Guid productCategoryId, bool onParent ) { var ret = new List <ProductSearchByCategoryType2Data>(); 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 and pid.product_identifier_rcd = 'gtin13' ) as gtin13 ,( select top 1 pid.identifier from product_identifier as pid where pid.product_id = p.product_id and pid.product_identifier_rcd = 'hn' ) as hn ,( select top 1 pa.value from product_attribute as pa where pa.product_id = p.product_id and pa.product_attribute_rcd = 'color' ) as color 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, gtin13\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 ProductSearchByCategoryType2DataOrdinals(reader); while (reader.Read()) { var data = new ProductSearchByCategoryType2Data(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }