// 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, CategoryFindDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.ProductCategoryCode)) { ProductCategoryCode = reader.GetString(ordinals.ProductCategoryCode); } if (!reader.IsDBNull(ordinals.ProductName)) { ProductName = reader.GetString(ordinals.ProductName); } if (!reader.IsDBNull(ordinals.ProductCategoryId)) { ProductCategoryId = reader.GetGuid(ordinals.ProductCategoryId); } if (!reader.IsDBNull(ordinals.ProductId)) { ProductId = reader.GetGuid(ordinals.ProductId); } }
/// <summary>Find categories</summary> /// <cardinality>Many</cardinality> public List <CategoryFindData> CategoryFind(string findWhat) { var ret = new List <CategoryFindData>(); string sql = @" select pc.product_category_code ,p.product_name ,pc.product_category_id ,p.product_id from product_category as pc inner join product_category_mapping as pcm on pcm.product_category_id = pc.product_category_id inner join product as p on p.product_id = pcm.product_id and p.product_became_id is null where pc.product_category_code like '%' + @find_what + '%' or p.product_name like '%' + @find_what + '%' "; using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) { conn.Open(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@find_what", SqlDbType.VarChar, 4).Value = findWhat; IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); var ordinals = new CategoryFindDataOrdinals(reader); while (reader.Read()) { var data = new CategoryFindData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }