public override IEnumerable <IDictionary <string, object> > RunQuery(SimpleQuery query)
{
var connection = _connectionProvider.CreateConnection();
return(new QueryBuilder(this).Build(query)
.GetCommand(connection)
.ToBufferedEnumerable(connection));
}
private IEnumerable <DataRow> GetSchema(string collectionName, params string[] constraints)
{
using (var cn = _connectionProvider.CreateConnection())
{
cn.Open();
return(cn.GetSchema(collectionName, constraints).AsEnumerable());
}
}
public async Task <Cart> GetAsync(Guid cartId)
{
using (IDbConnection connection = _connectionProvider.CreateConnection())
{
const string sql = "SELECT TOP 1 * FROM Cart WHERE Id = @Id;";
return(await connection.QueryFirstAsync <Cart>(sql, new { Id = cartId }));
}
}
public async Task <int> AddAsync(User entity, string clientId)
{
var sql = "InsertUsers";
using (var connection = _provider.CreateConnection(clientId))
{
return(await connection.Result.ExecuteAsync(
sql,
new { userName = entity.UserName, password = entity.Password },
commandType : CommandType.StoredProcedure));
}
}
public void Adicionar(FormaPagamento formaPagamento)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO formaPagamento
(Descricao) VALUES
(@Descricao)",
new
{
Descricao = formaPagamento.Descricao
});
}
}
public override IEnumerable <IDictionary <string, object> > RunQuery(SimpleQuery query, out IEnumerable <SimpleQueryClauseBase> unhandledClauses)
{
if (query.Clauses.OfType <WithCountClause>().Any())
{
return(RunQueryWithCount(query, out unhandledClauses));
}
var connection = _connectionProvider.CreateConnection();
return(new QueryBuilder(this).Build(query, out unhandledClauses)
.GetCommand(connection)
.ToEnumerable(connection));
}
public void Adicionar(Cliente cliente)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO clientes
(CPF, Nome, Telefone) VALUES
(@CPF, @Nome, @Telefone)",
new
{
CPF = cliente.CPF,
Nome = cliente.Nome,
Telefone = cliente.Telefone
});
}
}
public void Adicionar(VendaItem venda)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO vendaItem
(VendaId, ProdutoId, Quantidade) VALUES
(@VendaId, @ProdutoId, @Quantidade)",
new
{
VendaId = venda.VendaId,
ProdutoId = venda.ProdutoId,
Quantidade = venda.Quantidade
});
}
}
public void Adicionar(Produto produto)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO produtos
(Descricao, Preco, Categoria) VALUES
(@Descricao, @Preco, @Categoria)",
new
{
Descricao = produto.Descricao,
Preco = produto.Preco,
Categoria = produto.Categoria
});
}
}
public async Task <QueryResponse <ScheduleViewModel[]> > Handle(GetScheduleQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
const string sql = @"
SELECT SE.Id AS SessionId
,SC.Id AS ScreenId
,SC.Name AS ScreenName
,F.Id AS FilmId
,F.Title AS FilmTitle
,F.DurationInMinutes AS FilmDuration
FROM [session].[Sessions] SE
INNER JOIN [cine].[Screens] SC ON SE.ScreenId = SC.Id
INNER JOIN [cine].[Films] F ON SE.FilmId = F.Id
WHERE SC.CinemaId = @cinemaId AND SE.[Start] BETWEEN @start AND @end
";
var cinemas = await conn.QueryAsync <ScheduleViewModel>(sql, new
{
CinemaId = message.CinemaId,
Start = message.Date.Date,
End = message.Date.Date.AddDays(1).AddSeconds(-1)
});
return(new QueryResponse <ScheduleViewModel[]>
{
Data = cinemas.ToArray()
});
}
}
public DbContext(IConnectionProvider connectionProvider)
{
_connectionProvider = connectionProvider;
_connection = _connectionProvider.CreateConnection();
_connection.Open();
_transaction = _connection.BeginTransaction();
}
protected virtual string Execute(IQuery query,
IDictionary <string, string> parameters, IConnectionProvider provider)
{
using (IDbConnection connection = provider.CreateConnection())
using (var builder = new CommandBuilder(connection)) {
builder
.SetText(query.QueryText)
.SetType(GetCommandType(query.Options))
.SetTimeout(query.Options
.GetInteger(Strings.kCommandTimeoutOption, 30));
BindParameters(builder, query.Parameters, parameters);
string preferred_json_collection = query.Options
.GetString(Strings.kJsonCollectionOption,
Strings.kDefaultJsonCollection);
IDbCommand cmd = builder.Build();
connection.Open();
string response =
(query.QueryMethod == QueryMethod.Get)
? ExecuteReader(cmd, query, preferred_json_collection)
: ExecuteNonQuery(cmd, query, preferred_json_collection);
connection.Close();
return(response);
}
}
protected virtual string Execute(IQuery query,
IDictionary<string, string> parameters, IConnectionProvider provider) {
using (IDbConnection connection = provider.CreateConnection())
using (var builder = new CommandBuilder(connection)) {
builder
.SetText(query.QueryText)
.SetType(GetCommandType(query.Options))
.SetTimeout(query.Options
.GetInteger(Strings.kCommandTimeoutOption, 30));
BindParameters(builder, query.Parameters, parameters);
string preferred_json_collection = query.Options
.GetString(Strings.kJsonCollectionOption,
Strings.kDefaultJsonCollection);
IDbCommand cmd = builder.Build();
connection.Open();
string response =
(query.QueryMethod == QueryMethod.Get)
? ExecuteReader(cmd, query, preferred_json_collection)
: ExecuteNonQuery(cmd, query, preferred_json_collection);
connection.Close();
return response;
}
}
protected override UserPassword RetrieveUserPassword(string username)
{
IConnectionProvider connectivity = ApplicationManager.Current.ServiceProvider.GetService <IConnectionProvider>();
if (connectivity == null)
{
throw new Exception("Connectivity service not found.");
}
UserPassword userPass = new UserPassword();
using (MySqlConnection connection = (MySqlConnection)connectivity.CreateConnection())
{
connection.Open();
string sql =
@"SELECT Password, PasswordKey " +
"FROM User u " +
"JOIN Membership m ON m.userId=u.id " +
"WHERE u.Name=@name";
MySqlCommand command = new MySqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", username);
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
userPass.Password = (string)reader["Password"];
userPass.PasswordKey = Convert.FromBase64String((string)reader["PasswordKey"]);
}
}
return(userPass);
}
public void Adicionar(User user)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO usuarios
(id, AccessKey, Nome, CPF, Tipo) VALUES
(@Id, @AccessKey, @Nome, @CPF, @Tipo)",
new
{
Id = user.Id,
AccessKey = user.AccessKey,
Nome = user.Nome,
CPF = user.CPF,
Tipo = user.Tipo
});
}
}
public void Adicionar(Venda venda)
{
using (var con = _provider.CreateConnection())
{
con.Execute($@"INSERT INTO venda
(ValorTotal, IdUsuario, CpfCliente, IdFormaPagamento, Encomenda, Data) VALUES
(@ValorTotal, @IdUsuario, @CpfCliente, @IdFormaPagamento, @Encomenda, @Data)",
new
{
ValorTotal = venda.ValorTotal,
IdUsuario = venda.IdUsuario,
CpfCliente = venda.CpfCliente,
IdFormaPagamento = venda.IdFormaPagamento,
Encomenda = venda.Encomenda,
Data = venda.Data
});
}
}
public async Task <IReadOnlyCollection <Product> > GetAllAsync()
{
using (IDbConnection connection = _connectionProvider.CreateConnection())
{
IEnumerable <Product> products = await connection.GetAllAsync <Product>();
return(products.ToArray());
}
}
private void FillSchemaCache()
{
using (var connection = _connectionProvider.CreateConnection())
{
connection.Open();
FillTableCache(connection);
FillColumnCache(connection);
FillForeignKeyCache(connection);
connection.Close();
}
}
public async Task <GetCinemasResponse> Handle(GetCinemasQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
const string sql = @"
SELECT C.Id, C.Name FROM cine.Cinemas C
";
var cinemas = await conn.QueryAsync <CinemaViewModel>(sql);
return(new GetCinemasResponse(cinemas.ToArray()));
}
}
public void TestCreateConnection()
{
provider = new MsSqlConnectionProvider(connectionStringName);
object actual = provider.CreateConnection();
Assert.IsNotNull(actual);
Assert.IsInstanceOfType(typeof (IDbConnection), actual);
Assert.IsFalse(string.IsNullOrEmpty(((IDbConnection)actual).ConnectionString));
Assert.AreEqual(ConnectionState.Closed, ((IDbConnection) actual).State);
}
private string GetServerVersion(IConnectionProvider connectionProvider)
{
using (var connection = connectionProvider.CreateConnection())
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT VERSION();";
connection.OpenIfClosed();
return(command.ExecuteScalar() as string);
}
}
}
private string GetServerVersion(IConnectionProvider connectionProvider)
{
using (var connection = connectionProvider.CreateConnection())
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT VERSION();";
connection.OpenIfClosed();
return command.ExecuteScalar() as string;
}
}
}
public void TestCreateConnection()
{
provider = new MySqlConnectionProvider(connectionStringName);
object actual = provider.CreateConnection();
Assert.IsNotNull(actual);
Assert.IsInstanceOfType(typeof(IDbConnection), actual);
Assert.IsFalse(string.IsNullOrEmpty(((IDbConnection)actual).ConnectionString));
Assert.AreEqual(ConnectionState.Closed, ((IDbConnection)actual).State);
}
private DataTable SelectToDataTable(string sql)
{
var dataTable = new DataTable();
using (var cn = _connectionProvider.CreateConnection() as SqlCeConnection)
{
using (var adapter = new SqlCeDataAdapter(sql, cn))
{
adapter.Fill(dataTable);
}
}
return(dataTable);
}
public async Task <IReadOnlyCollection <CartItem> > GetCartItemsAsync()
{
using (IDbConnection connection = _connectionProvider.CreateConnection())
{
IEnumerable <CartItem> carts = await connection.GetAllAsync <CartItem>();
return(carts.ToArray());
}
}
public Comissao Adicionar(Comissao comissao)
{
using (var con = _provider.CreateConnection())
{
var valor = con.QueryFirstOrDefault <float>($@"SELECT SUM(ValorTotal) FROM venda
WHERE IdUsuario = @IdUsuario AND data BETWEEN @DataInicio AND @DataFim
GROUP BY idUsuario",
new
{
IdUsuario = comissao.IdUsuario,
DataInicio = comissao.DataInicio,
DataFim = comissao.DataFim
});
con.Execute($@"INSERT INTO comissao
(idUsuario, dataInicio, dataFim, valor) VALUES
(@IdUsuario, @DataInicio, @DataFim, @Valor)",
new
{
IdUsuario = comissao.IdUsuario,
DataInicio = comissao.DataInicio,
DataFim = comissao.DataFim,
Valor = valor * 0.1
});
return(con.QueryFirstOrDefault <Comissao>(@"
SELECT id, idUsuario, dataInicio, dataFim, valor
FROM comissao
WHERE IdUsuario = @IdUsuario AND
DataInicio = @DataInicio AND
DataFim = @DataFim",
new
{
IdUsuario = comissao.IdUsuario,
DataInicio = comissao.DataInicio,
DataFim = comissao.DataFim,
}));
}
}
public IDbConnection CreateConnection()
{
if (_sharedConnection != null)
{
return(_sharedConnection);
}
var connection = _connectionModifier(_connectionProvider.CreateConnection());
var args = ConnectionCreated.Raise(this, () => new ConnectionCreatedEventArgs(connection));
if (args != null && args.OverriddenConnection != null)
{
return(args.OverriddenConnection);
}
return(connection);
}
protected virtual bool CreateNewConnection()
{
IPooledDbConnection conn = connectionProvider.CreateConnection(this, context.ConnectionSettings, out error);
if (conn == null || !conn.IsOpen)
{
hasErrors = true;
return(false);
}
hasErrors = false;
lock (sync)
freeConnections.Enqueue(conn);
return(true);
}
public async Task <QueryResponse <CinemaViewModel[]> > Handle(GetCinemasQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
// TODO: Add paging, sorting and filtering
const string sql = @"
SELECT TOP 10 C.Id, C.Name
FROM cine.Cinemas C
";
var cinemas = await conn.QueryAsync <CinemaViewModel>(sql);
return(new QueryResponse <CinemaViewModel[]>
{
Data = cinemas.ToArray()
});
}
}
public async Task <QueryResponse <CinemaViewModel> > Handle(GetCinemaQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
const string sql = @"
SELECT C.Id, C.Name
FROM cine.Cinemas C
WHERE C.Id = @id
";
var cinema = await conn.QueryFirstOrDefaultAsync <CinemaViewModel>(sql, new { Id = message.CinemaId });
return(new QueryResponse <CinemaViewModel>
{
Data = cinema
});
}
}
public virtual bool Initialize()
{
if (isInitialized)
{
return(true);
}
connection = connectionProvider.CreateConnection(this, context.ConnectionSettings, out error);
if (connection == null)
{
hasErrors = true;
return(false);
}
hasErrors = false;
isInitialized = true;
return(true);
}
public async Task <QueryResponse <SessionTypeViewModel[]> > Handle(GetSessionsTypeQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
string sqlWhere = string.Empty;
if (message.IsPublished != null)
{
sqlWhere = string.Format(" {0} {1} {2}", sqlWhere, (sqlWhere.Length == 0 ? "WHERE" : "AND"), "Sessions.IsPublished = @IsPublished");
}
if (message.ReferenceDate != null)
{
sqlWhere = string.Format(" {0} {1} {2}", sqlWhere, (sqlWhere.Length == 0 ? "WHERE" : "AND"), "Sessions.[Start] >= @ReferenceDate");
}
string sql = string.Format(@"
SELECT Screens.CinemaId
,CASE
WHEN CAST(Sessions.Start AS time) < '14:00' THEN 'M'
WHEN CAST(Sessions.Start AS time) BETWEEN '14:00' AND '19:30' THEN 'T'
WHEN CAST(Sessions.Start AS time) > '19:30' THEN 'N'
END AS SessionType
FROM session.Sessions
INNER JOIN cine.Screens ON Sessions.ScreenId = Screens.Id
{0}
GROUP BY Screens.CinemaId
,CASE
WHEN CAST(Sessions.Start AS time) < '14:00' THEN 'M'
WHEN CAST(Sessions.Start AS time) BETWEEN '14:00' AND '19:30' THEN 'T'
WHEN CAST(Sessions.Start AS time) > '19:30' THEN 'N'
END " , sqlWhere);
var sessiones = await conn.QueryAsync <SessionTypeViewModel>(sql, new
{
IsPublished = message.IsPublished,
ReferenceDate = message.ReferenceDate
});
return(new QueryResponse <SessionTypeViewModel[]>
{
Data = sessiones.ToArray()
});
}
}
public async Task <QueryResponse <SessionFilmsViewModel[]> > Handle(GetSessionsFilmsQuery message)
{
using (var conn = _connectionProvider.CreateConnection())
{
string sqlWhere = string.Empty;
if (message.IsPublished != null)
{
sqlWhere = string.Format(" {0} {1} {2}", sqlWhere, (sqlWhere.Length == 0 ? "WHERE" : "AND"), "Sessions.IsPublished = @IsPublished");
}
if (message.ReferenceDate != null)
{
sqlWhere = string.Format(" {0} {1} {2}", sqlWhere, (sqlWhere.Length == 0 ? "WHERE" : "AND"), "Sessions.[Start] >= @ReferenceDate");
}
string sql = string.Format(@"
SELECT *
FROM cine.Films
WHERE cine.Films.Id IN
(
SELECT Sessions.FilmId
FROM session.Sessions
{0}
)" , sqlWhere);
var films = await conn.QueryAsync <SessionFilmsViewModel>(sql, new
{
IsPublished = message.IsPublished,
ReferenceDate = message.ReferenceDate
});
return(new QueryResponse <SessionFilmsViewModel[]>
{
Data = films.ToArray()
});
}
}
protected MicrOrmDataStrategy(IConnectionProvider connectionProvider)
{
ConnectionProvider = connectionProvider;
Connection = connectionProvider.CreateConnection();
Connection.Open();
}
