public static IList <ProductEntity> GetByVendor(string vendorId)
{
var sql = string.Format(@"SELECT * FROM products WHERE id in
(
SELECT product_id FROM vendor_products WHERE vendor_id = @p_vendor_id
)", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_vendor_id", DbType.String, vendorId);
var list = new List <ProductEntity>();
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var product = new ProductEntity();
product.Init(reader);
list.Add(product);
}
}
return(list);
}
public static IList <ProductEntity> Get(IList <string> ids)
{
var list = new List <ProductEntity>();
if (ids == null || ids.Count == 0)
{
return(list);
}
var sql = string.Format("SELECT {0} FROM products WHERE id in ('{1}')", COLUMN_SQL, string.Join("','", ids));
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
using (IDataReader reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new ProductEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public async Task CreateAsync(Product product)
{
var entity = new ProductEntity
{
Name = product.Name,
Description = product.Description,
Price = product.Price,
Image = product.Image
};
await _productCollection.CreateAsync(entity.Init());
}
public static IList <ProductEntity> Query(string condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <ProductEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "name";
}
var sql = string.Format(@"SELECT {0} FROM products WHERE 1=1{1}", COLUMN_SQL, GetConditionSql(condition));
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + COLUMN_SQL + @"
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize ";
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_condition", DbType.String, "%" + condition + "%");
db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize);
using (IDataReader reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new ProductEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public static ProductEntity GetByBarcode(string barcode)
{
var sql = string.Format("SELECT {0} FROM products WHERE barcode=@p_barcode", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_barcode", DbType.String, barcode);
using (IDataReader reader = db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new ProductEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public static IList <ProductEntity> Query()
{
var sql = string.Format("SELECT {0} FROM products ORDER BY name", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
var list = new List <ProductEntity>();
using (IDataReader reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new ProductEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}