// 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);
}
}
