/// <summary> /// 保存接口返回二进制流为文件方法 /// </summary> /// <param name="requestUri">接口地址</param> /// <param name="jsonString">json数据对象</param> /// <returns></returns> public static string DownloadBufferImage(string requestUri, string jsonString) { var result = string.Empty; try { HttpContent httpContent = new StringContent(jsonString); httpContent.Headers.ContentType = new MediaTypeHeaderValue("application/json"); //注意大量并发的时候不能释放资源的问题 using (var httpClient = new HttpClient()) { //停止使用using,使用HttpClientFactory //var httpClient = HttpClientFactory2.GetHttpClient(); httpClient.PostAsync(requestUri, httpContent).ContinueWith( (requestTask) => { var response = requestTask.Result; response.EnsureSuccessStatusCode(); var contentType = response.Content.Headers.ContentType; if (string.IsNullOrEmpty(contentType.CharSet)) { contentType.CharSet = "utf-8"; } LogUtil.WriteLog(response.Content.ReadAsByteArrayAsync().Result.LongLength.ToString(), "QRCode"); var data = response.Content.ReadAsByteArrayAsync().Result; var fileName = string.Empty; //会出现113个字节的内容,所以放大十倍 if (data.Length > 1130) { fileName = Guid.NewGuid().ToString("D") + ".jpg"; var filePath = string.Format(@"{0}\QRCode\" + fileName, AppDomain.CurrentDomain.BaseDirectory); var folder = Path.GetDirectoryName(filePath); if (!Directory.Exists(folder)) { Directory.CreateDirectory(folder); } using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); } result = "/QRCode/" + fileName; } else { LogUtil.WriteLog(Encoding.Default.GetString(data), "QRCode"); } }).Wait(5000); } } catch (Exception ex) { LogUtil.WriteException(ex); } return(result); }
/// <summary> /// 填充数据权限 /// </summary> /// <param name="dataSet">数据权限</param> /// <param name="commandType">命令分类</param> /// <param name="commandText">sql查询</param> /// <param name="tableName">填充表</param> /// <param name="dbParameters">参数集</param> /// <returns>数据权限</returns> public virtual DataSet Fill(DataSet dataSet, string commandText, string tableName, IDbDataParameter[] dbParameters, CommandType commandType) { var stopwatch = new Stopwatch(); stopwatch.Start(); // 自动打开 MustCloseConnection = true; if (DbConnection == null) { Open(); } if (DbConnection.State == ConnectionState.Closed) { DbConnection.Open(); } else if (DbConnection.State == ConnectionState.Broken) { DbConnection.Close(); DbConnection.Open(); } using (DbCommand = DbConnection.CreateCommand()) { try { #if (DEBUG) Trace.WriteLine(DateTime.Now + " :DbConnection Start: " + DbConnection.Database + " ,ThreadId: " + Thread.CurrentThread.ManagedThreadId); #endif //dbCommand.Parameters.Clear(); //if ((dbParameters != null) && (dbParameters.Length > 0)) //{ // for (int i = 0; i < dbParameters.Length; i++) // { // if (dbParameters[i] != null) // { // dbDataAdapter.SelectCommand.Parameters.Add(dbParameters[i]); // } // } //} DbCommand.Connection = DbConnection; DbCommand.CommandTimeout = DbConnection.ConnectionTimeout; DbCommand.CommandText = commandText; if (CurrentDbType == CurrentDbType.Oracle) { // 针对Oracle,全局替换换行符,避免报错或不执行 // 仅当前系统的换行符 DbCommand.CommandText = commandText.Replace(Environment.NewLine, " "); // 各种平台的换行符 //_dbCommand.CommandText = commandText.Replace("r\n", " ").Replace('\n', ' ').Replace('\r', ' '); } DbCommand.CommandType = commandType; if (_dbTransaction != null) { DbCommand.Transaction = _dbTransaction; } DbCommand.Parameters.Clear(); if ((dbParameters != null) && (dbParameters.Length > 0)) { for (var i = 0; i < dbParameters.Length; i++) { if (dbParameters[i] != null) { DbCommand.Parameters.Add(((ICloneable)dbParameters[i]).Clone()); } } // dbCommand.Parameters.AddRange(dbParameters); } //记录日志 SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters); DbDataAdapter = GetInstance().CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; if (DbConnection.State != ConnectionState.Open) { DbConnection.Open(); } DbDataAdapter.Fill(dataSet, tableName); SetBackParamValue(dbParameters); } catch (Exception e) { //Troy.Cui 2020.05.13 dataSet = null; //记录异常 var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(tableName); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } LogUtil.WriteException(e, sb.Put()); } finally { if (MustCloseConnection) { Close(); } else { DbDataAdapter?.SelectCommand.Parameters.Clear(); } } stopwatch.Stop(); var statisticsText = $"Elapsed time: {stopwatch.Elapsed.TotalMilliseconds}ms"; SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters, statisticsText); if (stopwatch.Elapsed.TotalMilliseconds >= BaseSystemInfo.SlowQueryMilliseconds) { var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(tableName); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } else { sb.Append(" "); } sb.Append(statisticsText); LogUtil.WriteLog(sb.Put(), "Slow.DbHelper.Fill"); } } return(dataSet); }
/// <summary> /// 执行查询 /// </summary> /// <param name="commandText">sql查询</param> /// <param name="dbParameters">参数集</param> /// <param name="commandType">命令分类</param> /// <returns>结果集流</returns> public virtual IDataReader ExecuteReader(string commandText, IDbDataParameter[] dbParameters, CommandType commandType) { var stopwatch = new Stopwatch(); stopwatch.Start(); //自动打开 MustCloseConnection = false; if (DbConnection == null) { Open(); } DbDataReader dbDataReader = null; if (DbConnection.State == ConnectionState.Closed) { DbConnection.Open(); } else if (DbConnection.State == ConnectionState.Broken) { DbConnection.Close(); DbConnection.Open(); } using (DbCommand = DbConnection.CreateCommand()) { try { #if (DEBUG) Trace.WriteLine(DateTime.Now + " :DbConnection Start: " + DbConnection.Database + " ,ThreadId: " + Thread.CurrentThread.ManagedThreadId); #endif DbCommand.Connection = DbConnection; DbCommand.CommandTimeout = DbConnection.ConnectionTimeout; DbCommand.CommandText = commandText; if (CurrentDbType == CurrentDbType.Oracle) { // 针对Oracle,全局替换换行符,避免报错或不执行 // 仅当前系统的换行符 DbCommand.CommandText = commandText.Replace(Environment.NewLine, " "); // 各种平台的换行符 //_dbCommand.CommandText = commandText.Replace("r\n", " ").Replace('\n', ' ').Replace('\r', ' '); } DbCommand.CommandType = commandType; if (_dbTransaction != null) { DbCommand.Transaction = _dbTransaction; } if (dbParameters != null) { DbCommand.Parameters.Clear(); foreach (var t in dbParameters) { if (t != null) { DbCommand.Parameters.Add(((ICloneable)t).Clone()); } } } // 写入日志 SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters); if (DbConnection.State != ConnectionState.Open) { DbConnection.Open(); } dbDataReader = DbCommand.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception e) { var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } LogUtil.WriteException(e, sb.Put()); } finally { //因为使用了CommandBehavior.CloseConnection,不需要关闭连接 //一定不要自动关闭连接,但需要在dataReader.Read()之后手动执行dataReader.Close() //if (MustCloseConnection) //{ // Close(); //} if (DbCommand != null) { DbCommand.Parameters.Clear(); } } stopwatch.Stop(); var statisticsText = $"Elapsed time: {stopwatch.Elapsed.TotalMilliseconds}ms"; SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters, statisticsText); if (stopwatch.Elapsed.TotalMilliseconds >= BaseSystemInfo.SlowQueryMilliseconds) { var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } else { sb.Append(" "); } sb.Append(statisticsText); LogUtil.WriteLog(sb.Put(), "Slow.DbHelper.ExecuteReader"); } } return(dbDataReader); }
/// <summary> /// 执行查询 /// </summary> /// <param name="commandText">sql查询</param> /// <param name="dbParameters">参数集</param> /// <param name="commandType">命令分类</param> /// <returns>Object</returns> public virtual object ExecuteScalar(string commandText, IDbDataParameter[] dbParameters, CommandType commandType) { var stopwatch = new Stopwatch(); stopwatch.Start(); // 自动打开 MustCloseConnection = true; if (DbConnection == null) { Open(); } object result = null; if (DbConnection.State == ConnectionState.Closed) { DbConnection.Open(); } else if (DbConnection.State == ConnectionState.Broken) { DbConnection.Close(); DbConnection.Open(); } using (DbCommand = DbConnection.CreateCommand()) { try { #if (DEBUG) Trace.WriteLine(DateTime.Now + " :DbConnection Start: " + DbConnection.Database + " ,ThreadId: " + Thread.CurrentThread.ManagedThreadId); #endif DbCommand.Connection = DbConnection; DbCommand.CommandTimeout = DbConnection.ConnectionTimeout; DbCommand.CommandText = commandText; if (CurrentDbType == CurrentDbType.Oracle) { // 针对Oracle,全局替换换行符,避免报错或不执行 // 仅当前系统的换行符 DbCommand.CommandText = commandText.Replace(Environment.NewLine, " "); // 各种平台的换行符 //_dbCommand.CommandText = commandText.Replace("r\n", " ").Replace('\n', ' ').Replace('\r', ' '); } DbCommand.CommandType = commandType; if (_dbTransaction != null) { DbCommand.Transaction = _dbTransaction; } if (dbParameters != null) { DbCommand.Parameters.Clear(); for (var i = 0; i < dbParameters.Length; i++) { if (dbParameters[i] != null) { DbCommand.Parameters.Add(((ICloneable)dbParameters[i]).Clone()); // dbCommand.Parameters.Add(dbParameters[i]); } } } //写入日志 SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters); if (DbConnection.State != ConnectionState.Open) { DbConnection.Open(); } result = DbCommand.ExecuteScalar(); // 这里进行输出参数的处理 if (CurrentDbType == CurrentDbType.SqlServer) { SetBackParamValue(dbParameters); } } catch (Exception e) { var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } LogUtil.WriteException(e, sb.Put()); } finally { //自动关闭 if (MustCloseConnection) { Close(); } else { DbCommand.Parameters.Clear(); } } stopwatch.Stop(); var statisticsText = $"Elapsed time: {stopwatch.Elapsed.TotalMilliseconds}ms"; SqlUtil.WriteLog(commandText, commandType.ToString(), dbParameters, statisticsText); if (stopwatch.Elapsed.TotalMilliseconds >= BaseSystemInfo.SlowQueryMilliseconds) { var sb = Pool.StringBuilder.Get(); sb.Append(commandText); sb.Append(" "); sb.Append(commandType.ToString()); if (dbParameters != null) { sb.Append(" dbParameters: "); foreach (var parameter in dbParameters) { sb.Append(parameter.ParameterName + "=" + parameter.Value + " "); } } else { sb.Append(" "); } sb.Append(statisticsText); LogUtil.WriteLog(sb.Put(), "Slow.DbHelper.ExecuteScalar"); } } return(result); }
/// <summary> /// 利用Net SqlBulkCopy 批量导入数据库,速度超快 /// </summary> /// <param name="dt">源内存数据表(先通过SELECT TOP 0获取空白DataTable)</param> /// <param name="destinationTableName">目标表名称</param> /// <param name="bulkCopyTimeout">超时限制(毫秒)</param> /// <param name="batchSize">批大小(默认0,即一次性导入)</param> public override bool SqlBulkCopyData(DataTable dt, string destinationTableName, int bulkCopyTimeout = 1000, int batchSize = 0) { var result = false; var stopwatch = new Stopwatch(); stopwatch.Start(); if (string.IsNullOrEmpty(destinationTableName)) { destinationTableName = dt.TableName; } // SQL 数据连接 SqlConnection sqlConnection = null; // 打开数据库 Open(); // 获取连接 sqlConnection = (SqlConnection)GetDbConnection(); using (var tran = sqlConnection.BeginTransaction()) { // 批量保存数据,只能用于Sql var sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, tran); sqlBulkCopy.BatchSize = batchSize; // 设置目标表名称 sqlBulkCopy.DestinationTableName = destinationTableName; // 设置超时限制 sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout; foreach (DataColumn dtColumn in dt.Columns) { sqlBulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName); } try { // 写入 sqlBulkCopy.WriteToServer(dt); // 提交事务 tran.Commit(); result = true; } catch (Exception ex) { tran.Rollback(); sqlBulkCopy.Close(); LogUtil.WriteException(ex); } finally { sqlBulkCopy.Close(); Close(); } } stopwatch.Stop(); var statisticsText = $"Elapsed time: {stopwatch.Elapsed.TotalMilliseconds}ms"; SqlUtil.WriteLog(destinationTableName, "SqlBulkCopy", null, statisticsText); if (stopwatch.Elapsed.TotalMilliseconds >= BaseSystemInfo.SlowQueryMilliseconds) { var sb = Pool.StringBuilder.Get(); sb.Append("SqlBulkCopy to Table " + destinationTableName + " , " + statisticsText); LogUtil.WriteLog(sb.Put(), "Slow.DbHelper.SqlBulkCopy"); } return(result); }
/// <summary> /// 读取CSV文件内容并转为DataTable /// </summary> /// <param name="fileName">完整路径文件名</param> /// <param name="separator">分隔符,默认为标准的英文,</param> /// <param name="firstLineIsHeader">第一行是否为表头,默认为否</param> /// <param name="encoding">编码类型</param> /// <param name="fieldList">字段列表字典(csv字段(无表头用C1,C2,C3,..格式),DataTable字段)</param> /// <param name="fieldListOnly">仅按照字段列表字典导入</param> /// <returns>DataTable自定义列名或以C1-CN开头的列名</returns> public static DataTable ToDataTable(string fileName, string separator = ",", bool firstLineIsHeader = false, Encoding encoding = null, Dictionary <string, string> fieldList = null, bool fieldListOnly = false) { var dt = new DataTable(); var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); var sr = new StreamReader(fs, encoding ?? EncodingUtil.Detect(fs)); //记录每次读取的一行记录 var line = ""; //记录每行记录中的各字段内容 string[] arr; //标示列数 var headColumnCount = 0; var lineColumnCount = 0; //字段是否已经添加 var isColumnAdded = false; //字段和列的对应关系,仅从指定列导入时会用到 var dicFieldIndex = new Dictionary <string, int>(); //逐行读取CSV中的数据 while ((line = sr.ReadLine()) != null) { var spr = separator.ToCharArray(); arr = line.Split(spr); if (firstLineIsHeader) { firstLineIsHeader = false; isColumnAdded = true; headColumnCount = GetLength(arr, separator); #region 生成DataTable数据列 //根据指定列名创建 if (fieldList != null && fieldListOnly) { foreach (var field in fieldList) { var dc = new DataColumn(field.Value); dt.Columns.Add(dc); //映射CSV的字段列索引 for (int i = 0; i < headColumnCount; i++) { if (ConvertColumnName(ReadSpecialCharacter(arr, i, separator), fieldList: fieldList).Equals(field.Value, StringComparison.OrdinalIgnoreCase)) { dicFieldIndex.Add(field.Value, i); } } } } else { //根据第一行实际列数,进行匹配映射来创建 for (int i = 0; i < headColumnCount; i++) { var dc = new DataColumn(ConvertColumnName(ReadSpecialCharacter(arr, i, separator), fieldList: fieldList)); dt.Columns.Add(dc); } } #endregion } else { #region 生成DataTable数据列 if (!isColumnAdded) { isColumnAdded = true; headColumnCount = GetLength(arr, separator); //根据指定列名创建 if (fieldList != null && fieldListOnly) { var fieldListIndex = 0; foreach (var field in fieldList) { var dc = new DataColumn(field.Value); dt.Columns.Add(dc); //按照顺序映射列索引 dicFieldIndex.Add(field.Value, fieldListIndex); fieldListIndex++; } } else { //根据第一行实际列数,进行匹配映射来创建 for (int i = 0; i < headColumnCount; i++) { var dc = new DataColumn(ConvertColumnName("C" + (i + 1), fieldList: fieldList)); dt.Columns.Add(dc); } } } #endregion lineColumnCount = GetLength(arr, separator); // //生成指定列或自动创建列 #region 写数据行 if (fieldList != null && fieldListOnly) { if (lineColumnCount > 0) { var dr = dt.NewRow(); foreach (var d in dicFieldIndex) { for (int j = 0; j < lineColumnCount; j++) { if (j == d.Value) { dr[d.Key] = ReadSpecialCharacter(arr, j, separator); } } } dt.Rows.Add(dr); } } else { //此行的列数要跟表头的列数一致才认为有效 if (lineColumnCount == headColumnCount) { var dr = dt.NewRow(); for (int j = 0; j < lineColumnCount; j++) { dr[j] = ReadSpecialCharacter(arr, j, separator); } dt.Rows.Add(dr); } else { LogUtil.WriteLog("headColumnCount:" + headColumnCount + ",lineColumnCount:" + lineColumnCount + "line:" + line, "CsvUtil.InvalidLine"); } } #endregion } } sr.Close(); fs.Close(); return(dt); }