public async Task <PagingResult <T> > ToPageListAsync(int pageIndex, int pageSize)
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect2008(_table, _fields, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_parameters.Parameters);
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
var result = new PagingResult <T>();
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = await command.ExecuteReaderAsync())
{
var handler = new SqlDataReaderSelectConverter();
result.Items = handler.ConvertToList <T>(sdr);
}
}
result.RecordCount = (int)param.Value;
return(result);
}
/// <summary>
/// 打印输出
/// </summary>
/// <param name="action">操作</param>
/// <param name="description">描述</param>
/// <param name="separator">分隔符</param>
public static void Print(Action <ISqlBuilder> action, string description = "", string separator = "")
{
ISqlBuilder builder = new SqlServerBuilder(new DefaultEntityMatedata());
action.Invoke(builder);
if (!string.IsNullOrEmpty(separator))
{
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine($"--------------------------------[ {separator} ]----------------------------------");
Console.WriteLine();
}
if (!string.IsNullOrEmpty(description))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(description);
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(builder.ToSql());
if (builder.GetParams() != null)
{
foreach (var item in builder.GetParams())
{
Console.WriteLine(item.ToString());
}
}
Console.WriteLine();
}
/// <summary>
/// 测试初始化
/// </summary>
public WhereClauseTest()
{
_parameterManager = new ParameterManager(new OracleDialect());
_database = new TestTableDatabase();
_builder = new SqlServerBuilder(new TestEntityMatedata(), null, _parameterManager);
_clause = new WhereClause(_builder, new OracleDialect(), new EntityResolver(), new EntityAliasRegister(), _parameterManager);
}
public async Task <int> UpdateIfNotExitsAsync <T>(T entity, Expression <Func <T, bool> > where)
{
var entityInfo = MyEntityContainer.Get(typeof(T));
var resolver = new EditConditionResolver <T>(entityInfo);
var result = resolver.Resolve(where.Body);
var condition = result.Condition;
var parameters = result.Parameters;
parameters.Add(entity);
condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Update(entityInfo, "");
sql += $" AND NOT EXISTS (SELECT 1 FROM [{entityInfo.TableName}] WHERE {condition})";
var command = new SqlCommand(sql);
command.Parameters.AddRange(parameters.Parameters);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
return(await command.ExecuteNonQueryAsync());
}
}
public async Task <PagingResult <T> > ToPageListAsync(int pageIndex, int pageSize)
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect(from, fields, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_parameters.Parameters);
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
var result = new PagingResult <T>();
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = await command.ExecuteReaderAsync())
{
var handler = new SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName).ToArray());
result.Items = handler.ConvertToEntityList(sdr);
}
}
result.RecordCount = (int)param.Value;
return(result);
}
/// <summary>
/// 如果不满足条件则创建一个实体，
/// 如限制用户名不能重复 InsertIfNotExist(user, u => u.Name == user.Name)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">要创建的实体</param>
/// <param name="where">条件</param>
/// <returns>新生成记录的ID，若失败返回0</returns>
public int InsertIfNotExists <T>(T entity, Expression <Func <T, bool> > where) where T : class, IEntity, new()
{
if (where == null)
{
return(Insert(entity));
}
var entityInfo = MyEntityContainer.Get(typeof(T));
var resolver = new EditConditionResolver <T>(entityInfo);
var result = resolver.Resolve(@where.Body);
var condition = result.Condition;
var parameters = result.Parameters;
parameters.Add(entity);
condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.InsertIfNotExists(entityInfo, condition);
var command = new SqlCommand(sql);
command.Parameters.AddRange(parameters.Parameters);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
var obj = command.ExecuteScalar();
if (obj != DBNull.Value)
{
entity.Id = Convert.ToInt32(obj);
}
return(entity.Id);
}
}
public async Task <int> InsertAsync <T>(T entity) where T : class, IEntity, new()
{
var entityInfo = MyEntityContainer.Get(typeof(T));
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Insert(entityInfo);
var parameters = new MyDbParameters();
parameters.Add(entity);
var command = new SqlCommand(sql);
command.Parameters.AddRange(parameters.Parameters);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
var obj = await command.ExecuteScalarAsync();
if (obj != DBNull.Value)
{
entity.Id = Convert.ToInt32(obj);
}
return(entity.Id);
}
}
public static void AddCommonDbContext <TDbContext>(this IServiceCollection services, string connectionString, DatabaseType databaseType)
where TDbContext : DbContext
{
services.AddDbContext <TDbContext>((provider, builder) =>
{
// 微软efcore支持的数据库提供程序
// https://docs.microsoft.com/zh-cn/ef/core/providers/
switch (databaseType)
{
case DatabaseType.MySql:
MySqlBuilder.UseMySql(builder, connectionString, mySqlDbContextOptionsBuilder => { });
break;
case DatabaseType.PostgreSql:
PostgreSqlBuilder.UsePostgreSql(builder, connectionString, npgsqlDbContextOptionsBuilder => { });
break;
case DatabaseType.SqlLite:
SqlLiteBuilder.UseSqlLite(builder, connectionString, sqliteDbContextOptionsBuilder => { });
break;
case DatabaseType.SqlServer:
SqlServerBuilder.UseSqlServer(builder, connectionString, sqlServerDbContextOptionsBuilder => { });
break;
}
});
}
public List <dynamic> ToPageList(int pageIndex, int pageSize, out int recordCount)
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.PagingSelect2008(_table, _fields, _where, _orderBy, pageIndex, pageSize);
var command = new SqlCommand(sql);
command.Parameters.AddRange(_parameters.Parameters);
var param = new SqlParameter("@RecordCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(param);
recordCount = 0;
List <dynamic> result;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
using (var sdr = command.ExecuteReader())
{
var handler = new SqlDataReaderSelectConverter();
result = handler.ConvertToDynamicList(sdr);
}
}
recordCount = (int)param.Value;
return(result);
}
public void TestSelect_9()
{
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample>(x => x.StringValue).Select <Sample2>(x => x.StringValue, "sample2StringValue")
.From <Sample>("a")
.Join <Sample2>("b").On <Sample, Sample2>((l, r) => l.StringValue == r.StringValue);
Output.WriteLine(_builder.ToSql());
}
public void DeveGerarScriptSelecao()
{
// Arrange
var builder = new SqlServerBuilder <PessoaMock>();
// Act
var select = builder.BuildSelect().ToLower();
// Assert
Assert.AreEqual("select id, nome, datanascimento from pessoa", select);
}
public void DeveSelecionarTodoRegistros()
{
// Arrange
var builder = new SqlServerBuilder <UsuarioMock>();
var repository = new Repository <UsuarioMock>(builder);
// Act
var select = (List <UsuarioMock>)repository.SelectAll();
// Assert
Assert.AreEqual(select.Count, 5);
}
public void TestSelect_8()
{
//结果
var result = new String();
result.AppendLine("Select [s].[StringValue],[s].[IsDeleted] ");
result.Append("From [Sample3] As [s]");
//执行
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample3>().From <Sample3>("s");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_10()
{
//结果
var result = new String();
result.AppendLine("Select [Sample_Email],[Sample_IntValue] ");
result.Append("From [b]");
//执行
_builder = new SqlServerBuilder(new TestEntityMatedata());
_builder.Select <Sample>(t => new object[] { t.Email, t.IntValue }).From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public async Task <dynamic> FirstOrDefaultAsync()
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(_table, _fields, _where, _orderBy, 1);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
var sdr = await command.ExecuteReaderAsync();
var handler = new SqlDataReaderSelectConverter();
return(handler.ConvertToDynamicEntity(sdr));
}
}
public void Test_RemoveSelect_1()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[Description],[s].[DisplayName],[s].[StringValue],[s].[IntValue] ");
result.Append("From [Sample2] As [s]");
//执行
_builder = new SqlServerBuilder(new DefaultEntityMatedata());
_builder.Select <Sample2>()
.RemoveSelect <Sample2>(x => x.Display)
.From <Sample2>("s");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
/// <summary>
/// 添加认证数据库上下文服务
/// </summary>
/// <typeparam name="T">用户和角色实体的主键类型</typeparam>
/// <param name="services">服务集</param>
/// <param name="connectionString">数据库连接串</param>
/// <param name="databaseType">数据库类型</param>
/// <param name="setupAction">用户认证选项</param>
public static void AddIdentityDbContext <TDbContext, TUser, TRole, Tkey>(this IServiceCollection services, string connectionString, DatabaseType databaseType,
Action <IdentityOptions> setupAction = null)
where TDbContext : IdentityDbContext <TUser, TRole, Tkey>
where TUser : IdentityUser <Tkey>
where TRole : IdentityRole <Tkey>
where Tkey : IEquatable <Tkey>
{
services.AddDbContext <TDbContext>((provider, builder) =>
{
// 微软efcore支持的数据库提供程序
// https://docs.microsoft.com/zh-cn/ef/core/providers/
switch (databaseType)
{
case DatabaseType.MySql:
MySqlBuilder.UseMySql(builder, connectionString, mySqlDbContextOptionsBuilder => { });
break;
case DatabaseType.PostgreSql:
PostgreSqlBuilder.UsePostgreSql(builder, connectionString, npgsqlDbContextOptionsBuilder => { });
break;
case DatabaseType.SqlLite:
SqlLiteBuilder.UseSqlLite(builder, connectionString, sqliteDbContextOptionsBuilder => { });
break;
case DatabaseType.SqlServer:
SqlServerBuilder.UseSqlServer(builder, connectionString, sqlServerDbContextOptionsBuilder => { });
break;
}
})
.AddIdentity <TUser, TRole>(setupAction) // 使用user和role 进行认证
.AddEntityFrameworkStores <TDbContext>() // 使用默认的EF的Store
.AddDefaultTokenProviders(); // 添加默认token生成工具，用其生成的token用来进行密码重置。
//services.AddTransient<IUserStore<BaseIdentityUser>, BaseIdentityUserStore>(); // 使用自定义userstore
//services.AddTransient<IRoleStore<BaseIdentityRole>, BaseIdentityRoleStore>(); // 使用自定义rolestore
services.AddApiAuthorization(); // 添加api认证Handler
services.AddScoped <IPermissionCacheService, PermissionCacheService>(); // 权限缓存服务
services.AddRepositories <TDbContext>(); // 批量注入数据仓储
services.AddScoped <BaseIdentityUnitOfWork>();
}
public async Task <int> InsertAsync <T>(List <T> entityList) where T : class, IEntity, new()
{
var entityInfo = MyEntityContainer.Get(typeof(T));
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Insert(entityInfo);
var count = 0;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
foreach (var entity in entityList)
{
using (var command = new SqlCommand(sql, conn, trans))
{
var parameters = new MyDbParameters();
parameters.Add(entity);
command.Parameters.AddRange(parameters.Parameters);
var obj = await command.ExecuteScalarAsync();
if (obj != DBNull.Value)
{
entity.Id = Convert.ToInt32(obj);
count++;
}
}
}
trans.Commit();
}
catch
{
trans.Rollback();
count = 0;
}
}
}
return(count);
}
public T FirstOrDefault()
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(from, fields, _where, _orderBy, 1);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
var sdr = command.ExecuteReader();
var handler = new SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName).ToArray());
return(handler.ConvertToEntity(sdr));
}
}
public DatabaseBasicStack()
{
var rg = new ResourceGroupBuilder("rg1")
.Name("rg1")
.Location("westeurope")
.Build();
var server = new SqlServerBuilder("sql1")
.Name("my-server")
.Location("westeurope")
.ResourceGroup(rg)
.AdministratorLogin("admin")
.AdministratorPassword("stize")
.Parent(rg)
.Build();
var db = new SqlDatabaseBuilder("primaryDB")
.Server(server.Name)
.ResourceGroup(rg.Name)
.Location(server.Location)
.Name("primaryDB")
.Parent(server)
.SkuTier("Basic")
.SkuServiceObjectiveName("S0")
.StorageAccountType(Pulumi.AzureNative.Sql.RequestedBackupStorageRedundancy.Geo)
.MaxDatabaseSizeGB(250)
.MinCapacity(100)
.DatabaseCollation("SQL_Latin1_General_CP1_CI_AS")
.SampleData(SampleName.AdventureWorksLT)
.Build();
var secondary = new SqlDatabaseBuilder("secondaryDB")
.Server("secondaryServer", "stize", "pa$5word")
.ResourceGroup(rg.Name)
.Location("westeurope")
.Name("my-db")
.SkuTier("Basic")
.SkuServiceObjectiveName("S0")
.CreateAsSecondary(db.Id)
.SecondaryType(SecondaryType.Geo)
.Build();
}
public SqlServerBasicStack()
{
var rg = new ResourceGroupBuilder("rg1")
.Name("rg1")
.Location("westeurope")
.Build();
var builder = new SqlServerBuilder("sql1");
builder
.Location("westeurope")
.ResourceGroup(rg.Name)
.Name("sql1")
.AdministratorLogin("stize")
.AdministratorPassword("pa$5word")
.IdentityType(IdentityType.SystemAssigned)
.PublicNetworkAccess(ServerPublicNetworkAccess.Enabled)
.TLSVersion_1_0();
builder.Build();
}
public async Task <List <dynamic> > ToListAsync()
{
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(_table, _fields, _where, _orderBy);
List <dynamic> result;
var visitor = new SqlDataReaderSelectConverter();
using (var conn = new SqlConnection(_connectionString))
{
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
conn.Open();
using (var sdr = await command.ExecuteReaderAsync())
{
result = visitor.ConvertToDynamicList(sdr);
}
}
return(result);
}
public async Task <int> UpdateAsync <T>(T entity) where T : class, IEntity, new()
{
var entityInfo = MyEntityContainer.Get(typeof(T));
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Update(entityInfo, "");
var parameters = new MyDbParameters();
parameters.Add(entity);
var command = new SqlCommand(sql);
command.Parameters.AddRange(parameters.Parameters);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
command.Connection = conn;
return(await command.ExecuteNonQueryAsync());
}
}
/// <summary>
/// 更新多个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entityList">要更新的实体列表</param>
/// <returns>受影响的记录数</returns>
public int Update <T>(List <T> entityList) where T : class, IEntity, new()
{
var entityInfo = MyEntityContainer.Get(typeof(T));
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Update(entityInfo, "");
var count = 0;
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
foreach (var entity in entityList)
{
using (var command = new SqlCommand(sql, conn, trans))
{
var param = new MyDbParameters();
param.Add(entity);
command.Parameters.AddRange(param.Parameters);
count += command.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
count = 0;
}
}
}
return(count);
}
public async Task <List <T> > ToListAsync()
{
var fields = GetFields();
var from = GetFrom();
var sqlBuilder = new SqlServerBuilder();
var sql = sqlBuilder.Select(from, fields, _where, _orderBy);
var visitor = new SqlDataReaderConverter <T>(_includeProperties.Select(p => p.PropertyName));
List <T> result;
using (var conn = new SqlConnection(_connectionString))
{
var command = new SqlCommand(sql, conn);
command.Parameters.AddRange(_parameters.Parameters);
conn.Open();
using (var sdr = await command.ExecuteReaderAsync())
{
result = visitor.ConvertToEntityList(sdr);
}
}
return(result);
}
/// <summary>
/// 测试初始化
/// </summary>
public SqlServerBuilderTest(ITestOutputHelper output)
{
_output = output;
_builder = new SqlServerBuilder();
}
/// <summary>
/// 测试初始化
/// </summary>
public SqlServerBuilderTest()
{
_builder = new SqlServerBuilder();
}
/// <summary>
/// 测试初始化
/// </summary>
public SqlServerBuilderTest(ITestOutputHelper output) : base(output)
{
_builder = new SqlServerBuilder();
}
