public PagerList <TEntity> QueryPagerList <TEntity>(object predicate = null, IList <ISort> sort = null, int page = 1, int resultsPerPage = 10, IDbContextTransaction transaction = null, int?commandTimeout = null) where TEntity : class
{
var items = DbConnection.GetPage <TEntity>(predicate, sort, page, resultsPerPage, transaction?.GetDbTransaction(), commandTimeout);
var totalCount = DbConnection.Count <TEntity>(predicate, transaction?.GetDbTransaction(), commandTimeout);
return(new PagerList <TEntity>(page, resultsPerPage, totalCount, items.ToList()));
}
public IAppDbContext Create(IDbContextTransaction dbContextTransaction = null)
{
var transaction = dbContextTransaction?.GetDbTransaction();
DbConnection connection;
if (transaction != null)
{
connection = transaction.Connection;
}
else
{
var connectionString = _configuration.GetConnectionString("AppDatabase");
connection = new SqlConnection(connectionString);
}
var options = new DbContextOptionsBuilder <AppDbContext>()
.UseSqlServer(
connection,
sqlOptions =>
{
sqlOptions.EnableRetryOnFailure();
})
.Options;
var db = new AppDbContext(options);
if (transaction != null)
{
db.Database.UseTransaction(transaction);
}
return(db);
}
/// <summary>
/// 获取当前事务
/// </summary>
/// <returns>事务</returns>
public DbTransaction GetCurrentTransaction()
{
IDbContextTransaction dbContextTransaction = this._dbContext.Database.CurrentTransaction;
DbTransaction dbTransaction = dbContextTransaction?.GetDbTransaction();
return(dbTransaction);
}
public void Rollback()
{
if (_transaction?.GetDbTransaction() != null)
{
_transaction.Rollback();
}
}
public Task <IEnumerable <TEntity> > QueryAsync <TEntity>(string sql, object param = null, IDbContextTransaction trans = null) where TEntity : class
{
var conn = GetConnection();
var result = conn.QueryAsync <TEntity>(sql, param, trans?.GetDbTransaction());
return(result);
}
/// <summary>
/// QueryPageListAsync
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public Task <PageResult <TEntity> > QueryPageListAsync <TEntity>(int pageIndex, int pageSize, string sql, object param = null, IDbContextTransaction trans = null) where TEntity : class
{
var connection = GetConnection();
pageSize = pageSize > 0 ? pageSize : 1;
pageIndex = pageIndex > 0 ? pageIndex : 1;
if (!PagingHelper.SplitSql(sql, out SqlParts parts))
{
throw new Exception("Unable to parse SQL statement for paged query");
}
long skip = (pageIndex - 1) * pageSize;
long take = pageSize;
var databaseType = DatabaseType.MySql;
if (_context.Database.IsMySql())
{
databaseType = DatabaseType.MySql;
}
else if (_context.Database.IsSqlServer())
{
databaseType = DatabaseType.SqlServer2012;
}
var sqlPage = PagingHelper.GetSqlPage(skip, take, parts, databaseType);
var sqlCount = parts.SqlCount;
var result = new PageResult <TEntity>
{
PageIndex = pageIndex,
PageSize = pageSize,
Total = connection.ExecuteScalar <long>(sqlCount)
};
if (result.Total == 0)
{
return(Task.FromResult(result));
}
result.PageCount = result.Total / pageSize;
if ((result.Total % pageSize) != 0)
{
result.PageCount++;
}
result.Items = connection.Query <TEntity>(sqlPage, param, trans?.GetDbTransaction())?.ToList();
return(Task.FromResult(result));
}
protected override void UseTransaction(DatabaseFacade facade, IDbContextTransaction transaction) => facade.UseTransaction(transaction.GetDbTransaction());
public async Task <int> ExecuteAsync(string sql, object param = null, IDbContextTransaction transaction = null, int?commandTimeout = null)
{
var tran = (transaction?.GetDbTransaction()) ?? DbTransaction;
return(await DbConnection.ExecuteAsync(sql, param, tran, commandTimeout));
}
public int Execute(string sql, object param = null, IDbContextTransaction transaction = null, int?commandTimeout = null)
{
var tran = (transaction?.GetDbTransaction()) ?? DbTransaction;
return(DbConnection.Execute(sql, param, tran, commandTimeout));
}
/// <summary>
/// Disables internal retries for Aborted errors for the transaction. Internal retries are enabled by default.
/// This method may only be called for read/write Spanner transactions.
/// </summary>
/// <param name="dbContextTransaction">The transaction to disable internal retries for.</param>
/// <exception cref="ArgumentException">If the transaction is not a read/write Spanner transaction</exception>
public static void DisableInternalRetries([NotNull] this IDbContextTransaction dbContextTransaction)
{
GaxPreconditions.CheckArgument(dbContextTransaction.GetDbTransaction() is SpannerRetriableTransaction, nameof(dbContextTransaction), "Must be a read/write Spanner transaction");
((SpannerRetriableTransaction)dbContextTransaction.GetDbTransaction()).EnableInternalRetries = false;
}
public async Task <int> ExecuteAsync(string sql, object param, IDbContextTransaction trans = null)
{
var conn = GetConnection();
return(await conn.ExecuteAsync(sql, param, trans?.GetDbTransaction()));
}
public async Task <IEnumerable <TEntity> > QueryAsync <TEntity>(string sql, object param = null, IDbContextTransaction transaction = null, int?commandTimeout = null) where TEntity : class
{
return(await DbConnection.QueryAsync <TEntity>(sql, param, transaction?.GetDbTransaction()));
}
static void Main(string[] args)
{
using (var db = new Northwind())
{
using (IDbContextTransaction t = db.Database.BeginTransaction())
{
WriteLine($"Transaction started with this isolation level: {t.GetDbTransaction().IsolationLevel}");
var loggerFactory = db.GetService <ILoggerFactory>();
loggerFactory.AddProvider(new ConsoleLogProvider());
WriteLine("List of categories and the number of products:");
IQueryable <Category> cats;
// = db.Categories.Include(c => c.Products);
Write("Enable eager loading? (Y/N): ");
bool eagerloading = (ReadKey().Key == ConsoleKey.Y);
bool explicitloading = false;
WriteLine();
if (eagerloading)
{
cats = db.Categories.Include(c => c.Products);
}
else
{
cats = db.Categories;
Write("Enable explicit loading? (Y/N): ");
explicitloading = (ReadKey().Key == ConsoleKey.Y);
WriteLine();
}
foreach (Category c in cats)
{
if (explicitloading)
{
Write($"Explicitly load products for {c.CategoryName}? (Y/N): ");
if (ReadKey().Key == ConsoleKey.Y)
{
var products = db.Entry(c).Collection(c2 => c2.Products);
if (!products.IsLoaded)
{
products.Load();
}
}
WriteLine();
}
WriteLine(
$"{c.CategoryName} has {c.Products.Count} products.");
}
WriteLine("List of products that cost more than a given price with most expensive first.");
string input;
decimal price;
do
{
Write("Enter a product price: ");
input = ReadLine();
} while (!decimal.TryParse(input, out price));
IQueryable <Product> prods = db.Products
.Where(product => product.UnitPrice > price)
.OrderByDescending(product => product.UnitPrice);
foreach (Product item in prods)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs {item.UnitPrice:$#,##0.00}");
}
var newProduct = new Product
{
CategoryID = 6, // Meat & Poultry
ProductName = "Bob's Burger",
UnitPrice = 500M
};
// mark product as added in change tracking
db.Products.Add(newProduct);
// save tracked changes to database
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs {item.UnitPrice:$#,##0.00}");
}
Product updateProduct = db.Products.First(p => p.ProductName.StartsWith("Bob"));
updateProduct.UnitPrice += 20M;
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs {item.UnitPrice:$#,##0.00}");
}
Product deleteProduct = db.Products.First(p => p.ProductName.StartsWith("Bob"));
db.Products.Remove(deleteProduct);
db.SaveChanges();
foreach (var item in db.Products)
{
WriteLine($"{item.ProductID}: {item.ProductName} costs {item.UnitPrice:$#,##0.00}");
}
t.Commit();
}
}
}
/// <summary>
/// To traction wrapper
/// </summary>
/// <param name="transaction"></param>
/// <param name="operator"></param>
/// <returns></returns>
public static ITransactionWrapper ToTransactionWrapper(this IDbContextTransaction transaction, TransactionAppendOperator @operator)
{
transaction.CheckNull(nameof(transaction));
return(TransactionWrapper.CreateFromTransaction(transaction.GetDbTransaction(), @operator));
}
/// <summary>
/// To traction wrapper
/// </summary>
/// <param name="transaction"></param>
/// <returns></returns>
public static ITransactionWrapper ToTransactionWrapper(this IDbContextTransaction transaction)
{
transaction.CheckNull(nameof(transaction));
return(TransactionWrapper.CreateFromTransaction(transaction.GetDbTransaction()));
}
public void BeginTransaction(DbIsolationLevel isolationLevel = DbIsolationLevel.Unspecified)
{
_transaction.GetDbTransaction();
}
public IDbTransaction GetTransaction()
{
return(_dbContextTransaction.GetDbTransaction());
}
/// <summary>
/// The commit timestamp of the transaction. This property is only valid for read/write Spanner transactions that have committed.
/// </summary>
/// <param name="dbContextTransaction"></param>
/// <returns>The commit timestamp of the transaction</returns>
/// <exception cref="ArgumentException">If the transaction is not a read/write Spanner transaction</exception>
/// <exception cref="InvalidOperationException">If the Spanner transaction has not committed</exception>
public static DateTime GetCommitTimestamp([NotNull] this IDbContextTransaction dbContextTransaction)
{
GaxPreconditions.CheckArgument(dbContextTransaction.GetDbTransaction() is SpannerRetriableTransaction, nameof(dbContextTransaction), "Must be a read/write Spanner transaction");
return(((SpannerRetriableTransaction)dbContextTransaction.GetDbTransaction()).CommitTimestamp);
}
public TEntity QueryFirstOrDefault <TEntity>(string sql, object param = null, IDbContextTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
return(DbConnection.QueryFirstOrDefault <TEntity>(sql, param, transaction?.GetDbTransaction(), commandTimeout, commandType));
}
/// <summary>
/// レコードリストを取得
/// </summary>
/// <typeparam name="TValue">レコードタイプ</typeparam>
/// <param name="dbContext">DBコンテキスト</param>
/// <param name="sql">SQL文</param>
/// <param name="param">パラメーター</param>
/// <param name="transaction">DBトランザクション</param>
/// <returns>取得結果</returns>
public static List <TValue> GetDatas <TValue>(this IAmwDBContext dbContext, string sql, object param = null, IDbContextTransaction transaction = null)
{
return(DBAccessBase.ToList <TValue>(sql, param, dbContext.Database.GetDbConnection(), transaction?.GetDbTransaction()));
}
/// <summary>
/// QueryAsync
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="trans"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public Task <IEnumerable <TEntity> > QueryAsync <TEntity>(string sql, object param = null, IDbContextTransaction trans = null, int?commandTimeout = null, CommandType?commandType = null) where TEntity : class
{
var conn = GetConnection();
return(conn.QueryAsync <TEntity>(sql, param, trans?.GetDbTransaction(), commandTimeout, commandType));
}
public DbTransaction GetDbTransaction()
{
return(_transaction.GetDbTransaction());
}
