上位机学习日记（5）系统运行日志功能的编写与日志查看功能
学习记录（5）系统运行日志功能的编写与日志查看功能
（一）系统运行日志功能的编写
需要组件：imagelist 与listview
第一步，添加listview 与 imagelist 控件。
- 编辑listview 的列成员：
- 修改listview的属性，绑定imagelist，设置视图属性
- 隐藏表头属性
- 在imagelist添加图片，并且按照以下顺序进行整理
第二步，编写消息方法
/// <summary>
/// 日志方法
/// </summary>
/// <param name="index">消息等级 0提示 1警告 2错误</param>
/// <param name="log">字符串</param>
private void AddLog(int index, string log)
{
if (this.lstInfo.InvokeRequired)
{
Invoke(new Action(() =>
{
ListViewItem lst = new ListViewItem(" " + CurrentTime, index);
lst.SubItems.Add(log);
lstInfo.Items.Insert(0, lst);
}));
}
else
{
ListViewItem lst = new ListViewItem(" " + CurrentTime, index);
lst.SubItems.Add(log);
lstInfo.Items.Insert(0, lst);
}
}
#endregion
/// <summary>
/// 当前系统时间
/// </summary>
private string CurrentTime
{
get { return DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); }
}
第三步，存入数据库中（可选）。
public static class MySQLHelper
{
private static string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConnection.Close();
}
}
/// <summary>
/// 获取单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConnection.Close();
}
}
/// <summary>
/// 返回结果集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static MySqlDataReader GetReader(string sql)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
DBConnection.Close();
throw ex;
}
}
/// <summary>
/// 返回数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
DBConnection.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBConnection.Close();
}
}
#region 启用事务执行多条SQL语句
/// <summary>
/// 启用事务执行多条SQL语句
/// </summary>
/// <param name="sqlList"></param>
/// <returns></returns>
public static bool UpdateByTran(List<string> sqlList)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = DBConnection;
try
{
DBConnection.Open();
cmd.Transaction = DBConnection.BeginTransaction();//开启事务
foreach (string itemsql in sqlList)
{
cmd.CommandText = itemsql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//提交事务
return true;
}
catch (Exception ex)
{
if (cmd.Transaction != null)
cmd.Transaction.Rollback();//回滚事务
throw new Exception("调用事务方法时出现错误：" + ex.Message);
}
finally
{
if (cmd.Transaction != null)
cmd.Transaction = null;//清空事务
DBConnection.Close();
}
}
#endregion
#region 错误信息写入日志
/// <summary>
/// 将错误信息写入日志文件
/// </summary>
/// <param name="msg"></param>
private static void WriteLog(string msg)
{
FileStream fs = new FileStream("Log.text", FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine("[{0}] 错误信息：{1}", DateTime.Now.ToString(), msg);
sw.Close();
fs.Close();
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql, MySqlParameter[] param)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
cmd.Parameters.AddRange(param);//添加参数
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
WriteLog("执行Update(string sql)方法发生错误，错误日志：" + ex.Message);
throw;
}
finally
{
DBConnection.Close();
}
}
/// <summary>
/// 返回单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql, SqlParameter[] param)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
cmd.Parameters.AddRange(param);//添加参数
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
WriteLog("执行GetSingleResult(string sql)方法发生错误，错误日志：" + ex.Message);
throw;
}
finally
{
DBConnection.Close();
}
}
/// <summary>
/// 返回数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static MySqlDataReader GetReader(string sql, SqlParameter[] param)
{
MySqlConnection DBConnection = new MySqlConnection(ConStr);
MySqlCommand cmd = new MySqlCommand(sql, DBConnection);
try
{
DBConnection.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
WriteLog("执行GetReader(string sql)方法发生错误，错误日志：" + ex.Message);
DBConnection.Close();
throw ex;
}
}
#endregion
#region 写入日志
public static void Log(string info, int type, string user)
{
string sql = "Insert into SysLog(LogTime,LogInfo,User,LogType) values('{0}','{1}','{2}',{3})";
sql = string.Format(sql, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), info, user, type);
MySQLHelper.Update(sql);
}
#endregion
#region 增加产量
public static void AddProduct(string LotNum, int State, string user)
{
string sql = "Insert into Product(ProductDateTime,LotNum,Result,Operator) values('{0}','{1}',{2},'{3}')";
sql = string.Format(sql, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), LotNum, State, user);
MySQLHelper.Update(sql);
}
#endregion
}
例子
MySQLHelper.Log("登录系统", 0, CommonMethods.objAdmin.LoginName);
（二）查看系统运行日志
功能样式如下：
页面上是加入了SplitContainer控件。其中左边是一个DataGridView控件，dock设置了fill。右边的日期和时间控件都是DateTimePicker。区别是CustomFormat属性设置的不同
其中DataGridView控件的列绑定了sql数据表的列信息
最后一列需要填充剩余空间。
查询功能语句:
private void FrmSysLog_Load(object sender, EventArgs e)
{
this.cmb_AlarmType.DataSource = new string[] { "日志信息", "报警信息", "全部信息" };
this.cmb_AlarmType.SelectedIndex = 2;
this.dgv_Log.AutoGenerateColumns = false;
}
/// <summary>
/// 信息查询
/// </summary>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="type"></param>
private void QueryProcess(string start, string end, int type)
{
//对开始时间和结束时间做判断
DateTime t_start = Convert.ToDateTime(start);
DateTime t_end = Convert.ToDateTime(end);
if (t_start >= t_end)
{
MessageBox.Show("开始时间必须小于结束时间", "查询提示");
return;
}
//SQL语句拼接
StringBuilder sb = new StringBuilder();
sb.Append("select * from SysLog where LogTime between '{0}' and '{1}'");
string sql = string.Format(sb.ToString(), start, end);
if (type <= 1)
{
sql += " and LogType=" + type;
}
DataSet ds = MySQLHelper.GetDataSet(sql);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
/// 先清空，再绑定
this.dgv_Log.DataSource = null;
this.dgv_Log.DataSource = dt;
}
else
{
MessageBox.Show("未查询到数据，请检查！", "查询提示");
}
}
else
{
MessageBox.Show("查询有误，请检查！", "查询提示");
}
}
数据查询表格美化：
-
添加行号（通过gdi绘制的方法）：
调用事件
/// <summary>
/// 添加行号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgv_Log_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
{
DgvRowPostPaint(this.dgv_Log, e);
}
private void DgvRowPostPaint(DataGridView dgv, DataGridViewRowPostPaintEventArgs e)
{
try
{
//添加行号
SolidBrush v_SolidBrush = new SolidBrush(dgv.RowHeadersDefaultCellStyle.ForeColor);
int v_LineNo = 0;
v_LineNo = e.RowIndex + 1;
string v_Line = v_LineNo.ToString();
e.Graphics.TextRenderingHint = System.Drawing.Text.TextRenderingHint.ClearTypeGridFit;
e.Graphics.DrawString(v_Line, e.InheritedRowStyle.Font, v_SolidBrush, e.RowBounds.Location.X + 10, e.RowBounds.Location.Y + 8);
}
catch (Exception ex)
{
MessageBox.Show("添加行号时发生错误，错误信息：" + ex.Message, "操作失败");
}
}
- 修改消息列的显示(数据库中显示为数字，这里显示为汉字)：
private void dgv_Log_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.RowIndex>=0)
{
if (e.ColumnIndex==3)
{
string result = string.Empty;
switch (e.Value)
{
case 0:
result = "提示";
break;
case 1:
result = "警告";
break;
case 2:
result = "报警";
break;
default:
break;
}
e.Value = result;
}
}
}
（三）打印功能与导出功能
演示如下：
打印功能调用代码案例
/// <summary>
/// 打印功能
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Print_Click(object sender, EventArgs e)
{
PrintDGV.Print_DataGridView(this.dgv_Log);
}
导出功能 调用函数如下:
/// <summary>
/// 导出功能
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Export_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter="XLS文件(*.xls)|*.xls|所有文件|*.*";
sfd.FileName = "日志导出";
sfd.DefaultExt = "xls";
sfd.AddExtension = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (NiceExcelSaveAndRead.SaveToExcelNew(sfd.FileName, this.dgv_Log))
{
MessageBox.Show("报表导出成功！", "报表导出");
}
else
{
MessageBox.Show("报表导出失败！", "报表导出");
}
}
}
