/// <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>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>
/// 执行查询
/// </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>
/// 利用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);
}
