/// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(ProductInfo model) {
string strSQL = "INSERT INTO Products(Title,CategoryId,Introduction,ImageUrl,CategoryIdRoute,IsDeleted,SonModel,DocumentUrl,Sort) VALUES(@Title,@CategoryId,@Introduction,@ImageUrl,@CategoryIdRoute,@IsDeleted,@SonModel,@DocumentUrl,@Sort);SELECT @@IDENTITY;";
SqlParameter[] parms = {
new SqlParameter("Id",SqlDbType.Int),
new SqlParameter("Title",SqlDbType.NVarChar),
new SqlParameter("Introduction",SqlDbType.NVarChar),
new SqlParameter("ImageUrl",SqlDbType.NVarChar),
new SqlParameter("CategoryId",SqlDbType.Int),
new SqlParameter("CategoryIdRoute",SqlDbType.NVarChar),
new SqlParameter("IsDeleted",SqlDbType.Int),
new SqlParameter("SonModel",SqlDbType.NVarChar),
new SqlParameter("DocumentUrl",SqlDbType.NVarChar),
new SqlParameter("Sort",SqlDbType.Int),
};
parms[0].Value = model.Id;
parms[1].Value = model.Title ?? string.Empty;
parms[2].Value = model.Introduction ?? string.Empty;
parms[3].Value = model.ImageUrl ?? string.Empty;
parms[4].Value = model.CategoryId;
parms[5].Value = model.CategoryIdRoute ?? string.Empty;
parms[6].Value = model.IsDeleted ? 1 : 0;
parms[7].Value = model.SonModel ?? string.Empty;
parms[8].Value = model.DocumentUrl ?? string.Empty;
parms[9].Value = model.Sort;
int id = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text,strSQL,parms));
model.Id = id;
//更新属性信息
UpdateProduct2PropValues(model);
return id;
}
/// <summary>
/// 添加或编辑
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static ProductInfo Create(ProductInfo model) {
if (model.Id == 0)
{
model.Id = ProductManage.Insert(model);
}
else {
ProductManage.Update(model);
}
return model;
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Update(ProductInfo model) {
string strSQL = "UPDATE Products SET ImageUrl = @ImageUrl ,Introduction = @Introduction, Title = @Title,IsDeleted = @IsDeleted,CategoryIdRoute = @CategoryIdRoute,SonModel = @SonModel,DocumentUrl = @DocumentUrl,Sort = @Sort,CategoryId = @CategoryId WHERE Id = @Id";
SqlParameter[] parms = {
new SqlParameter("Id",SqlDbType.Int),
new SqlParameter("Title",SqlDbType.NVarChar),
new SqlParameter("Introduction",SqlDbType.NVarChar),
new SqlParameter("ImageUrl",SqlDbType.NVarChar),
new SqlParameter("IsDeleted",SqlDbType.Int),
new SqlParameter("CategoryIdRoute",SqlDbType.NVarChar),
new SqlParameter("SonModel",SqlDbType.NVarChar),
new SqlParameter("DocumentUrl",SqlDbType.NVarChar),
new SqlParameter("Sort",SqlDbType.Int),
new SqlParameter("CategoryId",SqlDbType.Int),
};
parms[0].Value = model.Id;
parms[1].Value = model.Title ?? string.Empty;
parms[2].Value = model.Introduction ?? string.Empty;
parms[3].Value = model.ImageUrl ?? string.Empty;
parms[4].Value = model.IsDeleted ? 1 : 0;
parms[5].Value = model.CategoryIdRoute ?? string.Empty;
parms[6].Value = model.SonModel ?? string.Empty;
parms[7].Value = model.DocumentUrl ?? string.Empty;
parms[8].Value = model.Sort;
parms[9].Value = model.CategoryId;
int i = SQLPlus.ExecuteNonQuery(CommandType.Text,strSQL,parms) ;
if (i > 0)
{
//更新属性信息
UpdateProduct2PropValues(model);
}
return i;
}
/// <summary>
/// 更新产品所使用的属性以及属性值
/// </summary>
/// <param name="model"></param>
private static void UpdateProduct2PropValues(ProductInfo model)
{
//首先删除
string strSQL = "DELETE dbo.Product2PropValues WHERE ProductId = @ProductId";
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, new SqlParameter("ProductId", SqlDbType.Int) { Value = model.Id });
//在添加
strSQL = "INSERT INTO dbo.Product2PropValues(ProductId,PropId,Value) VALUES(@ProductId,@PropId,@Value)";
SqlParameter[] parms = {
new SqlParameter("ProductId",SqlDbType.Int),
new SqlParameter("PropId",SqlDbType.Int),
new SqlParameter("Value",SqlDbType.NVarChar),
};
parms[0].Value = model.Id;
foreach (var item in model.Props)
{
parms[1].Value = item.Id;
if (!string.IsNullOrEmpty(item.Value))
{
parms[2].Value = item.Value.Replace('"', '”');
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
}
}
private static ProductInfo GetByRow(DataRow dr) {
var model = new ProductInfo();
if (dr != null) {
model.CategoryId = dr.Field<int>("CategoryId");
model.CreateDateTime = dr.Field<DateTime>("CreateDateTime");
model.Id = dr.Field<int>("Id");
model.ImageUrl = dr.Field<string>("ImageUrl");
model.Introduction = dr.Field<string>("Introduction");
model.Title = dr.Field<string>("Title");
model.IsDeleted = dr.Field<bool>("IsDeleted");
model.CategoryIdRoute = dr.Field<string>("CategoryIdRoute");
model.SonModel = dr.Field<string>("SonModel");
model.Props = GetProductProps(model.Id);
model.DocumentUrl = dr.Field<string>("DocumentUrl");
model.Sort = dr.Field<int>("Sort");
}
return model;
}
public ActionResult Create(ProductInfo model,FormCollection fc) {
bool errors = false;
bool isAdd = model.Id == 0 ? true : false;
if(string.IsNullOrEmpty(model.Title)){
errors = true;
ModelState.AddModelError("Title", "Name can not be empty");
}
if(!errors && ModelState.IsValid){
//添加产品属性
foreach (var item in fc.AllKeys)
{
Match m = Regex.Match(item, @"prop_value_(\d+)", RegexOptions.IgnoreCase);
if (m.Success)
{
int propId = Utils.StrToInt(m.Groups[1].Value, 0);
if (propId > 0 && !string.IsNullOrEmpty(fc[item]))
{
model.Props.Add(new ProductPropInfo()
{
Id = propId,
Value = fc[item]
});
}
}
}
//保存
model = ProductService.Create(model);
if (isAdd)
{
ViewBag.Msg = "Add success.Continue?【<a href=\"create\">Yes</a>】 【<a href=\"list\">No</a>】";
}
else
{
ViewBag.Msg = "Update success.Continue?【<a href=\"create\">Yes</a>】 【<a href=\"list\">No</a>】";
}
}
return View(model);
}