public int UpdateQueryCount(AgentErrorCode entity) { string sql = "UPDATE agent_error_code SET queryCount=queryCount+1 where keyword=@keyword"; //string sql = "UPDATE cimuser SET userNickName=@userNickName WHERE userid=@userid"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@keyword", entity.keyword); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 修改数据 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Update(AgentErrorCode entity) { string sql = "UPDATE agent_error_code SET keyword=@keyword,module=@module,errorDesc=@errorDesc,errorImg=@errorImg,solution=@solution,contactName=@contactName,comment=@comment where keyword=@keyword"; //string sql = "UPDATE cimuser SET userNickName=@userNickName WHERE userid=@userid"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@keyword", entity.keyword); command.Parameters.AddWithValue("@module", entity.module); command.Parameters.AddWithValue("@errorDesc", entity.errorDesc); command.Parameters.AddWithValue("@errorImg", entity.errorImg); command.Parameters.AddWithValue("@solution", entity.solution); command.Parameters.AddWithValue("@contactName", entity.contactName); command.Parameters.AddWithValue("@comment", entity.comment); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8"; /// <summary> /// 添加数据 /// </summary> /// <returns></returns> public int Add(AgentErrorCode entity) { string sql = "INSERT INTO agent_error_code (keyword,module,errorDesc,errorImg,solution,contactName,comment) VALUE (@keyword,@module,@errorDesc,@errorImg,@solution,@contactName,@comment)"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@keyword", entity.keyword); command.Parameters.AddWithValue("@module", entity.module); command.Parameters.AddWithValue("@errorDesc", entity.errorDesc); command.Parameters.AddWithValue("@errorImg", entity.errorImg); command.Parameters.AddWithValue("@solution", entity.solution); command.Parameters.AddWithValue("@contactName", entity.contactName); command.Parameters.AddWithValue("@comment", entity.comment); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 异步 开始事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void worker_DoWork(object sender, DoWorkEventArgs e) { //需要执行的代码 worker.ReportProgress(1, "开始导入错误代码...\r\n"); //导入代理商类型说明 AgentErrorCodeDao agentErrorCodeDao = new AgentErrorCodeDao(); for (int i = 0; i < this.dgErrorCode.RowCount; i++) { AgentErrorCode agentErrorCode = new AgentErrorCode(); agentErrorCode.keyword = dgErrorCode[2, i].Value.ToString(); agentErrorCode.errorDesc = dgErrorCode[4, i].Value.ToString(); byte[] b = new byte[0]; String fullpath = dgErrorCode[3, i].Value.ToString(); if (File.Exists(fullpath)) { FileStream fs = new FileStream(fullpath, FileMode.Open, FileAccess.Read); //将图片以文件流的形式进行保存 BinaryReader br = new BinaryReader(fs); byte[] imagebytes = br.ReadBytes((int)fs.Length); //将流读入到字节数组中 fs.Close(); br.Close(); if (imagebytes.Length > 0) { agentErrorCode.errorImg = imagebytes; } } agentErrorCode.solution = dgErrorCode[5, i].Value.ToString(); agentErrorCode.contactName = dgErrorCode[6, i].Value.ToString(); // agentErrorCode.comment = dgErrorCode[5, i].Value.ToString(); agentErrorCode.module = dgErrorCode[1, i].Value.ToString(); if (agentErrorCodeDao.GetByKey(agentErrorCode.keyword) != null) { agentErrorCodeDao.Update(agentErrorCode); } else { //agentErrorCodeDao.Delete(agentErrorCode.keyword); agentErrorCodeDao.Add(agentErrorCode); } } worker.ReportProgress(2, "导入错误代码完成...\r\n"); //MessageBox.Show("数据上传完毕"); }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<AgentErrorCode> GetList(String qeuryString) { string sql = "SELECT seq,module,keyword,errorDesc,errorImg,solution,contactName,comment,queryCount FROM agent_error_code "; if(!String.IsNullOrEmpty(qeuryString)){ sql = sql + " where (keyword like \"%" + qeuryString + "%\" or errorDesc like \"%" + qeuryString + "%\")"; } using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); MySqlDataReader reader = command.ExecuteReader(); IList<AgentErrorCode> list = new List<AgentErrorCode>(); AgentErrorCode agentErrorCode = null; while (reader.Read()) { agentErrorCode = new AgentErrorCode(); agentErrorCode.seq = reader["seq"] == DBNull.Value ? 0 : int.Parse(reader["seq"].ToString()); agentErrorCode.keyword = reader["keyword"] == DBNull.Value ? null : reader["keyword"].ToString(); agentErrorCode.module = reader["module"] == DBNull.Value ? null : reader["module"].ToString(); agentErrorCode.errorDesc = reader["errorDesc"] == DBNull.Value ? null : reader["errorDesc"].ToString(); agentErrorCode.errorImg = reader["errorImg"] == DBNull.Value ? null : (byte[])reader["errorImg"]; agentErrorCode.solution = reader["solution"] == DBNull.Value ? null : reader["solution"].ToString(); agentErrorCode.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString(); agentErrorCode.comment = reader["comment"] == DBNull.Value ? null : reader["comment"].ToString(); agentErrorCode.queryCount = reader["queryCount"] == DBNull.Value ? 0 : int.Parse(reader["queryCount"].ToString()); list.Add(agentErrorCode); } mycn.Close(); return list; } }
/// <summary> /// 根据主键查询 /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public AgentErrorCode GetByKey(String keyword) { string sql = "SELECT seq,module,keyword,errorDesc,errorImg,solution,contactName,comment FROM agent_error_code where keyword=@keyword"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@keyword", keyword); MySqlDataReader reader = command.ExecuteReader(); AgentErrorCode agentErrorCode = null; if (reader.Read()) { agentErrorCode = new AgentErrorCode(); agentErrorCode.seq = reader["seq"] == DBNull.Value ? 0 : int.Parse(reader["seq"].ToString()); agentErrorCode.keyword = reader["keyword"] == DBNull.Value ? null : reader["keyword"].ToString(); agentErrorCode.module = reader["module"] == DBNull.Value ? null : reader["module"].ToString(); agentErrorCode.errorDesc = reader["errorDesc"] == DBNull.Value ? null : reader["errorDesc"].ToString(); agentErrorCode.errorImg = reader["errorImg"] == DBNull.Value ? null : (byte[])reader["errorImg"]; agentErrorCode.solution = reader["solution"] == DBNull.Value ? null : reader["solution"].ToString(); agentErrorCode.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString(); agentErrorCode.comment = reader["comment"] == DBNull.Value ? null : reader["comment"].ToString(); } mycn.Close(); return agentErrorCode; } }