/// <summary>
/// This method allows us to select a package from database. We use the NullableTypes to make the correspondance between
/// nullable int, decimal and double types in database and our own objects
/// </summary>
/// <param name="colProductId">id's of package searched</param>
/// <returns>A package Object if id matches with datas in database, null if not</returns>
public CollateralProduct SelectCollateralProduct(int colProductId)
{
const string sqlText = @"SELECT
[name]
,[desc]
,[deleted]
FROM CollateralProducts
WHERE id = @id";
CollateralProduct colProduct = new CollateralProduct();
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
{
cmd.AddParam("@id", colProductId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
reader.Read();
colProduct.Id = colProductId;
colProduct.Name = reader.GetString("name");
colProduct.Description = reader.GetString("desc");
colProduct.Delete = reader.GetBool("deleted");
reader.Dispose();
}
}
List <CollateralProperty> properties = new List <CollateralProperty>();
const string sqlPropertyText = @"SELECT
id
,type_id
,[name]
,[desc]
FROM CollateralProperties
WHERE product_id = @product_id";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlPropertyText, connection))
{
cmd.AddParam("@product_id", colProduct.Id);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(null);
}
while (reader.Read())
{
CollateralProperty collateralProperty = new CollateralProperty();
collateralProperty.Id = reader.GetInt("id");
collateralProperty.Type = (OCollateralPropertyTypes)Enum.ToObject(typeof(OCollateralPropertyTypes),
reader.GetInt("type_id"));
collateralProperty.Name = reader.GetString("name");
collateralProperty.Description = reader.GetString("desc");
if (collateralProperty.Type == OCollateralPropertyTypes.Collection)
{
List <string> propertyList = new List <string>();
const string sqlListText = @"SELECT [value]
FROM CollateralPropertyCollections
WHERE property_id = @property_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectList = new OpenCbsCommand(sqlListText, conn))
{
selectList.AddParam("@property_id", collateralProperty.Id);
using (OpenCbsReader listReader = selectList.ExecuteReader())
{
if (listReader.Empty)
{
return(null);
}
while (listReader.Read())
{
propertyList.Add(listReader.GetString("value"));
}
collateralProperty.Collection = propertyList;
}
}
}
properties.Add(collateralProperty);
}
colProduct.Properties = properties;
}
}
return(colProduct);
}