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