public TransactionState(IConnectionFactory factory, CommandRunnerMode mode, IsolationLevel isolationLevel, int commandTimeout)
{
_mode = mode;
_isolationLevel = isolationLevel;
this._commandTimeout = commandTimeout;
Connection = factory.Create();
}
public TransactionState(IConnectionFactory factory, CommandRunnerMode mode, IsolationLevel isolationLevel)
{
_mode = mode;
_isolationLevel = isolationLevel;
Connection = factory.Create();
Connection.Open();
BeginTransaction();
}
public DapperDBContext(string connectionStringName)
{
_connectionFactory = IConnectionFactory.Create(connectionStringName);
this._iquery = new QueryService(connectionStringName);
this._uow = new UnitOfWork(this._connectionFactory);
this._builder = this._connectionFactory.CreateBuilder();
this._command = new ExecuteService(this._connectionFactory, this._uow);
}
/// <inheritdoc />
public async Task <IConnection> GetConnectionAsync(string groupId, string memberId, CancellationToken cancellationToken)
{
var memberConnections = _memberConnectionAssignment.GetOrAdd(groupId, key => new ConcurrentDictionary <string, IConnection>());
IConnection connection;
// check if already assigned
if (memberConnections.TryGetValue(memberId, out connection))
{
return(connection);
}
var serverId = await GetGroupServerIdAsync(groupId, cancellationToken);
Endpoint endpoint;
if (!_serverEndpoints.TryGetValue(serverId, out endpoint))
{
throw new RoutingException($"Expected to resolve endpoint for serverId {serverId}, on behalf of group {groupId}");
}
return(_connectionSemaphore.Lock(() => {
// try again to avoid race conditions while waiting on lock
if (memberConnections.TryGetValue(memberId, out connection))
{
return connection;
}
IImmutableList <IConnection> connections;
if (!_connections.TryGetValue(endpoint, out connections))
{
connections = ImmutableList <IConnection> .Empty;
}
var assignedConnections = memberConnections.Values.ToList();
connection = connections.Except(assignedConnections).FirstOrDefault();
if (connection == null)
{
connection = _connectionFactory.Create(endpoint, ConnectionConfiguration, Log);
_connections = _connections.SetItem(endpoint, connections.Add(connection));
}
memberConnections[memberId] = connection;
return connection;
}, cancellationToken));
}
public TransactionState(IConnectionFactory factory, CommandRunnerMode mode, IsolationLevel isolationLevel)
{
_isolationLevel = isolationLevel;
Connection = factory.Create();
Connection.Open();
if (mode == CommandRunnerMode.Transactional)
{
Transaction = Connection.BeginTransaction(isolationLevel);
}
}
public async Task <GetAirportsResult> Handle(GetAirportsQuery request, CancellationToken cancellationToken)
{
using (var connection = _connectionFactory.Create())
{
string getAirportsQuery = null;
string getAirportsTotalCountQuery = null;
if (request.CountryId == null)
{
getAirportsQuery = @"select a.Iata, a.Lon, a.Lat, a.Name, st.Name as Status,
c.Name as Country, con.Name as Continent, atype.TypeName as Type, si.SizeName as Size
from dbo.Airports as a
FULL OUTER JOIN dbo.Countries as c on a.CountryId = c.Id
FULL OUTER JOIN dbo.Continents as con on c.ContinentId = con.Id
FULL OUTER JOIN dbo.Sizes as si on a.SizeId = si.Id
FULL OUTER JOIN dbo.Statuses as st on a.StatusId = st.Id
FULL OUTER JOIN dbo.AirportTypes as atype on a.TypeId = atype.Id
order by a.Name
OFFSET @SkipCount ROWS
FETCH NEXT @TakeCount ROWS ONLY";
getAirportsTotalCountQuery = "Select Count(*) from dbo.Airports";
}
else
{
getAirportsQuery = @"select a.Iata, a.Lon, a.Lat, a.Name, st.Name as Status,
c.Name as Country, con.Name as Continent, atype.TypeName as Type, si.SizeName as Size
from dbo.Airports as a
FULL OUTER JOIN dbo.Countries as c on a.CountryId = c.Id
FULL OUTER JOIN dbo.Continents as con on c.ContinentId = con.Id
FULL OUTER JOIN dbo.Sizes as si on a.SizeId = si.Id
FULL OUTER JOIN dbo.Statuses as st on a.StatusId = st.Id
FULL OUTER JOIN dbo.AirportTypes as atype on a.TypeId = atype.Id
where a.Id in (select a.Id
from dbo.Airports as a inner join dbo.Countries as c on a.CountryId = c.Id
where c.Id = @Country
order by a.Name
OFFSET @SkipCount ROWS
FETCH NEXT @TakeCount ROWS ONLY)";
getAirportsTotalCountQuery = @"Select Count(*)
from dbo.Airports as a inner join dbo.Countries as c on a.CountryId = c.Id
where c.Id = @Country";
}
var airports = await connection.QueryAsync <Airport>(getAirportsQuery, new { request.SkipCount, request.TakeCount, Country = request.CountryId });
var totalCount = await connection.ExecuteScalarAsync <int>(getAirportsTotalCountQuery, new { Country = request.CountryId });
return(new GetAirportsResult
{
TotalFound = totalCount,
Airports = airports
});
}
}
private void generateOrUpdateFeature(Type featureType, IFeatureSchema feature)
{
lock (_updateLock)
{
if (_checks.ContainsKey(featureType))
{
return;
}
var schemaObjects = feature.Objects;
schemaObjects.AssertValidNames(_options);
using (var conn = _factory.Create())
{
conn.Open();
var patch = new SchemaPatch(_options.DdlRules);
patch.Apply(conn, _options.AutoCreateSchemaObjects, schemaObjects);
patch.AssertPatchingIsValid(_options.AutoCreateSchemaObjects);
var ddl = patch.UpdateDDL;
if (patch.Difference != SchemaPatchDifference.None && ddl.IsNotEmpty())
{
var cmd = conn.CreateCommand(ddl);
try
{
cmd.ExecuteNonQuery();
_options.Logger().SchemaChange(ddl);
RegisterCheck(featureType, feature);
}
catch (Exception e)
{
throw MartenCommandExceptionFactory.Create(cmd, e);
}
}
else if (patch.Difference == SchemaPatchDifference.None)
{
RegisterCheck(featureType, feature);
}
}
}
}
public static IDBContext Create()
{
string masterConnectionString = "";
// string readConnectionString = "";
IConnectionFactory commandConnection = IConnectionFactory.Create(masterConnectionString);
// IConnectionFactory queryConnection = IConnectionFactory.Create(masterConnectionString);
// IQuery query = new QueryService(readConnectionString);
IDBContext db = new DapperDBContext(masterConnectionString);
return(db);
}
public async Task <IEnumerable <Domain.Core.Model.Persona.Persona> > GetAll()
{
IEnumerable <Domain.Core.Model.Persona.Persona> personaToReturn = cacheRepository.Get(CacheKeys.Personas);
if (personaToReturn != null)
{
return(personaToReturn);
}
using (IDbConnection dbConnection = connection.Create())
{
QueryObject byAll = new PersonaSelect().All();
personaToReturn = dbConnection.Query <Domain.Core.Model.Persona.Persona>(byAll);
cacheRepository.Set(CacheKeys.Personas, personaToReturn);
// Si lo quiero guardar durante 10 segundos
// cacheRepository.Set(CacheKeys.Personas, personaToReturn, TimeSpan.FromSeconds(30));
return(personaToReturn);
}
}
public static NpgsqlConnection LongLivedConnection(this IConnectionFactory factory)
{
var conn = factory.Create();
if (!conn.ConnectionString.Contains("Keepalive", StringComparison.OrdinalIgnoreCase))
{
conn.ConnectionString = conn.ConnectionString.TrimEnd(';') + ";Keepalive=10";
}
return(conn);
}
public IDbCommand Create(string commandText, object parameters = null, bool isStoredProcedure = false)
{
var cmd = _connectionFactory.Create().CreateCommand();
if (isStoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
var cmdTextParam = new CommandBuilder(cmd);
return(cmdTextParam.GetFinalCommand(commandText, parameters));
}
public async Task ConnectAsync()
{
_connection = _connectionFactory.Create(new Uri(string.Format("{0}://ws.pusherapp.com:{1}/app/{2}?protocol=5", _options.SchemeString, _options.Port,
ApplicationKey)));
_connection.OnData += ReceivedEvent;
_connection.OnError += OnError;
var waitForEventTask = WaitForSingleEventAsync <ConnectionEstablishedEventArgs>();
await _connection.Open();
await waitForEventTask;
}
public async Task <ArchiveDocumentPage> HandleAsync(ArchiveDocPageQuery query)
{
using (var connection = connectionFactory.Create(Constants.ArchiveDbName))
{
ArchiveDocument[] docList =
(await connection.QueryAsync <ArchiveDocument>(BuildQueryObject(query)))
.ToArray();
return(new ArchiveDocumentPage(
new PageInfo(query.PageNumber, docList.Length > (int)query.PageSize),
docList.Take(docList.Length - 1)));
}
}
public void Setup()
{
//setup mock IConnection
_connection = Substitute.For <IConnection>();
_connectionFactory = Substitute.For <IConnectionFactory>();
_connectionFactory
.Create(Arg.Is <Endpoint>(e => e.IP.Port == 1), Arg.Any <IConnectionConfiguration>(), Arg.Any <ILog>())
.Returns(_ => _connection);
_connectionFactory
.Resolve(Arg.Any <Uri>(), Arg.Any <ILog>())
.Returns(_ => new Endpoint(_.Arg <Uri>(), new IPEndPoint(IPAddress.Parse("127.0.0.1"), _.Arg <Uri>().Port)));
}
public FunctionBody DefinitionForFunction(FunctionName function)
{
var sql = @"
SELECT pg_get_functiondef(pg_proc.oid)
FROM pg_proc JOIN pg_namespace as ns ON pg_proc.pronamespace = ns.oid WHERE ns.nspname = :schema and proname = :function;
SELECT format('DROP FUNCTION %s.%s(%s);'
,n.nspname
,p.proname
,pg_get_function_identity_arguments(p.oid))
FROM pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = :function
AND n.nspname = :schema;
";
using (var conn = _factory.Create())
{
conn.Open();
try
{
var cmd = conn.CreateCommand().Sql(sql)
.With("schema", function.Schema)
.With("function", function.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
var definition = reader.GetString(0);
reader.NextResult();
var drops = new List <string>();
while (reader.Read())
{
drops.Add(reader.GetString(0));
}
return(new FunctionBody(function, drops.ToArray(), definition));
}
}
finally
{
conn.Close();
}
}
}
protected IDbConnection GetConnection()
{
if (defaultConnection != null)
{
return(defaultConnection);
}
else
{
var connection = connectionFactory.Create(connectionString);
connection.Open();
return(connection);
}
}
public void SetFloor(long floor)
{
var numberOfPages = (long)Math.Ceiling((double)floor / MaxLo);
var updateSql =
$"update {_options.DatabaseSchemaName}.mt_hilo set hi_value = :floor where entity_name = :name";
// This guarantees that the hilo row exists
AdvanceToNextHi();
using (var conn = _factory.Create())
{
conn.Open();
conn.CreateCommand(updateSql)
.With("floor", numberOfPages)
.With("name", _entityName)
.ExecuteNonQuery();
}
// And again to get it where we need it to be
AdvanceToNextHi();
}
public async Task <Account> GetContributionAccountByMemberId(int id)
{
using (var connection = _connectionFactory.Create())
{
var account = await connection.QueryAsync(
$"select balance from SAVEY_APP.accounts where account_type = 'APT' and MEMBER_ID = {id}");
return(account.Select(x => new Account
{
Balance = x.BALANCE,
AccountType = x.ACCOUNT_TYPE ?? "NOT"
}).FirstOrDefault());
}
}
public void Insert(GroupIdentityEntity group, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
connection.Execute(SqlCommandStorageService.GroupIdentityInsert(), new { name = group.Name });
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(GroupIdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
}
}
public void Insert(UserIdentityEntity user, int groupId, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
connection.Execute(SqlCommandStorageService.UserGroupsIdentityInsert(), new { userId = user.Id, groupId, primaryGroup = true });
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(UserGroupsIdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
}
}
private async Task <EventPage> fetchNextPage(long lastEncountered)
{
using (var conn = _connectionFactory.Create())
{
try
{
await conn.OpenAsync(_token).ConfigureAwait(false);
var lastPossible = lastEncountered + _options.PageSize;
var sql =
$@"
select seq_id from {_selector.Events.DatabaseSchemaName}.mt_events where seq_id > :last and seq_id <= :limit and age(transaction_timestamp(), {_selector.Events.DatabaseSchemaName}.mt_events.timestamp) >= :buffer order by seq_id;
{_selector.ToSelectClause(null)} where seq_id > :last and seq_id <= :limit and type = ANY(:types) and age(transaction_timestamp(), {_selector.Events.DatabaseSchemaName}.mt_events.timestamp) >= :buffer order by seq_id;
select min(seq_id) from {_selector.Events.DatabaseSchemaName}.mt_events where seq_id > :limit and type = ANY(:types) and age(transaction_timestamp(), {_selector.Events.DatabaseSchemaName}.mt_events.timestamp) >= :buffer;
select max(seq_id) from {_selector.Events.DatabaseSchemaName}.mt_events where seq_id >= :limit and age(transaction_timestamp(), {_selector.Events.DatabaseSchemaName}.mt_events.timestamp) >= :buffer
".Replace(" as d", "");
var cmd = conn.CreateCommand(sql)
.With("last", lastEncountered)
.With("limit", lastPossible)
.With("buffer", _settings.LeadingEdgeBuffer)
.With("types", EventTypeNames, NpgsqlDbType.Array | NpgsqlDbType.Varchar);
var page = await buildEventPage(lastEncountered, cmd).ConfigureAwait(false);
if (page.Count == 0 || page.IsSequential())
{
return(page);
}
var starting = page;
await Task.Delay(250, _token).ConfigureAwait(false);
page = await buildEventPage(lastEncountered, cmd).ConfigureAwait(false);
while (!page.CanContinueProcessing(starting.Sequences))
{
starting = page;
page = await buildEventPage(lastEncountered, cmd).ConfigureAwait(false);
}
return(page);
}
finally
{
conn.Close();
}
}
}
public void Insert(UserIdentityEntity user, UserLoginInfo login, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
connection.Execute(SqlCommandStorageService.UserLoginsIdentityRepositoryInsert(), new { loginProvider = login.LoginProvider, providerKey = login.ProviderKey, userId = user.Id });
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(UserExternalLoginsIdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
}
}
public (List <PostTeaserViewModel> posts, int totalNumberOfPages) GetListOfPosts(int page)
{
List <PostTeaserViewModel> posts;
int totalNumberOfPages;
using (var connection = connectionFactory.Create(AppSettings.ConnectionString))
{
posts = new HomePagePostsQuery(page - 1, AppSettings.NumberOfPostsPerPage).Execute(connection);
var totalNumberOfPosts = new TotalNumberOfPostsQuery().Execute(connection);
totalNumberOfPages = Decimal.ToInt32(Math.Ceiling((decimal)totalNumberOfPosts / AppSettings.NumberOfPostsPerPage));
}
return(posts, (totalNumberOfPages));
}
public void Insert(Claim userClaim, int userId, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
connection.Execute(SqlCommandStorageService.UserClaimIsdentityRepositoryInsert(), new { claimValue = userClaim.Value, claimType = userClaim.Type, userId });
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(UserClaimIsdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
}
}
public StagedEventData(StagedEventOptions options, IConnectionFactory factory, EventGraph events,
ISerializer serializer)
{
_options = options;
_events = events;
_conn = factory.Create();
_conn.Open();
_selector = new EventSelector(events, serializer);
_map = new NulloIdentityMap(serializer);
_sproc = new FunctionName(events.DatabaseSchemaName, "mt_mark_event_progression");
}
protected IDbConnection GetConnection()
{
if (defaultConnection != null)
{
return(defaultConnection);
}
else
{
var connection = connectionFactory.Create("Data Source=Catalog.db;Version=3;");
//new SQLiteConnection("Data Source=Catalog.db;Version=3;");
connection.Open();
return(connection);
}
}
public async Task <GetObjectivesQueryResult> Handle(GetObjectivesQuery request, CancellationToken cancellationToken)
{
var query = "SELECT * FROM [dbo].[objectives] ORDER BY CreatedDate OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY";
using (var connection = _connectionFactory.Create())
{
var items = await connection.QueryAsync <GetObjectivesQueryItem>(query, new { Skip = request.Skip, Take = request.Take });
return(new GetObjectivesQueryResult
{
Objectives = items
});
}
}
public void Delete(int userId, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
connection.Execute(SqlCommandStorageService.UserIdentityRepositoryDeleteById(), new { userId });
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(UserIdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
}
}
public int GetGroupId(string groupName, IConnectionFactory connectionFactory)
{
try
{
using (var connection = connectionFactory.Create())
{
return(connection.ExecuteScalar <int>(SqlCommandStorageService.GroupIdentityGetGroupId(), new { name = groupName }));
}
}
catch (Exception exception)
{
DemLogger.Current.Error(exception, $"{nameof(GroupIdentityRepository)}. Error in function {DemLogger.GetCallerInfo()}");
return(0);
}
}
public async Task Create(Member entity)
{
using (var connection = _connectionFactory.Create())
{
var parameters = new DynamicParameters();
parameters.Add(":P_FIRST_NAME", entity.FirstName, DbType.String);
parameters.Add(":P_MIDDLE_NAME", entity.MiddleName, DbType.String);
parameters.Add(":P_FIRST_SURNAME", entity.FirstSurname, DbType.String);
parameters.Add(":P_SECOND_SURNAME", entity.SecondSurname, DbType.String);
parameters.Add(":P_START_DATE", DateTime.Now, DbType.Date);
await connection.ExecuteAsync("SAVEY_APP.CREATE_MEMBER", parameters, commandType : CommandType.StoredProcedure);
}
}
public async Task Create(TransactionsModel trasanction)
{
using (var connection = _connectionFactory.Create())
{
var parameters = new DynamicParameters();
parameters.Add(":P_DATE", DateTime.Now, DbType.Date);
parameters.Add(":P_AMOUNT", trasanction.Amount, DbType.Decimal);
parameters.Add(":P_TYPE", trasanction.Type, DbType.String);
parameters.Add(":P_ACCOUNT_ID", trasanction.AccountId, DbType.Int32);
await connection.ExecuteAsync("SAVEY_APP.CREATE_TRASACTIONS", parameters,
commandType : CommandType.StoredProcedure);
}
}
public static IEnumerable <dynamic> ReadStream(this IConnectionFactory connectionFactory, string query, object parameters = null)
{
using (var connection = connectionFactory.Create())
{
connection.Open();
using (var command = connection.CreateCommand(query, parameters))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.ToExpando());
}
}
}
}
public DevelopmentDocumentSchema(IConnectionFactory connections)
{
_runner = new Lazy<CommandRunner>(() => new CommandRunner(connections.Create()));
}
public QulixContext(IConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
_connection = _connectionFactory.Create();
}
/// <summary>
/// Initializes a new instance of the <see cref="AdoNetContext" /> class.
/// </summary>
/// <param name="connectionFactory">Used to checkout a connection.</param>
public AdoNetContext(IConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
_connection = _connectionFactory.Create();
}
public Context(IConnectionFactory connectionFactory)
{
_connection = connectionFactory.Create();
}