/// <summary>
/// 2020.06.15增加Dapper事务处理
/// <param name="action"></param>
/// <param name="error"></param>
public void BeginTransaction(Func <ISqlDapper, bool> action, Action <Exception> error)
{
_transaction = true;
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
_transactionConnection = connection;
_transactionConnection.Open();
dbTransaction = _transactionConnection.BeginTransaction();
bool result = action(this);
if (result)
{
dbTransaction?.Commit();
}
else
{
dbTransaction?.Rollback();
}
}
catch (Exception ex)
{
dbTransaction?.Rollback();
error(ex);
}
finally
{
_transaction = false;
dbTransaction?.Dispose();
}
}
}
// private static bool _isMysql = false;
public static IServiceCollection AddModule(this IServiceCollection services, ContainerBuilder builder, IConfiguration configuration)
{
//services.AddSession();
//services.AddMemoryCache();
//初始化配置文件
AppSetting.Init(services, configuration);
Type baseType = typeof(IDependency);
var compilationLibrary = DependencyContext.Default
.CompileLibraries
.Where(x => !x.Serviceable &&
x.Type == "project")
.ToList();
var count1 = compilationLibrary.Count;
List <Assembly> assemblyList = new List <Assembly>();
foreach (var _compilation in compilationLibrary)
{
try
{
assemblyList.Add(AssemblyLoadContext.Default.LoadFromAssemblyName(new AssemblyName(_compilation.Name)));
}
catch (Exception ex)
{
Console.WriteLine(_compilation.Name + ex.Message);
}
}
builder.RegisterAssemblyTypes(assemblyList.ToArray())
.Where(type => baseType.IsAssignableFrom(type) && !type.IsAbstract)
.AsSelf().AsImplementedInterfaces()
.InstancePerLifetimeScope();
builder.RegisterType <UserContext>().InstancePerLifetimeScope();
builder.RegisterType <ActionObserver>().InstancePerLifetimeScope();
//model校验结果
builder.RegisterType <ObjectModelValidatorState>().InstancePerLifetimeScope();
string connectionString = DBServerProvider.GetConnectionString(null);
if (DBType.Name == DbCurrentType.MySql.ToString())
{
//services.AddDbContext<VOLContext>();
//mysql8.x的版本使用Pomelo.EntityFrameworkCore.MySql 3.1会产生异常，需要在字符串连接上添加allowPublicKeyRetrieval=true
services.AddDbContextPool <VOLContext>(optionsBuilder => { optionsBuilder.UseMySql(connectionString); }, 64);
}
else
{
services.AddDbContextPool <VOLContext>(optionsBuilder => { optionsBuilder.UseSqlServer(connectionString); }, 64);
}
//启用缓存
if (AppSetting.UseRedis)
{
builder.RegisterType <RedisCacheService>().As <ICacheService>().SingleInstance();
}
else
{
builder.RegisterType <MemoryCacheService>().As <ICacheService>().SingleInstance();
}
return(services);
}
public static IServiceProvider AddModule(this IServiceCollection services, IConfiguration configuration)
{
services.AddSession();
services.AddMemoryCache();
//初始化配置文件
AppSetting.Init(services, configuration);
ContainerBuilder builder = new ContainerBuilder();
//批量注入autofac
Type baseType = typeof(IDependency);
var compilationLibrary = DependencyContext.Default.CompileLibraries.Where(x => !x.Serviceable && x.Type != "package");
List <Assembly> assemblyList = new List <Assembly>();
foreach (var _compilation in compilationLibrary)
{
assemblyList.Add(AssemblyLoadContext.Default.LoadFromAssemblyName(new AssemblyName(_compilation.Name)));
}
builder.RegisterAssemblyTypes(assemblyList.ToArray())
.Where(type => baseType.IsAssignableFrom(type) && !type.IsAbstract)
.AsSelf().AsImplementedInterfaces() //.PropertiesAutowired()//属性注入
.InstancePerLifetimeScope();
//注入用管理类,将用户信息写入上下文管理，减少redis或memory使用，提高效率
services.AddScoped(typeof(UserContext));
services.AddScoped(typeof(Services.ActionObserver));
//注入EF
_isMysql = DBType.Name == DbCurrentType.MySql.ToString();
void dbContextOptions(DbContextOptionsBuilder optionsBuilder)
{
string connectionString = DBServerProvider.GetConnectionString(null);
if (_isMysql)
{
optionsBuilder.UseMySql(connectionString);
}
else
{
optionsBuilder.UseSqlServer(connectionString);
}
}
services.AddDbContext <VOLContext>(dbContextOptions);
//启用缓存
if (AppSetting.UseRedis)
{
services.AddSingleton(typeof(ICacheService), typeof(RedisCacheService));
}
else
{
services.AddSingleton <ICacheService, MemoryCacheService>();
}
builder.Populate(services);
return(new AutofacServiceProvider(builder.Build()));
}
public ServerDataManager(ServerConnectVariable serverConnect, DBServerProvider dbServerProvider)
{
_connectionName = serverConnect.ConnectionName;
_hostName = serverConnect.HostNameOrIP;
_userName = serverConnect.ServerUserName;
_password = serverConnect.UserPassword;
_databaseName = serverConnect.DatabaseName;
_tableName = serverConnect.TableName;
// _dbServerProvider = new DBServerProvider();
_dbServerProvider = dbServerProvider;
}
/// <summary>
/// 数据库连接,根据数据库类型自动识别，类型区分用配置名称是否包含主要关键字
/// MSSQL、MYSQL、ORACLE、SQLITE、MEMORY、NPGSQL
/// </summary>
/// <returns></returns>
public DbConnection OpenSharedConnection()
{
YuebonCacheHelper yuebonCacheHelper = new YuebonCacheHelper();
object connCode = yuebonCacheHelper.Get("CodeGeneratorDbConn");
DbConnectionOptions dbConnectionOptions = DBServerProvider.GeDbConnectionOptions();
DatabaseType dbType = DatabaseType.SqlServer;
if (connCode != null)
{
defaultSqlConnectionString = connCode.ToString();
string dbTypeCache = yuebonCacheHelper.Get("CodeGeneratorDbType").ToString();
dbType = (DatabaseType)Enum.Parse(typeof(DatabaseType), dbTypeCache);
}
else
{
defaultSqlConnectionString = dbConnectionOptions.ConnectionString;
dbType = dbConnectionOptions.DatabaseType;
TimeSpan expiresSliding = DateTime.Now.AddMinutes(30) - DateTime.Now;
yuebonCacheHelper.Add("CodeGeneratorDbConn", defaultSqlConnectionString, expiresSliding, false);
yuebonCacheHelper.Add("CodeGeneratorDbType", dbType, expiresSliding, false);
}
if (dbType == DatabaseType.SqlServer)
{
dbConnection = new SqlConnection(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.MySql)
{
dbConnection = new MySqlConnection(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.Oracle)
{
dbConnection = new OracleConnection(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.SQLite)
{
dbConnection = new SqliteConnection(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.Npgsql)
{
dbConnection = new NpgsqlConnection(defaultSqlConnectionString);
}
else
{
throw new NotSupportedException("The database is not supported");
}
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
return(dbConnection);
}
/// <summary>
/// 获取的数据库连接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="masterDb"></param>
/// <returns></returns>
public IDbConnection GetConnection <T>(bool masterDb = true) where T : class
{
if (dbConnection == null || dbConnection.State == ConnectionState.Closed)
{
dbConnection = DBServerProvider.GetDBConnection <T>(masterDb);
//if (MiniProfiler.Current != null)
//{
// dbConnection = new StackExchange.Profiling.Data.ProfiledDbConnection((DbConnection)dbConnection, MiniProfiler.Current);
//}
}
return(dbConnection);
}
private T Execute <T>(Func <IDbConnection, IDbTransaction, T> func, bool beginTransaction = false)
{
if (_transaction)
{
return(func(_transactionConnection, dbTransaction));
}
if (beginTransaction)
{
return(ExecuteTransaction(func));
}
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
return(func(connection, dbTransaction));
}
}
/// <summary>
///大批量数据插入,返回成功插入行数
////
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="table">数据表</param>
/// <returns>返回成功插入行数</returns>
private int MySqlBulkInsert(DataTable table, string tableName, string fileName = null, string tmpPath = null)
{
if (table.Rows.Count == 0)
{
return(0);
}
tmpPath = tmpPath ?? FileHelper.GetCurrentDownLoadPath();
int insertCount = 0;
string csv = DataTableToCsv(table);
string text = $"当前行:{table.Rows.Count}";
MemoryStream stream = null;
try
{
using (var Connection = DBServerProvider.GetDbConnection(_connectionString))
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
using (IDbTransaction tran = Connection.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(Connection as MySqlConnection)
{
LineTerminator = "\n",
TableName = tableName,
CharacterSet = "UTF8"
};
var array = Encoding.UTF8.GetBytes(csv);
using (stream = new MemoryStream(array))
{
stream = new MemoryStream(array);
bulk.SourceStream = stream; //File.OpenRead(fileName);
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
insertCount = bulk.Load();
tran.Commit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(insertCount);
// File.Delete(path);
}
/// <summary>
/// 通过Bulk批量插入
/// </summary>
/// <param name="table"></param>
/// <param name="tableName"></param>
/// <param name="sqlBulkCopyOptions"></param>
/// <param name="dbKeyName"></param>
/// <returns></returns>
private int MSSqlBulkInsert(DataTable table, string tableName, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.UseInternalTransaction, string dbKeyName = null)
{
if (!string.IsNullOrEmpty(dbKeyName))
{
Connection.ConnectionString = DBServerProvider.GetConnectionString(dbKeyName);
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Connection.ConnectionString, sqlBulkCopyOptions))
{
sqlBulkCopy.DestinationTableName = tableName;
sqlBulkCopy.BatchSize = table.Rows.Count;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(table);
return(table.Rows.Count);
}
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
string connectionString = DBServerProvider.GetConnectionString(null);
if (Const.DBType.Name == Enums.DbCurrentType.MySql.ToString())
{
optionsBuilder.UseMySql(connectionString);
}
else
{
optionsBuilder.UseSqlServer(connectionString);
}
//默认禁用实体跟踪
optionsBuilder = optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
//var loggerFactory = new LoggerFactory();
//loggerFactory.AddProvider(new EFLoggerProvider());
// optionsBuilder.UseLoggerFactory(loggerFactory);
base.OnConfiguring(optionsBuilder);
}
private T Execute <T>(Func <IDbConnection, IDbTransaction, T> func, bool beginTransaction = false)
{
if (_transaction)
{
return(func(_transactionConnection, dbTransaction));
}
if (beginTransaction)
{
return(ExecuteTransaction(func));
}
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
T reslutT = func(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
}
/// <summary>
/// 创建数据库读写上下文
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="writeAndRead">指定读、写操作</param>
/// <returns></returns>
public BaseDbContext CreateContext <TEntity>(WriteAndReadEnum writeAndRead)
{
DbConnectionOptions dbConnectionOptions = new DbConnectionOptions();
switch (writeAndRead)
{
case WriteAndReadEnum.Write:
dbConnectionOptions = DBServerProvider.GeDbConnectionOptions <TEntity>(true);
break;
case WriteAndReadEnum.Read:
dbConnectionOptions = DBServerProvider.GeDbConnectionOptions <TEntity>(false);
break;
default:
dbConnectionOptions = DBServerProvider.GeDbConnectionOptions <TEntity>(true);
break;
}
return(new BaseDbContext(dbConnectionOptions));
}
/// <summary>
///
/// </summary>
/// <param name="tableName">发送表名对应的提示模块</param>
/// <param name="audit">默认为发送通知消息给管理员审核,6,7为审核通过/未通过</param>
/// <param name="phoneNo">是否指定电话发送消息</param>
/// <returns></returns>
public static (bool, string) SendAliAuditSTK(string tableName, AuditType audit = AuditType.Notice, string phoneNo = null)
{
//获取所有审核权限的人发送APP消息通知
string msg = null;
List <string> list = new List <string>();
Enum.TryParse(tableName, out AuditType auditType);
switch (auditType)
{
case AuditType.App_Question:
msg = "提问";
break;
case AuditType.App_Expert:
msg = "专家申请认证";
break;
case AuditType.App_ReportPrice:
msg = "上报肉牛价格";
break;
}
if (string.IsNullOrEmpty(phoneNo))
{
list = DBServerProvider.GetSqlDapper().QueryList <string>($"SELECT PhoneNo FROM [vAuditUser] WHERE TableName='{tableName}'", null).Where(x => x.IsPhoneNo()).ToList();
if (list.Count == 0)
{
return(false, "没有分配审核帐号");
}
}
else
{
list.Add(phoneNo);
}
return(SendAliSTK(new Dictionary <string, object>()
{
{ "PhoneNo", string.Join(',', list) },
{ "PINType", (int)audit }, //模板类型5,审核给管理员通知,6提示审核通过，7，审核失败
{ "Msg", msg }
}));
}
private async Task <T> ExecuteAsync <T>(Func <IDbConnection, IDbTransaction, Task <T> > funcAsync, bool beginTransaction = false)
{
if (_transaction)
{
return(await funcAsync(_transactionConnection, dbTransaction));
}
if (beginTransaction)
{
return(await ExecuteTransactionAsync(funcAsync));
}
using (var connection = new SqlConnection(DBServerProvider.GetConnectionString(_connectionString)))
{
T reslutT = await funcAsync(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
}
/// <summary>
/// 配置，初始化数据库引擎
/// </summary>
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (dbConnectionOptions == null)
{
dbConnectionOptions = DBServerProvider.GeDbConnectionOptions();
}
string defaultSqlConnectionString = dbConnectionOptions.ConnectionString;
DatabaseType dbType = dbConnectionOptions.DatabaseType;
if (dbType == DatabaseType.SqlServer)
{
optionsBuilder.UseSqlServer(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.MySql)
{
optionsBuilder.UseMySql(defaultSqlConnectionString, new MySqlServerVersion(new Version(8, 0, 21)),
mySqlOptions => mySqlOptions
.CharSetBehavior(CharSetBehavior.NeverAppend));
}
else if (dbType == DatabaseType.Oracle)
{
optionsBuilder.UseOracle(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.SQLite)
{
optionsBuilder.UseSqlite(defaultSqlConnectionString);
}
else if (dbType == DatabaseType.Npgsql)
{
optionsBuilder.UseNpgsql(defaultSqlConnectionString);
}
else
{
throw new NotSupportedException("The database is not supported");
}
//使用查询跟踪行为
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
base.OnConfiguring(optionsBuilder);
}
private T ExecuteTransaction <T>(Func <IDbConnection, IDbTransaction, T> func)
{
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
connection.Open();
dbTransaction = connection.BeginTransaction();
T reslutT = func(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
catch (Exception ex)
{
dbTransaction?.Rollback();
throw ex;
}
}
}
private T ExecuteTransaction <T>(Func <IDbConnection, IDbTransaction, T> func)
{
using (_transactionConnection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
_transactionConnection.Open();
dbTransaction = _transactionConnection.BeginTransaction();
T reslutT = func(_transactionConnection, dbTransaction);
dbTransaction.Commit();
return(reslutT);
}
catch (Exception ex)
{
dbTransaction?.Rollback();
throw ex;
}
finally
{
dbTransaction?.Dispose();
}
}
}
private async Task <T> ExecuteTransactionAsync <T>(Func <IDbConnection, IDbTransaction, Task <T> > funcAsync)
{
using (var connection = new SqlConnection(DBServerProvider.GetConnectionString(_connectionString)))
{
try
{
connection.Open();
dbTransaction = connection.BeginTransaction();
T reslutT = await funcAsync(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
catch (Exception ex)
{
dbTransaction?.Rollback();
throw ex;
}
}
}
public async Task <IViewComponentResult> InvokeAsync(string dropDownIds)
{
if (string.IsNullOrEmpty(dropDownIds))
{
return(null);
}
string[] dicNos = dropDownIds.Split(',');
StringBuilder stringBuilder = new StringBuilder();
VOLContext context = DBServerProvider.GetEFDbContext();
var dicData = await(from d in context.Set <Sys_Dictionary>()
join list in context.Set <Sys_DictionaryList>()
on d.Dic_ID equals list.Dic_ID
into t
from list in t.DefaultIfEmpty()
where dicNos.Contains(d.DicNo)
select new { list.DicValue, list.DicName, d.Config, d.DbSql, list.OrderNo, d.DicNo }).ToListAsync();
foreach (var item in dicData.GroupBy(x => x.DicNo))
{
stringBuilder.AppendLine($" var optionConfig{item.Key} = {item.Select(x => x.Config).FirstOrDefault()}");
string dbSql = item.Select(s => s.DbSql).FirstOrDefault();
stringBuilder.AppendLine($@" var dataSource{item.Key} = {
(!string.IsNullOrEmpty(dbSql)
? DBServerProvider.GetSqlDapper().QueryList<object>(dbSql, null).Serialize()
: item.OrderByDescending(o => o.OrderNo).
Select(s => new { s.DicName, s.DicValue }).ToList()
.Serialize())
}.convertToValueText(optionConfig{item.Key})");
stringBuilder.AppendLine($" optionConfig{item.Key}.data = dataSource{item.Key};");
}
ViewBag.Dic = stringBuilder.ToString();
return(View("~/Views/Shared/Dictionary.cshtml"));
}
/// <summary>
/// string mySql = "Data Source=132.232.2.109;Database=mysql;User
/// ID=root;Password=mysql;pooling=true;CharSet=utf8;port=3306;sslmode=none";
/// this.conn = new MySql.Data.MySqlClient.MySqlConnection(mySql);
/// </summary>
/// <param name="connKeyName"></param>
public SqlDapper(string connKeyName)
{
_connectionString = DBServerProvider.GetConnectionString(connKeyName);
}
public SqlDapper()
{
_connectionString = DBServerProvider.GetConnectionString();
}
public SqlDapper(string connKeyName, DbCurrentType dbCurrentType)
{
_dbCurrentType = dbCurrentType;
_connectionString = DBServerProvider.GetConnectionString(connKeyName);
}
public ActionResult GetDatabaseList(string hostNameOrIP, string dataBaseUser, string userPassword, DBServerProvider databaseProvider)
{
List <Database> dbNameList = new List <Database>();
string message = "";
try
{
IServerConnect _sqlServerInterface = new SqlServerConnect(hostNameOrIP, dataBaseUser, userPassword, null, null);
dbNameList = _sqlServerInterface.GetDatabaseList();
message = Message.Message_OK;
}
catch (Exception ex)
{
message = ex.Message;
}
return(Json(new { dbNameList, message }, JsonRequestBehavior.AllowGet));
}
public ActionResult SaveDatabase(string connectionName, string hostNameOrIp, string serverUserName, string userPassword, string databaseName, string tableName, DBServerProvider databaseProvider) //FormCollection form
{
string message = Message.Message_OK;
ServerConnectVariable _serverConnectVar = new ServerConnectVariable();
_serverConnectVar.ConnectionName = connectionName;
_serverConnectVar.HostNameOrIP = hostNameOrIp;
_serverConnectVar.ServerUserName = serverUserName;
_serverConnectVar.UserPassword = userPassword;
_serverConnectVar.DatabaseName = databaseName;
_serverConnectVar.TableName = tableName;
_serverConnectVar.DatabaseProvider = databaseProvider.ToString();
//_serverConnectVar.ConnectionName = form["ConnectionName"].ToString();
//_serverConnectVar.HostNameOrIP = form["HostNameOrIP"].ToString();
//_serverConnectVar.DataBaseUser = form["DataBaseUser"].ToString();
//_serverConnectVar.UserPassword = form["UserPassword"].ToString();
try
{
IServerConnect _sqlServerInterface = new SqlServerConnect(
_serverConnectVar.HostNameOrIP,
_serverConnectVar.ServerUserName,
_serverConnectVar.UserPassword,
_serverConnectVar.DatabaseName,
_serverConnectVar.TableName
);
List <Field> getFiledList = _sqlServerInterface.GetSingleTableFieldList();
EM_ServerConnect obj = new EM_ServerConnect(
_serverConnectVar.ConnectionName,
_serverConnectVar.HostNameOrIP,
_serverConnectVar.ServerUserName,
_serverConnectVar.UserPassword,
_serverConnectVar.DatabaseName,
_serverConnectVar.TableName,
_serverConnectVar.DatabaseProvider);
IRepository <EM_ServerConnect, int> newRepo = new Repository <EM_ServerConnect, SqlDbContext, int>(new SqlDbContext());
var result = newRepo.Create(obj);
//db.EM_ServerConnect.Add(obj);
//db.SaveChanges();
message = Message.Message_OK;
return(Json(new { TableFieldList = getFiledList, ServerInfo = _serverConnectVar, message }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
message = ex.Message;
}
return(Json(new { ServerInfo = _serverConnectVar, message }, JsonRequestBehavior.AllowGet));
}
