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