/// <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);
}
