1. EF 扩展 sql 分页查询方法
public virtual PageEntity<T> SqlQuery<T>(string sql, int pageIndex, int pageSize) where T : class, new() { var ret = new PageEntity<T>(); var list = _dbContext.Database.SqlQuery<T>(sql); ret.PageIndex = pageIndex; ret.PageSize = pageSize; ret.TotalRecord = list.Count(); ret.Rows = list.Skip<T>((pageIndex - 1) * pageSize).Take<T>(pageSize).ToList(); return ret; }
2. 根据 url 把文件下载到本地，并返回文件保存的路径
/// <summary> /// 根据 url 把文件下载到本地，并返回文件保存的路径 /// </summary> public static string DownloadFileFromNetUrl(string url, string savePath) { try { if (!Directory.Exists(savePath)) { Directory.CreateDirectory(savePath); } // 保存到本地文件的路径 var filePath = string.Format("{0}{1}", savePath, Path.GetFileName(url)); // 有人下载过，直接返回文件路径，没人下载过，把文件下载到本地 if (!File.Exists(filePath)) { WebRequest req = WebRequest.Create(url); req.Method = "GET"; using (WebResponse webRes = req.GetResponse()) { int length = (int)webRes.ContentLength; HttpWebResponse response = webRes as HttpWebResponse; Stream stream = response.GetResponseStream(); //读取到内存 MemoryStream stmMemory = new MemoryStream(); byte[] buffer = new byte[length]; int i; //将字节逐个放入到Byte中 while ((i = stream.Read(buffer, 0, buffer.Length)) > 0) { stmMemory.Write(buffer, 0, i); } byte[] fileBytes = stmMemory.ToArray();//文件流Byte，需要文件流可直接return，不需要下面的保存代码 stmMemory.Close(); MemoryStream m = new MemoryStream(fileBytes); FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate); m.WriteTo(fs); m.Close(); fs.Close(); } } return filePath; } catch (System.Exception ex) { throw ex; } }
3.HttpWebRequest模拟发送Post请求
static void Main(string[] args) { string url = "http://admin/api/auth"; string data = "name=admin&password=admin"; string result = HttpPost(url, data); } /// <summary> /// POST请求与获取结果 /// </summary> public static string HttpPost(string Url, string paramData) { // 请求 HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url); request.Method = "POST"; request.KeepAlive = false; webReq.Headers.Add("Authorization", ""); // 权限验证 request.ContentType = "application/x-www-form-urlencoded"; request.ContentLength = postDataStr.Length; StreamWriter writer = new StreamWriter(request.GetRequestStream(), Encoding.ASCII); writer.Write(paramData); writer.Flush(); // 处理返回值 HttpWebResponse response = (HttpWebResponse)request.GetResponse(); var encoding = response.ContentEncoding; if (encoding == null || encoding.Length < 1) { encoding = "UTF-8"; //默认编码 } StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding(encoding)); var ret = reader.ReadToEnd(); return ret; }
4.HttpWebRequest上传大文件：提示远程主机强迫关闭了一个现有的连接
1.网络问题 2.文件太大，超时 3.文件太大，限制了。分为多份请求
5. 定时任务
任务计划程序
6.Asp.net core 全局异常监控和记录日志
系统异常监控可以说是重中之重，系统不可能一直运行良好，开发和运维也不可能24小时盯着系统，系统抛异常后我们应当在第一时间收到异常信息。在Asp.net Core里我使用拦截器和中间件两种方式来监控异常。全局异常监控的数据最好还是写入数据库，方便查询。 1.配置NLog Nuget 安装：NLog、NLog.Web.AspNetCore、Autofac.Extensions.DependencyInjection 2.添加nlog.config 配置文件 <?xml version="1.0" encoding="utf-8"?> <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" autoReload="true" internalLogLevel="Info"> <!-- 启用.net core的核心布局渲染器 --> <extensions> <add assembly="NLog.Web.AspNetCore" /> </extensions> <!-- 写入日志的目标配置 --> <targets> <!-- 调试 --> <target xsi:type="File" name="debug" fileName="logs/debug-${shortdate}.log" layout="${longdate}|${event-properties:item=EventId_Id}|${uppercase:${level}}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}|action: ${aspnet-mvc-action}" /> <!-- 警告 --> <target xsi:type="File" name="warn" fileName="logs/warn-${shortdate}.log" layout="${longdate}|${event-properties:item=EventId_Id}|${uppercase:${level}}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}|action: ${aspnet-mvc-action}" /> <!-- 错误 --> <target xsi:type="File" name="error" fileName="logs/error-${shortdate}.log" layout="${longdate}|${event-properties:item=EventId_Id}|${uppercase:${level}}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}|action: ${aspnet-mvc-action}" /> </targets> <!-- 映射规则 --> <rules> <!-- 调试 --> <logger name="*" minlevel="Trace" maxlevel="Debug" writeTo="debug" /> <!--跳过不重要的微软日志--> <logger name="Microsoft.*" maxlevel="Info" final="true" /> <!-- 警告 --> <logger name="*" minlevel="Info" maxlevel="Warn" writeTo="warn" /> <!-- 错误 --> <logger name="*" minlevel="Error" maxlevel="Fatal" writeTo="error" /> </rules> </nlog> 3.注入NLog 在Program.cs里注入NLog依赖，添加依赖前需要导入两个命名空间Microsoft.Extensions.Logging、 NLog.Web。 public class Program { public static void Main(string[] args) { CreateHostBuilder(args).Build().Run(); } public static IHostBuilder CreateHostBuilder(string[] args) => Host.CreateDefaultBuilder(args) .ConfigureWebHostDefaults(webBuilder => { webBuilder.UseStartup<Startup>(); }) //使用Nlog .UseNLog() //添加Autofac容器 .UseServiceProviderFactory(new AutofacServiceProviderFactory()); } 4.拦截器 在Asp.Mvc里最常用的拦截器，在Asp.net Core里也是支持的。先定义拦截器，再注入拦截器，这里自定义拦截器实现接口IExceptionFilter，接口会要求实现OnException方法，当系统发生未捕获的异常时就会触发这个方法。这里全局异常信息最好能放入数据库里，方便后台查询，再就是抛异常后最好能给负责人发邮件和发送报警短信，也可以直接拨打电话。 public class GlobalExceptionFilter : IExceptionFilter { private IWebHostEnvironment _env; private ILogger<GlobalExceptionFilter> _logger; public GlobalExceptionFilter(IWebHostEnvironment _env,ILogger<GlobalExceptionFilter> _logger) { this._env = _env; this._logger = _logger; } public void OnException(ExceptionContext context) { if (context.Exception.GetType() == typeof(BusException)) { //如果是自定义异常，则不做处理 } else { } //日志入库 //向负责人发报警邮件，异步 //向负责人发送报警短信或者报警电话，异步 Exception ex = context.Exception; //这里给系统分配标识，监控异常肯定不止一个系统。 int sysId = 1; //这里获取服务器ip时，需要考虑如果是使用nginx做了负载，这里要兼容负载后的ip， //监控了ip方便定位到底是那台服务器出故障了 string ip = context.HttpContext.Connection.RemoteIpAddress.ToString(); _logger.LogError($"系统编号：{sysId},主机IP:{ip},堆栈信息：{ex.StackTrace},异常描述：{ex.Message}"); context.Result = new JsonResult(ResultBody.error(ex.Message)); context.ExceptionHandled = true; } } 在Startup.ConfigureServices方法里注入全局异常处理拦截器。 public void ConfigureServices(IServiceCollection services) { services.AddControllersWithViews(); //注入全局异常处理 services.AddMvc(option => { option.Filters.Add(typeof(GlobalExceptionFilter)); }); } 5.中间件 定义中间件，定义中间件时先导入日志命名空间Microsoft.Extensions.Logging。 public class GlobalExceptionMiddleware { private readonly RequestDelegate next; private ILogger<GlobalExceptionMiddleware> logger; public GlobalExceptionMiddleware(RequestDelegate next, ILogger<GlobalExceptionMiddleware> logger) { this.next = next; this.logger = logger; } public async Task Invoke(HttpContext context) { try { await next.Invoke(context); } catch (Exception ex) { await HandleExceptionAsync(context, ex); } } private async Task HandleExceptionAsync(HttpContext context, Exception e) { if (e.GetType() == typeof(BusException)) { //如果是自定义异常，则不做处理 } else { } //记日志 int sysId = 1; string ip = context.Connection.RemoteIpAddress.ToString(); logger.LogError($"系统编号：{sysId},主机IP:{ip},堆栈信息：{e.StackTrace},异常描述：{e.Message}"); string result = System.Text.Json.JsonSerializer.Serialize(ResultBody.error(e.Message)); await context.Response.WriteAsync(result); } } 在Startup.Configure方法里注册中间件。 public void Configure(IApplicationBuilder app, IWebHostEnvironment env,ILoggerFactory loggerFactory) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } else { app.UseExceptionHandler("/Home/Error"); } //注册异常处理中间件 app.UseMiddleware<GlobalExceptionMiddleware>(); app.UseStaticFiles(); app.UseRouting(); app.UseAuthorization(); app.UseEndpoints(endpoints => { endpoints.MapControllerRoute( name: "default", pattern: "{controller=Home}/{action=Index}/{id?}"); }); } 中间件这里处理异常最后向客户端响应写入了一个字符串，这是个拦截器处理方式不同的地方。当然对客户端或者前端来说还是JSON对象更直观些。
7.图片下载
前端 function DownLoadImg(url) { window.location.href = `${api}/Activity/DownLoadImg?url=${url}`; } 后台 [HttpGet] public FileResult DownLoadImg(string url) { try { var fileName = Path.GetFileName(url); return File(new FileStream("C:\\\\UploadFiles\\" + fileName, FileMode.Open), "text/plain", fileName); } catch(Exception ex) { LogHelper.WriteErrorLog("活动下载图片：", ex.Message); return null; } }
8.DBHelper 查询封装
public class DbHelper { public static DataTable ExecuteSql(string strSql) { using (SqlConnection sqlConnection = new SqlConnection()) { string DB = ConfigurationManager.ConnectionStrings["DbConnString"].ToString(); //获取数据库连接 sqlConnection.ConnectionString = DB; //打开数据库 sqlConnection.Open(); //创建SqlCommand 的实例 SqlCommand MydbCommand = new SqlCommand(strSql, sqlConnection); try { //创建SqlDataAdapter 的实例 SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); sqlDataAdapter.SelectCommand = MydbCommand; //声明存放数据用DataSet DataSet dataSet = new DataSet(); //数据填充 sqlDataAdapter.Fill(dataSet, "s"); //返回值 return dataSet.Tables[0]; } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { //资源释放 MydbCommand.Dispose(); //关闭连接 sqlConnection.Close(); } } } public static DataTable GetUser() { var sql = string.Format(@"select * from sys_user"); return DbHelper.ExecuteSql(sql); } }
9.ExcelHelper
// 调用 try { // 模板文件存放路径 var templatePath = string.Format(@"{0}VisitNumber.xlsx", ConfigurationManager.AppSettings["ExportTemplatePath"]); // 模板填充数据后，保存到本地服务器路径 savePath = string.Format(@"{0}VisitNumber{1}.xlsx", ConfigurationManager.AppSettings["UploadPath"], DateTime.Now.ToString("yyyyMMddHHmmss")); var list = new List<MultiSheet>(); var sheet1 = new MultiSheet(); sheet1.Source = DbHelper.GetVisitNum(); sheet1.IsMergeCell = true; sheet1.MergeIndex = new List<int> { 0, 1, 2 }; list.Add(sheet1); var sheet2 = new MultiSheet(); sheet2.Source = DbHelper.GetVisitCountUser(); list.Add(sheet2); ExcelHelper.ToFillTemplate(templatePath, savePath, list); } catch (Exception ex) { FileLogger.WriteErrorLog("访问量统计：", ex.ToString()); } ///封装类 public class ExcelHelper { /// <summary> /// 多个 sheet 页数据 /// ①根据模板路径读取模板 /// ②数据填充到模板后 /// ③填充后保存到本地 /// </summary> /// <param name="fileTemplatePath">模板路径</param> /// <param name="excelPath">excel 保存路径</param> /// <returns></returns> public static void ToFillTemplate(string templatePath, string excelPath, List<MultiSheet> multiSheet) { //第一步：读取模板 excel，初始化 workbook using (FileStream fs = File.Open(templatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //把xls文件读入workbook变量里，之后就可以关闭了 XSSFWorkbook workbook = new XSSFWorkbook(fs); //第二步：设置sheet 页样式，填充数据 for (int i = 0; i < multiSheet.Count; i++) { // 第 i 个 sheet 页的数据 var itemSheet = multiSheet[i]; XSSFSheet sheet = workbook.GetSheetAt(i) as XSSFSheet; #region 设置sheet 页样式 IFont fontCommon = workbook.CreateFont(); fontCommon.FontHeightInPoints = 10; fontCommon.FontName = "微软雅黑"; //正式数据 ICellStyle styleCommonLeft = workbook.CreateCellStyle(); styleCommonLeft.Alignment = HorizontalAlignment.Left; styleCommonLeft.VerticalAlignment = VerticalAlignment.Center; styleCommonLeft.SetFont(fontCommon); ICellStyle styleCommonRight = workbook.CreateCellStyle(); styleCommonRight.Alignment = HorizontalAlignment.Right; styleCommonRight.SetFont(fontCommon); ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.Alignment = HorizontalAlignment.Left; XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy/mm/dd"); dateStyle.SetFont(fontCommon); #endregion #region 取得列宽 int[] arrColWidth = new int[itemSheet.Source.Columns.Count]; foreach (DataColumn item in itemSheet.Source.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } #endregion #region 填充内容 int rowIndex = 1; foreach (DataRow row in itemSheet.Source.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in itemSheet.Source.Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); newCell.CellStyle = styleCommonLeft; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; if (drValue.Trim() != "") { DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 } break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } rowIndex++; } #endregion #region 合并单元格 if (itemSheet.IsMergeCell) { // 合并索引有大于等于 if (itemSheet.MergeIndex.Where(r => r > itemSheet.Source.Columns.Count - 1).Count() > 0) { throw new Exception(string.Format(@"第{0}项合并索引大于当前数据源最大列数", i)); } for (int c = 0; c < itemSheet.MergeIndex.Count; c++) { for (int m = 1; m < itemSheet.Source.Rows.Count + 1; m++) { string value = sheet.GetRow(m).GetCell(itemSheet.MergeIndex[c]).StringCellValue; int end = m; for (int n = m + 1; n < itemSheet.Source.Rows.Count + 1; n++) { string value1 = sheet.GetRow(n).GetCell(itemSheet.MergeIndex[c]).StringCellValue; if (value != value1) { end = n - 1; break; } else if (value == value1 && n == itemSheet.Source.Rows.Count) { end = n; break; } } sheet.AddMergedRegion(new CellRangeAddress(m, end, itemSheet.MergeIndex[c], itemSheet.MergeIndex[c])); m = end; } } } #endregion } //第三步：将workbook 保存到本地路径 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; using (FileStream fs1 = new FileStream(excelPath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs1.Write(data, 0, data.Length); fs1.Flush(); } } } } /// <summary> /// 将数据源填充到模板，一个模板生成多个sheet页 /// </summary> /// <param name="templatePath"></param> /// <param name="savePath"></param> /// <param name="sheets"></param> public static void FillTemplate(string templatePath, string savePath, List<MultiSheet> multiSheets) { using (FileStream fs = File.Open(templatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook workbook = new XSSFWorkbook(fs); XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet; // 所有 sheet 页 for (var i = 0; i < multiSheets.Count; i++) { if (i == 0) { workbook.SetSheetName(workbook.GetSheetIndex(sheet), string.IsNullOrEmpty(multiSheets[i].SheetName) ? $"Sheet{i}" : multiSheets[i].SheetName); } else { sheet.CopySheet(string.IsNullOrEmpty(multiSheets[i].SheetName) ? $"Sheet{i}" : multiSheets[i].SheetName); } } // 填充数据 for (var i=0;i<multiSheets.Count;i++) { var multiSheet = multiSheets[i]; XSSFSheet worksheet = workbook.GetSheetAt(i) as XSSFSheet; //动态显示年月 IRow headerRow = sheet.GetRow(0); if (multiSheet.IsSpecial) { string dataStr = ""; if (DateTime.Today.ToString("MM") == "01") { dataStr = "入职天数（by January " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "02") { dataStr = "入职天数（by February " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "03") { dataStr = "入职天数（by March " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "04") { dataStr = "入职天数（by April " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "05") { dataStr = "入职天数（by May " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "06") { dataStr = "入职天数（by June " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "07") { dataStr = "入职天数（by July " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "08") { dataStr = "入职天数（by August " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "09") { dataStr = "入职天数（by September " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "10") { dataStr = "入职天数（by October " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "11") { dataStr = "入职天数（by November " + DateTime.Today.ToString("dd") + ")"; } if (DateTime.Today.ToString("MM") == "12") { dataStr = "入职天数（by December " + DateTime.Today.ToString("dd") + ")"; } headerRow.GetCell(6).SetCellValue(dataStr); } int[] arrColWidth = new int[multiSheet.Source.Columns.Count]; foreach (DataColumn item in multiSheet.Source.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } int rowIndex = 1; foreach (DataRow row in multiSheet.Source.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in multiSheet.Source.Columns) { XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; if (drValue.Trim() != "") { DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); } break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } rowIndex++; } } // 写到本地 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; using (FileStream fs1 = new FileStream(savePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs1.Write(data, 0, data.Length); fs1.Flush(); } } } } } /// <summary> /// 多个sheet 页填充扩展类 /// </summary> public class MultiSheet { /// <summary> /// sheet 页的名称 /// </summary> public string SheetName { get; set; } /// <summary> /// 每个sheet 页的 数据源 /// </summary> public DataTable Source { get; set; } /// <summary> /// 该数据源是否合并单元格 /// </summary> public bool IsMergeCell { get; set; } = false; /// <summary> /// 合并列的索引集合 /// </summary> public List<int> MergeIndex { get; set; } /// <summary> /// /// </summary> public bool IsSpecial { get; set; } }
