// 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, ProductSearchByNameDataOrdinals 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 name</summary> /// <cardinality>Many</cardinality> public List <ProductSearchByNameData> ProductSearchByName( string productName ) { var ret = new List <ProductSearchByNameData>(); string sql = @" select p.product_id ,p.product_name ,pi.identifier ,pa.value ,pin.product_info_value ,pim.product_image_type_rcd ,pit.product_image_type_name ,pim.image from product as p inner join product_category_mapping as pcm on pcm.product_id = p.product_id inner join product_attribute as pa on pa.product_id = p.product_id inner join product_identifier as pi on pi.product_id = p.product_id left join product_info as pin on pin.product_id = p.product_id left join product_image as pim on pim.product_id = p.product_id left join product_image_type_ref as pit on pim.product_id = p.product_id where p.product_name like '%' + @product_name + '%' order by p.date_time "; using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) { conn.Open(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@product_name", SqlDbType.NVarChar).Value = productName.Replace("'", "''"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); var ordinals = new ProductSearchByNameDataOrdinals(reader); while (reader.Read()) { var data = new ProductSearchByNameData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }