static void Main(string[] args)
{
var orm = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
//.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=10")
.UseAutoSyncStructure(true)
.UseConfigEntityFromDbFirst(true)
.Build();
var repos = orm.GetGuidRepository <Song22>();
var item = repos.Insert(new Song22());
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(item));
item.Title = "xxx";
repos.Update(item);
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(item));
Console.WriteLine(repos.UpdateDiy.Where(a => a.Id == item.Id).Set(a => a.Clicks + 1).ToSql());
repos.UpdateDiy.Where(a => a.Id == item.Id).Set(a => a.Clicks + 1).ExecuteAffrows();
item = repos.Find(item.Id);
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(item));
}
private IFreeSql CreateDb(FreeSql.DataType dbType, DbInfo currentDbOption)
{
var master = currentDbOption.ConnectionStrings?.FirstOrDefault(e => e.UseType == DbUseType.Master);
if (master == null)
{
throw new ArgumentNullException($"请设置租户 {Tenant.Code} 的主库连接字符串");
}
var slaveConnectionStrings = currentDbOption.ConnectionStrings?.Where(e => e.UseType == DbUseType.Slave).Select(e => e.ConnectionString).ToArray();
var freeSqlBuilder = new FreeSql.FreeSqlBuilder()
.UseConnectionString(dbType, master.ConnectionString);
if (slaveConnectionStrings?.Length > 0)
{
freeSqlBuilder = freeSqlBuilder.UseSlave(slaveConnectionStrings);
}
if (_env.IsDevelopment())
{
freeSqlBuilder = freeSqlBuilder.UseAutoSyncStructure(true); //自动同步实体结构【开发环境必备】
}
var fsql = freeSqlBuilder.Build();
fsql.Aop.ConfigEntityProperty += ConfigEntityProperty;
fsql.Aop.CurdBefore += CurdBefore;
fsql.Aop.AuditValue += AuditValue;
//fsql.Aop.SyncStructureAfter += SyncStructureAfter;
DataFilterAsync(fsql);
return(fsql);
}
public void SelectTest()
{
IFreeSql db = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Oracle, "user id=1user;password=123456;data source=//127.0.0.1:1521/XE;Pooling=true;Max Pool Size=1")
.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build();
var startTime = DateTime.Now;
var endTime = DateTime.Now;
var cou = db.Select <V_HospitalReport>()
.Where(a => a.ScheduledDttm.Date >= startTime.Date && a.ScheduledDttm.Date <= (endTime.AddDays(1)).Date)
.GroupBy(a =>
new
{
a.HospitalName,
a.Dep,
a.Instrna,
a.ConfirmDoctorName,
a.ScheduledDttm.Date
})
.Count();
}
public void TestLambdaParameterWhereIn()
{
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\TestLambdaParameterWhereIn.db")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseMonitorCommand(
cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
//, (cmd, traceLog) => Console.WriteLine(traceLog)
)
.Build())
{
string dwId = "123456";
string yhId = "654321";
var sql = fsql.Select <wygl_wygs_gzry_wyglqyModelTest1>()
.Where(a => a.dw_id == dwId &&
fsql.Select <wygl_wygs_gzry_wyglqyModel>()
.Where(b => b.yh_id == yhId).ToList(b => b.wyqy_id).Contains(a.wyqy_id)
);
var sql1 = sql.ToSql();
Assert.Equal(@"SELECT a.""dw_id"", a.""wyqy_id""
FROM ""wygl_wygs_gzry_wyglqyModelTest1"" a
WHERE (a.""dw_id"" = @exp_0 AND ((a.""wyqy_id"") in (SELECT b.""wyqy_id""
FROM ""wygl_wygs_gzry_wyglqyModel"" b
WHERE (b.""yh_id"" = @exp_1))))", sql1);
Assert.Equal(2, (sql as Select0Provider)._params.Count);
Assert.Equal("123456", (sql as Select0Provider)._params[0].Value);
Assert.Equal("654321", (sql as Select0Provider)._params[1].Value);
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure <CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
IFreeSql freeSql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, Configuration.GetConnectionString("meta"))
.Build();
services.AddSingleton(freeSql);
services.AddSingleton <IAdminService, AdminService>();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
services.AddIdentityServer()
.AddDeveloperSigningCredential()
.AddInMemoryApiResources(IdentityConfig.GetApiResources())
.AddInMemoryIdentityResources(IdentityConfig.GetIdentityResources())
.AddInMemoryClients(IdentityConfig.GetClients());
//services.AddCors(options =>
//{
// // this defines a CORS policy called "default"
// options.AddPolicy("default", policy =>
// {
// policy.WithOrigins("*")
// .AllowAnyHeader()
// .AllowAnyMethod();
// });
//});
}
/// <summary>
/// 数据库的相关服务注册
/// </summary>
/// <param name="services"></param>
/// <param name="configuration"></param>
/// <returns></returns>
public static IServiceCollection AddDBService(this IServiceCollection services, IConfiguration configuration)
{
#region 数据库
////连接符字串
string constr = configuration["DbConnectionString"];
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, constr)
//.UseAutoSyncStructure( true ) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
.Build(); //请务必定义成 Singleton 单例模式
services.AddSingleton <IFreeSql>(fsql);
#endregion
services.AddSingleton <IUser, UserService>();
services.AddSingleton <IBillType, BillTypeService>();
services.AddSingleton <IBill, BillService>();
return(services);
}
/// <summary>
/// 获取服务器数据库
/// </summary>
/// <param name="sqlconnect"></param>
/// <returns></returns>
private ActionResult ServerDbList(SqlConnect sqlconnect)
{
PageResponse reponse = new PageResponse();
try
{
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(sqlconnect.SqlType, sqlconnect.Address)
.Build();
var dbs = fsql.DbFirst.GetDatabases();
fsql.Dispose();
if (!string.IsNullOrEmpty(sqlconnect.DbName))
{
dbs = dbs.Where(p => p == sqlconnect.DbName).ToList();
}
List <TableConfig> list_table = new List <TableConfig>();
foreach (var name in dbs)
{
TableConfig tableConfig = new TableConfig()
{
Id = Guid.NewGuid().ToString(),
TableName = name
};
list_table.Add(tableConfig);
}
return(Response(list_table, list_table.Count()));
}
catch (Exception ex)
{
return(Response(ex));
}
}
public static void AddFreeSql(object key, DataBaseInfo dataBase)
{
if (!DataBase.ContainsKey(key))
{
var connectionString = dataBase.IsString ? dataBase.ConnectionString
: GetConnectionString(dataBase.DataType, dataBase.UserId, dataBase.Pwd, dataBase.Host, dataBase.DbName,
dataBase.Port, dataBase.ValidatorType);
Lazy <IFreeSql> fsql = new Lazy <IFreeSql>(() =>
{
var _fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(dataBase.DataType, connectionString)
.UseLazyLoading(true) //开启延时加载功能
//.UseAutoSyncStructure(true) //自动同步实体结构到数据库
.UseMonitorCommand(
cmd => Trace.WriteLine(cmd.CommandText), //监听SQL命令对象，在执行前
(cmd, traceLog) => Console.WriteLine(traceLog))
.UseLazyLoading(true)
.Build();
_fsql.Aop.CurdAfter = (s, e) =>
{
if (e.ElapsedMilliseconds > 200)
{
//记录日志
//发送短信给负责人
}
};
return(_fsql);
});
DataBase.Add(key, fsql);
}
}
static void Main(string[] args)
{
var freesql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.PostgreSQL, "")
.Build();
TableInfomationInitor.Initialize(freesql, typeof(Test), typeof(DomainFlags));
OrmNavigate <Test> .Join <Test2>(item => item.DomainFlags, item => item.Id);
var sql = new JoinTemplate <Test>(freesql);
var result = sql.ToList(item => new
{
item.Id,
item.DomainFlags,
DomainName = RightJoin <DomainFlags> .MapFrom(item => item.Name)
});
sql = new JoinTemplate <Test>(freesql);
var result1 = sql.ToList(item => new
{
item.Id,
item.DomainFlags,
DomainName = InnerJoin <DomainFlags> .MapFrom(item => item.Name)
});
Console.ReadKey();
}
static void Main(string[] args)
{
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "data source=task.db;max pool size=5")
.UseAutoSyncStructure(true)
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd => Console.WriteLine($"=========sql: {cmd.CommandText}\r\n"))
.Build();
Scheduler scheduler = new Scheduler(new MyTaskHandler(fsql));
var dt = DateTime.Now;
for (var a = 0; a < 2; a++)
{
//临时任务
scheduler.AddTempTask(TimeSpan.FromSeconds(20), () =>
{
Console.WriteLine($"[{DateTime.Now.ToString("HH:mm:ss.fff")}] 20秒后被执行，还剩 {scheduler.QuantityTempTask} 个临时任务");
});
//循环任务，执行10次，每次间隔10秒
scheduler.AddTask(topic: "test001", body: "data1", round: 10, seconds: 10);
}
var dtts = DateTime.Now.Subtract(dt).TotalMilliseconds;
Console.WriteLine($"[{DateTime.Now.ToString("HH:mm:ss.fff")}] 注册耗时 {dtts}ms，共计 {scheduler.QuantityTempTask} 个临时任务，{scheduler.QuantityTask} 个循环任务");
Console.ReadKey();
dtts = DateTime.Now.Subtract(dt).TotalMilliseconds;
Console.WriteLine($"[{DateTime.Now.ToString("HH:mm:ss.fff")}] 耗时 {dtts}ms，还剩 {scheduler.QuantityTempTask} 个任务，{scheduler.QuantityTask} 个循环任务");
scheduler.Dispose();
fsql.Dispose();
}
public void SelectTest()
{
using (IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.PostgreSQL, "Host=192.168.164.10;Port=5432;Username=postgres;Password=123456;Database=tedb;Pooling=true;Maximum Pool Size=1")
//.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build())
{
var orderSql = fsql
.Select <PayOrder>()
.As(nameof(PayOrder).ToLower())
.Where(p => p.Status == 1)
.ToSql(p => new
{
p.PayOrderId,
p.Money,
p.OrderTime
}, FreeSql.FieldAliasOptions.AsProperty);
Assert.Equal(@"SELECT payorder.""PayOrderId"", payorder.""Money"", payorder.""OrderTime""
FROM ""pay_order"" payorder
WHERE (payorder.""Status"" = 1)", orderSql);
}
}
public static void Builder()
{
var builder = new ContainerBuilder();
var cfg = CreateCfg();
builder.RegisterInstance(cfg).SingleInstance();
#region 注册freesql
var build = new FreeSql.FreeSqlBuilder();
if (cfg.DbType == ConstCfg.DbType_MSsql)
{
build = build.UseConnectionString(FreeSql.DataType.SqlServer, cfg.AccurateConn);
}
else if (cfg.DbType == ConstCfg.DbType_Sqlite)
{
build = build.UseConnectionString(FreeSql.DataType.Sqlite, cfg.AccurateConn);
}
else if (cfg.DbType == ConstCfg.DbType_Mysql)
{
build = build.UseConnectionString(FreeSql.DataType.MySql, cfg.AccurateConn);
}
IFreeSql freeSql = build.UseAutoSyncStructure(false)
.Build();
builder.RegisterInstance(freeSql).SingleInstance();
BAccurateContext.Fluent(freeSql);
builder.RegisterType <BAccurateContext>().InstancePerLifetimeScope();
#endregion
#region 注册服务
var allAssemblies = BuildManager.GetReferencedAssemblies().Cast <Assembly>();
var assembiles = allAssemblies.Where(x => x.ManifestModule.Name.Contains("BAccurate")).ToArray();
builder.RegisterAssemblyTypes(assembiles)
.Where(type => typeof(ITransient).IsAssignableFrom(type) && !type.IsAbstract)
.AsSelf()
.AsImplementedInterfaces()
.InstancePerDependency();
#endregion
//注册controller
builder.RegisterApiControllers(assembiles);
// 注册AutoMapper
builder.Register(c => DtoMapper.CreateConfig()).SingleInstance();
builder.RegisterType <Mapper>().As <IMapper>().InstancePerLifetimeScope();
builder.RegisterType <DtoMapper>().As <IFMapper>().InstancePerLifetimeScope();
// 注册核心业务
builder.RegisterType <OnlineUserMgr>().As <IOnlineUserMgr>().SingleInstance();
IOCContainer = builder.Build();
// webApi部分注册
HttpConfiguration config = GlobalConfiguration.Configuration;
config.DependencyResolver = new AutofacWebApiDependencyResolver(IOCContainer);
}
public async Task TestDbconnection(string id)
{
await Task.Run(() =>
{
if (Guid.TryParse(id, out Guid gid) && gid != Guid.Empty)
{
var entity = Curd.DataBase.Select.WhereDynamic(gid).ToOne();
try
{
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(entity.DataType, entity.ConnectionStrings).Build())
{
Invoke(() =>
{
InvokeJS("Helper.ui.removeDialog();");
InvokeJS($"Helper.ui.dialogSuccess('提示','数据库连接成功');");
});
}
}
catch (Exception e)
{
Invoke(() =>
{
InvokeJS("Helper.ui.removeDialog();");
InvokeJS($"Helper.ui.dialogError('连接失败','{e.Message}');");
});
}
}
});
}
public Startup(IConfiguration configuration)
{
Configuration = configuration;
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, Configuration.GetConnectionString("Mysql"))
//.UseLazyLoading(false) //开启延时加载功能，相当于执行了 1+N 次数据库查询，比较适合【 WinForm 开发】像树形结构，可以点击再展开UI，使用 Include(a => a.UploadFile) 只会查一次数据库
//由于null会默认输出日志到控制台，影响测试结果。这里传入一个空的日志输出对象
.UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText)) //监听SQL命令对象，在执行前
.UseAutoSyncStructure(true) //自动同步实体结构【开发环境必备】
.UseNoneCommandParameter(true) //不使用命令参数化执行，针对 Insert/Update 方便调试时直接展示无参SQL
.Build();
fsql.Aop.ConfigEntityProperty += (_, e) =>
{
if (fsql.Ado.DataType == FreeSql.DataType.MySql || fsql.Ado.DataType == FreeSql.DataType.OdbcMySql)
{
return;
}
if (e.Property.PropertyType.IsEnum == false)
{
return;
}
e.ModifyResult.MapType = typeof(string);
};
fsql.Aop.CurdBefore += (_, e) => Trace.WriteLine(e.Sql);
// 实现全局控制租户
fsql.GlobalFilter.Apply <WxappEntity>("Wxapp", a => a.WxappId == GlobalConfig.TalentId);
Fsql = fsql;
}
static void Main(string[] args)
{
var freesql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.PostgreSQL, "Host=127.0.0.1;Port=5432;Username=postgres;Password=123456; Database=test;Pooling=true;Minimum Pool Size=1")
.Build();
//初始化扫描
TableInfomation.Initialize(freesql, typeof(Test), typeof(Test2), typeof(Test3));
//配置 Join 关系
//OrmNavigate<Test>.Connect<Test2>(item => item.Domain, item => item.Id);
//OrmNavigate<Test>.Connect<Test3>(item => item.Type, item => item.Id);
//前端准备查询条件
QueryModel queryModel = new QueryModel();
queryModel.Size = 2;
queryModel.Orders = new OrderModel[] { new OrderModel()
{
FieldName = "Id", IsDesc = true
} };
//queryModel.Fuzzy = new FuzzyModel[] { new FuzzyModel { FuzzyField = "Name", FuzzyValue = "44" } };
//外联查询
Console.ReadKey();
}
public void SelectLambdaParameter()
{
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=freesqlTest;Pooling=true;min pool size=1;Max Pool Size=51")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseMonitorCommand(
cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
//, (cmd, traceLog) => Console.WriteLine(traceLog)
)
.Build())
{
fsql.Delete <ts_slp01>().Where("1=1").ExecuteAffrows();
var testItem = new ts_slp01();
var sql1 = fsql.Select <ts_slp01>().Where(a => a.name == testItem.GetTestName1()).ToSql();
var sql2 = fsql.Select <ts_slp01>().Where(a => a.name == testItem.GetTestName2()).ToSql();
Assert.Equal(@"SELECT a.[id], a.[name]
FROM [ts_slp01] a
WHERE (a.[name] = @exp_0)", sql1);
Assert.Equal(@"SELECT a.[id], a.[name]
FROM [ts_slp01] a
WHERE (a.[name] IS NULL)", sql2);
}
}
public Startup(IConfiguration configuration)
{
Configuration = configuration;
FreeSql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(global::FreeSql.DataType.MySql, Configuration.GetConnectionString("DefaultConnection"))
.UseAutoSyncStructure(true)
.Build();
}
/// <summary>
/// 获取相关数据库所有表结构
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public static List <DbTableInfo> GetAllTable(this DataSource ds)
{
using IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(ds.DbType, ds.ConnectionString)
.Build();
var res = fsql.DbFirst.GetTablesByDatabase(ds.DataBaseNames);
return(res);
}
public Startup(IConfiguration configuration)
{
Configuration = configuration;
Fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, "Server=localhost; Database=ReportServer; uid=sa;password=123456;")
.UseAutoSyncStructure(true)
.UseLazyLoading(true)
.UseMonitorCommand(cmd => Trace.WriteLine(cmd.CommandText))
.Build();
}
public static IFreeSql Builder(IConfiguration configuration)
{
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, configuration.GetConnectionString("SqlConnection"))
.UseAutoSyncStructure(true) //自动同步实体结构到数据库【开发环境必备】
//.UseMonitorCommand(cmd => Debug.WriteLine(cmd.CommandText)) //调试打印执行的sql
.Build();
return(fsql);
}
public Startup(IConfiguration configuration, ILoggerFactory loggerFactory)
{
Configuration = configuration;
Fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Attachs=xxxtb.db;Pooling=true;Max Pool Size=10")
.UseLogger(loggerFactory.CreateLogger <IFreeSql>())
.UseAutoSyncStructure(true)
.Build();
}
// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
services.AddConsul();
string connectString = "", redisConnectString = "";
//var connectString = Configuration.GetConnectionString("IdentityServerDB");
//var redisConnectString = Configuration.GetConnectionString("WisderMicroServiceRedis");
#region 从配置中心获取配置参数
var consulAddress = Environment.GetEnvironmentVariable("WisderRegistryAddress");
using (Consul.ConsulClient consulClient = new Consul.ConsulClient(config =>
{
config.Address = new Uri(consulAddress);
}))
{
connectString = Encoding.UTF8.GetString(consulClient.KV.Get("IdentityServerDB").Result.Response.Value);
redisConnectString = Encoding.UTF8.GetString(consulClient.KV.Get("WisderMicroServiceRedis").Result.Response.Value);
}
#endregion
services.AddSingleton <IFreeSql>((provider) =>
{
var fsql = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.MySql, connectString).Build();
return(fsql);
});
services.AddDistributedRedisCache(redisConnectString);
var migrationsAssembly = typeof(Startup).GetTypeInfo().Assembly.GetName().Name;
var basePath = PlatformServices.Default.Application.ApplicationBasePath;
services.AddIdentityServer(option =>
{
option.Caching.ClientStoreExpiration = TimeSpan.FromMinutes(5);
option.Caching.ResourceStoreExpiration = TimeSpan.FromMinutes(5);
option.Caching.CorsExpiration = TimeSpan.FromMinutes(5);
})
.AddSigningCredential(new X509Certificate2(Path.Combine(basePath,
Configuration["Certificates:CerPath"]),
Configuration["Certificates:Password"]))
.AddConfigurationStore(options =>
{
options.ConfigureDbContext = dbBuilder =>
{
dbBuilder.UseMySQL(connectString, sql => sql.MigrationsAssembly(migrationsAssembly));
};
}).AddOperationalStore(options =>
{
options.ConfigureDbContext = dbBuilder =>
{
dbBuilder.UseMySQL(connectString, sql => sql.MigrationsAssembly(migrationsAssembly));
};
}).AddConfigurationStoreCache()
.AddResourceOwnerValidator <UserPasswordLoginValidator>();
}
public void StringNullToEmpty()
{
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Oracle, "user id=1user;password=123456;data source=//127.0.0.1:1521/XE;Pooling=true;Max Pool Size=5;min pool size=1")
.UseAutoSyncStructure(true)
//.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
//.UseNoneCommandParameter(true)
.UseMonitorCommand(
cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
//, (cmd, traceLog) => Console.WriteLine(traceLog)
)
.Build())
{
var repo = fsql.GetRepository <TS_SL361, long>();
var item1 = new TS_SL361 {
CreatorId = ""
};
repo.Insert(item1);
var item2 = repo.Get(item1.Id);
Assert.Null(item2.CreatorId);
fsql.Aop.AuditDataReader += (_, e) =>
{
if (e.DataReader.GetFieldType(e.Index) == typeof(string) && e.Value == DBNull.Value)
{
e.Value = "";
}
};
item1 = new TS_SL361 {
CreatorId = ""
};
repo.Insert(item1);
item2 = repo.Get(item1.Id);
Assert.Equal(item1.CreatorId, item2.CreatorId);
fsql.Aop.AuditDataReader -= fsql.Aop.AuditDataReaderHandler;
item1 = new TS_SL361 {
CreatorId = ""
};
repo.Insert(item1);
item2 = repo.Get(item1.Id);
Assert.Null(item2.CreatorId);
}
}
/// <summary>
/// 获取数据库
/// </summary>
/// <param name="dbType"></param>
/// <param name="connectionStr"></param>
/// <returns></returns>
public IFreeSql GetDb(string connectionStr)
{
DbOption dbConfig = JsonConvert.DeserializeObject <DbOption>(connectionStr);
var freeSqlBuilder = new FreeSql.FreeSqlBuilder()
.UseConnectionString(dbConfig.DbType, dbConfig.ConnectionString)
.UseAutoSyncStructure(false);
var fsql = freeSqlBuilder.Build();
fsql.Aop.CurdBefore += CurdBefore;
return(fsql);
}
public void AopConfigEntity()
{
g.mysql.CodeFirst.ConfigEntity <ModelAopConfigEntity>(a => a.Property(b => b.pkid).IsPrimary(true));
g.mysql.Aop.ConfigEntity += (s, e) =>
{
var attr = e.EntityType.GetCustomAttributes(typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute), false).FirstOrDefault() as System.ComponentModel.DataAnnotations.Schema.TableAttribute;
if (attr != null)
{
e.ModifyResult.Name = attr.Name;
}
};
g.mysql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.GetCustomAttributes(typeof(System.ComponentModel.DataAnnotations.KeyAttribute), false).Any())
{
e.ModifyResult.IsPrimary = true;
}
};
var tsql1 = g.mysql.Select <ModelAopConfigEntity>().WhereDynamic(1).ToSql();
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "Data Source=:memory:")
.UseAutoSyncStructure(true)
.UseMappingPriority(MappingPriorityType.Attribute, MappingPriorityType.Aop, MappingPriorityType.FluentApi)
.Build())
{
ColumnInfo localFunc1() => fsql.CodeFirst.GetTableByEntity(typeof(ModelAopConfigEntity01)).Columns["CreatedTime"];
ColumnInfo localFunc2() => fsql.CodeFirst.GetTableByEntity(typeof(ModelAopConfigEntity02)).Columns["CreatedTime"];
Assert.Equal(DateTimeKind.Local, localFunc1().Attribute.ServerTime);
Assert.Equal(DateTimeKind.Local, localFunc2().Attribute.ServerTime);
fsql.CodeFirst.ConfigEntity <ModelAopConfigEntityBase>(a => a.Property(b => b.CreatedTime).ServerTime(DateTimeKind.Utc));
Assert.Equal(DateTimeKind.Utc, localFunc1().Attribute.ServerTime);
Assert.Equal(DateTimeKind.Utc, localFunc2().Attribute.ServerTime);
fsql.CodeFirst.ConfigEntity <ModelAopConfigEntityBase>(a => a.Property(b => b.CreatedTime).ServerTime(DateTimeKind.Local));
Assert.Equal(DateTimeKind.Local, localFunc1().Attribute.ServerTime);
Assert.Equal(DateTimeKind.Local, localFunc2().Attribute.ServerTime);
fsql.CodeFirst.ConfigEntity <ModelAopConfigEntity01>(a => a.Property(b => b.CreatedTime).ServerTime(DateTimeKind.Utc));
Assert.Equal(DateTimeKind.Utc, localFunc1().Attribute.ServerTime);
fsql.CodeFirst.ConfigEntity <ModelAopConfigEntity02>(a => a.Property(b => b.CreatedTime).ServerTime(DateTimeKind.Utc));
Assert.Equal(DateTimeKind.Utc, localFunc2().Attribute.ServerTime);
fsql.CodeFirst.ConfigEntity <ModelAopConfigEntityBase>(a => a.Property(b => b.CreatedTime).ServerTime(DateTimeKind.Local));
Assert.Equal(DateTimeKind.Utc, localFunc1().Attribute.ServerTime);
Assert.Equal(DateTimeKind.Utc, localFunc2().Attribute.ServerTime);
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
.UseAutoSyncStructure(true) //自动迁移实体的结构到数据库
.Build();
services.AddSingleton(fsql);
services.AddRepository(typeof(UserRepository));
services.AddService(typeof(UserService));
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
private static void Free(int eachCount)
{
GC.Collect(); //回收资源
System.Threading.Thread.Sleep(1); //休息1秒
PerHelper.Execute(eachCount, "Free like ", () =>
{
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, Config.connectionString)
.UseAutoSyncStructure(false) //自动同步实体结构到数据库
.Build();
var list2 = fsql.Queryable <Test>().Where(it => it.F_String.Contains("abc")).ToList();
});
}
private static void Free(int eachCount)
{
GC.Collect(); //回收资源
System.Threading.Thread.Sleep(1); //休息1秒
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, Config.connectionString)
.UseAutoSyncStructure(false) //自动同步实体结构到数据库
.Build();
PerHelper.Execute(eachCount, "free", () =>
{
var list2 = fsql.Queryable <Group>().IncludeMany(it => it.AUsers).Take(20).ToList();
//用.First(it=>it.Id==1)报错
});
}
static void Main(string[] args)
{
try
{
IFreeSql Fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, @"Server=WIN-TONY\SQLEXPRESS;User Id=sa;Password=123456;Database=LdCms_Db")
.UseAutoSyncStructure(false)
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd => Trace.WriteLine(cmd.CommandText))
.Build();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 生成种子数据
/// </summary>
public void GenerateSeedData()
{
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Server=localhost; Port=3306; Database=Tenant1db; Uid=root; Pwd=000000; Charset=utf8mb4;")
.Build();
var sysApiEntities = fsql.GetRepository <SysApiEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysConditionEntities = fsql.GetRepository <SysConditionEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysDictionaryDetailEntities = fsql.GetRepository <SysDictionaryDetailEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysDictionaryEntities = fsql.GetRepository <SysDictionaryEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysGroupEntities = fsql.GetRepository <SysGroupEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysPermissionEntities = fsql.GetRepository <SysPermissionEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysResourceEntities = fsql.GetRepository <SysResourceEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysRoleEntities = fsql.GetRepository <SysRoleEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysRPermissionConditionEntities = fsql.GetRepository <SysRPermissionConditionEntity>().Select.ToList();
var SysRPermissionGroupEntities = fsql.GetRepository <SysRPermissionGroupEntity>().Select.ToList();
var SysRPermissionRoleEntities = fsql.GetRepository <SysRPermissionRoleEntity>().Select.ToList();
var SysRResourceApiEntities = fsql.GetRepository <SysRResourceApiEntity>().Select.ToList();
var SysRRoleResourceEntities = fsql.GetRepository <SysRRoleResourceEntity>().Select.ToList();
var SysSettingEntities = fsql.GetRepository <SysSettingEntity>().Select.Where(x => x.IsDeleted == false).ToList();
var SysUserEntities = fsql.GetRepository <SysUserEntity>().Select.Where(x => x.IsDeleted == false).ToList();
SeedDataEntity seedDataEntity = new SeedDataEntity()
{
SysApiEntities = sysApiEntities,
SysConditionEntities = SysConditionEntities,
SysDictionaryDetailEntities = SysDictionaryDetailEntities,
SysDictionaryEntities = SysDictionaryEntities,
SysGroupEntities = SysGroupEntities,
SysPermissionEntities = SysPermissionEntities,
SysResourceEntities = SysResourceEntities,
SysRoleEntities = SysRoleEntities,
SysRPermissionConditionEntities = SysRPermissionConditionEntities,
SysRPermissionGroupEntities = SysRPermissionGroupEntities,
SysRPermissionRoleEntities = SysRPermissionRoleEntities,
SysRResourceApiEntities = SysRResourceApiEntities,
SysRRoleResourceEntities = SysRRoleResourceEntities,
SysSettingEntities = SysSettingEntities,
SysUserEntities = SysUserEntities
};
WriteSeedData(seedDataEntity);
fsql.Dispose();
}
