我手上有关数据交换的项目比较多,而且比较分散. 最新业务量大到一定程度,我需要知道每个事项任务每天数据量交换的情况, 有一部分是用SSIS包执行的,有一部分是通过xml数据交换的,
还有一部分, 是通过pdf文件上传做交换的,还有ftp文件下载等等,总之就是很杂.
我想写一个工具, 把这些交换的东西全部整合一下,做一下统计,然后每天出一个报告. 我就开始准备我的程序.
我想要实现的功能包括如下几个方面:
1.多数据库连接 ,各个系统都在不同的数据库里面
2.要将结果生成到Excel里面
3.为了转发方便, 我需要生成邮件,然后群发,这样就可以共享了.
首先我使用 EntityFrameWork 作为数据底层, 这样也是为了方便操作数据库, 然后 我希望对各表进行读取,有一些复杂的查询语句,我希望能够直接执行,
因为要时常统计多个数据库中的表信息,所以我做了如下的封装. (我只希望有查询的功能)
public class SqlDBHelper<T> where T : class { DbContext context = null; public SqlDBHelper() { context = new DbContext("defaultDBEntity"); } public SqlDBHelper(string dbEntityName) { context = new DbContext(dbEntityName); } /// <summary> /// 按条件查询 /// </summary> /// <param name="where"></param> /// <returns></returns> public IQueryable<T> FindList(Expression<Func<T, bool>> where) { var temp = context.Set<T>().Where(where); return temp; } /// <summary> /// 按条件查询，排序 /// </summary> /// <typeparam name="S"><peparam> /// <param name="where"></param> /// <param name="orderBy"></param> /// <param name="isAsc"></param> /// <returns></returns> public IQueryable<T> FindList<S>(Expression<Func<T, bool>> where, Expression<Func<T, S>> orderBy, bool isAsc) { var list = context.Set<T>().Where(where); if (isAsc) list = list.OrderBy<T, S>(orderBy); else list = list.OrderByDescending<T, S>(orderBy); return list; } /// <summary> /// 按条件查询，分页 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <param name="where"></param> /// <returns></returns> public IQueryable<T> FindPagedList(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> where) { var list = context.Set<T>().Where(where); rowCount = list.Count(); list = list.Skip(pageSize * (pageIndex - 1)).Take(pageSize); return list; } /// <summary> /// 按条件查询，分页，排序 /// </summary> /// <typeparam name="S"><peparam> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="rowCount"></param> /// <param name="where"></param> /// <param name="orderBy"></param> /// <param name="isAsc"></param> /// <returns></returns> public IQueryable<T> FindPagedList<S>(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> where, Expression<Func<T, S>> orderBy, bool isAsc) { var list = context.Set<T>().Where(where); rowCount = list.Count(); if (isAsc) list = list.OrderBy<T, S>(orderBy).Skip(pageSize * (pageIndex - 1)).Take(pageSize); else list = list.OrderByDescending<T, S>(orderBy).Skip(pageSize * (pageIndex - 1)).Take(pageSize); return list; } public IQueryable<T> ExecuteQuery(string sql) { //"select Name,Count(*) Count from T_Persons where Id>{0} and CreateDateTime<={1} group by Name" var q1 = context.Database.SqlQuery<T>(sql).AsQueryable(); return q1; } }
如果希望有 增删改, 可以补上以下代码. (统计,由于是正式库,应该只赋予只读的数据权限)
/// <summary> /// 新增一个实体 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Add(T entity) { context.Entry<T>(entity).State = System.Data.Entity.EntityState.Added; return context.SaveChanges(); } /// <summary> /// 删除一个实体 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Remove(T entity) { context.Entry<T>(entity).State = System.Data.Entity.EntityState.Deleted; return context.SaveChanges(); } /// <summary> /// 修改一个实体 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Update(T entity) { context.Entry<T>(entity).State = System.Data.Entity.EntityState.Modified; return context.SaveChanges(); } /// <summary> /// 批量新增实体 /// </summary> /// <param name="dbContext"></param> /// <returns></returns> public int AddList(params T[] entities) { int result = 0; for (int i = 0; i < entities.Count(); i++) { if (entities[i] == null) continue; context.Entry<T>(entities[i]).State = System.Data.Entity.EntityState.Added; if (i != 0 && i % 20 == 0) { result += context.SaveChanges(); } } if (entities.Count() > 0) result += context.SaveChanges(); return result; } /// <summary> /// 批量删除实体 /// </summary> /// <param name="where"></param> /// <returns></returns> public int RemoveList(Expression<Func<T, bool>> where) { var temp = context.Set<T>().Where(where); foreach (var item in temp) { context.Entry<T>(item).State = System.Data.Entity.EntityState.Deleted; } return context.SaveChanges(); }
具体使用的时候, 指定数据库实体,然后设置 实体表对象,进行查询
const string objEntName = "DBTest3Entities"; public List<CheckDataModel> GetDataUploadDZ() { DBHelper<DataUploadDZ> helper1 = new DBHelper<DataUploadDZ>(objEntName); DataUploadDZ uploadDz = helper1.FindList(c => c.Id > 0).OrderByDescending(c => c.CreateTime).Take(1).FirstOrDefault(); //省略 逻辑处理,以及 return 语句 }
最后从 各个数据库里面,查询出对应的信息, 整理成统一的格式, 进行 生成Excel 以及发送Html 邮件
List<CheckDataModel> list12 = new List<CheckDataModel>(); //省略逻辑处理 list1.RemoveAll(c => c.LogicName == null || c.LogicName == ""); for (int i = 0; i < list1.Count; i++) { list1[i].Xh = (i + 1).ToString(); } //然后将数据List 写入表格, 以及导出Excel //数据有了, 现在开始生成 Html页面, 以及Excel附件 DataTable table = TableListHelper.ToDataTable<CheckDataModel>(list1); DataRow newRow = table.NewRow(); newRow[0] = " 序号 "; newRow[1] = " 运行时间设定 "; newRow[2] = " 业务名称 "; newRow[3] = " 业务简称代码 "; newRow[4] = " 最后一次运行时间 "; newRow[5] = " 运行状态 "; newRow[6] = " 错误信息 "; //序号</th><th>运行时间设定</th><th>业务名称</th><th>业务简称代码</th><th>最后一次运行时间</th><th>运行状态</th><th>错误信息</th> table.Rows.InsertAt(newRow, 0); string filePath = @"E:ApplicationRunCheckDataExcel" + "每日数据交换运行状况检查" + DateTime.Now.ToString("yyyy-MM-dd") + ".xlsx"; if (File.Exists(filePath)) { FileInfo fi = new FileInfo(filePath); if (fi.Length < 1024) { fi.Delete(); //如果文件小于1kb, 删除该文件 } } if (!File.Exists(filePath)) { //如果文件不存在,就生成excel,并且发送邮件 NpoiExcelHelper.CreateExcel(table, filePath); } string htmlContent = GetHtmlContent(list1); SendEmail(htmlContent, filePath);
首先将List转换成 DataTable
public static DataTable ToDataTable<T>(IEnumerable<T> collection) { var props = typeof(T).GetProperties(); var dt = new DataTable(); dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray()); if (collection.Count() > 0) { for (int i = 0; i < collection.Count(); i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in props) { object obj = pi.GetValue(collection.ElementAt(i), null); tempList.Add(obj); } object[] array = tempList.ToArray(); dt.LoadDataRow(array, true); } } return dt; }
然后生成Html代码,这里生成一个Html,带表格的页面
private string GetHtmlContent(List<CheckDataModel> listData) { StringBuilder sb = new StringBuilder(256); sb.Append("<!DOCTYPE html>"); sb.Append("<html lang="en" xmlns="http://www.w3.org/1999/xhtml">"); sb.Append("<head> <meta charset="utf-8" /> <title></title></head>"); sb.Append("<style>").AppendLine(); sb.Append("table { border-left: 1px solid black;border-top: 1px solid black;}").AppendLine(); sb.Append("td { border-right: 1px solid black; border-bottom: 1px solid black;padding:0px 5px;}").AppendLine(); sb.Append("th { border-right: 1px solid black; border-bottom: 1px solid black;padding:0px 5px;}").AppendLine(); sb.Append("</style>").AppendLine(); sb.Append("<html><body>").AppendLine(); sb.Append("<table cellspacing="0" cellpadding="0">").AppendLine(); ; sb.Append(string.Format("<th>序号</th><th>运行时间设定</th><th>业务名称</th><th>业务简称代码</th><th>最后一次运行时间</th><th>运行状态</th><th>错误信息</th>")); for (int i = 0; i < listData.Count; i++) { sb.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>", listData[i].Xh, listData[i].RunTimeSD, listData[i].LogicName, listData[i].LogicCode, listData[i].LastRunTime, listData[i].RunStatus,
(string.IsNullOrEmpty(listData[i].Remark) ? " " : listData[i].Remark))); } sb.Append("</table>").AppendLine(); sb.Append("</body></html>"); string result = sb.ToString(); return result; }
发邮件,使用QQ的 STMP3 ,这里特别说一下, QQ现在不是直接使用邮箱密码,而是要发送短信去生成一个 code,用这种方式可以保护邮箱密码不被泄露
private void SendEmail(string htmlContent, string filePath) { try { var reciver = ConfigurationManager.AppSettings["EmailAddress"].ToString(); string mailfrom = "XXXXX@qq.com"; string mailPwd = "********"; //mcyabgovpiuebbfd // var content = rtxt_Content.Text; MailMessage message = new MailMessage(); //设置发件人,发件人需要与设置的邮件发送服务器的邮箱一致 MailAddress fromAddr = new MailAddress(mailfrom); message.From = fromAddr; //设置收件人,可添加多个,添加方法与下面的一样 message.To.Add(reciver); //设置抄送人 // message.CC.Add("XXXXX@qq.com"); //设置邮件标题 message.Subject = "每日运行数据检查" + DateTime.Now.ToString("yyyy-MM-dd"); if (filePath != "") { //如果有附件,添加附件 Attachment data = new Attachment(filePath, MediaTypeNames.Application.Octet); ContentDisposition disposition = data.ContentDisposition; disposition.CreationDate = System.IO.File.GetCreationTime(filePath); disposition.ModificationDate = System.IO.File.GetLastWriteTime(filePath); disposition.ReadDate = System.IO.File.GetLastAccessTime(filePath); message.Attachments.Add(data); System.Net.Mime.ContentType ctype = new System.Net.Mime.ContentType(); } message.IsBodyHtml = true; //是否为html格式 //设置邮件内容 message.Body = htmlContent; //设置邮件发送服务器,服务器根据你使用的邮箱而不同,可以到相应的 邮箱管理后台查看,下面是QQ的 SmtpClient client = new SmtpClient("smtp.qq.com"); //设置发送人的邮箱账号和密码 client.Credentials = new NetworkCredential(mailfrom, mailPwd); //启用ssl,也就是安全发送 client.EnableSsl = true; //发送邮件 client.Send(message); } catch (Exception ex) { throw ex; } }
邮件内容,并且带上附件Excel, 这里生成Excel的方式, 我比较喜欢用 NPOI组件,这个不仅生成Excel 快速,而且可以不用安装Office组件,依耐性低.
NPOI 组件, 是第三方 开源的, 可以到它的服务器上,去下载源代码,这里我只是简单介绍下使用方式.
public class NpoiExcelHelper { public static void CreateExcel(DataTable table,string path) { XSSFWorkbook workbook2007 = new XSSFWorkbook(); //新建xlsx工作簿 ISheet sheet = workbook2007.CreateSheet("Sheet1"); for (int i = 0; i < table.Rows.Count; i++) { IRow row = sheet.CreateRow(i); //i表示了创建行的索引，从0开始 //创建单元格 for (int j = 0; j < table.Columns.Count; j++) { ICell cell = row.CreateCell(j); //同时这个函数还有第二个重载，可以指定单元格存放数据的类型 cell.SetCellValue(table.Rows[i][j].ToString()); } } FileStream file2007 = new FileStream(path, FileMode.Create); workbook2007.Write(file2007); file2007.Close(); workbook2007.Close(); } }
这里生成出来的是 xlsx的文件格式, 也就是 2007及以上版本.
嗯,好了,整个过程就是这样了. 下面总结一下流程.
1.设置多个数据库的Entity
2.进行查询,封装成统一的List<T>,显示结果
3.为了友好的显示,要生成Excel, 先将List<T>转换成DataTable,再写入Excel
4.根据DataTable,生成Html内容,然后作为邮件内容,发送出去.
5.邮件中顺带,将excel作为附件,一起发送.
6.按自己喜欢的方式, 我这里是是配置成Windows计划任务,这样我每天上班打开电脑就能知道昨天的运行情况了.(一天检查1次到2次)
说明: 不方便提供下载,所以进行了加密.