/// <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);
}
