/// <summary> /// 修改数据 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Update(Agent entity) { string sql = "UPDATE agent SET agentNo=@agentNo ,agentName=@agentName,contactEmail=@contactEmail,contactName=@contactName,contactTel=@contactTel,contactWechatAccount=@contactWechatAccount,status=@status where agentNo=@agentNo "; //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("@agentNo", entity.agentNo); command.Parameters.AddWithValue("@agentName", entity.agentName); command.Parameters.AddWithValue("@contactEmail", entity.contactEmail); command.Parameters.AddWithValue("@contactName", entity.contactName); command.Parameters.AddWithValue("@contactTel", entity.contactTel); command.Parameters.AddWithValue("@contactWechatAccount", entity.contactWechatAccount); command.Parameters.AddWithValue("@status", entity.status); 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(Agent entity) { string sql = "INSERT INTO agent (agentNo,agentName,contactEmail,contactName,contactTel,contactWechatAccount,status) VALUE (@agentNo,@agentName,@contactEmail,@contactName,@contactTel,@contactWechatAccount,@status)"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@agentNo", entity.agentNo); command.Parameters.AddWithValue("@agentName", entity.agentName); command.Parameters.AddWithValue("@contactEmail", entity.contactEmail); command.Parameters.AddWithValue("@contactName", entity.contactName); command.Parameters.AddWithValue("@contactTel", entity.contactTel); command.Parameters.AddWithValue("@contactWechatAccount", entity.contactWechatAccount); command.Parameters.AddWithValue("@status", entity.status); 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"); worker.ReportProgress(3, "开始导入代理商...\r\n"); //导入代理商 AgentDao agentDao = new AgentDao(); for (int i = 0; i < dgAgent.RowCount; i++) { Agent agent = new Agent(); agent.agentNo = dgAgent[0, i].Value.ToString(); agent.agentName = dgAgent[1, i].Value.ToString(); agent.contactEmail = dgAgent[2, i].Value.ToString(); agent.contactName = dgAgent[3, i].Value.ToString(); agent.contactTel = dgAgent[4, i].Value.ToString(); agent.contactWechatAccount = dgAgent[5, i].Value.ToString(); agent.status = dgAgent[6, i].Value.ToString(); agentDao.Delete(agent.agentNo); agentDao.Add(agent); } worker.ReportProgress(4, "导入代理商完成...\r\n"); //MessageBox.Show("数据上传完毕"); }
/// <summary> /// 根据主键查询 /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public Agent Get(String primaryKey) { string sql = "SELECT agentNo,agentName,contactEmail,contactName,contactTel,contactWechatAccount,status FROM agent where agentNo=@agentNo"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@agentNo", primaryKey); MySqlDataReader reader = command.ExecuteReader(); Agent agent = null; if (reader.Read()) { agent = new Agent(); agent.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agent.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agent.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString(); agent.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString(); agent.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString(); agent.contactWechatAccount = reader["contactWechatAccount"] == DBNull.Value ? null : reader["contactWechatAccount"].ToString(); agent.status = reader["status"] == DBNull.Value ? null : reader["status"].ToString(); } mycn.Close(); return agent; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<Agent> GetList() { string sql = "SELECT agentNo,agentName,contactEmail,contactName,contactTel,contactWechatAccount,status FROM agent"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); MySqlDataReader reader = command.ExecuteReader(); IList<Agent> list = new List<Agent>(); Agent agent = null; while (reader.Read()) { agent = new Agent(); agent.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agent.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agent.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString(); agent.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString(); agent.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString(); agent.contactWechatAccount = reader["contactWechatAccount"] == DBNull.Value ? null : reader["contactWechatAccount"].ToString(); agent.status = reader["status"] == DBNull.Value ? null : reader["status"].ToString(); list.Add(agent); } mycn.Close(); return list; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public AgentFee GetByKey(String agentFeeMonth, string agentNo) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT t1.agentNo,t1.agentName, t1.agentFeeSeq,"); for (int i = 1; i <= 100; i++) { sb.Append("t1.feeName").Append(i.ToString()).Append(",").Append("t1.fee").Append(i.ToString()).Append(","); } sb.Append("feeTotal,invoiceFee,preInvoiceFee, (select group_concat(distinct t3.agentType separator ';') from agent_type t3 where t1.agentNo = t3.agentNo and t3.agentFeeMonth=@agentFeeMonth) agentType,"); sb.Append("(select group_concat(distinct t4.agentTypeComment separator '<br>') from agent_type_comment t4 , agent_type t5 where t1.agentNo = t5.agentNo and t4.agentType = t5.agentType and t4.agentFeeMonth=t5.agentFeeMonth and t4.agentFeeMonth=@agentFeeMonth) agentTypeComment"); // sb.Append("t2.agentName,t2.contactEmail,t2.contactName,t2.contactTel"); sb.Append(" FROM agent_Fee t1 where agentFeeMonth=@agentFeeMonth"); // sb.Append(" and t1.agentNo= t2.agentNo "); sb.Append(" and t1.agentNo= @agentNo "); // sb.Append(" and t2.status!='Y'"); string sql = sb.ToString();// "SELECT agentNo, agentFeeSeq,feeName1,fee1,feeName2,fee2,feeName3,fee3,feeName4,fee4,feeTotal FROM agent_Fee"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@agentFeeMonth", agentFeeMonth); command.Parameters.AddWithValue("@agentNo", agentNo); MySqlDataReader reader = command.ExecuteReader(); AgentFee agentFee = null; if (reader.Read()) { agentFee = new AgentFee(); agentFee.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agentFee.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentFee.agentFeeSeq = reader["agentFeeSeq"] == DBNull.Value ? null : reader["agentFeeSeq"].ToString(); agentFee.agentFeeMonth = agentFeeMonth; for (int i = 1; i <= 100; i++) { FieldInfo feeNameField = agentFee.GetType().GetField("feeName" + i); FieldInfo feeField = agentFee.GetType().GetField("fee" + i); String feeNameFieldValue = reader["feeName"+i] == DBNull.Value ? null : reader["feeName"+i].ToString(); String feeFieldValue = reader["fee"+i] == DBNull.Value ? null : reader["fee"+i].ToString(); feeNameField.SetValue(agentFee, feeNameFieldValue); feeField.SetValue(agentFee, feeFieldValue); } agentFee.feeTotal = reader["feeTotal"] == DBNull.Value ? null : reader["feeTotal"].ToString(); agentFee.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString(); agentFee.preInvoiceFee = reader["preInvoiceFee"] == DBNull.Value ? null : reader["preInvoiceFee"].ToString(); Agent agent = new Agent(); agent.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); // agent.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString(); //agent.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString(); // agent.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString(); agent.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString(); agent.agentTypeComment = reader["agentTypeComment"] == DBNull.Value ? null : reader["agentTypeComment"].ToString(); agentFee.agent = agent; } mycn.Close(); return agentFee; } }