//我的方法
public DataTable DistinctFromDatabase(DataTable csv)
{
DataTable mac = new DataTable();
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where MACADDRESS in (";
for (int i = 0; i < csv.Rows.Count; i++)
{
sql += $"'{csv.Rows[i][1].ToString()}',";
}
sql = sql.TrimEnd(',');
sql += ")";
mac = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
for (int i = csv.Rows.Count - 1; i >= 0; i--)
{
for (int j = 0; j < mac.Rows.Count; j++)
{
if (csv.Rows[i][1].ToString() == mac.Rows[j][1].ToString())
{
conflict = conflict + mac.Rows[j][1].ToString() + " ,";
csv.Rows.RemoveAt(i);
break;
}
}
}
return(csv);
}
private void btnSearch_Click(object sender, EventArgs e)
{
string queryList = cbbList.Text + "%";
string queryMac = txtQueryMAC.Text + "%";
string queryDes = txtQueryDescription.Text + "%";
string queryFrom = dtpQueryFrom.Text;
string queryThru = dtpQueryThru.Text;
string sql = @"select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where convert(varchar,VALIDFROM,111) >= convert(varchar,@queryFrom,111) and STATUS !='deleting'";
sql += queryList != "" ? " and LIST like @queryList" : "";
sql += queryMac != "" ? " and MACADDRESS like @queryMac" : "";
sql += queryDes != "" ? " and DESCRIPTION like @queryDes" : "";
sql += queryThru != "" ? " and convert(varchar,VALIDTHRU,111) <= convert(varchar,@queryThru,111)" : "";
SqlParameter[] paras =
{
new SqlParameter("@queryList", queryList),
new SqlParameter("@queryMac", queryMac),
new SqlParameter("@queryDes", queryDes),
new SqlParameter("@queryFrom", Convert.ToDateTime(queryFrom)),
new SqlParameter("@queryThru", Convert.ToDateTime(queryThru))
};
dgvData.DataSource = null;
try
{
dgvData.DataSource = SqlHelpers.ExecuteDataTable(CommandType.Text, sql, paras);
}
catch (Exception ex)
{
MessageBox.Show("查询数据失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnUploadToDatabase_Click(object sender, EventArgs e)
{
try
{
InsertData insertdata = new InsertData();
string errorInfo = insertdata.InsertDataIntoSQLServerUsingSQLBulkCopy(csv, TableName);
if (errorInfo == "")
{
MessageBox.Show("上传到数据库成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show($"上传到数据库失败,{errorInfo}", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("上传到数据库失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
frmMain frmmain = (frmMain)this.Owner;
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;
dgvFileData.DataSource = null;
}
public void Delete()
{
DataTable dt = new DataTable();
try
{
string sql = "select LIST,MACADDRESS,DESCRIPTION from DhcpFilterStatus where STATUS = 'delete_1'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
char[] MyChar = { '_', 'D' };
for (int i = 0; i < dt.Rows.Count; i++)
{
//using (PowerShell PowerShellInstance = PowerShell.Create())
//{
// string ScriptADD = "Add-DhcpServerv4Filter -List " + "\"" + dt.Rows[i][0].ToString() + "\"" + " -MacAddress " + "\"" + dt.Rows[i][1].ToString().TrimEnd(MyChar) + "\"" + " -Description " + "\"" + dt.Rows[i][2].ToString() + "\"" + " -Force";
// PowerShellInstance.AddScript(ScriptADD);
// Collection<PSObject> PSOutput = PowerShellInstance.Invoke();
// Collection<ErrorRecord> errors = PowerShellInstance.Streams.Error.ReadAll();
// if (errors.Count > 0)
// {
// foreach (ErrorRecord er in errors)
// {
// string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '删除于dhcp服务器_2', '{dt.Rows[i][1].ToString().TrimEnd(MyChar)}', '{er.Exception.Message}', getdate())";
// SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
// return;
// }
// }
//}
using (PowerShell PowerShellInstance = PowerShell.Create())
{
string Script = "Remove-DhcpServerv4Filter -MacAddress " + "\"" + dt.Rows[i][1].ToString().Substring(0, dt.Rows[i][1].ToString().Length - 2) + "\"";
PowerShellInstance.AddScript(Script);
Collection <PSObject> PSOutput = PowerShellInstance.Invoke();
Collection <ErrorRecord> errors = PowerShellInstance.Streams.Error.ReadAll();
if (errors.Count > 0)
{
foreach (ErrorRecord er in errors)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '删除于dhcp服务器_2', '{dt.Rows[i][1].ToString().Substring(0, dt.Rows[i][1].ToString().Length - 2)}', '{er.Exception.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
return;
}
}
else if (errors.Count == 0)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '删除于dhcp服务器_2', '{dt.Rows[i][1].ToString().Substring(0, dt.Rows[i][1].ToString().Length - 2)}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
string sqlStatus = "delete from DhcpFilterStatus where STATUS = 'delete_1'";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sqlStatus);
}
}
}
Thread.Sleep(1);
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'Delete', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
public string Select(string sql)
{
string a = ";lt";
string b = ";gt";
sql = sql.Replace(a, "<").Replace(b, ">");
DataTable dt = SqlHelpers.ExecuteDataTable(SqlHelpers.connectionString, CommandType.Text, sql);
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
return(JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.None, timeFormat).ToLower());
}
//我的方法
public void ValidThru()
{
DataTable dt = new DataTable();
try
{
string sql = "select LIST,MACADDRESS from DhcpFilterStatus where STATUS='validthru_1'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
using (PowerShell PowerShellInstance = PowerShell.Create())
{
string Script = "Remove-DhcpServerv4Filter -MacAddress " + "\"" + dt.Rows[i][1].ToString() + "\"";
PowerShellInstance.AddScript(Script);
Collection <PSObject> PSOutput = PowerShellInstance.Invoke();
Collection <ErrorRecord> errors = PowerShellInstance.Streams.Error.ReadAll();
if (errors.Count > 0)
{
foreach (ErrorRecord er in errors)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', '{er.Exception.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
return;
}
else if (errors.Count == 0)
{
try
{
string Dsql = "delete from DhcpFilterStatus where MACADDRESS=" + "'" + dt.Rows[i][1].ToString() + "'";
SqlHelpers.ExecuteNonQuery(CommandType.Text, Dsql);
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
//writestr($"过期MAC:{dt.Rows[i][1].ToString()}删除成功");
}
catch (Exception ex)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', '{ex.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
//writestr($"过期MAC:{dt.Rows[i][1].ToString()}从Dhcp服务器删除成功，但是从数据库删除失败,原因:" + ex.Message);
return;
}
}
}
}
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'ValidThru', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
public frmMain()
{
InitializeComponent();
frmimportFile.Owner = this;
frmadd.Owner = this;
frmalter.Owner = this;
dtpQueryFrom.Value = DateTime.Today;
dtpQueryThru.Value = DateTime.Today.AddYears(10);
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where STATUS !='deleting'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
dgvData.DataSource = dt;
}
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);
}
}
public void Alter()
{
DataTable dt = new DataTable();
try
{
string sql = "select LIST,MACADDRESS,DESCRIPTION from DhcpFilterStatus where STATUS = 'alter_1'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
using (PowerShell PowerShellInstance = PowerShell.Create())
{
string Script = "Add-DhcpServerv4Filter -List " + "\"" + dt.Rows[i][0].ToString() + "\"" + " -MacAddress " + "\"" + dt.Rows[i][1].ToString() + "\"" + " -Description " + "\"" + dt.Rows[i][2].ToString() + "\"" + " -Force";
PowerShellInstance.AddScript(Script);
Collection <PSObject> PSOutput = PowerShellInstance.Invoke();
Collection <ErrorRecord> errors = PowerShellInstance.Streams.Error.ReadAll();
if (errors.Count > 0)
{
foreach (ErrorRecord er in errors)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '修改到dhcp服务器_2', '{dt.Rows[i][1].ToString()}', '{er.Exception.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
return;
}
return;
}
else if (errors.Count == 0)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '修改到dhcp服务器_2', '{dt.Rows[i][1].ToString()}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
string sqlStatus = "update DhcpFilterStatus set STATUS='done' where STATUS = 'alter_1'";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sqlStatus);
}
}
}
Thread.Sleep(10);
}
catch (Exception err)
{
//writestr(err.Message);
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'Alter', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvData.Rows.Count == 0)
{
return;
}
else if (dgvData.CurrentRow.Selected == false)
{
return;
}
else
{
string info = "确定要删除吗？";
DialogResult result = MessageBox.Show(info, "系统消息", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
string currentMAC = dgvData.CurrentRow.Cells[1].Value.ToString();
SqlParameter[] paras =
{
new SqlParameter("@currentMAC", currentMAC),
};
try
{
string Dsql = "update DhcpFilterStatus set MACADDRESS=@currentMAC+'_D', STATUS='deleting' where MACADDRESS=@currentMAC";
SqlHelpers.ExecuteNonQuery(CommandType.Text, Dsql, paras);
MessageBox.Show("删除成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("从数据库删除失败,原因:" + ex.Message, "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where STATUS !='deleting'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
dgvData.DataSource = dt;
}
else
{
return;
}
}
}
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);
}
}
public string GetData(string configKey, string sqlCode)
{
string result = "";
#region old code
string xmlName = XmlHelper.GetBaseConfigValue(configKey);
List <string> sqlArray = new List <string>();
if (xmlName.Equals("") == false)
{
var list = XmlHelper.ReadXml(xmlName, sqlCode);
if (list != null && list.Count > 0)
{
foreach (var sql in list)
{
sqlArray.Add(sql.Value);
}
}
if (sqlArray.Count > 0)
{
DataTable dt = SqlHelpers.ExecuteDataTable(SqlHelpers.connectionString, CommandType.Text, sqlArray[0]);
result = JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.None,
new JsonSerializerSettings
{
ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
}
);
}
}
else
{
result = "error:";
}
#endregion
return(result);
}
public string SelectOriginal(string sql)
{
string a = ";lt";
string b = ";gt";
sql = sql.Replace(a, "<").Replace(b, ">");
DataTable dt = SqlHelpers.ExecuteDataTable(SqlHelpers.connectionString, CommandType.Text, sql);
//名称和日期都进行格式
Newtonsoft.Json.JsonSerializerSettings setting = new Newtonsoft.Json.JsonSerializerSettings {
ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver()
};
JsonConvert.DefaultSettings = new Func <JsonSerializerSettings>(() =>
{
//日期类型默认格式化处理
setting.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;
setting.DateFormatString = "yyyy-MM-dd HH:mm:ss";
//空值处理
setting.NullValueHandling = NullValueHandling.Ignore;
return(setting);
});
return(JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.None, setting));
}