/// <summary> /// 是否存在记录 /// </summary> /// <param name="sqlwhere">条件</param> /// <param name="files">查询字段</param> /// <param name="orderby">排序</param> /// <returns>bool</returns> public virtual T GetModel(string sqlwhere, string files, string orderby) { if (string.IsNullOrEmpty(sqlwhere)) { sqlwhere = "1=1"; } if (string.IsNullOrEmpty(files)) { files = "*"; } if (string.IsNullOrEmpty(orderby)) { orderby = "id desc"; } try { T t = WriteDataBase.SingleOrDefault <T>(Sql.Builder.Select(files).From(TableName).Where(sqlwhere).OrderBy(orderby)); return(t); } catch (Exception ex) { LogHelper.Error(ex); WriteDataBase.CloseSharedConnection(); return(null); } finally { WriteDataBase.CloseSharedConnection(); } }
/// <summary> /// 根据主键进行删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual bool Delete(object id) { try { T t = Get(id); if (t != null) { return(Delete(t)); } else { return(false); } } catch (Exception ex) { LogHelper.Error(ex); WriteDataBase.CloseSharedConnection(); return(false); } finally { WriteDataBase.CloseSharedConnection(); } }
/// <summary> /// 快捷添加系统默认导航 /// </summary> public int Add(string parent_name, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type) { //先根据名称查询该父ID StringBuilder strSql1 = new StringBuilder(); strSql1.Append("select top 1 id from " + databaseprefix + "navigation"); strSql1.Append(" where name=@0"); object obj1 = ReadDataBase.ExecuteScalar <object>(strSql1.ToString(), parent_name); if (obj1 == null) { return(0); } int parent_id = Convert.ToInt32(obj1); StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "navigation("); strSql.Append("parent_id,channel_id,nav_type,name,title,link_url,sort_id,action_type,is_lock,is_sys)"); strSql.Append(" values ("); strSql.Append("@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)"); strSql.Append(";SELECT @@@IDENTITY;"); object obj2 = WriteDataBase.ExecuteScalar <object>(strSql.ToString(), parent_id, channel_id, DTEnums.NavigationEnum.System.ToString(), nav_name, title, link_url, sort_id, action_type, 0, 1); return(Convert.ToInt32(obj2)); }
/// <summary> /// 删除已移除的扩展字段及频道数据表列 /// </summary> private void FieldDelete(IDbConnection conn, IDbTransaction trans, Model.site_channel newModel, Model.site_channel oldModel) { if (oldModel.channel_fields == null) { return; } string fieldIds = string.Empty; foreach (Model.site_channel_field modelt in oldModel.channel_fields) { //查找对应的字段ID,不在旧实体则删除 if (newModel.channel_fields.Find(p => p.field_id == modelt.field_id) == null) { //记住要删除的字段ID fieldIds += modelt.field_id + ","; //删除该旧字段 WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "site_channel_field where channel_id=" + newModel.id + " and field_id=" + modelt.field_id); } } //删除频道数据表列 if (fieldIds.Length > 0) { List <Model.article_attribute_field> field = new List <Model.article_attribute_field>(); field = WriteDataBase.Query <Model.article_attribute_field>(conn, trans, Sql.Builder.Select("id,name").From(databaseprefix + "article_attribute_field").Where("id in(" + fieldIds.TrimEnd(',') + ")")).ToList(); foreach (var dr in field) { //删除频道数据表列 ReadDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + " drop column " + dr.name); } } }
/// <summary> /// 更新或保存记录 /// </summary> /// <param name="t"></param> /// <returns></returns> public bool SaveOrUpdate(T t) { try { Type type = typeof(T); object objPrimary = type.GetProperty(PrimaryKey).GetValue(t, null); if (objPrimary == null) { return(Save(t)); } else { return(Update(t)); } } catch (Exception ex) { LogHelper.Error(ex); WriteDataBase.CloseSharedConnection(); return(false); } finally { WriteDataBase.CloseSharedConnection(); } }
/// <summary> /// 更新数据不为默认 /// </summary> public void UpDefault(IDbConnection conn, IDbTransaction trans) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "sites set is_default=0 where is_default=1"); WriteDataBase.Execute(conn, trans, strSql.ToString()); }
/// <summary> /// 得到一个对象实体 /// </summary> public Model.weixin_request_rule GetModel(int account_id, int request_type) { StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); Model.weixin_request_rule model = new Model.weixin_request_rule(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); foreach (PropertyInfo p in pros) { //拼接字段,忽略List<T> if (!p.Name.Equals("values") && !p.Name.Equals("contents")) { str1.Append(p.Name + ","); } } strSql.Append("select top 1 " + str1.ToString().Trim(',')); strSql.Append(" from " + databaseprefix + "weixin_request_rule"); strSql.AppendFormat(" where account_id={0} and request_type='{1}'", account_id, request_type); DataSet ds = WriteDataBase.QueryFillDataSet(strSql.ToString()); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 执行插件SQL语句 /// </summary> public bool ExeSqlStr(string dirPath, string xPath) { bool result = true; int count = 0; List <string> ls = ReadChildNodesValue(dirPath + DTKeys.FILE_PLUGIN_XML_CONFING, xPath); if (ls != null) { using (IDbConnection conn = new DapperView().Context()) { using (IDbTransaction trans = conn.BeginTransaction()) { try { count += WriteDataBase.ExecuteSqlTran(conn, trans, ls); trans.Commit(); return(count > 0); } catch (Exception ex) { trans.Rollback(); return(false); } finally { conn.Close(); } } } } return(result); }
/// <summary> /// 删除一条数据,及子表所有相关数据 /// </summary> public bool Delete(int id) { List <string> sqllist = new List <string>(); //删除管理角色权限 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "manager_role_value "); strSql.Append(" where role_id=@0"); sqllist.Add(strSql.ToString()); //删除管理角色 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "manager_role "); strSql2.Append(" where id=@0"); sqllist.Add(strSql2.ToString()); int rowsAffected = WriteDataBase.ExecuteSqlTran(sqllist, id); if (rowsAffected > 0) { return(true); } else { return(false); } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_amount_log model) { int i = 0; using (IDbConnection conn = new DapperView().Context()) { using (IDbTransaction trans = conn.BeginTransaction()) { try { #region 主表信息========================== StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); //数据字段 StringBuilder str2 = new StringBuilder(); //数据参数 //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); List <object> paras = new List <object>(); strSql.Append("insert into " + databaseprefix + "user_amount_log("); foreach (PropertyInfo pi in pros) { //如果不是主键则追加sql字符串 if (!pi.Name.Equals("id")) { //判断属性值是否为空 if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals("")) { str1.Append(pi.Name + ","); //拼接字段 str2.Append("@" + i + ","); //声明参数 i++; paras.Add(pi.GetValue(model, null)); //对参数赋值 } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(") values ("); strSql.Append(str2.ToString().Trim(',')); strSql.Append(") "); strSql.Append(";SELECT @@@IDENTITY;"); object obj = WriteDataBase.ExecuteScalar <object>(conn, trans, strSql.ToString(), paras.ToArray()); model.id = Convert.ToInt32(obj); #endregion #region 用户表信息======================== StringBuilder strSql1 = new StringBuilder(); strSql1.Append("update " + databaseprefix + "users set amount=amount+" + model.value); strSql1.Append(" where id=@0"); WriteDataBase.Execute(conn, trans, strSql1.ToString(), model.user_id); #endregion trans.Commit();//提交事务 } catch (Exception ex) { trans.Rollback();//回滚事务 return(0); } } } return(model.id); }
/// <summary> /// 查找不存在的图片并删除已移除的图片及数据 /// </summary> public void DeleteList(IDbConnection conn, IDbTransaction trans, List <Model.article_albums> models, int channel_id, 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 delIds = idList.ToString().TrimEnd(','); string strwhere = "channel_id=" + channel_id + " and article_id=" + article_id; if (!string.IsNullOrEmpty(delIds)) { strwhere += " and id not in(" + delIds + ")"; } List <Model.article_albums> albums = new List <Model.article_albums>(); albums = WriteDataBase.Query <Model.article_albums>(conn, trans, Sql.Builder.Select("channel_id,id,thumb_path,original_path").From(TableName).Where(strwhere)).ToList(); foreach (var dr in albums) { int rows = WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr.id); //删除数据库 if (rows > 0) { FileHelper.DeleteFile(dr.thumb_path); //删除缩略图 FileHelper.DeleteFile(dr.original_path); //删除原图 } } }
/// <summary> /// 删除文章对应的Tags标签关系 /// </summary> public bool Delete(IDbConnection conn, IDbTransaction trans, int channel_id, int article_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "article_tags_relation"); strSql.Append(" where channel_id=@0 and article_id=@1"); return(WriteDataBase.Execute(strSql.ToString(), channel_id, article_id) > 0); }
/// <summary> /// 修改一列数据 /// </summary> public bool UpdateField(string channel_name, int id, string strValue) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " set " + strValue); strSql.Append(" where id=" + id); return(WriteDataBase.Execute(strSql.ToString()) > 0); }
/// <summary> /// 删除7天前的日志数据 /// </summary> public int Delete(int dayCount) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "manager_log "); strSql.Append(" where DATEDIFF(day, add_time, getdate()) > " + dayCount); return(WriteDataBase.Delete(strSql.ToString())); }
/// <summary> /// 删除一条数据,带事务 /// </summary> public bool Delete(IDbConnection conn, IDbTransaction trans, string nav_name) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "navigation"); strSql.Append(" where name=@0"); return(WriteDataBase.Execute(conn, trans, strSql.ToString(), nav_name) > 0); }
/// <summary> /// 获取会员组折扣 /// </summary> public int GetDiscount(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 discount from " + databaseprefix + "user_groups"); strSql.Append(" where id=" + id); return(WriteDataBase.Execute(strSql.ToString())); }
/// <summary> /// 修改一列数据 /// </summary> public int UpdateField(int id, string strValue) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "users set " + strValue); strSql.Append(" where id=" + id); return(WriteDataBase.Execute(strSql.ToString())); }
/// <summary> /// 修改一列数据 /// </summary> public bool UpdateField(string order_no, string strValue) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "orders set " + strValue); strSql.Append(" where order_no='" + order_no + "'"); return(WriteDataBase.Execute(strSql.ToString()) > 0); }
/// <summary> /// 修改一条记录,带事务 /// </summary> public bool Update(IDbConnection conn, IDbTransaction trans, string old_name, string new_name) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "navigation set name=@0"); strSql.Append(" where name=@1"); int rows = WriteDataBase.Execute(conn, trans, strSql.ToString(), new_name, old_name); return(rows > 0); }
/// <summary> /// 修改一列数据 /// </summary> public bool UpdateField(string build_path, string strValue) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "sites set " + strValue); strSql.Append(" where build_path=@0"); int rows = WriteDataBase.Execute(strSql.ToString(), build_path); return(rows > 0); }
/// <summary> /// 修改一列数据 /// </summary> public bool UpdateField(int id, string strValue) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "sites set " + strValue); strSql.Append(" where id=" + id); int rows = WriteDataBase.Execute(strSql.ToString()); return(rows > 0); }
/// <summary> /// 根据用户名删除一条数据 /// </summary> public bool Delete(int id, string user_name) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "user_point_log "); strSql.Append(" where id=@0 and user_name=@1"); int rows = WriteDataBase.Execute(strSql.ToString(), id, user_name); return(rows > 0); }
/// <summary> /// 增加一条数据 /// </summary> public void Add(IDbConnection conn, IDbTransaction trans, List <Model.article_albums> models, int channel_id, int article_id) { int i = 0; if (models != null) { StringBuilder strSql; StringBuilder str1;; //数据字段 StringBuilder str2; //数据参数 foreach (Model.article_albums modelt in models) { i = 0; strSql = new StringBuilder(); str1 = new StringBuilder(); str2 = new StringBuilder(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = modelt.GetType().GetProperties(); List <object> paras = new List <object>(); strSql.Append("insert into " + databaseprefix + "article_albums("); foreach (PropertyInfo pi in pros) { //如果不是主键则追加sql字符串 if (!pi.Name.Equals("id")) { //判断属性值是否为空 if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals("")) { str1.Append(pi.Name + ","); //拼接字段 str2.Append("@" + i + ","); //声明参数 i++; switch (pi.Name) { case "channel_id": paras.Add(channel_id); break; case "article_id": paras.Add(article_id); //刚插入的文章ID break; default: paras.Add(pi.GetValue(modelt, null)); //对参数赋值 break; } } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(") values ("); strSql.Append(str2.ToString().Trim(',')); strSql.Append(") "); WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray());//带事务 } } }
/// <summary> /// 根据用户名删除一条数据 /// </summary> public bool Delete(int id, string user_name) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "user_message "); strSql.Append(" where id=@0 and (post_user_name=@1 or accept_user_name=@2)"); int rows = WriteDataBase.Execute(strSql.ToString(), id, user_name, user_name); return(rows > 0); }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "navigation"); strSql.Append(" where id in(" + GetIds(id) + ")"); int rows = WriteDataBase.Execute(strSql.ToString()); return(rows > 0); }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetImagesList(int article_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,article_id,thumb_path,original_path,sort_id,remark,add_time,channel_id "); strSql.Append(" FROM " + databaseprefix + "article_albums "); strSql.Append(" where article_id=" + article_id); strSql.Append(" order by sort_id asc, id asc"); DataSet ds = WriteDataBase.QueryFillDataSet(strSql.ToString()); return(ds.Tables[0]); }
/// <summary> /// 快捷添加系统默认导航,带事务 /// </summary> public int Add(IDbConnection conn, IDbTransaction trans, int parent_id, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type, int is_lock) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "navigation("); strSql.Append("parent_id,channel_id,nav_type,name,title,link_url,sort_id,action_type,is_sys,is_lock)"); strSql.Append(" values ("); strSql.Append("@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)"); strSql.Append(";SELECT @@@IDENTITY;"); object obj = WriteDataBase.ExecuteScalar <object>(conn, trans, strSql.ToString(), parent_id, channel_id, DTEnums.NavigationEnum.System.ToString(), nav_name, title, link_url, sort_id, action_type, 1, is_lock); return(Convert.ToInt32(obj)); }
/// <summary> /// 删除已移除的图片扩展字段 /// </summary> private void ThumDelete(IDbConnection conn, IDbTransaction trans, List <Model.site_channel_thum> thums, int channel_id) { List <Model.site_channel_thum> thum = new List <Model.site_channel_thum>(); thum = WriteDataBase.Query <Model.site_channel_thum>(conn, trans, Sql.Builder.Select("id").From(databaseprefix + "site_channel_thum").Where("channel_id=" + channel_id)).ToList(); foreach (var dr in thum) { Model.site_channel_thum model = thums.Find(p => p.id == dr.id); //查找对应的字段ID if (model == null) { WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "site_channel_thum where channel_id=" + channel_id + " and id=" + dr.id);//删除该行 } } }
/// <summary> /// 修改一条记录,带事务 /// </summary> public bool Update(IDbConnection conn, IDbTransaction trans, string old_name, int parent_id, string nav_name, string title, int sort_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "navigation set"); strSql.Append(" parent_id=@0,"); strSql.Append(" name=@1,"); strSql.Append(" title=@2,"); strSql.Append(" sort_id=@3"); strSql.Append(" where name=@4"); int rows = WriteDataBase.Execute(conn, trans, strSql.ToString(), parent_id, nav_name, title, sort_id, old_name); return(rows > 0); }
/// <summary> /// 执行插件SQL语句 /// </summary> public bool ExeSqlStrs(string dirPath, string xPath) { bool result = true; List <string> ls = ReadChildNodesValue(dirPath + DTKeys.FILE_PLUGIN_XML_CONFING, xPath); if (ls != null) { if (WriteDataBase.ExecuteSqlTran(ls) == 0) { result = false; } } return(result); }