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(AgentScore entity)
{
string sql = "INSERT INTO agent_score (agentNo,agentName,branchNo,branchName,score,standardScore,dateTime) VALUE (@agentNo,@agentName,@branchNo,@branchName,@score,@standardScore,@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("@score", entity.score);
command.Parameters.AddWithValue("@standardScore", entity.standardScore);
command.Parameters.AddWithValue("@dateTime", entity.dateTime);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentScore> GetLatestByKeyword(String keyword, String dateTime)
{
string sql = "SELECT agentNo,agentName,branchNo,branchName,score,standardScore,dateTime FROM agent_score";
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<AgentScore> list = new List<AgentScore>();
AgentScore agentScore = null;
while (reader.Read())
{
agentScore = new AgentScore();
agentScore.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentScore.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
agentScore.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString();
agentScore.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString();
agentScore.score = reader["score"] == DBNull.Value ? null : reader["score"].ToString();
agentScore.standardScore = reader["standardScore"] == DBNull.Value ? null : reader["standardScore"].ToString();
agentScore.dateTime = reader["dateTime"] == DBNull.Value ? null : reader["dateTime"].ToString();
list.Add(agentScore);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(3, "开始导入积分...\r\n");
//导入积分
AgentScoreDao agentScoreDao = new AgentScoreDao();
for (int i = 0; i < dgAgentScore.RowCount; i++)
{
AgentScore agentScore = new AgentScore();
agentScore.dateTime = dgAgentScore[0, i].Value.ToString();
agentScore.agentNo = dgAgentScore[1, i].Value.ToString();
agentScore.agentName = dgAgentScore[2, i].Value.ToString();
agentScore.branchNo = dgAgentScore[3, i].Value.ToString();
agentScore.branchName = dgAgentScore[4, i].Value.ToString();
agentScore.score = dgAgentScore[5, i].Value.ToString();
agentScore.standardScore = dgAgentScore[6, i].Value.ToString();
agentScoreDao.Delete(agentScore.agentNo.Trim(), agentScore.branchNo.Trim(), agentScore.dateTime.Trim());
agentScoreDao.Add(agentScore);
if (!String.IsNullOrEmpty(agentScore.agentNo))
{
worker.ReportProgress(4, "正在导入代理商:" + agentScore.agentNo + "积分...\r\n");
}
else
{
worker.ReportProgress(4, "正在导入渠道:" + agentScore.branchNo + "积分...\r\n");
}
}
worker.ReportProgress(5, "导入积分完成...\r\n");
//MessageBox.Show("数据上传完毕");
}