private void btnAdd_Click(object sender, EventArgs e)
{
frmMain frmmain = (frmMain)this.Owner;
string addList = string.Empty;
string addMac = string.Empty;
if (ValidInfo.IsAllowOrDeny(cbbAddList.Text) == true)
{
addList = cbbAddList.Text;
}
else
{
MessageBox.Show("LIST必须为Allow或者Deny", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (ValidInfo.IsMAC(txtAddMAC.Text) == true)
{
addMac = txtAddMAC.Text;
}
else
{
MessageBox.Show("MACADDRESS必须是MAC地址", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string addDes = txtAddDescription.Text;
string addFrom = dtpAddFrom.Text;
string addThru = dtpAddThru.Text;
string ADsql = "insert into DhcpFilterStatus(LIST, MACADDRESS, DESCRIPTION, VALIDFROM, VALIDTHRU, STATUS) values(@addList, @addMac, @addDes, @addFrom, @addThru, 'adding')";
SqlParameter[] paras =
{
new SqlParameter("@addList", addList),
new SqlParameter("@addMac", addMac),
new SqlParameter("@addDes", addDes),
new SqlParameter("@addFrom", Convert.ToDateTime(addFrom)),
new SqlParameter("@addThru", Convert.ToDateTime(addThru))
};
try
{
SqlHelpers.ExecuteNonQuery(CommandType.Text, ADsql, paras);
MessageBox.Show("添加成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where STATUS !='deleting'";
frmmain.dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
frmmain.dgvData.DataSource = frmmain.dt;
}
catch (Exception ex)
{
MessageBox.Show("添加到数据库失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnAlter_Click(object sender, EventArgs e)
{
frmMain frmmain = (frmMain)this.Owner;
string alterList = string.Empty;
if (ValidInfo.IsAllowOrDeny(cbbAlterList.Text) == true)
{
alterList = cbbAlterList.Text;
}
else
{
MessageBox.Show("LIST必须为Allow或者Deny", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string currentMAC = frmmain.dgvData.CurrentRow.Cells[1].Value.ToString();
string alterDes = txtAlterDescription.Text;
string alterFrom = dtpAlterValidFrom.Text;
string alterThru = dtpAlterValidThru.Text;
string ATsql = "update DhcpFilterStatus set LIST=@alterList, DESCRIPTION=@alterDes, VALIDFROM=@alterFrom, VALIDTHRU=@alterThru, STATUS='altering' where MACADDRESS=@currentMAC";
SqlParameter[] paras =
{
new SqlParameter("@currentMAC", currentMAC),
new SqlParameter("@alterList", alterList),
new SqlParameter("@alterDes", alterDes),
new SqlParameter("@alterFrom", Convert.ToDateTime(alterFrom)),
new SqlParameter("@alterThru", Convert.ToDateTime(alterThru))
};
try
{
SqlHelpers.ExecuteNonQuery(CommandType.Text, ATsql, paras);
MessageBox.Show("修改成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where STATUS !='deleting'";
frmmain.dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
frmmain.dgvData.DataSource = frmmain.dt;
}
catch (Exception ex)
{
MessageBox.Show("修改到数据库失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnImport_Click(object sender, EventArgs e)
{
string fileName = string.Empty;
string duplicate = string.Empty;
List <string> database = new List <string>();
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "CSV文件(*.csv)|*.csv|TXT文件(*.txt)|*.txt|所有文件(*.*)|*.*";
if (openfile.ShowDialog() == DialogResult.OK)
{
fileName = openfile.FileName;
}
else
{
return;
}
try
{
int k = 0;
int t = 0;
csv = ValidInfo.GetDataTabletFromCSVFile(fileName);
for (int i = 0; i < csv.Columns.Count; i++)
{
csv.Columns[i].ColumnName = csv.Columns[i].ColumnName.ToUpper();
}//将列名大写
for (int i = 0; i < csv.Rows.Count; i++)///////////添加对于CSV文件中数据的验证
{
if (ValidInfo.IsAllowOrDeny(csv.Rows[i][0].ToString()) == true && ValidInfo.IsMAC(csv.Rows[i][1].ToString()) == true)//验证allow和macaddress的正确性
{
k += 1;
}
else
{
MessageBox.Show($"读取数据失败!{csv.Rows[i][1].ToString()}有误", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
dgvFileData.DataSource = null;
return;
}
for (int j = i + 1; j < csv.Rows.Count; j++)
{
if (csv.Rows[i][1].ToString() == csv.Rows[j][1].ToString())
{
duplicate = duplicate + csv.Rows[j][1].ToString() + " ,";
csv.Rows.RemoveAt(j);
t += 1;
}
}//去除重复项
database.Add(csv.Rows[i][1].ToString());//获取dt csv的macaddress列，方便下一步进行数据库去重
}
int pre = csv.Rows.Count;
DistinctFromDatabase(csv);//和数据库比对进行去重
int aft = csv.Rows.Count;
int total = pre - aft;
if ((duplicate == string.Empty) && (total == 0))
{
MessageBox.Show($"读取数据成功，共{k}条", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show($"读取{k-total}条数据,原文件中与数据库冲突的有{total}项，{conflict.TrimEnd(',')}" + "\n" +
$"原文件中重复项有{t}项，{duplicate.TrimEnd(',')}", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
conflict = string.Empty;
dgvFileData.AutoGenerateColumns = false;
dgvFileData.DataSource = null;
dgvFileData.DataSource = csv;
csv.Columns.Add("STATUS", Type.GetType("System.String"));
for (int i = 0; i < csv.Rows.Count; i++)
{
csv.Rows[i][csv.Columns.Count - 1] += "uploading";
}
}
catch (Exception ex)
{
MessageBox.Show("读取数据失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
