/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.user_recharge model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <MySqlParameter> paras = new List <MySqlParameter>();
strSql.Append("update " + databaseprefix + "user_recharge set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null)
{
str1.Append(pi.Name + "=@" + pi.Name + ","); //声明参数
paras.Add(new MySqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(" where id=@id ");
paras.Add(new MySqlParameter("@id", model.id));
return(DbHelperMySql.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0);
}
/// <summary>
/// 查找不存在的文件并删除已移除的附件及数据
/// </summary>
public void DeleteList(MySqlConnection conn, MySqlTransaction trans, List <Model.article_attach> models, int channel_id, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_attach modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string delIds = idList.ToString().TrimEnd(',');
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,file_path from " + databaseprefix + "article_attach where channel_id=" + channel_id + " and article_id=" + article_id);
if (!string.IsNullOrEmpty(delIds))
{
strSql.Append(" and id not in(" + delIds + ")");
}
DataSet ds = DbHelperMySql.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperMySql.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_attach where id=" + dr["id"].ToString()); //删除数据库
if (rows > 0)
{
FileHelper.DeleteFile(dr["file_path"].ToString()); //删除文件
}
}
}
/// <summary>
/// 快捷添加系统默认导航，带事务
/// </summary>
public int Add(MySqlConnection conn, MySqlTransaction trans, int parent_id, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type)
{
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("@parent_id,@channel_id,@nav_type,@name,@title,@link_url,@sort_id,@action_type,@is_lock,@is_sys)");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_id", MySqlDbType.Int32, 4),
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@nav_type", MySqlDbType.VarChar, 50),
new MySqlParameter("@name", MySqlDbType.VarChar, 50),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@link_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 500),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4)
};
parameters[0].Value = parent_id;
parameters[1].Value = channel_id;
parameters[2].Value = DTEnums.NavigationEnum.System.ToString();
parameters[3].Value = nav_name;
parameters[4].Value = title;
parameters[5].Value = link_url;
parameters[6].Value = sort_id;
parameters[7].Value = action_type;
parameters[8].Value = 0;
parameters[9].Value = 1;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
return(GetMaxId(conn, trans));
}
/// <summary>
/// 删除及重建该频道视图
/// </summary>
public void RehabChannelViews(MySqlConnection conn, MySqlTransaction trans, Model.channel model, string old_name)
{
//删除旧的视图
StringBuilder strSql1 = new StringBuilder();
//strSql1.Append("if exists (select 1 from sysobjects where id = object_id('view_channel_" + old_name + "') and type = 'V')");
try
{
strSql1.Append("drop view view_channel_" + old_name);
DbHelperMySql.ExecuteSql(conn, trans, strSql1.ToString());
}
catch { }
//添加视图
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("CREATE VIEW view_channel_" + model.name + " as");
strSql2.Append(" SELECT " + databaseprefix + "article.*");
if (model.channel_fields != null)
{
foreach (Model.channel_field modelt in model.channel_fields)
{
Model.article_attribute_field fieldModel = new DAL.Mysql.article_attribute_field(databaseprefix).GetModel(modelt.field_id);
if (fieldModel != null)
{
strSql2.Append("," + databaseprefix + "article_attribute_value." + fieldModel.name);
}
}
}
strSql2.Append(" FROM " + databaseprefix + "article_attribute_value INNER JOIN");
strSql2.Append(" " + databaseprefix + "article ON " + databaseprefix + "article_attribute_value.article_id = " + databaseprefix + "article.id");
strSql2.Append(" WHERE " + databaseprefix + "article.channel_id=" + model.id);
DbHelperMySql.ExecuteSql(conn, trans, strSql2.ToString());
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.sms_template model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "sms_template set ");
strSql.Append("title=@title,");
strSql.Append("call_index=@call_index,");
strSql.Append("content=@content,");
strSql.Append("is_sys=@is_sys");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@call_index", MySqlDbType.VarChar, 50),
new MySqlParameter("@content", MySqlDbType.LongText),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.title;
parameters[1].Value = model.call_index;
parameters[2].Value = model.content;
parameters[3].Value = model.is_sys;
parameters[4].Value = model.id;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.manager_role model)
{
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "manager_role(");
strSql.Append("role_name,role_type,is_sys)");
strSql.Append(" values (");
strSql.Append("@role_name,@role_type,@is_sys)");
MySqlParameter[] parameters =
{
new MySqlParameter("@role_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@role_type", MySqlDbType.Int32, 4),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.role_name;
parameters[1].Value = model.role_type;
parameters[2].Value = model.is_sys;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
model.id = GetMaxId(conn, trans);
StringBuilder strSql2;
if (model.manager_role_values != null)
{
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "manager_role_value(");
strSql2.Append("role_id,nav_name,action_type)");
strSql2.Append(" values (");
strSql2.Append("@role_id,@nav_name,@action_type)");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@role_id", MySqlDbType.Int32, 4),
new MySqlParameter("@nav_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 50)
};
parameters2[0].Value = model.id;
parameters2[1].Value = modelt.nav_name;
parameters2[2].Value = modelt.action_type;
DbHelperMySql.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
}
}
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(model.id);
}
/// <summary>
/// ²éÕÒ²»´æÔÚµÄͼƬ²¢É¾³ýÒÑɾ³ýµÄͼƬ¼°Êý¾Ý
/// </summary>
public void DeleteList(MySqlConnection conn, MySqlTransaction 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,thumb_path,original_path from " + databaseprefix + "article_albums where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperMySql.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperMySql.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr["id"].ToString()); //ɾ³ýÊý¾Ý¿â
if (rows > 0)
{
Utils.DeleteFile(dr["thumb_path"].ToString()); //ɾ³ýËõÂÔͼ
Utils.DeleteFile(dr["original_path"].ToString()); //ɾ³ýÔͼ
}
}
}
/// <summary>
/// 删除已移除的扩展字段及频道数据表列
/// </summary>
private void FieldDelete(MySqlConnection conn, MySqlTransaction 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 + ",";
//删除该旧字段
DbHelperMySql.ExecuteSql(conn, trans, "delete from " + databaseprefix + "site_channel_field where channel_id=" + newModel.id + " and field_id=" + modelt.field_id);
}
}
//删除频道数据表列
if (fieldIds.Length > 0)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,[name] from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id in(" + fieldIds.TrimEnd(',') + ")");
DataSet ds = DbHelperMySql.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
//删除频道数据表列
DbHelperMySql.ExecuteSql(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + " drop column " + dr["name"].ToString());
}
}
}
/// <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=@id and user_name=@user_name");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100)
};
parameters[0].Value = id;
parameters[1].Value = user_name;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.message model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update message set ");
if (model.MSG != null)
{
strSql.Append("MSG='" + model.MSG + "',");
}
else
{
strSql.Append("MSG= null ,");
}
if (model.RLZBH != null)
{
strSql.Append("RLZBH='" + model.RLZBH + "',");
}
else
{
strSql.Append("RLZBH= null ,");
}
if (model.RLR != null)
{
strSql.Append("RLR='" + model.RLR + "',");
}
else
{
strSql.Append("RLR= null ,");
}
if (model.RLSJ != null)
{
strSql.Append("RLSJ='" + model.RLSJ + "',");
}
else
{
strSql.Append("RLSJ= null ,");
}
if (model.RLDMM != null)
{
strSql.Append("RLDMM='" + model.RLDMM + "',");
}
else
{
strSql.Append("RLDMM= null ,");
}
int n = strSql.ToString().LastIndexOf(",");
strSql.Remove(n, 1);
strSql.Append(" where MSGCD='" + model.MSGCD + "' ");
int rowsAffected = DbHelperMySql.ExecuteSql(strSql.ToString());
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 修改一条记录，带事务
/// </summary>
public bool Update(MySqlConnection conn, MySqlTransaction 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=@parent_id,");
strSql.Append(" name=@name,");
strSql.Append(" title=@title,");
strSql.Append(" sort_id=@sort_id");
strSql.Append(" where name=@old_name");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_id", MySqlDbType.Int32, 4),
new MySqlParameter("@name", MySqlDbType.VarChar, 50),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@old_name", MySqlDbType.VarChar, 50)
};
parameters[0].Value = parent_id;
parameters[1].Value = nav_name;
parameters[2].Value = title;
parameters[3].Value = sort_id;
parameters[4].Value = old_name;
return(DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters) > 0);
}
/// <summary>
/// 创建一个只有一个Id主键的空表
/// </summary>
/// <param name="tablename"></param>
/// <param name="defaultColumns"></param>
/// <returns></returns>
public bool CreateTable(string tablename, params string[] defaultColumns)
{
if (_providerConfig.DbProvider == DbProvider.MySql)
{
var sqlClause = string.Format(@" CREATE TABLE IF NOT EXISTS `{0}` (`Id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;", tablename);
DbHelperMySql.ExecuteSql(_providerConfig.DbConnectionString, sqlClause);
}
else
{
var sqlClause = string.Format(@"IF NOT EXISTS(SELECT * FROM sysobjects where id=OBJECT_ID('{0}') and xtype='U')
BEGIN CREATE TABLE {0} (
[Id] INT NOT NULL IDENTITY,
PRIMARY KEY CLUSTERED ([Id] ASC));
END", tablename);
DbHelperSql.ExecuteSql(_providerConfig.DbConnectionString, sqlClause);
}
foreach (var column in defaultColumns)
{
if (!string.IsNullOrEmpty(column) && !ColumHasExists(tablename, column))
{
AddColumn(tablename, column, TemplateColumnDataType.TemplateString, 255);
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.link model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "link set ");
strSql.Append("site_path=@site_path,");
strSql.Append("title=@title,");
strSql.Append("user_name=@user_name,");
strSql.Append("user_tel=@user_tel,");
strSql.Append("email=@email,");
strSql.Append("site_url=@site_url,");
strSql.Append("img_url=@img_url,");
strSql.Append("is_image=@is_image,");
strSql.Append("sort_id=@sort_id,");
strSql.Append("is_red=@is_red,");
strSql.Append("is_lock=@is_lock,");
strSql.Append("add_time=@add_time");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@site_path", MySqlDbType.VarChar, 100),
new MySqlParameter("@title", MySqlDbType.VarChar, 255),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 50),
new MySqlParameter("@user_tel", MySqlDbType.VarChar, 20),
new MySqlParameter("@email", MySqlDbType.VarChar, 50),
new MySqlParameter("@site_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@img_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@is_image", MySqlDbType.Int32, 4),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@is_red", MySqlDbType.Int32, 4),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@add_time", MySqlDbType.DateTime),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.site_path;
parameters[1].Value = model.title;
parameters[2].Value = model.user_name;
parameters[3].Value = model.user_tel;
parameters[4].Value = model.email;
parameters[5].Value = model.site_url;
parameters[6].Value = model.img_url;
parameters[7].Value = model.is_image;
parameters[8].Value = model.sort_id;
parameters[9].Value = model.is_red;
parameters[10].Value = model.is_lock;
parameters[11].Value = model.add_time;
parameters[12].Value = model.id;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_attribute_field model = GetModel(id);//取得扩展字段实体
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
//删除所关联的频道数据表相关列
DataTable dt = new DAL.MySql.site_channel(databaseprefix).GetFieldList(conn, trans, id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = Convert.ToInt32(DbHelperMySql.GetSingle(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + model.name + "'"));
if (rowsCount > 0)
{
//删除频道数据表一列
DbHelperMySql.ExecuteSql(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " drop column " + model.name);
}
}
}
//删除频道关联字段表
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "site_channel_field");
strSql1.Append(" where field_id=@field_id");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@field_id", MySqlDbType.Int32, 4)
};
parameters1[0].Value = id;
DbHelperMySql.ExecuteSql(conn, trans, strSql1.ToString(), parameters1);
//删除扩展字段主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
trans.Commit();//提交事务
}
catch
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 修改一列数据
/// </summary>
public void UpdateField(int id, string strValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "payment set " + strValue);
strSql.Append(" where id=" + id);
DbHelperMySql.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 根据条件批量删除
/// </summary>
public bool Delete(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "user_code");
strSql.Append(" where " + strWhere);
return(DbHelperMySql.ExecuteSql(strSql.ToString()) > 0);
}
/// <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(DbHelperMySql.ExecuteSql(strSql.ToString()) > 0);
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_category ");
strSql.Append(" where class_list like '%," + id + ",%'");
return(DbHelperMySql.ExecuteSql(strSql.ToString()) > 0);
}
/// <summary>
/// 修改一列数据
/// </summary>
public bool UpdateField(string name, string strValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "navigation set " + strValue);
strSql.Append(" where name='" + name + "'");
return(DbHelperMySql.ExecuteSql(strSql.ToString()) > 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(DbHelperMySql.ExecuteSql(strSql.ToString()) > 0);
}
/// <summary>
/// 修改一列数据
/// </summary>
public bool UpdateField(int id, string strValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "site_channel set " + strValue);
strSql.Append(" where id=" + id);
return(DbHelperMySql.ExecuteSql(strSql.ToString()) > 0);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.navigation model)
{
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "navigation(");
strSql.Append("parent_id,channel_id,nav_type,name,title,sub_title,icon_url,link_url,sort_id,is_lock,remark,action_type,is_sys)");
strSql.Append(" values (");
strSql.Append("@parent_id,@channel_id,@nav_type,@name,@title,@sub_title,@icon_url,@link_url,@sort_id,@is_lock,@remark,@action_type,@is_sys)");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_id", MySqlDbType.Int32, 4),
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@nav_type", MySqlDbType.VarChar, 50),
new MySqlParameter("@name", MySqlDbType.VarChar, 50),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@sub_title", MySqlDbType.VarChar, 100),
new MySqlParameter("@icon_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@link_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@remark", MySqlDbType.VarChar, 500),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 500),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.parent_id;
parameters[1].Value = model.channel_id;
parameters[2].Value = model.nav_type;
parameters[3].Value = model.name;
parameters[4].Value = model.title;
parameters[5].Value = model.sub_title;
parameters[6].Value = model.icon_url;
parameters[7].Value = model.link_url;
parameters[8].Value = model.sort_id;
parameters[9].Value = model.is_lock;
parameters[10].Value = model.remark;
parameters[11].Value = model.action_type;
parameters[12].Value = model.is_sys;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
model.id = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(model.id);
}
///// <summary>
///// 删除7天前的日志数据
///// </summary>
public int Delete(int dayCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "manager_log");
strSql.Append(" where DATEDIFF(d, add_time, getdate())>" + dayCount);
return(DbHelperMySql.ExecuteSql(strSql.ToString()));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_point_log model, bool is_upgrade)
{
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "user_point_log(");
strSql.Append("user_id,user_name,value,remark,add_time)");
strSql.Append(" values (");
strSql.Append("@user_id,@user_name,@value,@remark,@add_time)");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@value", MySqlDbType.Int32, 4),
new MySqlParameter("@remark", MySqlDbType.VarChar, 500),
new MySqlParameter("@add_time", MySqlDbType.Date)
};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.value;
parameters[3].Value = model.remark;
parameters[4].Value = model.add_time;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
model.id = GetMaxId(conn, trans);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update " + databaseprefix + "users set point=point+" + model.value);
if (model.value > 0 && is_upgrade)
{
strSql2.Append(",exp=exp+" + model.value);
}
strSql2.Append(" where id=@id");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters2[0].Value = model.user_id;
DbHelperMySql.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(model.id);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_recharge model)
{
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "user_recharge(");
strSql.Append("user_id,user_name,recharge_no,payment_id,amount,status,add_time,complete_time)");
strSql.Append(" values (");
strSql.Append("@user_id,@user_name,@recharge_no,@payment_id,@amount,@status,@add_time,@complete_time)");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@recharge_no", MySqlDbType.VarChar, 100),
new MySqlParameter("@payment_id", MySqlDbType.Int32, 4),
new MySqlParameter("@amount", MySqlDbType.Decimal, 5),
new MySqlParameter("@status", MySqlDbType.Int32, 4),
new MySqlParameter("@add_time", MySqlDbType.Date),
new MySqlParameter("@complete_time", MySqlDbType.Date)
};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.recharge_no;
parameters[3].Value = model.payment_id;
parameters[4].Value = model.amount;
parameters[5].Value = model.status;
parameters[6].Value = model.add_time;
if (model.complete_time != null)
{
parameters[7].Value = model.complete_time;
}
else
{
parameters[7].Value = DBNull.Value;
}
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_message model)
{
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "user_message(");
strSql.Append("type,post_user_name,accept_user_name,is_read,title,content,post_time,read_time)");
strSql.Append(" values (");
strSql.Append("@type,@post_user_name,@accept_user_name,@is_read,@title,@content,@post_time,@read_time)");
MySqlParameter[] parameters =
{
new MySqlParameter("@type", MySqlDbType.Int32, 4),
new MySqlParameter("@post_user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@accept_user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@is_read", MySqlDbType.Int32, 4),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@content", MySqlDbType.VarChar),
new MySqlParameter("@post_time", MySqlDbType.Date),
new MySqlParameter("@read_time", MySqlDbType.Date)
};
parameters[0].Value = model.type;
parameters[1].Value = model.post_user_name;
parameters[2].Value = model.accept_user_name;
parameters[3].Value = model.is_read;
parameters[4].Value = model.title;
parameters[5].Value = model.content;
parameters[6].Value = model.post_time;
if (model.read_time != null)
{
parameters[7].Value = model.read_time;
}
else
{
parameters[7].Value = DBNull.Value;
}
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.feedback model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "feedback set ");
strSql.Append("site_path=@site_path,");
strSql.Append("title=@title,");
strSql.Append("content=@content,");
strSql.Append("user_name=@user_name,");
strSql.Append("user_tel=@user_tel,");
strSql.Append("user_qq=@user_qq,");
strSql.Append("user_email=@user_email,");
strSql.Append("add_time=@add_time,");
strSql.Append("reply_content=@reply_content,");
strSql.Append("reply_time=@reply_time,");
strSql.Append("is_lock=@is_lock");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@site_path", MySqlDbType.VarChar, 100),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@content", SqlDbType.NText),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 50),
new MySqlParameter("@user_tel", MySqlDbType.VarChar, 30),
new MySqlParameter("@user_qq", MySqlDbType.VarChar, 30),
new MySqlParameter("@user_email", MySqlDbType.VarChar, 100),
new MySqlParameter("@add_time", MySqlDbType.DateTime),
new MySqlParameter("@reply_content", MySqlDbType.LongText),
new MySqlParameter("@reply_time", MySqlDbType.DateTime),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.site_path;
parameters[1].Value = model.title;
parameters[2].Value = model.content;
parameters[3].Value = model.user_name;
parameters[4].Value = model.user_tel;
parameters[5].Value = model.user_qq;
parameters[6].Value = model.user_email;
parameters[7].Value = model.add_time;
parameters[8].Value = model.reply_content;
parameters[9].Value = model.reply_time;
parameters[10].Value = model.is_lock;
parameters[11].Value = model.id;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.user_amount_log model)
{
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "user_amount_log set ");
strSql.Append("user_id=@user_id,");
strSql.Append("user_name=@user_name,");
strSql.Append("value=@value,");
strSql.Append("remark=@remark,");
strSql.Append("add_time=@add_time");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@value", MySqlDbType.Decimal, 5),
new MySqlParameter("@remark", MySqlDbType.VarChar, 500),
new MySqlParameter("@add_time", MySqlDbType.Date),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.value;
parameters[3].Value = model.remark;
parameters[4].Value = model.add_time;
parameters[5].Value = model.id;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("update " + databaseprefix + "users set amount=amount+" + model.value);
strSql2.Append(" where id=@id");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters2[0].Value = model.user_id;
DbHelperMySql.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
trans.Commit();
}
catch
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.manager model)
{
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "manager(");
strSql.Append("role_id,role_type,user_name,password,salt,real_name,telephone,email,is_lock,add_time)");
strSql.Append(" values (");
strSql.Append("@role_id,@role_type,@user_name,@password,@salt,@real_name,@telephone,@email,@is_lock,@add_time)");
MySqlParameter[] parameters =
{
new MySqlParameter("@role_id", MySqlDbType.Int32, 4),
new MySqlParameter("@role_type", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@password", MySqlDbType.VarChar, 100),
new MySqlParameter("@salt", MySqlDbType.VarChar, 20),
new MySqlParameter("@real_name", MySqlDbType.VarChar, 50),
new MySqlParameter("@telephone", MySqlDbType.VarChar, 30),
new MySqlParameter("@email", MySqlDbType.VarChar, 30),
new MySqlParameter("@is_lock", MySqlDbType.Int32, 4),
new MySqlParameter("@add_time", MySqlDbType.Date)
};
parameters[0].Value = model.role_id;
parameters[1].Value = model.role_type;
parameters[2].Value = model.user_name;
parameters[3].Value = model.password;
parameters[4].Value = model.salt;
parameters[5].Value = model.real_name;
parameters[6].Value = model.telephone;
parameters[7].Value = model.email;
parameters[8].Value = model.is_lock;
parameters[9].Value = model.add_time;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.user_message model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "user_message set ");
strSql.Append("type=@type,");
strSql.Append("post_user_name=@post_user_name,");
strSql.Append("accept_user_name=@accept_user_name,");
strSql.Append("is_read=@is_read,");
strSql.Append("title=@title,");
strSql.Append("content=@content,");
strSql.Append("post_time=@post_time,");
strSql.Append("read_time=@read_time");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@type", MySqlDbType.Int32, 4),
new MySqlParameter("@post_user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@accept_user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@is_read", MySqlDbType.Int32, 4),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@content", MySqlDbType.LongText),
new MySqlParameter("@post_time", MySqlDbType.Date),
new MySqlParameter("@read_time", MySqlDbType.Date),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.type;
parameters[1].Value = model.post_user_name;
parameters[2].Value = model.accept_user_name;
parameters[3].Value = model.is_read;
parameters[4].Value = model.title;
parameters[5].Value = model.content;
parameters[6].Value = model.post_time;
if (model.read_time != null)
{
parameters[7].Value = model.read_time;
}
else
{
parameters[7].Value = DBNull.Value;
}
parameters[8].Value = model.id;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}