/// <summary>
/// 将Excel数据导入至数据库
/// </summary>
/// <param name="fileName">Excel文件名</param>
/// <param name="tableName">要插入数据库的表名</param>
/// <param name="CONNECTION">连接字符串</param>
public static void IOtoSQL(string fileName, string tableName, string CONNECTION)
{
SQLTool mysql = new SQLTool();
mysql.open(CONNECTION);
try
{
DataTable dt = ReadExcelToTable(fileName);
if (!checkExcel(tableName, dt))
{
throw new Exception("文件格式不符合要求\n请查看帮助信息");
}
dt.Columns.Add("autoid", typeof(int));
mysql.BatchInsert(dt, tableName);
MessageBox.Show(System.IO.Path.GetFileNameWithoutExtension(fileName) + "\n数据导入成功");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysql.close();
}
}
/// <summary>
/// 绘出该校该批次下文/理科所有专业数据线
/// </summary>
/// <param name="school">学校名</param>
/// <param name="depart">批次</param>
/// <param name="batch">文理</param>
private void drawChart(string school, string depart, string batch)
{
SQLTool mysql = new SQLTool();
mysql.open(CONNECTION);
try
{
if (!batch.Equals("%三批%") && mysql.isThree(SQLCmdStr.isThree(school)))
{
MessageBox.Show(string.Format("{0}在三本有招生!!!", school));
}
DataTable dtMajor = mysql.select(SQLCmdStr.selectMajor(school, depart, batch));
DataTable dtDif = mysql.select(SQLCmdStr.selectDif(depart, batch));
DataTable dtRank = mysql.select(SQLCmdStr.selectRank(school, depart, batch));
DataTable dtSum = mysql.select(SQLCmdStr.selectSum(school, depart, batch));
FormChart fc = new FormChart();
fc.init_FormChart(school, dtMajor, dtDif, dtRank, dtSum);
fc.Show();
if (fc.IsDisposed)
{
fc.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysql.close();
}
}
/// <summary>
/// 从数据库读取批次线，初始化表
/// </summary>
private void init_dt_batch()
{
SQLTool mysql = new SQLTool();
mysql.open(CONNECTION);
try
{
dt_batch = mysql.select(SQLCmdStr.selectLine());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysql.close();
}
}
private void buttonQuery_Click(object sender, EventArgs e)
{
string cmd = null;
if (radioButtonScore.Checked)
{
cmd = SQLCmdStr.selectSegbyScore(comboBoxYearL.Text, comboBoxYearR.Text, comboBoxRegional.Text, radioButtonArt.Checked, textBoxScoreL.Text, textBoxScoreR.Text, textBoxRankL.Text, textBoxRankR.Text);
}
else
{
cmd = SQLCmdStr.selectSegbyLine(comboBoxYearL.Text, comboBoxYearR.Text, comboBoxRegional.Text, radioButtonArt.Checked, comboBoxBatch.Text, textBoxDif.Text, textBoxRankL.Text, textBoxRankR.Text);
}
if (cmd == null)
{
return;
}
SQLTool mysql = new SQLTool();
mysql.open(CONNECTION);
try
{
DataTable dt = mysql.select(cmd);
FormStatistics fs = new FormStatistics();
fs.init_Statistics(dt, DateTime.Now.Year - 1);
fs.Show();
if (fs.IsDisposed)
{
fs.Dispose();
}
dataGridViewSQL.DataSource = dt;
dataGridViewSQL.Update();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
mysql.close();
}
}
/// <summary>
/// 从数据库里删除表tableName内所有数据
/// </summary>
/// <param name="tableName">表名</param>
private void DeleteData(string tableName)
{
SQLTool mysql = new SQLTool();
try
{
mysql.open(CONNECTION);
mysql.delete(SQLCmdStr.deleteTable(tableName));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
finally
{
mysql.close();
}
MessageBox.Show(tableName + " 删除成功");
}