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);
}
}
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 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 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();
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "Data Source=document.db; Pooling=true;Min Pool Size=1")
//.UseConnectionString(FreeSql.DataType.MsAccess, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=document.mdb") //如果提示Microsoft.ACE.OLEDB.12.0未注册下载安装 https://download.microsoft.com/download/E/4/2/E4220252-5FAE-4F0A-B1B9-0B48B5FBCCF9/AccessDatabaseEngine_X64.exe
.UseAutoSyncStructure(true) //自动同步实体结构【开发环境必备】
.UseMonitorCommand(cmd => Console.Write(cmd.CommandText))
.Build();
var r = new Random();
var ItemList = new List <Item>()
{
new Item {
Text = "假装 First item", Description = "This is an item description."
},
new Item {
Text = "的哥 Second item", Description = "This is an item description."
},
new Item {
Text = "四风 Third item", Description = "This is an item description."
},
new Item {
Text = "加州 Fourth item", Description = "This is an item description."
},
new Item {
Text = "阳光 Fifth item", Description = "This is an item description."
},
new Item {
Text = "孔雀 Sixth item - " + r.Next(11000).ToString(), Description = "This is an item description."
}
};
if (fsql.Select <Item>().Count() < 100)
{
fsql.Insert <Item>().AppendData(ItemList).ExecuteAffrows();
}
ItemList = fsql.Select <Item>().ToList();
Console.WriteLine("\r\n\r\nItemListCount: " + ItemList.Count());
Console.WriteLine("\r\n\r\nLastItem: " + ItemList.Last().Text);
}
public Startup(IConfiguration configuration, ILoggerFactory loggerFactory)
{
Configuration = configuration;
Fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
.UseAutoSyncStructure(true)
.Build();
//Fsql.CodeFirst.EfCoreFluentApiTestGeneric();
Fsql.CodeFirst.EfCoreFluentApiTestDynamic();
BaseDBContext.Fsql = Fsql;
var sql11 = Fsql.Select <Topic1>().ToSql();
//SELECT a."Id", a."Title", a."CreateTime" FROM "Topic1" a
var sql12 = Fsql.Insert <Topic1>().AppendData(new Topic1()).ToSql();
//INSERT INTO "Topic1"("Id", "Title", "CreateTime") VALUES(@Id0, @Title0, @CreateTime0)
var sql21 = Fsql.Select <Topic2>().ToSql();
//SELECT a."Id", a."Title", a."CreateTime" FROM "Topic2" a
var sql22 = Fsql.Insert <Topic2>().AppendData(new Topic2()).ToSql();
//INSERT INTO "Topic2"("Id", "Title", "CreateTime") VALUES(@Id0, @Title0, @CreateTime0)
using (var db = new Topic1Context())
{
db.Topic1s.Add(new Topic1());
}
using (var db = new Topic2Context())
{
db.Topic2s.Add(new Topic2());
}
var sql13 = Fsql.Select <Topic1>().ToSql();
//SELECT a."topic1_id", a."Title", a."CreateTime" FROM "topic1_sss" a
var sql14 = Fsql.Insert <Topic1>().AppendData(new Topic1()).ToSql();
//INSERT INTO "topic1_sss"("Title", "CreateTime") VALUES(@Title0, @CreateTime0)
var sql23 = Fsql.Select <Topic2>().ToSql();
//SELECT a."topic2_id", a."Title", a."CreateTime" FROM "topic2_sss" a
var sql24 = Fsql.Insert <Topic2>().AppendData(new Topic2()).ToSql();
//INSERT INTO "topic2_sss"("Title", "CreateTime") VALUES(@Title0, @CreateTime0)
}
public void SelectTest()
{
using (var 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 exp0 = 10;
var cou = db.Select <V_HospitalReport>()
.Where(a => a.ScheduledDttm.Date >= startTime.Date && a.ScheduledDttm.Date <= (endTime.AddDays(1)).Date)
.ToList(a => new
{
subCount = db.Select <V_HOSPITALREPORT>().Where(b => b.SCHEDULED_DTTM == exp0).Count()
});
}
}
public Startup(IConfiguration configuration, ILoggerFactory loggerFactory)
{
Configuration = configuration;
Fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
.UseLogger(loggerFactory.CreateLogger <IFreeSql>())
.UseAutoSyncStructure(true)
.UseLazyLoading(true)
.UseMonitorCommand(cmd => Trace.WriteLine(cmd.CommandText))
.Build();
var sysu = new SysUser {
};
Fsql.Insert <SysUser>().AppendData(sysu).ExecuteAffrows();
Fsql.Insert <SysUserLogOn>().AppendData(new SysUserLogOn {
UserLogOnId = sysu.UserId
}).ExecuteAffrows();
var a = Fsql.Select <SysUserLogOn>().ToList();
var b = Fsql.Select <SysUserLogOn>().Any();
}
/// <summary>
/// version : 0.0.7
///
/// 对扩展不友好，不易用，操作复杂
/// 可维护信高，仅支持固定语法，可用度不高
/// 缓存+表达式树
///
/// </summary>
private static void TestFreeSql()
{
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, ParamConst.MysqlConnStr)
.UseSlave("connectionString1", "connectionString2") //使用从数据库，支持多个
.UseMonitorCommand(
cmd => Console.WriteLine(cmd.CommandText), //监听SQL命令对象，在执行前
(cmd, traceLog) => Console.WriteLine(traceLog)) //监听SQL命令对象，在执行后
.UseLogger(null) //使用日志，不指定默认输出控制台 ILogger
.UseCache(null) //使用缓存，不指定默认使用内存 IDistributedCache
.UseAutoSyncStructure(true) //自动同步实体结构到数据库
.UseSyncStructureToLower(true) //转小写同步结构
.Build();
var list = fsql.Select <UserInfo>();
Console.WriteLine(JsonConvert.SerializeObject(list));
}
public void WithLambdaParameter01()
{
using (var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=freesqlTest;Pooling=true;Max Pool Size=6")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseMonitorCommand(
cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
//, (cmd, traceLog) => Console.WriteLine(traceLog)
)
.UseLazyLoading(true)
.Build())
{
fsql.Delete <ts_wlp01>().Where("1=1").ExecuteAffrows();
var id1 = Guid.NewGuid();
var id2 = Guid.NewGuid();
var affrows = fsql.Insert(new[] {
new ts_wlp01
{
id = id1,
status = ts_wlp01_status.completed,
},
new ts_wlp01
{
id = id2,
status = ts_wlp01_status.pending,
}
}).ExecuteAffrows();
Assert.Equal(2, affrows);
var items = fsql.Select <ts_wlp01>().OrderBy(a => a.status).ToList();
Assert.Equal(2, items.Count);
Assert.Equal(id1, items[0].id);
Assert.Equal(ts_wlp01_status.completed, items[0].status);
Assert.Equal(id2, items[1].id);
Assert.Equal(ts_wlp01_status.pending, items[1].status);
var item1Select = fsql.Select <ts_wlp01>().Where(a => a.status == ts_wlp01_status.completed);
var item1S0p = item1Select as Select0Provider;
Assert.Single(item1S0p._params);
var p0 = item1S0p._params[0];
Assert.Equal(p0.DbType, System.Data.DbType.String);
Assert.Equal(p0.Value, "completed");
items = item1Select.ToList();
Assert.Single(items);
Assert.Equal(id1, items[0].id);
Assert.Equal(ts_wlp01_status.completed, items[0].status);
var item2Select = fsql.Select <ts_wlp01>().Where(a => a.status == ts_wlp01_status.pending);
var item2S0p = item2Select as Select0Provider;
Assert.Single(item2S0p._params);
p0 = item2S0p._params[0];
Assert.Equal(p0.DbType, System.Data.DbType.String);
Assert.Equal(p0.Value, "pending");
items = item2Select.ToList();
Assert.Single(items);
Assert.Equal(id2, items[0].id);
Assert.Equal(ts_wlp01_status.pending, items[0].status);
// use var
var item1status = ts_wlp01_status.completed;
item1Select = fsql.Select <ts_wlp01>().Where(a => a.status == item1status);
item1S0p = item1Select as Select0Provider;
Assert.Single(item1S0p._params);
p0 = item1S0p._params[0];
Assert.Equal(p0.DbType, System.Data.DbType.String);
Assert.Equal(p0.Value, "completed");
items = item1Select.ToList();
Assert.Single(items);
Assert.Equal(id1, items[0].id);
Assert.Equal(item1status, items[0].status);
var item2status = ts_wlp01_status.pending;
item2Select = fsql.Select <ts_wlp01>().Where(a => a.status == item2status);
item2S0p = item2Select as Select0Provider;
Assert.Single(item2S0p._params);
p0 = item2S0p._params[0];
Assert.Equal(p0.DbType, System.Data.DbType.String);
Assert.Equal(p0.Value, "pending");
items = item2Select.ToList();
Assert.Single(items);
Assert.Equal(id2, items[0].id);
Assert.Equal(item2status, items[0].status);
}
}