//public async Task<IEnumerable<Cotacao>> Getcotacaoes() //{ // string sql = "select * from [cotacaoDB].[dbo].[cotacao]"; // string sqlcotacaoFriend = "select Name from cotacaoFriends where cotacaoId = @cotacaoId"; // IList<Cotacao> listcotacao = new List<Cotacao>(); // using (var connectionDb = connection.Connection()) // { // connectionDb.Open(); // var result = await connectionDb.QueryAsync<dynamic>(sql); // if (result.Any()) // { // foreach (var item in result.ToList()) // { // var cotacao = new Cotacao((int)item.cotacaoId, (string)item.Name, (string)item.Power); // var resultcotacaoFriend = await connectionDb.QueryAsync<dynamic>(sqlcotacaoFriend, // new // { // cotacaoId = cotacao.cotacaoId // }); // if (result.Any()) // { // foreach (var friend in resultcotacaoFriend.ToList()) // { // cotacao.AddcotacaoFriend((string)friend.Name); // } // } // listcotacao.Add(cotacao); // } // } // } // return listcotacao; //} //public async Task<Cotacao> Insertcotacao(Cotacao cotacao) //{ // string sql = "Insert into [cotacaoDB].[dbo].[cotacao] ([cotacaoId],[Name],[Power]) values (@cotacaoId, @Name, @Power)"; // using (var connectionDb = connection.Connection()) // { // connectionDb.Open(); // var cotacaoResult = await connectionDb.ExecuteAsync(sql, // new // { // cotacaoId = cotacao.cotacaoId, // Name = cotacao.Name, // Power = cotacao.Power // }); // if (cotacao.cotacaoFriend.Any()) // { // string sqlcotacaoFriend = "Insert into [cotacaoDB].[dbo].[cotacaoFriends] ([cotacaoId],[Name]) values (@cotacaoId, @Name)"; // foreach (var friend in cotacao.cotacaoFriend) // { // var cotacaoFriendResult = await connectionDb.ExecuteAsync(sqlcotacaoFriend, // new // { // cotacaoId = cotacao.cotacaoId, // Name = friend // }); // } // } // return cotacao; // } //} //public async Task<Cotacao> Updatecotacao(Cotacao cotacao) //{ // string sql = "UPDATE [cotacaoDB].[dbo].[cotacao] SET[Name] = @Name, [Power]= @Power WHERE [cotacaoId] = @cotacaoId;"; // using (var connectionDb = connection.Connection()) // { // connectionDb.Open(); // //var cotacaoResult = await connectionDb.ExecuteAsync(sql, // // new // // { // // cotacaoId = cotacao.cotacaoId, // // Name = cotacao.Name, // // Power = cotacao.Power // // }); // //return cotacao; // } //} //public async Task<Cotacao> Getcotacao(int cotacaoId) //{ // string sql = "select * from [cotacaoDB].[dbo].[cotacao] where [cotacaoId] = @cotacaoId"; // string sqlcotacaoFriend = "select Name from cotacaoFriends where cotacaoId = @cotacaoId"; // IList<Cotacao> listcotacao = new List<Cotacao>(); // using (var connectionDb = connection.Connection()) // { // connectionDb.Open(); // var result = await connectionDb.QueryFirstOrDefaultAsync<Cotacao>(sql, // new // { // cotacaoId = cotacaoId // }); // if (result != null) // { // var resultcotacaoFriend = await connectionDb.QueryAsync<dynamic>(sqlcotacaoFriend, // new // { // cotacaoId = result.cotacaoId // }); // foreach (var friend in resultcotacaoFriend.ToList()) // { // result.AddcotacaoFriend((string)friend.Name); // } // } // return result; // } //} //public async Task<bool> Deletcotacao(int cotacaoId) //{ // string sql = "Delete [cotacaoDB].[dbo].[cotacao] where [cotacaoId] = @cotacaoId"; // string sqlFriendcotacao = "Delete [cotacaoDB].[dbo].[cotacaoFriends] where [cotacaoId] = @cotacaoId"; // using (var connectionDb = connection.Connection()) // { // connectionDb.Open(); // await connectionDb.QueryAsync<cotacao>(sqlFriendcotacao, // new // { // cotacaoId = cotacaoId // }); // await connectionDb.QueryAsync<cotacao>(sql, // new // { // cotacaoId = cotacaoId // }); // return true; // } //} public async Task <IEnumerable <Cotacao> > GetCotacaos(int cotacaoId) { string sqlcotacao = $"Select * From Cotacao {(cotacaoId > 0 ? $"Where IdCotacao = {cotacaoId}" : "")}"; IList <Cotacao> listcotacao = new List <Cotacao>(); using (var connectionDb = connection.Connection()) { connectionDb.Open(); var result = await connectionDb.QueryAsync <Cotacao>(sqlcotacao); if (result.Any()) { foreach (var cotacao in result.ToList()) { string sqlcotacaoItem = $"Select * From CotacaoItem {(cotacao.IdCotacao > 0 ? $"Where IdCotacao = {cotacao.IdCotacao}" : "")}"; cotacao.cotacaoItens = new List <CotacaoItem>(); var resultcotacaoItem = await connectionDb.QueryAsync <CotacaoItem>(sqlcotacaoItem); if (result.Any()) { foreach (var cotacaoItem in resultcotacaoItem.ToList()) { cotacao.cotacaoItens.Add(cotacaoItem); } } listcotacao.Add(cotacao); } } } return(listcotacao); }
public async Task <User> GetByUsernameAsync(String username) { using (IDbConnection connection = _factory.Connection( )) { return(await connection.QuerySingleOrDefaultAsync <User> ($"SELECT * from users WHERE username = @username", new { username = username })); } }
public async Task <IEnumerable <M3EmpresaCad> > Get() { string sql = $"Select IdEmpresa, NmRazaoSocial From [config].[CadEmpresas]"; using var connectionDb = _connection.Connection(_connectionString); connectionDb.Open(); return(await connectionDb.QueryAsync <M3EmpresaCad>(sql)); }
public async Task <IEnumerable <Device> > Get() { string sql = $"SELECT d.Device_ID, " + $"d.FreeBatteryPercentage, d.DEVICE_NAME, d.LastConnection, u.unit_group_name, d.FreeMemoryPercentage, d.FreeStoragePercentage, d.DEVICE_EXT_UNIT, dp.MDM_PROP_VALUE " + $"from[device] d inner join[unit_group] u on u.[UNIT_GROUP_ID] = d.[UNIT_GROUP_ID] " + $"left join[DEVICE_MDM_PROPERTY] dp on d.[DEVICE_ID] = dp.[DEVICE_ID] and dp.[MDM_PROP_KEY] = 'TELEPHONY_OPERATOR' " + $"where d.[DEVICE_EXT_UNIT] in ('rde', 'rd6', 'army') and d.[ENABLED] = 1;"; using var connectionDb = _connection.Connection(_connectionString); connectionDb.Open(); var result = await connectionDb.QueryAsync(sql); return(new List <Device>()); //return connectionDb.QueryAsync<Device, Group, DeviceProperty>( // "SELECT * " + // "FROM dbo.Estados E " + // "INNER JOIN dbo.Regioes R ON R.IdRegiao = E.IdRegiao " + // "ORDER BY E.NomeEstado", // map: (d, u, dp) => // { // d.Group = u; // d.DeviceProperty = dp; // return d; // }, // splitOn: "SiglaEstado,IdRegiao"); }
public IResultBag AddPartida(Partida partida) { try { using (var connection = _conn.Connection()) { connection.Open(); connection.ExecuteAsync("INSERT INTO Pontuacao(Pontos, Dia) VALUES(@Pontos, @Dia)", partida); //connection.Query($""); return(new ResultBag("Adicionado com sucesso!", true)); } } catch (Exception ex) { return(new ResultBag(ex.Message, false)); } }
public Task <BankInfo> GetBank(int id) { return(Task.Run(() => { using (var db = _conn.Connection()) { db.Open(); var info = db.QueryFirstOrDefaultAsync <BankInfo>(BankCommand._GetById, new { ID = id }); return info.GetAwaiter().GetResult(); } })); }
public IEnumerable <Vehicle> GetVehiclesBySelectedInfo(Vehicle vehicle) { var vehicles = new List <Vehicle>() { vehicle }; var sql = @"select * from vehicles v inner join standardequipment se on se.vehicleid = v.vehicleid inner join engineinfo e on e.vehicleid = v.vehicleid inner join dealers d on d.dealerid = v.dealerid where "; if (vehicle.Make != "Any") { sql = $"{ sql } v.Make = '{ vehicle.Make }' and "; } if (vehicle.Model != "Any") { sql = $"{ sql } v.Model = '{ vehicle.Model }' and "; } if (vehicle.BodyType != "Any") { sql = $"{ sql } v.BodyType = '{ vehicle.BodyType }' and "; } sql = $"{ sql } v.Price > { vehicle.MinPrice } and v.Price < { vehicle.MaxPrice }"; using (IDbConnection conn = _conn.Connection()) { conn.Open(); vehicles = conn.Query <Vehicle, StandardEquipment, EngineInfo, Dealer, Vehicle> ( sql, (vehicle, standardEquipment, engineInfo, dealer) => { vehicle.StandardEquipment = standardEquipment; vehicle.Dealer = dealer; vehicle.EngineInfo = engineInfo; return(vehicle); }, splitOn: "VehicleID, DealerID").Distinct().OrderBy(x => x.Price).ToList(); } return(vehicles); }
public User Get(User userinfo) { User user = null; var sql = $"select * from users u where u.username = \'{userinfo.UserName}\' and u.password = \'{userinfo.Password}\';"; using (IDbConnection conn = _conn.Connection()) { conn.Open(); user = conn.QueryFirstOrDefault <User>(sql); } return(user); }
public async Task <IEnumerable <BookSoldItem> > List(Guid salesmanId) { var sql = @"SELECT CAST(id as VARCHAR) as Id, CAST(user_id as VARCHAR) as UserId, title as Title, author as Author, price as Price, sold_date as SoldDate FROM books_sold WHERE CAST(user_id as VARCHAR) = @userId"; using (IDbConnection connection = _factory.Connection( )) { return(await connection.QueryAsync <BookSoldItem> (sql, new { userId = salesmanId.ToString( ) })); } }
public async Task <IEnumerable <Hero> > GetHeroes() { string sql = "select * from [HeroDB].[dbo].[Hero]"; string sqlHeroFriend = "select Name from HeroFriends where HeroId = @HeroId"; IList <Hero> listHero = new List <Hero>(); using (var connectionDb = _connection.Connection()) { connectionDb.Open(); var result = await connectionDb.QueryAsync <dynamic>(sql); if (result.Any()) { foreach (var item in result.ToList()) { var hero = new Hero(Guid.Parse(item.HeroId), (string)item.Name, (string)item.Power); var resultHeroFriend = await connectionDb.QueryAsync <dynamic>(sqlHeroFriend, new { HeroId = hero.HeroId }); if (result.Any()) { foreach (var friend in resultHeroFriend.ToList()) { hero.AddHeroFriend((string)friend.Name); } } listHero.Add(hero); } } } return(listHero); }
public IResultBag AddJogo(JogoBasquete jogoBasquete) { try { using (var conn = _connection.Connection()) { conn.Open(); var sql = new StringBuilder(); sql.AppendLine(" INSERT INTO Jogos "); sql.AppendLine(" (DataJogo, QtdPontos) "); sql.AppendLine(" VALUES "); sql.AppendLine(" (@DataJogo, @QtdPontos) "); conn.ExecuteAsync(sql.ToString(), jogoBasquete); } return(new ResultBag(true)); } catch (Exception ex) { return(new ResultBag(ex.Message, false)); } }
public async Task <IEnumerable <SalemanListItem> > List() { using (IDbConnection connection = _factory.Connection( )) { connection.Open( ); var sql = @"SELECT users.id as Id, users.username as Username, user_profiles.id as userId, user_profiles.email as Email, user_profiles.address as Address FROM users LEFT JOIN user_profiles ON user_profiles.user_id = users.id WHERE users.is_deleted = 0 AND users.role = @role"; return(await connection.QueryAsync <SalemanListItem> (sql, new { role = (int)UserRoleEnum.Salesman })); } }
public async Task <DateTime> CurrentDateAsync() { using (var connection = _factory.Connection( )) { return(await connection.QuerySingleAsync <DateTime> ("SELECT now()")); } }
public BaseRepository(IConnectionFactory connectionFactory) { _connectionFactory = connectionFactory; conn = _connectionFactory.Connection(); }
public GamesRepository(IConnectionFactory factory) { _connection = factory.Connection(); }