/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(AdvertiseModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update HQ_Advertise set ");
strSql.Append("AdType=@AdType,");
strSql.Append("LinkPic=@LinkPic,");
strSql.Append("LinkData=@LinkData,");
strSql.Append("LinkType=@LinkType,");
strSql.Append("Status=@Status,");
strSql.Append("CreateTime=@CreateTime,");
strSql.Append("BeginTime=@BeginTime,");
strSql.Append("EndTime=@EndTime,");
strSql.Append("SortNum=@SortNum,");
strSql.Append("Title=@Title,");
strSql.Append("Remark=@Remark,");
strSql.Append("PlatType=@PlatType");
strSql.Append(" where AdId=@AdId");
SqlParameter[] parameters =
{
new SqlParameter("@AdType", SqlDbType.SmallInt, 2),
new SqlParameter("@LinkPic", SqlDbType.VarChar, 300),
new SqlParameter("@LinkData", SqlDbType.VarChar, 400),
new SqlParameter("@LinkType", SqlDbType.SmallInt, 2),
new SqlParameter("@Status", SqlDbType.TinyInt, 1),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@SortNum", SqlDbType.Int, 4),
new SqlParameter("@Title", SqlDbType.VarChar, 150),
new SqlParameter("@Remark", SqlDbType.VarChar, 150),
new SqlParameter("@PlatType", SqlDbType.SmallInt, 2),
new SqlParameter("@AdId", SqlDbType.Int, 4)
};
parameters[0].Value = model.AdType;
parameters[1].Value = model.LinkPic;
parameters[2].Value = model.LinkData;
parameters[3].Value = model.LinkType;
parameters[4].Value = model.Status;
parameters[5].Value = model.CreateTime;
parameters[6].Value = model.BeginTime;
parameters[7].Value = model.EndTime;
parameters[8].Value = model.SortNum;
parameters[9].Value = model.Title;
parameters[10].Value = model.Remark;
parameters[11].Value = model.PlatType;
parameters[12].Value = model.AdId;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 新增广告信息
/// </summary>
/// <param name="ad"></param>
/// <returns></returns>
public static int InsertAdvertiseDetail(AdvertiseModel ad, IEnumerable <AdProductModel> products)
{
var conn = ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString;
var AdvertiseID = 0;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbhelper = new SqlDbHelper(conn))
{
dbhelper.BeginTransaction();
var cmd = new SqlCommand("Gungnir..[Advertise_InsertAdvertiseDetail]");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AdColumnID", ad.AdColumnID);
cmd.Parameters.AddWithValue("@AdName", ad.AdName);
cmd.Parameters.AddWithValue("@BeginDateTime", ad.BeginDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", ad.EndDateTime);
cmd.Parameters.AddWithValue("@Position", ad.Position);
cmd.Parameters.AddWithValue("@Image", ad.Image);
cmd.Parameters.AddWithValue("@State", ad.State);
cmd.Parameters.AddWithValue("@Url", ad.Url);
cmd.Parameters.AddWithValue("@bgColor", ad.bgColor);
cmd.Parameters.Add(new SqlParameter
{
ParameterName = "@Result",
DbType = DbType.Int32,
Direction = ParameterDirection.Output
});
dbhelper.ExecuteNonQuery(cmd);
AdvertiseID = Convert.ToInt32(cmd.Parameters["@Result"].Value);
if (AdvertiseID > 0)
{
if (products != null && products.Count() > 0)
{
foreach (var p in products)
{
p.AdvertiseID = AdvertiseID;
var result = InsertProducts(dbhelper, p);
if (result < 0)
{
dbhelper.Rollback();
return(-2);
}
}
}
}
else
{
dbhelper.Rollback();
return(-1);
}
dbhelper.Commit();
return(AdvertiseID);
}
}
public async Task <ActionResult> Advertisement()
{
var model = new AdvertiseModel
{
Id = Guid.NewGuid().ToString(),
StartDate = DateTime.Today
};
return(View(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(AdvertiseModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into HQ_Advertise(");
strSql.Append("AdType,LinkPic,LinkData,LinkType,Status,CreateTime,BeginTime,EndTime,SortNum,Title,Remark,PlatType)");
strSql.Append(" values (");
strSql.Append("@AdType,@LinkPic,@LinkData,@LinkType,@Status,@CreateTime,@BeginTime,@EndTime,@SortNum,@Title,@Remark,@PlatType)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@AdType", SqlDbType.SmallInt, 2),
new SqlParameter("@LinkPic", SqlDbType.VarChar, 300),
new SqlParameter("@LinkData", SqlDbType.VarChar, 400),
new SqlParameter("@LinkType", SqlDbType.SmallInt, 2),
new SqlParameter("@Status", SqlDbType.TinyInt, 1),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@BeginTime", SqlDbType.DateTime),
new SqlParameter("@EndTime", SqlDbType.DateTime),
new SqlParameter("@SortNum", SqlDbType.Int, 4),
new SqlParameter("@Title", SqlDbType.VarChar, 150),
new SqlParameter("@Remark", SqlDbType.VarChar, 150),
new SqlParameter("@PlatType", SqlDbType.SmallInt, 2)
};
parameters[0].Value = model.AdType;
parameters[1].Value = model.LinkPic;
parameters[2].Value = model.LinkData;
parameters[3].Value = model.LinkType;
parameters[4].Value = model.Status;
parameters[5].Value = model.CreateTime;
parameters[6].Value = model.BeginTime;
parameters[7].Value = model.EndTime;
parameters[8].Value = model.SortNum;
parameters[9].Value = model.Title;
parameters[10].Value = model.Remark;
parameters[11].Value = model.PlatType;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public async Task <ActionResult> SaveAdvertisement(AdvertiseModel model)
{
if (model.MainImage == null)
{
return(View("Advertisement"));
}
using (var readStream = model.MainImage.OpenReadStream())
{
var result = await _fileUploader.UploadFileAsync($@"Advertisement/{model.MainImage.FileName}", readStream)
.ConfigureAwait(false);
if (!result)
{
throw new Exception($"Could not upload the image to file repository. Please see the logs for details.");
}
}
var advertisements = new List <AdvertiseModel>();
var advertisementsJson = await _fileUploader.GetFileFromS3(@"Advertisement/Advertisements.json");
if (!string.IsNullOrEmpty(advertisementsJson))
{
advertisements = DeserializeObject <List <AdvertiseModel> >(advertisementsJson);
}
model.FileName = model.MainImage.FileName;
model.MainImage = null;
advertisements.Add(model);
var jsonString = JsonSerializer.Serialize(advertisements);
await _fileUploader.SaveFileAsync($@"Advertisement/Advertisements.json", jsonString);
return(RedirectToAction("Index", "Home"));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public AdvertiseModel GetModel(int AdId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from HQ_Advertise ");
strSql.Append(" where AdId=@AdId");
SqlParameter[] parameters =
{
new SqlParameter("@AdId", SqlDbType.Int, 4)
};
parameters[0].Value = AdId;
AdvertiseModel model = new AdvertiseModel();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 更新广告信息
/// </summary>
/// <param name="AdvertiseModel"></param>
/// <returns></returns>
public static int UpdateAdvertise(AdvertiseModel AdvertiseModel, IEnumerable <AdProductModel> products, out int result)
{
var conn = ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbhelper = new SqlDbHelper(conn))
{
dbhelper.BeginTransaction();
var cmd = new SqlCommand("Gungnir..[Advertise_UpdateAdvertise]");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AdColumnID", AdvertiseModel.AdColumnID);
cmd.Parameters.AddWithValue("@AdName", AdvertiseModel.AdName);
cmd.Parameters.AddWithValue("@PKID", AdvertiseModel.PKID);
cmd.Parameters.AddWithValue("@BeginDateTime", AdvertiseModel.BeginDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", AdvertiseModel.EndDateTime);
cmd.Parameters.AddWithValue("@Position", AdvertiseModel.Position);
cmd.Parameters.AddWithValue("@Image", AdvertiseModel.Image);
cmd.Parameters.AddWithValue("@State", AdvertiseModel.State);
cmd.Parameters.AddWithValue("@Url", AdvertiseModel.Url);
cmd.Parameters.AddWithValue("@bgColor", AdvertiseModel.bgColor);
cmd.Parameters.Add(new SqlParameter
{
ParameterName = "@Result",
DbType = DbType.Int32,
Direction = ParameterDirection.Output
});
dbhelper.ExecuteNonQuery(cmd);
result = Convert.ToInt32(cmd.Parameters["@Result"].Value);
if (result > 0)
{
if (products != null && products.Count() > 0)
{
if (DeleteProducts(AdvertiseModel.AdColumnID, AdvertiseModel.PKID.ToString()) > 0)
{
foreach (var p in products)
{
p.AdvertiseID = AdvertiseModel.PKID;
if (InsertProducts(dbhelper, p) <= 0)
{
dbhelper.Rollback();
return(-2);
}
}
}
else
{
dbhelper.Rollback();
return(-3);
}
}
else if (AdvertiseModel.AdColumnID.Contains("F_11"))
{
dbhelper.Rollback();
result = -98;
}
}
else
{
dbhelper.Rollback();
return(-1);
}
dbhelper.Commit();
return(result);
}
}
/// <summary>
/// 新增广告信息
/// </summary>
/// <param name="ad"></param>
/// <returns></returns>
public static int InsertAdvertiseDetail(AdvertiseModel ad, IEnumerable <AdProductModel> products)
{
return(DALWebSiteHomeAd.InsertAdvertiseDetail(ad, products));
}
/// <summary>
/// 更新广告信息
/// </summary>
/// <param name="AdvertiseModel"></param>
/// <returns></returns>
public static int UpdateAdvertise(AdvertiseModel AdvertiseModel, IEnumerable <AdProductModel> products, out int result)
{
return(DALWebSiteHomeAd.UpdateAdvertise(AdvertiseModel, products, out result));
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(AdvertiseModel model)
{
return(dal.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(AdvertiseModel model)
{
return(dal.Add(model));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public AdvertiseModel DataRowToModel(DataRow row)
{
AdvertiseModel model = new AdvertiseModel();
if (row != null)
{
if (row["AdId"] != null && row["AdId"].ToString() != "")
{
model.AdId = int.Parse(row["AdId"].ToString());
}
if (row["AdType"] != null && row["AdType"].ToString() != "")
{
model.AdType = int.Parse(row["AdType"].ToString());
}
if (row["LinkPic"] != null)
{
model.LinkPic = row["LinkPic"].ToString();
}
if (row["LinkData"] != null)
{
model.LinkData = row["LinkData"].ToString();
}
if (row["LinkType"] != null && row["LinkType"].ToString() != "")
{
model.LinkType = int.Parse(row["LinkType"].ToString());
}
if (row["Status"] != null && row["Status"].ToString() != "")
{
model.Status = int.Parse(row["Status"].ToString());
}
if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
{
model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
}
if (row["BeginTime"] != null && row["BeginTime"].ToString() != "")
{
model.BeginTime = DateTime.Parse(row["BeginTime"].ToString());
}
if (row["EndTime"] != null && row["EndTime"].ToString() != "")
{
model.EndTime = DateTime.Parse(row["EndTime"].ToString());
}
if (row["SortNum"] != null && row["SortNum"].ToString() != "")
{
model.SortNum = int.Parse(row["SortNum"].ToString());
}
if (row["Title"] != null)
{
model.Title = row["Title"].ToString();
}
if (row["Remark"] != null)
{
model.Remark = row["Remark"].ToString();
}
if (row["PlatType"] != null && row["PlatType"].ToString() != "")
{
model.PlatType = int.Parse(row["PlatType"].ToString());
}
}
return(model);
}
