/// <summary>
/// 查找不存在的文件并删除已删除的附件及数据
/// </summary>
public void DeleteList(SqlConnection conn, SqlTransaction trans, List <Model.download_attach> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.download_attach modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,file_path from dt_download_attach where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperSQL2.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperSQL2.ExecuteSql(conn, trans, "delete from dt_download_attach where id=" + dr["id"].ToString()); //删除数据库
if (rows > 0)
{
Utils.DeleteFile(dr["file_path"].ToString()); //删除文件
}
}
}
/// <summary>
/// ²éÕÒ²»´æÔÚµÄͼƬ²¢É¾³ýÒÑɾ³ýµÄͼƬ¼°Êý¾Ý
/// </summary>
public void DeleteList(SqlConnection conn, SqlTransaction trans, List <Model.article_albums> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_albums modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,big_img,small_img from dt_article_albums where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperSQL2.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperSQL2.ExecuteSql(conn, trans, "delete from dt_article_albums where id=" + dr["id"].ToString()); //ɾ³ýÊý¾Ý¿â
if (rows > 0)
{
Utils.DeleteFile(dr["big_img"].ToString()); //ɾ³ýÔͼ
Utils.DeleteFile(dr["small_img"].ToString()); //ɾ³ýËõÂÔͼ
}
}
}
/// <summary>
/// 验证节点是否被包含
/// </summary>
/// <param name="id">待查询的节点</param>
/// <param name="parent_id">父节点</param>
/// <returns></returns>
private bool IsContainNode(int id, int parent_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from dt_category ");
strSql.Append(" where class_list like '%," + id + ",%' and id=" + parent_id);
return(DbHelperSQL2.Exists(strSql.ToString()));
}
/// <summary>
/// 取得父节点id
/// </summary>
/// <param name="id">id</param>
/// <returns></returns>
public int GetParentId(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 parent_id from dt_category");
strSql.Append(" where id=" + id);
return(Convert.ToInt32(DbHelperSQL2.GetSingle(strSql.ToString())));
}
/// <summary>
/// 修改一列数据
/// </summary>
public void UpdateField(int id, string strValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dt_article set " + strValue);
strSql.Append(" where id=" + id);
DbHelperSQL2.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 删除一条数据
/// </summary>
public void Delete(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from dt_category ");
strSql.Append(" where class_list like '%," + id + ",%' ");
DbHelperSQL2.Query(strSql.ToString());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.download_attach GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,article_id,title,file_path,file_ext,file_size,down_num,point from dt_download_attach ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
Model.download_attach model = new Model.download_attach();
DataSet ds = DbHelperSQL2.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["article_id"] != null && ds.Tables[0].Rows[0]["article_id"].ToString() != "")
{
model.article_id = int.Parse(ds.Tables[0].Rows[0]["article_id"].ToString());
}
if (ds.Tables[0].Rows[0]["title"] != null && ds.Tables[0].Rows[0]["title"].ToString() != "")
{
model.title = ds.Tables[0].Rows[0]["title"].ToString();
}
if (ds.Tables[0].Rows[0]["file_path"] != null && ds.Tables[0].Rows[0]["file_path"].ToString() != "")
{
model.file_path = ds.Tables[0].Rows[0]["file_path"].ToString();
}
if (ds.Tables[0].Rows[0]["file_ext"] != null && ds.Tables[0].Rows[0]["file_ext"].ToString() != "")
{
model.file_ext = ds.Tables[0].Rows[0]["file_ext"].ToString();
}
if (ds.Tables[0].Rows[0]["file_size"] != null && ds.Tables[0].Rows[0]["file_size"].ToString() != "")
{
model.file_size = int.Parse(ds.Tables[0].Rows[0]["file_size"].ToString());
}
if (ds.Tables[0].Rows[0]["down_num"] != null && ds.Tables[0].Rows[0]["down_num"].ToString() != "")
{
model.down_num = int.Parse(ds.Tables[0].Rows[0]["down_num"].ToString());
}
if (ds.Tables[0].Rows[0]["point"] != null && ds.Tables[0].Rows[0]["point"].ToString() != "")
{
model.point = int.Parse(ds.Tables[0].Rows[0]["point"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <Model.download_attach> GetList(int article_id)
{
List <Model.download_attach> modelList = new List <Model.download_attach>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,article_id,title,file_path,file_ext,file_size,down_num,point ");
strSql.Append(" FROM dt_download_attach ");
strSql.Append(" where article_id=" + article_id);
DataTable dt = DbHelperSQL2.Query(strSql.ToString()).Tables[0];
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
Model.download_attach model;
for (int n = 0; n < rowsCount; n++)
{
model = new Model.download_attach();
if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
{
model.id = int.Parse(dt.Rows[n]["id"].ToString());
}
if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
{
model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
}
if (dt.Rows[n]["title"] != null && dt.Rows[n]["title"].ToString() != "")
{
model.title = dt.Rows[n]["title"].ToString();
}
if (dt.Rows[n]["file_path"] != null && dt.Rows[n]["file_path"].ToString() != "")
{
model.file_path = dt.Rows[n]["file_path"].ToString();
}
if (dt.Rows[n]["file_ext"] != null && dt.Rows[n]["file_ext"].ToString() != "")
{
model.file_ext = dt.Rows[n]["file_ext"].ToString();
}
if (dt.Rows[n]["file_size"] != null && dt.Rows[n]["file_size"].ToString() != "")
{
model.file_size = int.Parse(dt.Rows[n]["file_size"].ToString());
}
if (dt.Rows[n]["down_num"] != null && dt.Rows[n]["down_num"].ToString() != "")
{
model.down_num = int.Parse(dt.Rows[n]["down_num"].ToString());
}
if (dt.Rows[n]["point"] != null && dt.Rows[n]["point"].ToString() != "")
{
model.point = int.Parse(dt.Rows[n]["point"].ToString());
}
modelList.Add(model);
}
}
return(modelList);
}
/// <summary>
/// 取得指定类别下的列表
/// </summary>
/// <param name="parent_id">父ID</param>
/// <param name="channel_id">频道ID</param>
/// <returns></returns>
public DataTable GetChildList(int parent_id, int channel_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,channel_id,title,call_index,parent_id,class_list,class_layer,sort_id,link_url,img_url,content,seo_title,seo_keywords,seo_description from dt_category");
strSql.Append(" where channel_id=" + channel_id + " and parent_id=" + parent_id + " order by sort_id asc,id desc");
DataSet ds = DbHelperSQL2.Query(strSql.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 取得某个频道下父节点下的id
/// </summary>
/// <param name="parent_id"></param>
/// <param name="channel_id"></param>
/// <returns></returns>
public DataTable GetTopList(int parent_id, int channel_id)
{
StringBuilder strsql = new StringBuilder();
strsql.Append("select * from dt_category ");
strsql.Append(" where channel_id=" + channel_id + " and parent_id=" + parent_id + " order by sort_id asc");
DataSet ds = DbHelperSQL2.Query(strsql.ToString());
DataTable dt = ds.Tables[0] as DataTable;
return(dt);
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetNewsList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM view_article_news");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL2.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL2.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select BarCode,Tf_TrayId,cTray,FileName,fltCapacity,fltVol,fltResistance,tf_CheckGrade,tf_Group,tf_GroupNum,tf_Location,cState,cDate,cStateCode ");
strSql.Append(" FROM Tb_CheckData ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(DbHelperSQL2.Query(strSql.ToString()));
}
/// <summary>
/// 是否存在该记录
/// </summary>
public bool Exists(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from dt_category");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
return(DbHelperSQL2.Exists(strSql.ToString(), parameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(DBAccess.Model.Tb_CheckDataModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Tb_CheckData(");
strSql.Append("BarCode,Tf_TrayId,cTray,FileName,fltCapacity,fltVol,fltResistance,tf_CheckGrade,tf_Group,tf_GroupNum,tf_Location,cState,cDate,cStateCode)");
strSql.Append(" values (");
strSql.Append("@BarCode,@Tf_TrayId,@cTray,@FileName,@fltCapacity,@fltVol,@fltResistance,@tf_CheckGrade,@tf_Group,@tf_GroupNum,@tf_Location,@cState,@cDate,@cStateCode)");
SqlParameter[] parameters =
{
new SqlParameter("@BarCode", SqlDbType.NVarChar, 32),
new SqlParameter("@Tf_TrayId", SqlDbType.NVarChar, 32),
new SqlParameter("@cTray", SqlDbType.NVarChar, 32),
new SqlParameter("@FileName", SqlDbType.NVarChar, 32),
new SqlParameter("@fltCapacity", SqlDbType.Float, 8),
new SqlParameter("@fltVol", SqlDbType.Float, 8),
new SqlParameter("@fltResistance", SqlDbType.Float, 8),
new SqlParameter("@tf_CheckGrade", SqlDbType.NVarChar, 32),
new SqlParameter("@tf_Group", SqlDbType.NVarChar, 32),
new SqlParameter("@tf_GroupNum", SqlDbType.Int, 4),
new SqlParameter("@tf_Location", SqlDbType.NVarChar, 32),
new SqlParameter("@cState", SqlDbType.Float, 8),
new SqlParameter("@cDate", SqlDbType.NVarChar, 32),
new SqlParameter("@cStateCode", SqlDbType.NVarChar, 32)
};
parameters[0].Value = model.BarCode;
parameters[1].Value = model.Tf_TrayId;
parameters[2].Value = model.cTray;
parameters[3].Value = model.FileName;
parameters[4].Value = model.fltCapacity;
parameters[5].Value = model.fltVol;
parameters[6].Value = model.fltResistance;
parameters[7].Value = model.tf_CheckGrade;
parameters[8].Value = model.tf_Group;
parameters[9].Value = model.tf_GroupNum;
parameters[10].Value = model.tf_Location;
parameters[11].Value = model.cState;
parameters[12].Value = model.cDate;
parameters[13].Value = model.cStateCode;
int rows = DbHelperSQL2.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 返回信息标题
/// </summary>
public string GetTitle(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 title from dt_article");
strSql.Append(" where id=" + id);
string title = Convert.ToString(DbHelperSQL2.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(title))
{
return("");
}
return(title);
}
/// <summary>
/// 是否存在该记录
/// </summary>
public bool Exists(string BarCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from Tb_CheckData");
strSql.Append(" where BarCode=@BarCode ");
SqlParameter[] parameters =
{
new SqlParameter("@BarCode", SqlDbType.NVarChar, 32)
};
parameters[0].Value = BarCode;
return(DbHelperSQL2.Exists(strSql.ToString(), parameters));
}
/// <summary>
/// 批量删除数据
/// </summary>
public bool DeleteList(string BarCodelist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Tb_CheckData ");
strSql.Append(" where BarCode in (" + BarCodelist + ") ");
int rows = DbHelperSQL2.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// »ñµÃÊý¾ÝÁбí
/// </summary>
public List <Model.article_albums> GetList(int article_id)
{
List <Model.article_albums> modelList = new List <Model.article_albums>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,article_id,big_img,small_img,remark ");
strSql.Append(" FROM dt_article_albums ");
strSql.Append(" where article_id=" + article_id);
DataTable dt = DbHelperSQL2.Query(strSql.ToString()).Tables[0];
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
Model.article_albums model;
for (int n = 0; n < rowsCount; n++)
{
model = new Model.article_albums();
if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
{
model.id = int.Parse(dt.Rows[n]["id"].ToString());
}
if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
{
model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
}
if (dt.Rows[n]["big_img"] != null && dt.Rows[n]["big_img"].ToString() != "")
{
model.big_img = dt.Rows[n]["big_img"].ToString();
}
if (dt.Rows[n]["small_img"] != null && dt.Rows[n]["small_img"].ToString() != "")
{
model.small_img = dt.Rows[n]["small_img"].ToString();
}
if (dt.Rows[n]["remark"] != null && dt.Rows[n]["remark"].ToString() != "")
{
model.remark = dt.Rows[n]["remark"].ToString();
}
modelList.Add(model);
}
}
return(modelList);
}
/// <summary>
/// 获得前几行数据
/// </summary>
public DataSet GetNewsList(int Top, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(" id,channel_id,category_id,title,link_url,img_url,seo_title,seo_keywords,seo_description,content,sort_id,click,is_lock,user_id,add_time,author,[from],zhaiyao,is_msg,is_top,is_red,is_hot,is_slide ");
//strSql.Append(" id,channel_id,category_id,title,link_url,img_url,seo_title,seo_keywords,seo_description,content,sort_id,click,is_lock,user_id,add_time,author,[from],zhaiyao,is_msg,is_top,is_red,is_hot,is_slide,digg_good,digg_bad ");
strSql.Append(" FROM view_article_news ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return(DbHelperSQL2.Query(strSql.ToString()));
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM Tb_CheckData ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
object obj = DbHelperSQL2.GetSingle(strSql.ToString());
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 取得所有类别列表
/// </summary>
/// <param name="parent_id">父ID</param>
/// <param name="channel_id">频道ID</param>
/// <returns></returns>
public DataTable GetList(int parent_id, int channel_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,channel_id,title,call_index,parent_id,class_list,class_layer,sort_id,link_url,img_url,content,seo_title,seo_keywords,seo_description from dt_category");
strSql.Append(" where channel_id=" + channel_id + " order by sort_id asc,id desc");
DataSet ds = DbHelperSQL2.Query(strSql.ToString());
DataTable oldData = ds.Tables[0] as DataTable;
if (oldData == null)
{
return(null);
}
//复制结构
DataTable newData = oldData.Clone();
//调用迭代组合成DAGATABLE
GetChilds(oldData, newData, parent_id, channel_id);
return(newData);
}
/// <summary>
/// 修改子节点的ID列表及深度（自身迭代）
/// </summary>
/// <param name="parent_id"></param>
private void UpdateChilds(SqlConnection conn, SqlTransaction trans, int parent_id)
{
//查找父节点信息
Model.category model = GetModel(conn, trans, parent_id);
if (model != null)
{
//查找子节点
string strSql = "select id from dt_category where parent_id=" + parent_id;
DataSet ds = DbHelperSQL2.Query(conn, trans, strSql); //带事务
foreach (DataRow dr in ds.Tables[0].Rows)
{
//修改子节点的ID列表及深度
int id = int.Parse(dr["id"].ToString());
string class_list = model.class_list + id + ",";
int class_layer = model.class_layer + 1;
DbHelperSQL2.ExecuteSql(conn, trans, "update dt_category set class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + id); //带事务
//调用自身迭代
this.UpdateChilds(conn, trans, id); //带事务
}
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(string BarCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Tb_CheckData ");
strSql.Append(" where BarCode=@BarCode ");
SqlParameter[] parameters =
{
new SqlParameter("@BarCode", SqlDbType.NVarChar, 32)
};
parameters[0].Value = BarCode;
int rows = DbHelperSQL2.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public DBAccess.Model.Tb_CheckDataModel GetModel(string BarCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 BarCode,Tf_TrayId,cTray,FileName,fltCapacity,fltVol,fltResistance,tf_CheckGrade,tf_Group,tf_GroupNum,tf_Location,cState,cDate,cStateCode from Tb_CheckData ");
strSql.Append(" where BarCode=@BarCode ");
SqlParameter[] parameters =
{
new SqlParameter("@BarCode", SqlDbType.NVarChar, 32)
};
parameters[0].Value = BarCode;
DBAccess.Model.Tb_CheckDataModel model = new DBAccess.Model.Tb_CheckDataModel();
DataSet ds = DbHelperSQL2.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.article_news GetNewsModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,channel_id,category_id,title,link_url,img_url,seo_title,seo_keywords,seo_description,content,sort_id,click,is_lock,user_id,add_time,author,[from],zhaiyao,is_msg,is_top,is_red,is_hot,is_slide from view_article_news ");
//strSql.Append("select top 1 id,channel_id,category_id,title,link_url,img_url,seo_title,seo_keywords,seo_description,content,sort_id,click,is_lock,user_id,add_time,author,[from],zhaiyao,is_msg,is_top,is_red,is_hot,is_slide,digg_good,digg_bad from view_article_news ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
Model.article_news model = new Model.article_news();
DataSet ds = DbHelperSQL2.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
#region 父表信息
if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["channel_id"] != null && ds.Tables[0].Rows[0]["channel_id"].ToString() != "")
{
model.channel_id = int.Parse(ds.Tables[0].Rows[0]["channel_id"].ToString());
}
if (ds.Tables[0].Rows[0]["category_id"] != null && ds.Tables[0].Rows[0]["category_id"].ToString() != "")
{
model.category_id = int.Parse(ds.Tables[0].Rows[0]["category_id"].ToString());
}
if (ds.Tables[0].Rows[0]["title"] != null && ds.Tables[0].Rows[0]["title"].ToString() != "")
{
model.title = ds.Tables[0].Rows[0]["title"].ToString();
}
if (ds.Tables[0].Rows[0]["link_url"] != null && ds.Tables[0].Rows[0]["link_url"].ToString() != "")
{
model.link_url = ds.Tables[0].Rows[0]["link_url"].ToString();
}
if (ds.Tables[0].Rows[0]["img_url"] != null && ds.Tables[0].Rows[0]["img_url"].ToString() != "")
{
model.img_url = ds.Tables[0].Rows[0]["img_url"].ToString();
}
if (ds.Tables[0].Rows[0]["seo_title"] != null && ds.Tables[0].Rows[0]["seo_title"].ToString() != "")
{
model.seo_title = ds.Tables[0].Rows[0]["seo_title"].ToString();
}
if (ds.Tables[0].Rows[0]["seo_keywords"] != null && ds.Tables[0].Rows[0]["seo_keywords"].ToString() != "")
{
model.seo_keywords = ds.Tables[0].Rows[0]["seo_keywords"].ToString();
}
if (ds.Tables[0].Rows[0]["seo_description"] != null && ds.Tables[0].Rows[0]["seo_description"].ToString() != "")
{
model.seo_description = ds.Tables[0].Rows[0]["seo_description"].ToString();
}
if (ds.Tables[0].Rows[0]["content"] != null && ds.Tables[0].Rows[0]["content"].ToString() != "")
{
model.content = ds.Tables[0].Rows[0]["content"].ToString();
}
if (ds.Tables[0].Rows[0]["sort_id"] != null && ds.Tables[0].Rows[0]["sort_id"].ToString() != "")
{
model.sort_id = int.Parse(ds.Tables[0].Rows[0]["sort_id"].ToString());
}
if (ds.Tables[0].Rows[0]["click"] != null && ds.Tables[0].Rows[0]["click"].ToString() != "")
{
model.click = int.Parse(ds.Tables[0].Rows[0]["click"].ToString());
}
if (ds.Tables[0].Rows[0]["is_lock"] != null && ds.Tables[0].Rows[0]["is_lock"].ToString() != "")
{
model.is_lock = int.Parse(ds.Tables[0].Rows[0]["is_lock"].ToString());
}
if (ds.Tables[0].Rows[0]["user_id"] != null && ds.Tables[0].Rows[0]["user_id"].ToString() != "")
{
model.user_id = int.Parse(ds.Tables[0].Rows[0]["user_id"].ToString());
}
if (ds.Tables[0].Rows[0]["add_time"] != null && ds.Tables[0].Rows[0]["add_time"].ToString() != "")
{
model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
}
if (ds.Tables[0].Rows[0]["author"] != null && ds.Tables[0].Rows[0]["author"].ToString() != "")
{
model.author = ds.Tables[0].Rows[0]["author"].ToString();
}
if (ds.Tables[0].Rows[0]["from"] != null && ds.Tables[0].Rows[0]["from"].ToString() != "")
{
model.from = ds.Tables[0].Rows[0]["from"].ToString();
}
if (ds.Tables[0].Rows[0]["zhaiyao"] != null && ds.Tables[0].Rows[0]["zhaiyao"].ToString() != "")
{
model.zhaiyao = ds.Tables[0].Rows[0]["zhaiyao"].ToString();
}
if (ds.Tables[0].Rows[0]["is_msg"] != null && ds.Tables[0].Rows[0]["is_msg"].ToString() != "")
{
model.is_msg = int.Parse(ds.Tables[0].Rows[0]["is_msg"].ToString());
}
if (ds.Tables[0].Rows[0]["is_top"] != null && ds.Tables[0].Rows[0]["is_top"].ToString() != "")
{
model.is_top = int.Parse(ds.Tables[0].Rows[0]["is_top"].ToString());
}
if (ds.Tables[0].Rows[0]["is_red"] != null && ds.Tables[0].Rows[0]["is_red"].ToString() != "")
{
model.is_red = int.Parse(ds.Tables[0].Rows[0]["is_red"].ToString());
}
if (ds.Tables[0].Rows[0]["is_hot"] != null && ds.Tables[0].Rows[0]["is_hot"].ToString() != "")
{
model.is_hot = int.Parse(ds.Tables[0].Rows[0]["is_hot"].ToString());
}
if (ds.Tables[0].Rows[0]["is_slide"] != null && ds.Tables[0].Rows[0]["is_slide"].ToString() != "")
{
model.is_slide = int.Parse(ds.Tables[0].Rows[0]["is_slide"].ToString());
}
//if (ds.Tables[0].Rows[0]["digg_good"] != null && ds.Tables[0].Rows[0]["digg_good"].ToString() != "")
//{
// model.digg_good = int.Parse(ds.Tables[0].Rows[0]["digg_good"].ToString());
//}
//if (ds.Tables[0].Rows[0]["digg_bad"] != null && ds.Tables[0].Rows[0]["digg_bad"].ToString() != "")
//{
// model.digg_bad = int.Parse(ds.Tables[0].Rows[0]["digg_bad"].ToString());
//}
#endregion 父表信息end
model.albums = new article_albums().GetList(id); //相册信息
// model.attribute_values = new attribute_value().GetList(id); //扩展属性
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 增加一条数据,及其子表数据
/// </summary>
public int Add(Model.article_news model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into dt_article(");
strSql.Append("channel_id,category_id,title,link_url,img_url,seo_title,seo_keywords,seo_description,content,sort_id,click,is_lock,user_id,add_time)");
strSql.Append(" values (");
strSql.Append("@channel_id,@category_id,@title,@link_url,@img_url,@seo_title,@seo_keywords,@seo_description,@content,@sort_id,@click,@is_lock,@user_id,@add_time)");
strSql.Append(";set @ReturnValue= @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@channel_id", SqlDbType.Int, 4),
new SqlParameter("@category_id", SqlDbType.Int, 4),
new SqlParameter("@title", SqlDbType.NVarChar, 100),
new SqlParameter("@link_url", SqlDbType.NVarChar, 255),
new SqlParameter("@img_url", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_title", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_description", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@sort_id", SqlDbType.Int, 4),
new SqlParameter("@click", SqlDbType.Int, 4),
new SqlParameter("@is_lock", SqlDbType.TinyInt, 1),
new SqlParameter("@user_id", SqlDbType.Int, 4),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@ReturnValue", SqlDbType.Int)
};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.category_id;
parameters[2].Value = model.title;
parameters[3].Value = model.link_url;
parameters[4].Value = model.img_url;
parameters[5].Value = model.seo_title;
parameters[6].Value = model.seo_keywords;
parameters[7].Value = model.seo_description;
parameters[8].Value = model.content;
parameters[9].Value = model.sort_id;
parameters[10].Value = model.click;
parameters[11].Value = model.is_lock;
parameters[12].Value = model.user_id;
parameters[13].Value = model.add_time;
parameters[14].Direction = ParameterDirection.Output;
List <CommandInfo> sqllist = new List <CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
//副表信息
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into dt_article_news(");
strSql2.Append("id,author,[from],zhaiyao,is_msg,is_top,is_red,is_hot,is_slide)");
strSql2.Append(" values (");
strSql2.Append("@id,@author,@from,@zhaiyao,@is_msg,@is_top,@is_red,@is_hot,@is_slide)");
SqlParameter[] parameters2 =
{
new SqlParameter("@id", SqlDbType.Int, 4),
new SqlParameter("@author", SqlDbType.NVarChar, 100),
new SqlParameter("@from", SqlDbType.NVarChar, 50),
new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 255),
new SqlParameter("@is_msg", SqlDbType.TinyInt, 1),
new SqlParameter("@is_top", SqlDbType.TinyInt, 1),
new SqlParameter("@is_red", SqlDbType.TinyInt, 1),
new SqlParameter("@is_hot", SqlDbType.TinyInt, 1),
new SqlParameter("@is_slide", SqlDbType.TinyInt, 1)
};
parameters2[0].Direction = ParameterDirection.InputOutput;
parameters2[1].Value = model.author;
parameters2[2].Value = model.from;
parameters2[3].Value = model.zhaiyao;
parameters2[4].Value = model.is_msg;
parameters2[5].Value = model.is_top;
parameters2[6].Value = model.is_red;
parameters2[7].Value = model.is_hot;
parameters2[8].Value = model.is_slide;
cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(cmd);
////顶和踩
//StringBuilder strSql3 = new StringBuilder();
//strSql3.Append("insert into dt_article_diggs(");
//strSql3.Append("id,digg_good,digg_bad)");
//strSql3.Append(" values (");
//strSql3.Append("@id,@digg_good,@digg_bad)");
//SqlParameter[] parameters3 = {
// new SqlParameter("@id", SqlDbType.Int,4),
// new SqlParameter("@digg_good", SqlDbType.Int,4),
// new SqlParameter("@digg_bad", SqlDbType.Int,4)};
//parameters3[0].Direction = ParameterDirection.InputOutput;
//parameters3[1].Value = model.digg_good;
//parameters3[2].Value = model.digg_bad;
//cmd = new CommandInfo(strSql3.ToString(), parameters3);
//sqllist.Add(cmd);
//图片相册
if (model.albums != null)
{
StringBuilder strSql4;
foreach (Model.article_albums models in model.albums)
{
strSql4 = new StringBuilder();
strSql4.Append("insert into dt_article_albums(");
strSql4.Append("article_id,big_img,small_img,remark)");
strSql4.Append(" values (");
strSql4.Append("@article_id,@big_img,@small_img,@remark)");
SqlParameter[] parameters4 =
{
new SqlParameter("@article_id", SqlDbType.Int, 4),
new SqlParameter("@big_img", SqlDbType.NVarChar, 255),
new SqlParameter("@small_img", SqlDbType.NVarChar, 255),
new SqlParameter("@remark", SqlDbType.NVarChar, 500)
};
parameters4[0].Direction = ParameterDirection.InputOutput;
parameters4[1].Value = models.big_img;
parameters4[2].Value = models.small_img;
parameters4[3].Value = models.remark;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
}
}
////扩展属性
//if (model.attribute_values != null)
//{
// StringBuilder strSql5;
// foreach (Model.attribute_value models in model.attribute_values)
// {
// strSql5 = new StringBuilder();
// strSql5.Append("insert into dt_attribute_value(");
// strSql5.Append("article_id,attribute_id,title,content)");
// strSql5.Append(" values (");
// strSql5.Append("@article_id,@attribute_id,@title,@content)");
// SqlParameter[] parameters5 = {
// new SqlParameter("@article_id", SqlDbType.Int,4),
// new SqlParameter("@attribute_id", SqlDbType.Int,4),
// new SqlParameter("@title", SqlDbType.NVarChar,100),
// new SqlParameter("@content", SqlDbType.NText)};
// parameters5[0].Direction = ParameterDirection.InputOutput;
// parameters5[1].Value = models.attribute_id;
// parameters5[2].Value = models.title;
// parameters5[3].Value = models.content;
// cmd = new CommandInfo(strSql5.ToString(), parameters5);
// sqllist.Add(cmd);
// }
//}
DbHelperSQL2.ExecuteSqlTranWithIndentity(sqllist);
return((int)parameters[14].Value);
}
/// <summary>
/// 更新一条数据,及其子表数据
/// </summary>
public bool Update(Model.article_news model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL2.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dt_article set ");
strSql.Append("channel_id=@channel_id,");
strSql.Append("category_id=@category_id,");
strSql.Append("title=@title,");
strSql.Append("link_url=@link_url,");
strSql.Append("img_url=@img_url,");
strSql.Append("seo_title=@seo_title,");
strSql.Append("seo_keywords=@seo_keywords,");
strSql.Append("seo_description=@seo_description,");
strSql.Append("content=@content,");
strSql.Append("sort_id=@sort_id,");
strSql.Append("click=@click,");
strSql.Append("is_lock=@is_lock,");
strSql.Append("user_id=@user_id,");
strSql.Append("add_time=@add_time");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@channel_id", SqlDbType.Int, 4),
new SqlParameter("@category_id", SqlDbType.Int, 4),
new SqlParameter("@title", SqlDbType.NVarChar, 100),
new SqlParameter("@link_url", SqlDbType.NVarChar, 255),
new SqlParameter("@img_url", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_title", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_description", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@sort_id", SqlDbType.Int, 4),
new SqlParameter("@click", SqlDbType.Int, 4),
new SqlParameter("@is_lock", SqlDbType.TinyInt, 1),
new SqlParameter("@user_id", SqlDbType.Int, 4),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.category_id;
parameters[2].Value = model.title;
parameters[3].Value = model.link_url;
parameters[4].Value = model.img_url;
parameters[5].Value = model.seo_title;
parameters[6].Value = model.seo_keywords;
parameters[7].Value = model.seo_description;
parameters[8].Value = model.content;
parameters[9].Value = model.sort_id;
parameters[10].Value = model.click;
parameters[11].Value = model.is_lock;
parameters[12].Value = model.user_id;
parameters[13].Value = model.add_time;
parameters[14].Value = model.id;
DbHelperSQL2.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//修改副表
StringBuilder strSql21 = new StringBuilder();
strSql21.Append("update dt_article_news set ");
strSql21.Append("author=@author,");
strSql21.Append("[from]=@from,");
strSql21.Append("zhaiyao=@zhaiyao,");
strSql21.Append("is_msg=@is_msg,");
strSql21.Append("is_top=@is_top,");
strSql21.Append("is_red=@is_red,");
strSql21.Append("is_hot=@is_hot,");
strSql21.Append("is_slide=@is_slide");
strSql21.Append(" where id=@id ");
SqlParameter[] parameters21 =
{
new SqlParameter("@author", SqlDbType.NVarChar, 100),
new SqlParameter("@from", SqlDbType.NVarChar, 50),
new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 255),
new SqlParameter("@is_msg", SqlDbType.TinyInt, 1),
new SqlParameter("@is_top", SqlDbType.TinyInt, 1),
new SqlParameter("@is_red", SqlDbType.TinyInt, 1),
new SqlParameter("@is_hot", SqlDbType.TinyInt, 1),
new SqlParameter("@is_slide", SqlDbType.TinyInt, 1),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters21[0].Value = model.author;
parameters21[1].Value = model.from;
parameters21[2].Value = model.zhaiyao;
parameters21[3].Value = model.is_msg;
parameters21[4].Value = model.is_top;
parameters21[5].Value = model.is_red;
parameters21[6].Value = model.is_hot;
parameters21[7].Value = model.is_slide;
parameters21[8].Value = model.id;
DbHelperSQL2.ExecuteSql(conn, trans, strSql21.ToString(), parameters21);
////修改顶和踩
//StringBuilder strSql22 = new StringBuilder();
//strSql22.Append("update dt_article_diggs set ");
//strSql22.Append("digg_good=@digg_good,");
//strSql22.Append("digg_bad=@digg_bad");
//strSql22.Append(" where id=@id ");
//SqlParameter[] parameters22 = {
// new SqlParameter("@digg_good", SqlDbType.Int,4),
// new SqlParameter("@digg_bad", SqlDbType.Int,4),
// new SqlParameter("@id", SqlDbType.Int,4)};
//parameters22[0].Value = model.digg_good;
//parameters22[1].Value = model.digg_bad;
//parameters22[2].Value = model.id;
//DbHelperSQL2.ExecuteSql(conn, trans, strSql22.ToString(), parameters22);
//删除已删除的图片
new article_albums().DeleteList(conn, trans, model.albums, model.id);
//添加/修改相册
if (model.albums != null)
{
StringBuilder strSql2;
foreach (Model.article_albums models in model.albums)
{
strSql2 = new StringBuilder();
if (models.id > 0)
{
strSql2.Append("update dt_article_albums set ");
strSql2.Append("article_id=@article_id,");
strSql2.Append("big_img=@big_img,");
strSql2.Append("small_img=@small_img,");
strSql2.Append("remark=@remark");
strSql2.Append(" where id=@id");
SqlParameter[] parameters2 =
{
new SqlParameter("@article_id", SqlDbType.Int, 4),
new SqlParameter("@big_img", SqlDbType.NVarChar, 255),
new SqlParameter("@small_img", SqlDbType.NVarChar, 255),
new SqlParameter("@remark", SqlDbType.NVarChar, 500),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters2[0].Value = models.article_id;
parameters2[1].Value = models.big_img;
parameters2[2].Value = models.small_img;
parameters2[3].Value = models.remark;
parameters2[4].Value = models.id;
DbHelperSQL2.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
}
else
{
strSql2.Append("insert into dt_article_albums(");
strSql2.Append("article_id,big_img,small_img,remark)");
strSql2.Append(" values (");
strSql2.Append("@article_id,@big_img,@small_img,@remark)");
SqlParameter[] parameters2 =
{
new SqlParameter("@article_id", SqlDbType.Int, 4),
new SqlParameter("@big_img", SqlDbType.NVarChar, 255),
new SqlParameter("@small_img", SqlDbType.NVarChar, 255),
new SqlParameter("@remark", SqlDbType.NVarChar, 500)
};
parameters2[0].Value = models.article_id;
parameters2[1].Value = models.big_img;
parameters2[2].Value = models.small_img;
parameters2[3].Value = models.remark;
DbHelperSQL2.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
}
}
}
////添加/修改属性
//if (model.attribute_values != null)
//{
// StringBuilder strSql3;
// foreach (Model.attribute_value models in model.attribute_values)
// {
// strSql3 = new StringBuilder();
// if (models.id > 0)
// {
// strSql3.Append("update dt_attribute_value set ");
// strSql3.Append("article_id=@article_id,");
// strSql3.Append("attribute_id=@attribute_id,");
// strSql3.Append("title=@title,");
// strSql3.Append("content=@content");
// strSql3.Append(" where id=@id");
// SqlParameter[] parameters3 = {
// new SqlParameter("@article_id", SqlDbType.Int,4),
// new SqlParameter("@attribute_id", SqlDbType.Int,4),
// new SqlParameter("@title", SqlDbType.NVarChar,100),
// new SqlParameter("@content", SqlDbType.NText),
// new SqlParameter("@id", SqlDbType.Int,4)};
// parameters3[0].Value = models.article_id;
// parameters3[1].Value = models.attribute_id;
// parameters3[2].Value = models.title;
// parameters3[3].Value = models.content;
// parameters3[4].Value = models.id;
// DbHelperSQL2.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
// }
// else
// {
// strSql3.Append("insert into dt_attribute_value(");
// strSql3.Append("article_id,attribute_id,title,content)");
// strSql3.Append(" values (");
// strSql3.Append("@article_id,@attribute_id,@title,@content)");
// SqlParameter[] parameters3 = {
// new SqlParameter("@article_id", SqlDbType.Int,4),
// new SqlParameter("@attribute_id", SqlDbType.Int,4),
// new SqlParameter("@title", SqlDbType.NVarChar,100),
// new SqlParameter("@content", SqlDbType.NText)};
// parameters3[0].Value = models.article_id;
// parameters3[1].Value = models.attribute_id;
// parameters3[2].Value = models.title;
// parameters3[3].Value = models.content;
// DbHelperSQL2.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
// }
// }
//}
trans.Commit();
}
catch
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(DBAccess.Model.Tb_CheckDataModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Tb_CheckData set ");
strSql.Append("Tf_TrayId=@Tf_TrayId,");
strSql.Append("cTray=@cTray,");
strSql.Append("FileName=@FileName,");
strSql.Append("fltCapacity=@fltCapacity,");
strSql.Append("fltVol=@fltVol,");
strSql.Append("fltResistance=@fltResistance,");
strSql.Append("tf_CheckGrade=@tf_CheckGrade,");
strSql.Append("tf_Group=@tf_Group,");
strSql.Append("tf_GroupNum=@tf_GroupNum,");
strSql.Append("tf_Location=@tf_Location,");
strSql.Append("cState=@cState,");
strSql.Append("cDate=@cDate,");
strSql.Append("cStateCode=@cStateCode");
strSql.Append(" where BarCode=@BarCode ");
SqlParameter[] parameters =
{
new SqlParameter("@Tf_TrayId", SqlDbType.NVarChar, 32),
new SqlParameter("@cTray", SqlDbType.NVarChar, 32),
new SqlParameter("@FileName", SqlDbType.NVarChar, 32),
new SqlParameter("@fltCapacity", SqlDbType.Float, 8),
new SqlParameter("@fltVol", SqlDbType.Float, 8),
new SqlParameter("@fltResistance", SqlDbType.Float, 8),
new SqlParameter("@tf_CheckGrade", SqlDbType.NVarChar, 32),
new SqlParameter("@tf_Group", SqlDbType.NVarChar, 32),
new SqlParameter("@tf_GroupNum", SqlDbType.Int, 4),
new SqlParameter("@tf_Location", SqlDbType.NVarChar, 32),
new SqlParameter("@cState", SqlDbType.Float, 8),
new SqlParameter("@cDate", SqlDbType.NVarChar, 32),
new SqlParameter("@cStateCode", SqlDbType.NVarChar, 32),
new SqlParameter("@BarCode", SqlDbType.NVarChar, 32)
};
parameters[0].Value = model.Tf_TrayId;
parameters[1].Value = model.cTray;
parameters[2].Value = model.FileName;
parameters[3].Value = model.fltCapacity;
parameters[4].Value = model.fltVol;
parameters[5].Value = model.fltResistance;
parameters[6].Value = model.tf_CheckGrade;
parameters[7].Value = model.tf_Group;
parameters[8].Value = model.tf_GroupNum;
parameters[9].Value = model.tf_Location;
parameters[10].Value = model.cState;
parameters[11].Value = model.cDate;
parameters[12].Value = model.cStateCode;
parameters[13].Value = model.BarCode;
int rows = DbHelperSQL2.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据，及子表所有相关数据
/// </summary>
public bool Delete(int id)
{
//取得相册MODEL
List <Model.article_albums> albumsList = new article_albums().GetList(id);
//取得附件MODEL
// List<Model.download_attach> attachList = new download_attach().GetList(id);
List <CommandInfo> sqllist = new List <CommandInfo>();
//删除文章模型数据
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from dt_article_news ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);
////删除下载模型数据
//StringBuilder strSql2 = new StringBuilder();
//strSql2.Append("delete from dt_article_download ");
//strSql2.Append(" where id=@id ");
//SqlParameter[] parameters2 = {
// new SqlParameter("@id", SqlDbType.Int,4)};
//parameters2[0].Value = id;
//cmd = new CommandInfo(strSql2.ToString(), parameters2);
//sqllist.Add(cmd);
////删除商品模型数据
//StringBuilder strSql3 = new StringBuilder();
//strSql3.Append("delete from dt_article_goods ");
//strSql3.Append(" where id=@id ");
//SqlParameter[] parameters3 = {
// new SqlParameter("@id", SqlDbType.Int,4)};
//parameters3[0].Value = id;
//cmd = new CommandInfo(strSql3.ToString(), parameters3);
//sqllist.Add(cmd);
////删除内容模型数据
//StringBuilder strSql4 = new StringBuilder();
//strSql4.Append("delete from dt_article_content ");
//strSql4.Append(" where id=@id ");
//SqlParameter[] parameters4 = {
// new SqlParameter("@id", SqlDbType.Int,4)};
//parameters4[0].Value = id;
//cmd = new CommandInfo(strSql4.ToString(), parameters4);
//sqllist.Add(cmd);
////删除顶和踩
//StringBuilder strSql5 = new StringBuilder();
//strSql5.Append("delete from dt_article_diggs ");
//strSql5.Append(" where id=@id ");
//SqlParameter[] parameters5 = {
// new SqlParameter("@id", SqlDbType.Int,4)};
//parameters5[0].Value = id;
//cmd = new CommandInfo(strSql5.ToString(), parameters5);
//sqllist.Add(cmd);
////删除商品价格
//StringBuilder strSql6 = new StringBuilder();
//strSql6.Append("delete from dt_goods_group_price ");
//strSql6.Append(" where article_id=@article_id ");
//SqlParameter[] parameters6 = {
// new SqlParameter("@article_id", SqlDbType.Int,4)};
//parameters6[0].Value = id;
//cmd = new CommandInfo(strSql6.ToString(), parameters6);
//sqllist.Add(cmd);
////删除下载的附件
//StringBuilder strSql7 = new StringBuilder();
//strSql7.Append("delete from dt_download_attach ");
//strSql7.Append(" where article_id=@article_id ");
//SqlParameter[] parameters7 = {
// new SqlParameter("@article_id", SqlDbType.Int,4)};
//parameters7[0].Value = id;
//cmd = new CommandInfo(strSql7.ToString(), parameters7);
//sqllist.Add(cmd);
//删除图片相册
StringBuilder strSql8 = new StringBuilder();
strSql8.Append("delete from dt_article_albums ");
strSql8.Append(" where article_id=@article_id ");
SqlParameter[] parameters8 =
{
new SqlParameter("@article_id", SqlDbType.Int, 4)
};
parameters8[0].Value = id;
cmd = new CommandInfo(strSql8.ToString(), parameters8);
sqllist.Add(cmd);
////删除扩展属性
//StringBuilder strSql9 = new StringBuilder();
//strSql9.Append("delete from dt_attribute_value ");
//strSql9.Append(" where article_id=@article_id ");
//SqlParameter[] parameters9 = {
// new SqlParameter("@article_id", SqlDbType.Int,4)};
//parameters9[0].Value = id;
//cmd = new CommandInfo(strSql9.ToString(), parameters9);
//sqllist.Add(cmd);
////删除评论
//StringBuilder strSql10 = new StringBuilder();
//strSql10.Append("delete from dt_article_comment ");
//strSql10.Append(" where article_id=@article_id ");
//SqlParameter[] parameters10 = {
// new SqlParameter("@article_id", SqlDbType.Int,4)};
//parameters10[0].Value = id;
//cmd = new CommandInfo(strSql10.ToString(), parameters10);
//sqllist.Add(cmd);
//删除主表信息
StringBuilder strSql11 = new StringBuilder();
strSql11.Append("delete from dt_article ");
strSql11.Append(" where id=@id ");
SqlParameter[] parameters11 =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters11[0].Value = id;
cmd = new CommandInfo(strSql11.ToString(), parameters11);
sqllist.Add(cmd);
int rowsAffected = DbHelperSQL2.ExecuteSqlTran(sqllist);
if (rowsAffected > 0)
{
new article_albums().DeleteFile(albumsList); //删除图片
//new download_attach().DeleteFile(attachList); //删除附件
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.category model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL2.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into dt_category(");
strSql.Append("channel_id,title,call_index,parent_id,class_list,class_layer,sort_id,link_url,img_url,content,seo_title,seo_keywords,seo_description)");
strSql.Append(" values (");
strSql.Append("@channel_id,@title,@call_index,@parent_id,@class_list,@class_layer,@sort_id,@link_url,@img_url,@content,@seo_title,@seo_keywords,@seo_description)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@channel_id", SqlDbType.Int, 4),
new SqlParameter("@title", SqlDbType.NVarChar, 100),
new SqlParameter("@call_index", SqlDbType.NVarChar, 50),
new SqlParameter("@parent_id", SqlDbType.Int, 4),
new SqlParameter("@class_list", SqlDbType.NVarChar, 500),
new SqlParameter("@class_layer", SqlDbType.Int, 4),
new SqlParameter("@sort_id", SqlDbType.Int, 4),
new SqlParameter("@link_url", SqlDbType.NVarChar, 255),
new SqlParameter("@img_url", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@seo_title", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_description", SqlDbType.NVarChar, 255)
};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.title;
parameters[2].Value = model.call_index;
parameters[3].Value = model.parent_id;
parameters[4].Value = model.class_list;
parameters[5].Value = model.class_layer;
parameters[6].Value = model.sort_id;
parameters[7].Value = model.link_url;
parameters[8].Value = model.img_url;
parameters[9].Value = model.content;
parameters[10].Value = model.seo_title;
parameters[11].Value = model.seo_keywords;
parameters[12].Value = model.seo_description;
object obj = DbHelperSQL2.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务
model.id = Convert.ToInt32(obj);
if (model.parent_id > 0)
{
Model.category model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
//修改节点列表和深度
DbHelperSQL2.ExecuteSql(conn, trans, "update dt_category set class_list='" + model.class_list + "', class_layer=" + model.class_layer + " where id=" + model.id); //带事务
trans.Commit();
}
catch
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}