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(AgentStar entity)
{
string sql = "INSERT INTO agent_star (agentNo,agentName,branchNo,branchName,star,dateTime) VALUE (@agentNo,@agentName,@branchNo,@branchName,@star,@dateTime)";
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("@branchNo", entity.branchNo);
command.Parameters.AddWithValue("@branchName", entity.branchName);
command.Parameters.AddWithValue("@star", entity.star);
command.Parameters.AddWithValue("@dateTime", entity.dateTime);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentStar> GetLatestByKeyword(String keyword,String dateTime)
{
string sql = "SELECT agentNo,agentName,branchNo,branchName,star,dateTime FROM agent_star";
sql = sql + " where 1=1";
if (!String.IsNullOrEmpty(dateTime))
{
sql = sql + " and dateTime = \"" + dateTime + "\"";
}
if (!String.IsNullOrEmpty(keyword))
{
sql = sql + " and ((agentNo = \"" + keyword + "\")";
sql = sql + " or (branchNo = \"" + keyword + "\"))";
}
sql = sql + " order by agentNo asc,dateTime asc";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
MySqlDataReader reader = command.ExecuteReader();
IList<AgentStar> list = new List<AgentStar>();
AgentStar agentStar = null;
while (reader.Read())
{
agentStar = new AgentStar();
agentStar.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentStar.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
agentStar.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString();
agentStar.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString();
agentStar.star = reader["star"] == DBNull.Value ? null : reader["star"].ToString();
agentStar.dateTime = reader["dateTime"] == DBNull.Value ? null : reader["dateTime"].ToString();
list.Add(agentStar);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(1, "开始导入星级...\r\n");
//导入星级
AgentStarDao agentStarDao = new AgentStarDao();
for (int i = 0; i < dgAgentStar.RowCount; i++)
{
AgentStar agentStar = new AgentStar();
agentStar.dateTime = dgAgentStar[0, i].Value.ToString();
agentStar.agentNo = dgAgentStar[1, i].Value.ToString();
agentStar.agentName = dgAgentStar[2, i].Value.ToString();
agentStar.branchNo = dgAgentStar[3, i].Value.ToString();
agentStar.branchName = dgAgentStar[4, i].Value.ToString();
agentStar.star = dgAgentStar[5, i].Value.ToString();
agentStarDao.Delete(agentStar.agentNo.Trim(), agentStar.branchNo.Trim(), agentStar.dateTime.Trim());
agentStarDao.Add(agentStar);
if (!String.IsNullOrEmpty(agentStar.agentNo))
{
worker.ReportProgress(2, "正在导入代理商:" + agentStar.agentNo + "星级...\r\n");
}
else
{
worker.ReportProgress(2, "正在导入渠道:" + agentStar.branchNo + "星级...\r\n");
}
}
worker.ReportProgress(3, "导入星级完成...\r\n");
//MessageBox.Show("数据上传完毕");
}