/// <summary>
/// 获取口令(公众号)
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="grant_Type">grant_Type</param>
/// <returns></returns>
public List <MAccess_Token> GetAccess_Token(IDbTransaction trans, IDbConnection conn, string grant_Type)
{
string sqlText = @"select KeyID,AppID,WeixinID,AppSecret,Access_Token,Grant_Type,AddTime,OutTime,ModifyTime,IsDelete from Access_Token WHERE Grant_Type=@Grant_Type and IsDelete = 0 ";
MySqlParameters destionParameters = new MySqlParameters();
destionParameters.Add(new MySqlParameter()
{
ParameterName = "@Grant_Type", MySqlDbType = MySqlDbType.VarChar, Value = grant_Type
});
List <MAccess_Token> access_TokenList = new List <MAccess_Token>();
using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray()))
{
MAccess_Token access_Token = null;
while (reader.Read())
{
access_Token = new MAccess_Token();
access_Token.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
access_Token.AppID = reader["AppID"] == DBNull.Value ? string.Empty : reader["AppID"].ToString();
access_Token.WeixinID = reader["WeixinID"] == DBNull.Value ? string.Empty : reader["WeixinID"].ToString();
access_Token.AppSecret = reader["AppSecret"] == DBNull.Value ? string.Empty : reader["AppSecret"].ToString();
access_Token.Access_Token = reader["Access_Token"] == DBNull.Value ? string.Empty : SecurityUtility.DecryptString(reader["Access_Token"].ToString());
access_Token.Grant_Type = reader["Grant_Type"] == DBNull.Value ? string.Empty : reader["Grant_Type"].ToString();
access_Token.AddTime = reader["AddTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["AddTime"]);
access_Token.OutTime = reader["OutTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["OutTime"]);
access_TokenList.Add(access_Token);
}
}
return(access_TokenList);
}
protected void Page_Load(object sender, EventArgs e)
{
string filePath = WebHelper.GetFilePath();
string fileName = QueryString["f"];
Response.Clear();
Response.Buffer = true;
this.EnableViewState = false;
if (!String.IsNullOrEmpty(fileName) && !fileName.StartsWith(".."))
{
if ("file".Equals(QueryString["t"]))
{
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Path.GetFileName(fileName)));
//Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/octet-stream";
}
//文件存在直接获取
if (File.Exists(filePath + fileName))
{
Response.WriteFile(filePath + fileName);
}
else
{
if (fileName.IndexOfAny(("' (").ToCharArray()) != -1)
{
return;
}
//需要从数据库先获取
DataTable dt = null;
MySqlParameters mySql = new MySqlParameters("InfoAdds");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add(" top 1 content");
mySql.Add("FilePath", fileName, "FilePath={0}");
try
{
dt = new DataFrom().GetDataTable(mySql);
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error(mySql.GetSql(DBType.Sql2005, false), ex);
}
if (dt != null &&
dt.Rows.Count == 1 &&
!Convert.IsDBNull(dt.Rows[0][0]))
{
byte[] content = (byte[])dt.Rows[0][0];
Response.BinaryWrite(content);
Response.Flush();
System.IO.FileStream fs = new System.IO.FileStream(filePath + fileName, System.IO.FileMode.CreateNew);
System.IO.BinaryWriter w = new System.IO.BinaryWriter(fs);
w.Write(content);
w.Close();
}
}
}
Response.End();
}
/// <summary>
/// 更新令牌
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="tokenstr">口令</param>
/// <param name="outTime">失效时间</param>
/// <returns>是否更新成功</returns>
public bool UpdateAccess_Token(IDbTransaction trans, IDbConnection conn, string keyID, string tokenstr, string refreshToken, DateTime outTime)
{
string insertsql = @"UPDATE Access_Token SET Access_Token = @Access_Token,OutTime = @OutTime";
MySqlParameters parameters = new MySqlParameters();
if (!string.IsNullOrEmpty(refreshToken))
{
insertsql += " ,RefreshToken = @RefreshToken ";
parameters.Add(new MySqlParameter()
{
ParameterName = "@RefreshToken", MySqlDbType = MySqlDbType.VarChar, Value = refreshToken
});
}
insertsql += " WHERE KeyID=@KeyID and IsDelete = 0 limit 1";
parameters.Add(new MySqlParameter()
{
ParameterName = "@KeyID", MySqlDbType = MySqlDbType.VarChar, Value = keyID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@Access_Token", MySqlDbType = MySqlDbType.VarChar, Value = tokenstr
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@OutTime", MySqlDbType = MySqlDbType.DateTime, Value = outTime
});
return(Common.DBUtility.MySqlHelper.ExecuteSql(null, conn, insertsql, parameters.ToArray()) == 1);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (txtPasswd.Text == txtPwdCheck.Text)
{
bool isOk = false;
MySqlParameters mySql = new MySqlParameters("s_user");
DataFrom dataFrom = new DataFrom();
try
{
mySql.EditSqlMode = SqlMode.Update;
mySql.Add("pwd", txtPasswd.Text);
mySql.Add("id", CurrentUser.Id, "and id={0}");
mySql.Add("oldpwd", txtPwdOld.Text, "and pwd={0}");
isOk = dataFrom.SqlExecute(mySql) == 1;
}
catch (Exception ex)
{
Logger.Error(SqlHelper.GetSql(mySql, dataFrom.Dbtype), ex);
}
if (isOk)
{
Alert("密码修改成功！下次请使用新密码");
}
else
{
Alert("原密码输入错误，请重输！");
}
}
else
{
Alert("两次密码输入不一致，请重输！");
}
}
/// <summary>
///将实体插入数据库
/// </summary>
/// <returns>主键值，当为String.Empty时执行插入失败</returns>
public string Insert()
{
MySqlParameters mySql = getMySql(SqlMode.Insert);
mySql.IsAddGetIDSql = true;
return(new Dao().GetScalar(mySql, true));
}
/// <summary>
/// 返回是否有此功能
/// </summary>
/// <returns></returns>
public bool HasFunction()
{
if (_User == null)
{
return(false);
}
else if (_User.IsAdmin())
{
return(true);
}
else if (String.IsNullOrEmpty(_User.Functions))
{
bool result = false;
MySqlParameters mySql = new MySqlParameters(SqlMode.Select);
mySql.Sql = @"select count(*) from s_rolefun rf
inner join s_roleuser ur on ur.roleid=rf.roleid";
mySql.Add("uid", _User.Id, "ur.uid={0}");
mySql.Add("funid", _functionCode, " and rf.funid={0}");
try
{
Dao dao = new Dao();
result = !"0".Equals(dao.GetScalar(mySql, true));
}
catch
{
result = false;
}
return(result);
}
else
{
return(DataHelper.InStrings(_User.Functions, _functionCode));
}
}
public string[] GetUserNameList(string prefixText, int count)
{
try
{
MySqlParameters mySql = new MySqlParameters("s_user");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("top " + count + " name+'['+id+']'");
mySql.Add("iflag", Constants.TRUE_ID, "and iflag={0}");
mySql.Add("id", "%" + prefixText + "%", "and (id like {0} or name like {0})");
/*
* string depid=this.Context.Request.QueryString["depid"];
* if (!String.IsNullOrEmpty(depid))
* {
* mySql.Add("depid", depid + '%', "and depid like {0}");
* }*/
return(DataHelper.GetDataFirstCol(new DataFrom().GetDataTable(mySql)));
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error("获得用户错误", ex);
return(null);
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
bool isOk = true;
try
{
//添加
MySqlParameters mySqlParameters = new MySqlParameters(null);
mySqlParameters.Add("name", name, null);
mySqlParameters.Add("title", txtName.Text, null);
mySqlParameters.Add("userid", ckbPrivate.Checked ? CurrentUser.Id : "public", null);
mySqlParameters.Add("creater", CurrentUser.Id, null);
isOk = Template.SaveSubjectData(mySqlParameters, MyInputs1.Columns);
}
catch (Exception ex)
{
isOk = false;
Logger.Error("添加查询方案失败", ex);
RedirectError("添加失败，请稍候再试。原因:" + ex.Message);
return;
}
if (isOk)
{
Close(null, false);
}
else
{
MyInputs1.DataBind();
MyGridView1.DataBind(Template.GetSubjectData(name, MyInputs1.Columns, CurrentUser.Id));
}
}
/// <summary>
///将实体修改提交到数据库
/// </summary>
/// <returns>是否成功</returns>
public bool Update()
{
bool result = false;
MySqlParameters mySql = getMySql(SqlMode.Update);
result = new Dao().SqlExecute(mySql, true) > 0;
return(result);
}
/// <summary>
/// 获得DataTable
/// </summary>
/// <param name="table">表名</param>
/// <param name="field">字段 多个用,分割</param>
/// <param name="where">条件表达式(参数用{0}比表示) 不包含where</param>
/// <param name="value">替换表达式的参数值</param>
/// <param name="count">获取数量 小于1表示不限制</param>
/// <returns></returns>
public DataTable GetTable(string table, string field, string where, string value, int count)
{
MySqlParameters mySql = new MySqlParameters(null);
mySql.Add("p", value, where);
mySql.Sql = SqlHelper.GetCountSql(dao.Dbtype, "select " + field + " from " + table, count);
return(dao.GetDataTable(mySql, true));
}
/// <summary>
///按照主键找到记录
/// </summary>
/// <param name="id">主键值</param>
/// <returns>包含一条记录的DataTable</returns>
public DataTable FindById(string id)
{
MySqlParameters mySql = new MySqlParameters("LogInfo");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("*");
mySql.Add("ID", id, "id={0}");
return(new Dao().GetDataTable(mySql, true));
}
/// <summary>
/// 根据MySqlParameters对象在数据库中执行SQl操作
/// </summary>
/// <param name="parameters">自定义Sql及参数列表</param>
/// <returns></returns>
public int SqlExecute(MySqlParameters parameters)
{
int result = dao.SqlExecute(parameters, true);
if (IsSysLog && result > 0)
{
new LogDeal().Write("执行操作语句:" + parameters.GetSql(dao.Dbtype, false));
}
return(result);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">自定义Sql参数列表 null表示无参数</param>
/// <returns></returns>
public string ProcedureExecute(string procName, MySqlParameters parameters)
{
if (parameters == null)
{
return(dao.ProcedureExecute(procName));
}
else
{
return(dao.ProcedureExecute(procName, parameters.GetDataParamters(dao.Dbtype)));
}
}
/// <summary>
/// 执行存储过程，返回DataTable
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">自定义Sql参数列表 null表示无参数</param>
/// <returns>不存在时为null</returns>
public DataTable GetDataTableProcedure(string procName, MySqlParameters parameters)
{
if (parameters == null)
{
return(dao.GetDataTableProcedure(procName));
}
else
{
return(dao.GetDataTableProcedure(procName, parameters.GetDataParamters(dao.Dbtype)));
}
}
/// <summary>
/// 获得字典数据
/// </summary>
/// <param name="id">代码ID</param>
/// <returns></returns>
public DataTable GetCodeTable(string id)
{
MySqlParameters mySql = new MySqlParameters("f_code");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("code");
mySql.Add("name");
mySql.Add("id", id, "iflag=1 and id={0}");
mySql.SqlEnd = " order by sn";
return(GetTable(mySql));
}
/// <summary>
/// 记录token
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="access_Token">令牌</param>
/// <returns>是否成功</returns>
public bool InsertAccess_Token(IDbTransaction trans, IDbConnection conn, MAccess_Token access_Token)
{
string insertsql = @"Insert into Access_Token(KeyID,AppID,WeixinID,AppSecret,Access_Token,RefreshToken,Grant_Type,OpenID,Scope,UnionID,AddTime,OutTime,ModifyTime,IsDelete
)VALUES(@KeyID,@AppID,@WeixinID,@AppSecret,@Access_Token,@RefreshToken,@Grant_Type,@OpenID,@Scope,@UnionID,now(),@OutTime,now(),0);";
MySqlParameters parameters = new MySqlParameters();
parameters.Add(new MySqlParameter()
{
ParameterName = "@KeyID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.KeyID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@AppID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.AppID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@WeixinID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.WeixinID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@AppSecret", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.AppSecret
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@Access_Token", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Access_Token
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@RefreshToken", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.RefreshToken
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@Grant_Type", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Grant_Type
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@OpenID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.OpenID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@Scope", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Scope
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@UnionID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.UnionID
});
parameters.Add(new MySqlParameter()
{
ParameterName = "@OutTime", MySqlDbType = MySqlDbType.DateTime, Value = access_Token.OutTime
});
return(Common.DBUtility.MySqlHelper.ExecuteSql(trans, conn, insertsql, parameters.ToArray()) == 1);
}
/// <summary>
/// 获得Sql集合
/// </summary>
/// <param name="sqlMode"></param>
/// <returns></returns>
private MySqlParameters getMySql(SqlMode sqlMode)
{
MySqlParameters mySql = new MySqlParameters("LogInfo");
mySql.EditSqlMode = sqlMode;
mySql.Add("UserId", _UserId);
mySql.Add("IP", _IP);
mySql.Add("Des", _Des);
mySql.Add("Optime", _Optime);
if (sqlMode == SqlMode.Update)
{
mySql.Add("ID", _id, "id={0}");
}
return(mySql);
}
/// <summary>
/// 修改用户密码
/// </summary>
/// <param name="old"></param>
/// <param name="pwd"></param>
public void UpdatePwd(string old, string pwd)
{
MySqlParameters mySql = new MySqlParameters(_table);
mySql.EditSqlMode = SqlMode.Update;
mySql.Add("pwd", pwd);
mySql.Add("userAccount", _myUser.Id, "and id={0}");
if (!String.IsNullOrEmpty(old))
{
mySql.Add("password", old, "and pwd={0}");
}
if (dao.SqlExecute(mySql, true) == 0)
{
_error = "修改密码错误";
}
}
/// <summary>
/// 更新令牌
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="tokenstr">口令</param>
/// <param name="outTime">失效时间</param>
/// <returns>是否更新成功</returns>
public bool UpdateAccess_Token(IDbTransaction trans, IDbConnection conn, string keyID, string tokenstr, string refreshToken, DateTime outTime)
{
string insertsql = @"UPDATE Access_Token SET Access_Token = @Access_Token,OutTime = @OutTime";
MySqlParameters parameters = new MySqlParameters();
if (!string.IsNullOrEmpty(refreshToken))
{
insertsql += " ,RefreshToken = @RefreshToken ";
parameters.Add(new MySqlParameter() { ParameterName = "@RefreshToken", MySqlDbType = MySqlDbType.VarChar, Value = refreshToken });
}
insertsql += " WHERE KeyID=@KeyID and IsDelete = 0 limit 1";
parameters.Add(new MySqlParameter() { ParameterName = "@KeyID", MySqlDbType = MySqlDbType.VarChar, Value = keyID });
parameters.Add(new MySqlParameter() { ParameterName = "@Access_Token", MySqlDbType = MySqlDbType.VarChar, Value = tokenstr });
parameters.Add(new MySqlParameter() { ParameterName = "@OutTime", MySqlDbType = MySqlDbType.DateTime, Value = outTime });
return Common.DBUtility.MySqlHelper.ExecuteSql(null, conn, insertsql, parameters.ToArray()) == 1;
}
/// <summary>
/// 获取口令（用户的）
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="appid">appid</param>
/// <param name="appSecret">appSecret</param>
/// <param name="openID">OpendID</param>
/// <param name="scope">Scope</param>
/// <returns>用户令牌</returns>
public MAccess_Token GetAccess_Token(IDbTransaction trans, IDbConnection conn, string appid, string appSecret, string openID, string scope)
{
string sqlText = @"select KeyID,AppID,AppSecret,WeixinID,Access_Token, RefreshToken,Grant_Type,OpenID,Scope,UnionID,AddTime,OutTime,ModifyTime,IsDelete from Access_Token
WHERE AppID = @AppID and AppSecret= @AppSecret and OpenID=@OpenID and Scope=@Scope and IsDelete = 0 limit 1";
MySqlParameters destionParameters = new MySqlParameters();
destionParameters.Add(new MySqlParameter()
{
ParameterName = "@AppID", MySqlDbType = MySqlDbType.VarChar, Value = appid
});
destionParameters.Add(new MySqlParameter()
{
ParameterName = "@AppSecret", MySqlDbType = MySqlDbType.VarChar, Value = appSecret
});
destionParameters.Add(new MySqlParameter()
{
ParameterName = "@OpenID", MySqlDbType = MySqlDbType.VarChar, Value = openID
});
destionParameters.Add(new MySqlParameter()
{
ParameterName = "@Scope", MySqlDbType = MySqlDbType.VarChar, Value = scope
});
MAccess_Token access_Token = null;
using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray()))
{
while (reader.Read())
{
access_Token = new MAccess_Token();
access_Token.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
access_Token.AppID = reader["AppID"] == DBNull.Value ? string.Empty : reader["AppID"].ToString();
access_Token.AppSecret = reader["AppSecret"] == DBNull.Value ? string.Empty : reader["AppSecret"].ToString();
access_Token.WeixinID = reader["WeixinID"] == DBNull.Value ? string.Empty : reader["WeixinID"].ToString();
access_Token.Access_Token = reader["Access_Token"] == DBNull.Value ? string.Empty : SecurityUtility.DecryptString(reader["Access_Token"].ToString());
access_Token.RefreshToken = reader["RefreshToken"] == DBNull.Value ? string.Empty : SecurityUtility.DecryptString(reader["RefreshToken"].ToString());
access_Token.Grant_Type = reader["Grant_Type"] == DBNull.Value ? string.Empty : reader["Grant_Type"].ToString();
access_Token.OpenID = reader["OpenID"] == DBNull.Value ? string.Empty : reader["OpenID"].ToString();
access_Token.Scope = reader["Scope"] == DBNull.Value ? string.Empty : reader["Scope"].ToString();
access_Token.UnionID = reader["UnionID"] == DBNull.Value ? string.Empty : reader["UnionID"].ToString();
access_Token.AddTime = reader["AddTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["AddTime"]);
access_Token.OutTime = reader["OutTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["OutTime"]);
}
}
return(access_Token);
}
public string[] GetManagerUserList(string prefixText, int count)
{
try
{
MySqlParameters mySql = new MySqlParameters("s_user");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("top " + count + " name+'['+id+']'");
mySql.Add("iflag", Constants.TRUE_ID, "and iflag={0} and (role=4 or role=6)");
mySql.Add("id", "%" + prefixText + "%", "and (id like {0} or name like {0})");
return(DataHelper.GetDataFirstCol(new DataFrom().GetDataTable(mySql)));
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error("获得用户错误", ex);
return(null);
}
}
/// <summary>
/// 获得用户信息
/// </summary>
/// <param name="userAccount"></param>
/// <param name="password"></param>
/// <returns></returns>
private DataTable getUserTable(string userAccount, string password)
{
MySqlParameters mySql = new MySqlParameters(_table);
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("id");
mySql.Add("name");
mySql.Add("city");
mySql.Add("iflag");
mySql.Add("email");
mySql.Add("userAccount", userAccount, "and (id={0} or email={0})");
if (!String.IsNullOrEmpty(password))
{
mySql.Add("password", password, "and pwd={0}");
}
return(dao.GetDataTable(mySql, true));
}
public string[] GetMarkList(string prefixText, int count)
{
try
{
MySqlParameters mySql = new MySqlParameters("D_Mark");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("top " + count + " MarkDes+'['+MarkID+']'");
mySql.Add("id", "%" + prefixText + "%", "(MarkDes like {0} or MarkID like {0})");
mySql.SqlEnd = "order by MarkDes";
return(DataHelper.GetDataFirstCol(new DataFrom().GetDataTable(mySql)));
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error("获得标签错误", ex);
return(null);
}
}
public string[] GetResearcherList(string prefixText, int count)
{
try
{
MySqlParameters mySql = new MySqlParameters("Researcher");
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("top " + count + " Name+'['+cast(ID as varchar)+']'");
mySql.Add("id", "%" + prefixText + "%", "Name like {0}");
mySql.SqlEnd = "order by Name";
return(DataHelper.GetDataFirstCol(new DataFrom().GetDataTable(mySql)));
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error("获得企业错误", ex);
return(null);
}
}
/// <summary>
/// 记录token
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="access_Token">令牌</param>
/// <returns>是否成功</returns>
public bool InsertAccess_Token(IDbTransaction trans, IDbConnection conn, MAccess_Token access_Token)
{
string insertsql = @"Insert into Access_Token(KeyID,AppID,WeixinID,AppSecret,Access_Token,RefreshToken,Grant_Type,OpenID,Scope,UnionID,AddTime,OutTime,ModifyTime,IsDelete
)VALUES(@KeyID,@AppID,@WeixinID,@AppSecret,@Access_Token,@RefreshToken,@Grant_Type,@OpenID,@Scope,@UnionID,now(),@OutTime,now(),0);";
MySqlParameters parameters = new MySqlParameters();
parameters.Add(new MySqlParameter() { ParameterName = "@KeyID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.KeyID });
parameters.Add(new MySqlParameter() { ParameterName = "@AppID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.AppID });
parameters.Add(new MySqlParameter() { ParameterName = "@WeixinID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.WeixinID });
parameters.Add(new MySqlParameter() { ParameterName = "@AppSecret", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.AppSecret });
parameters.Add(new MySqlParameter() { ParameterName = "@Access_Token", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Access_Token });
parameters.Add(new MySqlParameter() { ParameterName = "@RefreshToken", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.RefreshToken });
parameters.Add(new MySqlParameter() { ParameterName = "@Grant_Type", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Grant_Type });
parameters.Add(new MySqlParameter() { ParameterName = "@OpenID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.OpenID });
parameters.Add(new MySqlParameter() { ParameterName = "@Scope", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.Scope });
parameters.Add(new MySqlParameter() { ParameterName = "@UnionID", MySqlDbType = MySqlDbType.VarChar, Value = access_Token.UnionID });
parameters.Add(new MySqlParameter() { ParameterName = "@OutTime", MySqlDbType = MySqlDbType.DateTime, Value = access_Token.OutTime });
return Common.DBUtility.MySqlHelper.ExecuteSql(trans, conn, insertsql, parameters.ToArray()) == 1;
}
/// <summary>
/// 根据MySqlParameters对象执行一条Sql语句是否出错（insert/update/delete）不再抛出异常
/// </summary>
/// <param name="parameters">自定义Sql及参数列表</param>
/// <returns>是否出错(判断数据提交是否出错) true表示出现错误</returns>
public bool SqlExecuteFail(MySqlParameters parameters)
{
bool isRun = false;
try
{
isRun = dao.SqlExecute(parameters, true) > 0;
}
catch
{
isRun = false;
}
if (IsSysLog && isRun)
{
new LogDeal().Write("执行操作语句:" + parameters.GetSql(dao.Dbtype, false));
}
return(!isRun);
}
/// <summary>
/// 读出详细信息赋给相应控件,控件包括Label,TextBox,CheckBox,RichTextBox,UserCtrls,ListControl
/// </summary>
/// <param name="selFields">选择字段数组,可以加表名及运算，比如：string[] selFields={"title","content","Derivation","convert(varchar(10),Dates,20) as Dates"};</param>
/// <param name="ctrls">控件数组与字段数组按顺序一一对应，比如：Control[] ctrls={txtTitle,txtContent,txtDerivation,cdrDates};</param>
/// <param name="parameters">自定义Sql及参数列表 SQL语句只要表名及条件语句（包含from）</param>
/// <returns>有无记录</returns>
public bool Detail(string[] selFields, Control[] ctrls, MySqlParameters parameters)
{
bool isRun = false;
IDataReader dr = GetDataReader(parameters);
if (dr != null)
{
if (dr.Read())
{
isRun = true;
for (int i = 0; i < selFields.Length && i < ctrls.Length; i++)
{
setCtrlValue(ctrls[i], dr[i]);
}
}
dr.Close();
}
return(isRun);
}
/// <summary>
/// 计入一条日志
/// </summary>
/// <param name="des"></param>
public void Write(string des)
{
/*//调用实体类写法
* LogInfo logInfo = new LogInfo();
* logInfo.UserId = userId;
* logInfo.IP = IP;
* logInfo.Des = des;
* logInfo.Insert();
*/
MySqlParameters parameters = new MySqlParameters("LogInfo");
parameters.EditSqlMode = SqlMode.Insert;
parameters.Add("UserId", userId);
parameters.Add("IP", IP);
parameters.Add("Des", des);
parameters.Add("Optime", DateTime.Now);
Dao dao = new Dao();
dao.SqlExecute(parameters, true);
}
protected void btnSet_Click(object sender, EventArgs e)
{
if (calendarWorkDay.SelectedDate != DateTime.MinValue)
{
MySqlParameters mySql = new MySqlParameters("workweek");
try
{
mySql.EditSqlMode = SqlMode.Update;
mySql.Add("iflag", WorkDayState.Checked ? 1 : 0);
mySql.Add("workday", calendarWorkDay.SelectedDate, "workday={0}");
deal.SqlExecute(mySql);
SetWorkWeekInfo();
}
catch (Exception ex)
{
Logger.Error(SqlHelper.GetSql(mySql, deal.Dbtype), ex);
RedirectError("提交失败，请稍候再试。原因:" + ex.Message);
}
}
}
/// <summary>
/// 组织MySqlParameters
/// </summary>
/// <param name="sqlMode">SQL语句类别</param>
private void setMySql(SqlMode sqlMode)
{
mySql = new MySqlParameters("S_Test");
if (sqlMode == SqlMode.Select)
{
mySql.EditSqlMode = SqlMode.Select;
mySql.Add("id", id, " and id={0}");
}
else if (id == 0)
{
mySql.EditSqlMode = SqlMode.Insert;
}
else
{
mySql.EditSqlMode = SqlMode.Update;
mySql.Add("id", id, " and id={0}");
}
WebHelper.SetMySql(mySql, RDate);
WebHelper.SetMySql(mySql, FunID);
mySql.Add("title", txtTitle.Text);
WebHelper.SetMySql(mySql, Notes);
if (sqlMode == SqlMode.Select)
{
mySql.Add("FileName", null);
}
else if (!WebHelper.IsNullOrEmpty(FileName))
{
mySql.Add("FileName", getFileName(mySql.EditSqlMode));
}
WebHelper.SetMySql(mySql, IClass);
WebHelper.SetMySql(mySql, IFlag);
mySql.Add("optime", DateTime.Now);
mySql.Add("UserID", CurrentUser.Id);
#region 处理扩展信息 将字段、值 加入mySql
AddMySql(MyInputs1);
#endregion
}
/// <summary>
/// 根据企业ID获取公众号信息
/// </summary>
/// <param name="trans"></param>
/// <param name="conn"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public List<MQiushibaike> GetJokeByKeyWords(IDbTransaction trans, IDbConnection conn, string keywords)
{
string sqlText = @"select KeyID,JokerName,JokeContent,Lauds,IsDelete from Qiushibaike WHERE JokeContent like @JokeContent and IsDelete = 0 limit 3";
MySqlParameters destionParameters = new MySqlParameters();
destionParameters.Add(new MySqlParameter() { ParameterName = "@JokeContent", MySqlDbType = MySqlDbType.VarChar, Value = string.Format("%{0}%", keywords) });
List<MQiushibaike> qiushibaikeList = new List<MQiushibaike>();
MQiushibaike qiushibaike = null;
using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray()))
{
while (reader.Read())
{
qiushibaike = new MQiushibaike();
qiushibaike.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
qiushibaike.JokerName = reader["JokerName"] == DBNull.Value ? string.Empty : reader["JokerName"].ToString();
qiushibaike.JokeContent = reader["JokeContent"] == DBNull.Value ? string.Empty : reader["JokeContent"].ToString();
qiushibaike.Lauds = reader["Lauds"] == DBNull.Value ? 0 : Convert.ToInt32(reader["Lauds"]);
qiushibaikeList.Add(qiushibaike);
}
}
return qiushibaikeList;
}
/// <summary>
/// Returns the MySql dump parameter representation of the specified MySqlParameters enumeration.
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public static string GetParameterValue(MySqlParameters parameter)
{
string _value = "";
string _name = Enum.GetName(typeof(MySqlParameters), parameter);
char[] _chars = _name.ToCharArray();
if (_chars.Length > 0)
{
_value += "-";
foreach (char _char in _chars)
{
if (Char.IsLetter(_char) &&
Char.IsUpper(_char))
{
_value += "-";
}
_value += _char.ToString().ToLower();
}
}
return(_value);
}
public string UpdateData(string table, string field, string value, string key, string keyvalue)
{
MySqlParameters mySql = new MySqlParameters(table);
mySql.EditSqlMode = SqlMode.Update;
mySql.Add(field, DataHelper.GetIDFromBracket(value));
if (key.IndexOf(Constants.MY_SPLIT) == -1)
{
mySql.Add(key, keyvalue, key + "={0}");
}
else
{
string[] keys = DataHelper.GetStrings(key);
string[] keyvalues = DataHelper.GetStrings(keyvalue);
if (keys.Length == keyvalues.Length)
{
for (int i = 0; i < keys.Length; i++)
{
mySql.Add(keys[i], keyvalues[i], keys[i] + "={0}");
}
}
else
{
return("主键值不对应不能修改");
}
}
try
{
return(new DataFrom().SqlExecute(mySql).ToString());
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(this.GetType()).Error("执行更新错误" + SqlHelper.GetSql(mySql, DBType.Sql2005), ex);
return(LanguageResource.Current.PUBLIC_DealError);
}
}
/// <summary>
/// 获取口令(公众号)
/// </summary>
/// <param name="trans">事物</param>
/// <param name="conn">连接</param>
/// <param name="appid">appid</param>
/// <param name="appSecret">appSecret</param>
/// <param name="grant_Type">grant_Type</param>
/// <returns></returns>
public MAccess_Token GetAccess_Token(IDbTransaction trans, IDbConnection conn, string appid, string appSecret, string grant_Type)
{
string sqlText = @"select KeyID,WeixinID,AppID,AppSecret,Access_Token,Grant_Type,AddTime,OutTime,ModifyTime,IsDelete from Access_Token
WHERE AppID = @AppID and AppSecret= @AppSecret and Grant_Type=@Grant_Type and IsDelete = 0 limit 1";
MySqlParameters destionParameters = new MySqlParameters();
destionParameters.Add(new MySqlParameter() { ParameterName = "@AppID", MySqlDbType = MySqlDbType.VarChar, Value = appid });
destionParameters.Add(new MySqlParameter() { ParameterName = "@AppSecret", MySqlDbType = MySqlDbType.VarChar, Value = appSecret });
destionParameters.Add(new MySqlParameter() { ParameterName = "@Grant_Type", MySqlDbType = MySqlDbType.VarChar, Value = grant_Type });
MAccess_Token access_Token = null;
using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray()))
{
while (reader.Read())
{
access_Token = new MAccess_Token();
access_Token.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString();
access_Token.AppID = reader["AppID"] == DBNull.Value ? string.Empty : reader["AppID"].ToString();
access_Token.AppSecret = reader["AppSecret"] == DBNull.Value ? string.Empty : reader["AppSecret"].ToString();
access_Token.WeixinID = reader["WeixinID"] == DBNull.Value ? string.Empty : reader["WeixinID"].ToString();
access_Token.Access_Token = reader["Access_Token"] == DBNull.Value ? string.Empty : SecurityUtility.DecryptString(reader["Access_Token"].ToString());
access_Token.Grant_Type = reader["Grant_Type"] == DBNull.Value ? string.Empty : reader["Grant_Type"].ToString();
access_Token.AddTime = reader["AddTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["AddTime"]);
access_Token.OutTime = reader["OutTime"] == DBNull.Value ? new DateTime(1990, 1, 1) : Convert.ToDateTime(reader["OutTime"]);
}
}
return access_Token;
}
/// <summary>
/// Returns the MySql dump parameter representation of the specified MySqlParameters enumeration.
/// </summary>
/// <param name="parameter">MySqlParamater to evaluate</param>
/// <returns>MySql application parameter representation of the specified enumeration.</returns>
public static string GetParameterValue(MySqlParameters parameter)
{
string _value = "";
string _name = Enum.GetName(typeof(MySqlParameters), parameter);
char[] _chars = _name.ToCharArray();
if (_chars.Length > 0)
{
_value += "-";
foreach (char _char in _chars)
{
if (Char.IsLetter(_char) &&
Char.IsUpper(_char)) _value += "-";
_value += _char.ToString().ToLower();
}
}
return _value;
}
/// <summary>
/// Adds a new parameter in the collection.
/// </summary>
/// <param name="parameter">MySql application parameter</param>
/// <returns>Index of the newly added MySql application parameter within the collection.</returns>
public int Add(MySqlParameters parameter)
{ return base.Add(MySql.GetParameterValue(parameter)); }
/// <summary>
/// Returns whether the specified parameter already exists in the collection or not.
/// </summary>
/// <param name="parameter">MySql application parameter to evaluate.</param>
/// <returns>True if parameter already exists within the application otherwise false.</returns>
public bool Contains(MySqlParameters parameter)
{ return base.Contains(MySql.GetParameterValue(parameter)); }
/// <summary>
/// Removes the specified parameter from the collection.
/// </summary>
/// <param name="parameter">MySql application parameter to remove.</param>
public void Remove(MySqlParameters parameter)
{ base.Remove(MySql.GetParameterValue(parameter)); }
