/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Hi.Model.BD_Promotion model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [BD_Promotion](");
strSql.Append("[CompID],[ProTitle],[Type],[ProType],[Discount],[ProInfos],[IsEnabled],[ProStartTime],[ProEndTime],[CreateUserID],[CreateDate],[ts],[dr],[modifyuser])");
strSql.Append(" values (");
strSql.Append("@CompID,@ProTitle,@Type,@ProType,@Discount,@ProInfos,@IsEnabled,@ProStartTime,@ProEndTime,@CreateUserID,@CreateDate,@ts,@dr,@modifyuser)");
strSql.Append(";select @@Identity");
SqlParameter[] parameters =
{
new SqlParameter("@CompID", SqlDbType.Int),
new SqlParameter("@ProTitle", SqlDbType.NVarChar, 200),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@ProType", SqlDbType.Int),
new SqlParameter("@Discount", SqlDbType.Decimal),
new SqlParameter("@ProInfos", SqlDbType.NVarChar, 2000),
new SqlParameter("@IsEnabled", SqlDbType.Int),
new SqlParameter("@ProStartTime", SqlDbType.DateTime),
new SqlParameter("@ProEndTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ts", SqlDbType.Char, 19),
new SqlParameter("@dr", SqlDbType.Int),
new SqlParameter("@modifyuser", SqlDbType.Int)
};
parameters[0].Value = model.CompID;
if (model.ProTitle != null)
{
parameters[1].Value = model.ProTitle;
}
else
{
parameters[1].Value = DBNull.Value;
}
parameters[2].Value = model.Type;
parameters[3].Value = model.ProType;
parameters[4].Value = model.Discount;
if (model.ProInfos != null)
{
parameters[5].Value = model.ProInfos;
}
else
{
parameters[5].Value = DBNull.Value;
}
parameters[6].Value = model.IsEnabled;
parameters[7].Value = model.ProStartTime;
parameters[8].Value = model.ProEndTime;
parameters[9].Value = model.CreateUserID;
parameters[10].Value = model.CreateDate;
parameters[11].Value = model.ts;
parameters[12].Value = model.dr;
parameters[13].Value = model.modifyuser;
return(SqlHelper.GetInt(SqlHelper.GetSingle(SqlHelper.LocalSqlServer, strSql.ToString(), parameters)));
}
/// <summary>
/// 修改促销活动（事务）
/// </summary>
/// <param name="proModel"></param>
/// <param name="proList"></param>
/// <returns></returns>
public int ProUpdate(Hi.Model.BD_Promotion proModel, List <Hi.Model.BD_PromotionDetail> proList)
{
int Id = 0;
SqlTransaction TranSaction = null;
SqlConnection Connection = new SqlConnection(LocalSqlServer);
try
{
Connection.Open();
TranSaction = Connection.BeginTransaction();
//修改促销活动主表信息
Id = UpdateProSql(proModel, Connection, TranSaction);
if (Id == 0)
{
Id = 0;
TranSaction.Rollback();
return(Id);
}
//修改促销活动时 先删除原商品信息
int delId = DelProDSql(proModel.ID, Connection, TranSaction);
//新增促销商品明细
if (proList != null && proList.Count > 0)
{
foreach (var item in proList)
{
item.ProID = proModel.ID;
int count = ProDAdd(item, Connection, TranSaction);
if (count == 0)
{
Id = 0;
TranSaction.Rollback();
}
}
}
TranSaction.Commit();
}
catch (Exception ex)
{
Id = 0;
TranSaction.Rollback();
throw ex;
}
finally
{
Connection.Close();
}
return(Id);
}
/// <summary>
/// 新增促销活动
/// </summary>
/// <param name="proModel">促销活动主信息</param>
/// <param name="proList">促销活动商品信息</param>
/// <returns></returns>
public int AddPro(Hi.Model.BD_Promotion proModel, List <Hi.Model.BD_PromotionDetail> proList)
{
int Id = 0;
SqlTransaction TranSaction = null;
SqlConnection Connection = new SqlConnection(LocalSqlServer);
try
{
Connection.Open();
TranSaction = Connection.BeginTransaction();
Id = ProAdd(proModel, Connection, TranSaction);
if (Id == 0)
{
Id = 0;
TranSaction.Rollback();
}
if (proList != null && proList.Count > 0)
{
foreach (var item in proList)
{
item.ProID = Id;
int count = ProDAdd(item, Connection, TranSaction);
if (count == 0)
{
Id = 0;
TranSaction.Rollback();
}
}
}
TranSaction.Commit();
}
catch (Exception ex)
{
Id = 0;
TranSaction.Rollback();
throw ex;
}
finally
{
Connection.Close();
}
return(Id);
}
/// <summary>
/// 由一行数据得到一个实体
/// </summary>
private Hi.Model.BD_Promotion GetModel(DataRow r)
{
Hi.Model.BD_Promotion model = new Hi.Model.BD_Promotion();
model.ID = SqlHelper.GetInt(r["ID"]);
model.CompID = SqlHelper.GetInt(r["CompID"]);
model.ProTitle = SqlHelper.GetString(r["ProTitle"]);
model.Type = SqlHelper.GetInt(r["Type"]);
model.ProType = SqlHelper.GetInt(r["ProType"]);
model.Discount = SqlHelper.GetDecimal(r["Discount"]);
model.ProInfos = SqlHelper.GetString(r["ProInfos"]);
model.IsEnabled = SqlHelper.GetInt(r["IsEnabled"]);
model.ProStartTime = SqlHelper.GetDateTime(r["ProStartTime"]);
model.ProEndTime = SqlHelper.GetDateTime(r["ProEndTime"]);
model.CreateUserID = SqlHelper.GetInt(r["CreateUserID"]);
model.CreateDate = SqlHelper.GetDateTime(r["CreateDate"]);
model.ts = SqlHelper.GetString(r["ts"]);
model.dr = SqlHelper.GetInt(r["dr"]);
model.modifyuser = SqlHelper.GetInt(r["modifyuser"]);
return(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Hi.Model.BD_Promotion model, SqlTransaction Tran)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [BD_Promotion] set ");
strSql.Append("[CompID]=@CompID,");
strSql.Append("[ProTitle]=@ProTitle,");
strSql.Append("[Type]=@Type,");
strSql.Append("[ProType]=@ProType,");
strSql.Append("[Discount]=@Discount,");
strSql.Append("[ProInfos]=@ProInfos,");
strSql.Append("[IsEnabled]=@IsEnabled,");
strSql.Append("[ProStartTime]=@ProStartTime,");
strSql.Append("[ProEndTime]=@ProEndTime,");
strSql.Append("[CreateUserID]=@CreateUserID,");
strSql.Append("[CreateDate]=@CreateDate,");
strSql.Append("[ts]=@ts,");
strSql.Append("[dr]=@dr,");
strSql.Append("[modifyuser]=@modifyuser");
strSql.Append(" where [ID]=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@CompID", SqlDbType.Int),
new SqlParameter("@ProTitle", SqlDbType.NVarChar, 200),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@ProType", SqlDbType.Int),
new SqlParameter("@Discount", SqlDbType.Decimal),
new SqlParameter("@ProInfos", SqlDbType.NVarChar, 2000),
new SqlParameter("@IsEnabled", SqlDbType.Int),
new SqlParameter("@ProStartTime", SqlDbType.DateTime),
new SqlParameter("@ProEndTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ts", SqlDbType.Char, 19),
new SqlParameter("@dr", SqlDbType.Int),
new SqlParameter("@modifyuser", SqlDbType.Int)
};
parameters[0].Value = model.ID;
parameters[1].Value = model.CompID;
if (model.ProTitle != null)
{
parameters[2].Value = model.ProTitle;
}
else
{
parameters[2].Value = DBNull.Value;
}
parameters[3].Value = model.Type;
parameters[4].Value = model.ProType;
parameters[5].Value = model.Discount;
if (model.ProInfos != null)
{
parameters[6].Value = model.ProInfos;
}
else
{
parameters[6].Value = DBNull.Value;
}
parameters[7].Value = model.IsEnabled;
parameters[8].Value = model.ProStartTime;
parameters[9].Value = model.ProEndTime;
parameters[10].Value = model.CreateUserID;
parameters[11].Value = model.CreateDate;
parameters[12].Value = model.ts;
parameters[13].Value = model.dr;
parameters[14].Value = model.modifyuser;
// return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
if (Tran != null)
{
return(SqlHelper.ExecuteSql(strSql.ToString(), Tran, parameters) > 0);
}
return(SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0);
}
/// <summary>
/// 新增促销主表（事务）
/// </summary>
/// <param name="model">实体</param>
/// <param name="sqlconn"></param>
/// <param name="sqltans"></param>
/// <returns></returns>
public int ProAdd(Hi.Model.BD_Promotion model, SqlConnection sqlconn, SqlTransaction sqltans)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [BD_Promotion](");
strSql.Append("[CompID],[ProTitle],[Type],[ProType],[Discount],[ProInfos],[IsEnabled],[ProStartTime],[ProEndTime],[CreateUserID],[CreateDate],[ts],[dr],[modifyuser])");
strSql.Append(" values (");
strSql.Append("@CompID,@ProTitle,@Type,@ProType,@Discount,@ProInfos,@IsEnabled,@ProStartTime,@ProEndTime,@CreateUserID,@CreateDate,@ts,@dr,@modifyuser)");
strSql.Append(";select @@Identity");
SqlParameter[] parameters =
{
new SqlParameter("@CompID", SqlDbType.Int),
new SqlParameter("@ProTitle", SqlDbType.NVarChar, 200),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@ProType", SqlDbType.Int),
new SqlParameter("@Discount", SqlDbType.Decimal),
new SqlParameter("@ProInfos", SqlDbType.NVarChar, 2000),
new SqlParameter("@IsEnabled", SqlDbType.Int),
new SqlParameter("@ProStartTime", SqlDbType.DateTime),
new SqlParameter("@ProEndTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ts", SqlDbType.Char, 19),
new SqlParameter("@dr", SqlDbType.Int),
new SqlParameter("@modifyuser", SqlDbType.Int)
};
parameters[0].Value = model.CompID;
if (model.ProTitle != null)
{
parameters[1].Value = model.ProTitle;
}
else
{
parameters[1].Value = DBNull.Value;
}
parameters[2].Value = model.Type;
parameters[3].Value = model.ProType;
parameters[4].Value = model.Discount;
if (model.ProInfos != null)
{
parameters[5].Value = model.ProInfos;
}
else
{
parameters[5].Value = DBNull.Value;
}
parameters[6].Value = model.IsEnabled;
parameters[7].Value = model.ProStartTime;
parameters[8].Value = model.ProEndTime;
parameters[9].Value = model.CreateUserID;
parameters[10].Value = model.CreateDate;
parameters[11].Value = model.ts;
parameters[12].Value = model.dr;
parameters[13].Value = model.modifyuser;
SqlCommand cmd = new SqlCommand(strSql.ToString(), sqlconn, sqltans);
cmd.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
if (parameter.SqlDbType == SqlDbType.DateTime)
{
if (parameter.Value == DBNull.Value)
{
parameter.Value = DBNull.Value;
}
}
cmd.Parameters.Add(parameter);
}
}
int rowsAffected = SqlHelper.GetInt(cmd.ExecuteScalar().ToString());
if (rowsAffected > 0)
{
return(rowsAffected);
}
else
{
return(0);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Hi.Model.BD_Promotion model, SqlTransaction Tran)
{
return(dal.Update(model, Tran));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Hi.Model.BD_Promotion model, System.Data.SqlClient.SqlTransaction Tran)
{
return(dal.Add(model, Tran));
}
/// <summary>
/// 修改促销主表（事务）
/// </summary>
/// <param name="model"></param>
/// <param name="sqlconn"></param>
/// <param name="sqltans"></param>
/// <returns></returns>
public int UpdateProSql(Hi.Model.BD_Promotion model, SqlConnection sqlconn, SqlTransaction sqltans)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [BD_Promotion] set ");
strSql.Append("[CompID]=@CompID,");
strSql.Append("[ProTitle]=@ProTitle,");
strSql.Append("[Type]=@Type,");
strSql.Append("[ProType]=@ProType,");
strSql.Append("[Discount]=@Discount,");
strSql.Append("[ProInfos]=@ProInfos,");
strSql.Append("[IsEnabled]=@IsEnabled,");
strSql.Append("[ProStartTime]=@ProStartTime,");
strSql.Append("[ProEndTime]=@ProEndTime,");
strSql.Append("[CreateUserID]=@CreateUserID,");
strSql.Append("[CreateDate]=@CreateDate,");
strSql.Append("[ts]=@ts,");
strSql.Append("[dr]=@dr,");
strSql.Append("[modifyuser]=@modifyuser");
strSql.Append(" where [ID]=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@CompID", SqlDbType.Int),
new SqlParameter("@ProTitle", SqlDbType.NVarChar, 200),
new SqlParameter("@Type", SqlDbType.Int),
new SqlParameter("@ProType", SqlDbType.Int),
new SqlParameter("@Discount", SqlDbType.Decimal),
new SqlParameter("@ProInfos", SqlDbType.NVarChar, 2000),
new SqlParameter("@IsEnabled", SqlDbType.Int),
new SqlParameter("@ProStartTime", SqlDbType.DateTime),
new SqlParameter("@ProEndTime", SqlDbType.DateTime),
new SqlParameter("@CreateUserID", SqlDbType.Int),
new SqlParameter("@CreateDate", SqlDbType.DateTime),
new SqlParameter("@ts", SqlDbType.Char, 19),
new SqlParameter("@dr", SqlDbType.Int),
new SqlParameter("@modifyuser", SqlDbType.Int)
};
parameters[0].Value = model.ID;
parameters[1].Value = model.CompID;
if (model.ProTitle != null)
{
parameters[2].Value = model.ProTitle;
}
else
{
parameters[2].Value = DBNull.Value;
}
parameters[3].Value = model.Type;
parameters[4].Value = model.ProType;
parameters[5].Value = model.Discount;
if (model.ProInfos != null)
{
parameters[6].Value = model.ProInfos;
}
else
{
parameters[6].Value = DBNull.Value;
}
parameters[7].Value = model.IsEnabled;
parameters[8].Value = model.ProStartTime;
parameters[9].Value = model.ProEndTime;
parameters[10].Value = model.CreateUserID;
parameters[11].Value = model.CreateDate;
parameters[12].Value = model.ts;
parameters[13].Value = model.dr;
parameters[14].Value = model.modifyuser;
SqlCommand cmd = new SqlCommand(strSql.ToString(), sqlconn, sqltans);
cmd.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
if (parameter.SqlDbType == SqlDbType.DateTime)
{
if (parameter.Value == DBNull.Value)
{
parameter.Value = DBNull.Value;
}
}
cmd.Parameters.Add(parameter);
}
}
int rowsAffected = SqlHelper.GetInt(cmd.ExecuteNonQuery().ToString());
if (rowsAffected > 0)
{
return(rowsAffected);
}
else
{
return(0);
}
}