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