protected override void OnStart(string[] args)
{
EventLog.WriteEntry("我的服务启动");//在系统事件查看器里的应用程序事件里来源的描述
try
{
string sql_log = "insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '启动服务_2', 'DhcpDeleteService', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '启动服务_2', 'DhcpDeleteService', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
//System.Timers.Timer t1 = new System.Timers.Timer();
//t1.Interval = 10000;
//t1.Elapsed += new System.Timers.ElapsedEventHandler(AutoDelete);//到达时间的时候执行自动删除事件
//t1.AutoReset = true;//设置是执行一次（false）还是一直执行(true)；
//t1.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件；
System.Timers.Timer t2 = new System.Timers.Timer();
t2.Interval = Convert.ToInt32(ConfigurationManager.AppSettings.Get("Update_Delete_Alter_Add"));
t2.Elapsed += new System.Timers.ElapsedEventHandler(UpdateDatabase);
t2.AutoReset = true;
t2.Enabled = true;
}
/// <summary>
/// 写入登录日志的方法
/// </summary>
/// <param name="userid">用户ID</param>
/// <param name="username">用户名称</param>
/// <param name="roleid">权限ID</param>
/// <param name="rolename">权限名称</param>
/// <param name="logintdate">登录时间</param>
public void SaveLog(int userid, string username, int roleid, string rolename, string clientip, string createuser, string type)
{
var values = XmlHelper.ReadXml("SqlQuery", "insertloginlog");
string sql = string.Format(values.First().Value, userid, username, roleid, rolename, createuser, clientip, DateTime.Now, DateTime.Now, type);
SqlHelpers.ExecuteNonQuery(sql);
}
/// <summary>
/// INSERT/UPDATE/DELETE公共方法
/// </summary>
/// <param name="param">参数</param>
/// <returns>影响记录数</returns>
public int InsUpdDelDataTableToParam(Dictionary <string, List <Object> > param)
{
Dictionary <string, Dimention> report = GetReport();
string sql = string.Empty;
int count = 0;
List <string> repSql = new List <string>();
try
{
if (param != null)
{
foreach (var d in param)
{
Dimention dimention = report[d.Key];
sql = dimention.SqlAll;
int i = 0;
foreach (Object value in d.Value)
{
////替换后的sql语句
sql = sql.Replace("{" + i + "}", value.ToString());
i = i + 1;
}
}
}
count = SqlHelpers.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
FileLog.WriteLog(ex.ToString());
}
return(count);
}
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 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);
}
}
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);
}
}
protected override void OnStop()
{
try
{
string sql_log = "insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '停止服务_2', 'DhcpDeleteService', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '停止服务_2', 'DhcpDeleteService', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
EventLog.WriteEntry("我的服务停止");
}
public void UpdateDatabase(object source, System.Timers.ElapsedEventArgs e)
{
try
{
System.Timers.Timer t4 = (System.Timers.Timer)source;
t4.Enabled = false;
Upload();//顺序不能改
Delete();
Alter();
Add();
t4.Enabled = true;
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'UpdateDatabase', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
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);
}
}
/// <summary>
/// 定时检查，并执行方法
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
public void AutoDelete(object source, System.Timers.ElapsedEventArgs e)
{
try
{
if (DateTime.Now.Hour == Convert.ToInt32(ConfigurationManager.AppSettings.Get("AutoDelete")))
{
System.Timers.Timer t3 = (System.Timers.Timer)source;
t3.Enabled = false;
ValidThru();
t3.Enabled = true;
}
else
{
return;
}
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'AutoDelete', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
//增加操作日志
public HttpResponseMessage Post_Log([FromBody] string value)
{
string userid = UtilFunction.GetCookie("uid");
string username = UtilFunction.GetCookie("uname");
string roleid = UtilFunction.GetCookie("roleid");
string rolename = Convert.ToString(SqlHelpers.ExecuteScalar("select rolename from angel_sys_role where id = '" + roleid + "'"));
string logposition = value.Split('"')[5];
string operationtype = value.Split('"')[9];
try
{
int count = Convert.ToInt32(SqlHelpers.ExecuteScalar("SELECT COUNT(*) FROM angel_sys_operationlog"));
int ID = 0;
if (count == 0)
{
ID = 1;
}
else
{
ID = Convert.ToInt32(SqlHelpers.ExecuteScalar("SELECT MAX(ID) FROM angel_sys_operationlog")) + 1;
}
string sql = "INSERT INTO angel_sys_operationlog VALUES (" + ID + ", '" + userid + "', '" + username + "', '" + roleid + "','" + rolename + "','" + logposition + "','" + operationtype + "','" + value.ToString() + "','" + username + "','" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
SqlHelpers.ExecuteNonQuery(sql);
StringBuilder result = new StringBuilder();
result.Append("{");
string code = "\"code\":{{\"id\":{0},\"msg\":\"{1}\"}}";
code = string.Format(code, 1, "成功");
result.Append(code);
result.Append("}");
return(GetJSONMessage(result.ToString()));
}
catch (Exception er)
{
return(GetJSONMessage(""));
}
}
public int RunSQL(string list)
{
return(SqlHelpers.ExecuteNonQuery(list));
}
public string Create(string sql)
{
int result = SqlHelpers.ExecuteNonQuery(sql);
return("{\"ins\":" + result + "}");
}
public string Delete(string sql)
{
int result = SqlHelpers.ExecuteNonQuery(sql);
return("{\"del\":" + result + "}");
}
public string MulteBatch(List <string> sqlArray)
{
int result = SqlHelpers.ExecuteNonQuery(sqlArray);
return("\"ins\":" + result + "");
}