public async Task <IEnumerable <GameEntity> > GetAsync()
{
using (var connection = _connectionFactory.Create())
{
return(await connection.QueryAsync <GameEntity>("SELECT * FROM GameData.Games"));
}
}
public IEnumerable <Sample> Get()
{
var query = new StringBuilder();
query.Append("SELECT Id, Name, Description FROM Samples");
using (var connection = connectionFactory.Create())
{
return(connection.Query <Sample>(query.ToString()).ToArray());
}
}
public void Create_MSSql_Works()
{
var config = ConfigurationManager.ConnectionStrings["SqlClientTest"];
_configSvcMock.ProviderName = config.ProviderName;
_configSvcMock.ConnectionString = config.ConnectionString;
using (var connection = _dbConnFact.Create())
{
Assert.IsNotNull(connection);
Assert.IsInstanceOfType(connection, typeof(SqlConnection));
}
}
/// <summary>
/// Handles providing the connection for use with the command.
/// </summary>
/// <param name="action">The handler for use with the connection.</param>
protected void UsingConnection(Action <TConnection, IDbTransaction> action)
{
if (Connection != null)
{
action(Connection, Transaction);
}
else
{
using (var conn = ConnectionFactory.Create())
{
action(conn, null);
}
}
}
private async Task <IEnumerable <Event> > GetEventsAsync(string sql, object parm)
{
var connection = connectionFactory.Create(ConnectionType.EventSourcing);
var datas = await connection.QueryAsync <PersistentObject>(sql, parm);
var result = datas.Select(data =>
{
var json = data.Event;
var type = assembly.GetType(data.EventType);
var @event = JsonConvert.DeserializeObject(json, type);
return((Event)@event);
});
return(result);
}
protected IDbConnection OpenConnection()
{
var connection = connectionFactory.Create(connectionString);
connection.Open();
return(connection);
}
/// <inheritdoc />
public IDictionary <string, object> Handle(GetHeaderQuery query)
{
using (var connection = _connectionFactory.Create())
{
connection.Open();
using (var command = connection.CreateCommand())
{
_prepareQuery.Handle(query, command, CommandStringTypes.GetHeader);
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
var headerRaw = _readColumn.ReadAsByteArray(CommandStringTypes.GetHeader, 0, reader);
if (headerRaw != null)
{
return(_serialization.InternalSerializer
.ConvertBytesTo <IDictionary <string, object> >(
headerRaw));
}
}
}
}
return(null);
}
/// <inheritdoc />
public QueueRemoveResult Handle(DeleteQueueTablesCommand inputCommand)
{
using (var connection = _dbConnectionFactory.Create())
{
connection.Open();
using (var trans = _transactionFactory.Create(connection).BeginTransaction())
{
foreach (var table in _tableNameHelper.Tables)
{
var delete =
_tableExists.Handle(
new GetTableExistsTransactionQuery(connection, trans, table));
if (!delete)
{
continue;
}
using (var commandSql = connection.CreateCommand())
{
commandSql.Transaction = trans;
_prepareDeleteTable.Handle(new DeleteTableCommand(table), commandSql,
CommandStringTypes.DeleteTable);
commandSql.ExecuteNonQuery();
}
}
trans.Commit();
return(new QueueRemoveResult(QueueRemoveStatus.Success));
}
}
}
public async Task <Result <BookItem, Error> > LoadAsync(string bookId)
{
try
{
using var connection = _dbConnectionFactory.Create();
var sql = $"SELECT * from books.book_items WHERE book_id = '{bookId}'";
var bookItem = await connection.QuerySingleOrDefaultAsync <BookItem>(sql);
return(bookItem == null?Result.Failure <BookItem, Error>(ErrorTypes.FailedLocatingItem(bookId)) : Result.Success <BookItem, Error>(bookItem));
}
catch (MySqlException mySqlException)
{
_logger.Fatal(mySqlException, "failed loading book: {BookId}", bookId);
return(Result.Failure <BookItem, Error>(ErrorTypes.FailedLoadingItem(bookId, mySqlException.Number)));
}
}
public List <DropDownOption> CalcSelectDDSizeAndTilesPerBoxAll()
{
var options = new List <DropDownOption>();
try {
using (IDbConnection connection = connectionFactory.Create(connectionString)) {
using (IDbCommand command = connection.CreateCommand()) {
command.CommandText = "CalcSelectDDSizeAndTilesPerBoxAll";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 30;
connection.Open();
using (IDataReader r = command.ExecuteReader(CommandBehavior.CloseConnection)) {
while (r.Read())
{
DropDownOption option = new DropDownOption {
value = r["SizeAndNumInBox"].ToString(),
text = r["Descr"].ToString()
};
options.Add(option);
}
}
LastErrorMsg = string.Empty;
}
}
} catch (Exception ex) {
LastErrorMsg = ex.Message;
//consider logging error
options = new List <DropDownOption>();
}
return(options);
}
private async Task <BaseMemento> GetMementoAsync(string sql, object parm)
{
var connection = connectionFactory.Create(ConnectionType.EventSourcing);
var data = await connection.QueryFirstOrDefaultAsync <PersistentObject>(sql, parm);
if (data == null)
{
return(null);
}
var json = data.Memento;
var type = Type.GetType(data.MementoType);
var result = JsonConvert.DeserializeObject(json, type);
return((BaseMemento)result);
}
/// <summary>
/// Generates a connection and executes the action within a using statement.
/// Useful for single-line operations.
/// </summary>
/// <typeparam name="TConn">The connection type.</typeparam>
/// <typeparam name="T">The type returned from the action.</typeparam>
/// <param name="connectionFactory">The connection factory to generate connections from.</param>
/// <param name="action">The action to execute.</param>
/// <returns>The value from the action.</returns>
public static async Task <T> UsingAsync <TConn, T>(this IDbConnectionFactory <TConn> connectionFactory, Func <TConn, Task <T> > action)
where TConn : IDbConnection
{
using (var conn = connectionFactory.Create())
{
return(await action(conn));
}
}
/// <summary>
/// Generates a connection and executes the action within a using statement.
/// Useful for single-line operations.
/// </summary>
/// <typeparam name="TConn">The connection type.</typeparam>
/// <param name="connectionFactory">The connection factory to generate connections from.</param>
/// <param name="action">The action to execute.</param>
public static void Using <TConn>(this IDbConnectionFactory <TConn> connectionFactory, Action <TConn> action)
where TConn : IDbConnection
{
using (var conn = connectionFactory.Create())
{
action(conn);
}
}
/// <summary>
/// Generates a connection and executes the action within a using statement.
/// Useful for single-line operations.
/// </summary>
/// <typeparam name="TConn">The connection type.</typeparam>
/// <typeparam name="T">The type returned from the action.</typeparam>
/// <param name="connectionFactory">The connection factory to generate connections from.</param>
/// <param name="action">The action to execute.</param>
/// <returns>The value from the action.</returns>
public static T Using <TConn, T>(this IDbConnectionFactory <TConn> connectionFactory, Func <TConn, T> action)
where TConn : IDbConnection
{
using (var conn = connectionFactory.Create())
{
return(action(conn));
}
}
public DapDatabaseFactory(IDbConnectionFactory connFactory, ISqlDialectFactory dialectFactory)
{
connFactory.ThrowIfArgumentIsNull("connFactory");
dialectFactory.ThrowIfArgumentIsNull("dialectFactory");
_dbConnection = connFactory.Create();
_sqlDialect = dialectFactory.Create();
}
/// <summary>
/// Creates a new instance of <see cref="IdentityRecord" />.
/// </summary>
/// <param name="dbConnectionFactory"></param>
public IdentityRecord(IDbConnectionFactory dbConnectionFactory)
{
DbConnection = dbConnectionFactory.Create();
DbObjectsEscapeChar = dbConnectionFactory.DbObjectsEscapeChar;
DbObjectsPrefix = dbConnectionFactory.DbObjectsPrefix;
DbSchema = dbConnectionFactory.DbSchema;
GuidConverter = dbConnectionFactory.GuidConverter;
}
public async Task <int> AddAsync(User entity)
{
int result = 0;
using (var connection = _db.Create())
{
int entityInserted = await connection.ExecuteScalarAsync <int>(@"INSERT INTO [dbo].[Users]
([Name]
,[LastName]
,[Address]
,[CreatedDate])
VALUES
(@Name
, @LastName
, @Address
, getdate())
SELECT CAST(SCOPE_IDENTITY() as int)", new
{
Name = entity.Name,
LastName = entity.LastName,
Address = entity.Address
}, null);
result = entityInserted;
}
return(result);
}
/// <inheritdoc/>
public Task <int> Count()
{
var sql = @"
SELECT COUNT(*)
FROM [work].[QMessage]
WHERE [QueueName] = @QueueName;
";
using (var dbConnection = _dbConnectionFactory.Create()) {
dbConnection.Open();
var command = dbConnection.CreateCommand();
command.AddParameterWithValue("@QueueName", _queueNameResolver.Resolve(), DbType.String);
command.CommandText = sql;
command.CommandType = CommandType.Text;
var count = command.ExecuteScalar();
return(Task.FromResult((int)count));
}
}
public async Task <GetOrdersTransactionResult> Handle(GetOrdersTransaction request, CancellationToken cancellationToken)
{
var connection = factory.Create(ConnectionType.Reporting);
const string SELECT_SQL = "SELECT * FROM orders ORDER BY CreatedTimeUtc DESC ";
const string PAGING_SQL = " LIMIT @offset,@take ";
const string COUNT_SQL = "SELECT COUNT(1) / @pageSize AS PageCount FROM orders";
string selectSQL = SELECT_SQL;
var offset = 0;
var take = 0;
int pageIndex = 1;
int totalPageCount = 1;
if (request.PagingOptions != null &&
request.PagingOptions.PageIndex > 0 &&
request.PagingOptions.PageSize > 0)
{
selectSQL += PAGING_SQL;
offset = (request.PagingOptions.PageIndex - 1) * request.PagingOptions.PageSize;
take = request.PagingOptions.PageSize;
var count = await connection.QuerySingleAsync <double>(COUNT_SQL, new { pageSize = request.PagingOptions.PageSize });
pageIndex = request.PagingOptions.PageIndex;
totalPageCount = Convert.ToInt32(Math.Ceiling(count));
}
var queryResults = await connection.QueryAsync <PersistentObject>(selectSQL, new
{
offset,
take
});
var result = new GetOrdersTransactionResult();
foreach (var data in queryResults)
{
var item = mapper.MapToGetOrdersTransactionResultItem(data);
result.Add(item);
}
result.PagingInfo = new PagingInfo {
CurrentPageIndex = pageIndex, PageCount = totalPageCount
};
return(result);
}
public ExecutionContext(IDbConnectionFactory factory, IProviderConfiguration provider, IDbCommandStringifier stringifier, IMapCompiler compiler, IMapCache mapCache)
{
MapCompiler = compiler;
Stringifier = stringifier;
Provider = provider;
MapCache = mapCache;
_completed = 0;
_opened = 0;
Connection = factory.Create();
}
/// <summary>
/// Creates a new <see cref="IUnitOfWork"/>.
/// </summary>
/// <returns>The unit of work.</returns>
public virtual IUnitOfWork Create()
{
var context = (TContext)Activator.CreateInstance(typeof(TContext), mDbConnectionFactory.Create());
var uow = new UnitOfWork(context, mLogFile);
//Set the current user.
//var repository = uow.GetRepository<User>();
//uow.SetCurrentUser(repository.FirstOrDefault());
return(uow);
}
/// <summary>
/// Creates and opens the database connection, or returns the already open connection if it has previously been initialized
/// </summary>
protected IDbConnection GetDbConnection()
{
if (_dbConnection == null)
{
_dbConnection = _dbConnectionFactory.Create();
_dbConnection.Open();
}
return(_dbConnection);
}
/// <summary>
/// Creates and opens a connection.
/// </summary>
/// <param name="factory"></param>ConnectionFac
/// <returns></returns>
public static IDbConnection CreateAndOpen(this IDbConnectionFactory factory)
{
//Create the connection
var connection = factory.Create();
//Open it!
connection.Open();
//Well, that was actually pretty easy.
return(connection);
}
public void Handle(SetStatusTableStatusCommand command)
{
using (var connection = _dbConnectionFactory.Create())
{
connection.Open();
using (var commandSql = connection.CreateCommand())
{
_prepareCommand.Handle(command, commandSql, CommandStringTypes.UpdateStatusRecord);
commandSql.ExecuteNonQuery();
}
}
}
/// <inheritdoc />
public void Handle(DeleteStatusTableStatusCommand <long> command)
{
using (var connection = _dbConnectionFactory.Create())
{
connection.Open();
using (var commandSql = connection.CreateCommand())
{
_prepareCommand.Handle(command, commandSql, CommandStringTypes.DeleteFromStatus);
commandSql.ExecuteNonQuery();
}
}
}
/// <inheritdoc />
public void Handle(MoveRecordToErrorQueueCommand <long> command)
{
if (_options.Value.EnableHoldTransactionUntilMessageCommitted)
{
HandleForTransaction(command);
}
else
{
using (var conn = _dbConnectionFactory.Create())
{
conn.Open();
using (var trans = _transactionFactory.Create(conn).BeginTransaction())
{
using (var commandSql = conn.CreateCommand())
{
commandSql.Transaction = trans;
_prepareCommand.Handle(command, commandSql, CommandStringTypes.MoveToErrorQueue);
var iCount = commandSql.ExecuteNonQuery();
if (iCount != 1)
{
return;
}
//the record is now in the error queue, remove it from the main queue
_deleteMetaCommandHandler.Handle(new DeleteMetaDataCommand(command.QueueId, conn, trans));
if (!_options.Value.EnableStatusTable)
{
trans.Commit();
return;
}
//update the status record
_setStatusCommandHandler.Handle(new SetStatusTableStatusTransactionCommand(command.QueueId, conn, QueueStatuses.Error, trans));
}
trans.Commit();
}
}
}
}
/// <summary>
/// Creates an ambient database context with the specified options or joins an existing one.
/// </summary>
/// <param name="join">
/// Determines whether to join and inherit an existing ambient database context. If one isn't found, a
/// new ambient database context will be created based on the specified configurations provides via
/// <see cref="Create"/> method. The default is <c>true</c>.
/// </param>
/// <param name="suppress">
/// Determines whether the transaction support should be disabled. The default is <c>false</c>.
/// </param>
/// <param name="isolationLevel">
/// Sets the transaction isolation level. The default is <see cref="IsolationLevel.ReadCommitted"/>.
/// </param>
/// <returns>
/// The <see cref="IAmbientDbContext"/> instance.
/// </returns>
/// <exception cref="AmbientDbContextException">
/// when the database connection factory returns a connection in a non-closed state.
/// </exception>
public IAmbientDbContext Create(bool join = true, bool suppress = false, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
{
var connection = _connectionFactory.Create();
if (connection.State != ConnectionState.Closed)
{
throw new AmbientDbContextException("The database connection factory returned a database connection in a non-closed state. This behavior is not allowed as the ambient database context will maintain database connection state as required.");
}
var ambientDbContext = new AmbientDbContext(connection, join, suppress, isolationLevel);
return(ambientDbContext);
}
public RepositoryContext(TDatabase dbSettings, IDbConnectionFactory connFactory)
{
if (dbSettings == null)
{
throw new ArgumentNullException(nameof(dbSettings));
}
if (connFactory == null)
{
throw new ArgumentNullException(nameof(connFactory));
}
_connection = new Lazy <IDbConnection>(() => connFactory.Create(dbSettings.ConnectionString));
}
private async Task <SqlConnection> MakeConnection()
{
var connection = (await _dbConnectionFactory.Create(_configurator)) as SqlConnection;
if (connection == null)
{
throw new InvalidDbConnectionException(typeof(SqlConnection));
}
await connection.OpenAsync();
return(connection);
}
private DbConnection GetOrCreateOpenedConnection()
{
ThrowIfDisposed();
if (_connection != null)
{
return(_connection);
}
_connection = _connectionFactory.Create();
_connection.Open();
return(_connection);
}
