public async Task<IEnumerable<ProposalReadModel>> GetAll() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString)) { return await connection.QueryAsync<ProposalReadModel>("select Id, Status, CreatedBy, CreatedOn, UpdatedOn from Proposals;"); } }
static async Task<IEnumerable<dynamic>> QueryDatabase() { using (var connection = new SqlConnection("Data Source=localhost;Initial Catalog=Test;User ID=sa;Password=password;")) { await connection.OpenAsync(); var data = await connection.QueryAsync<dynamic>("select * from dbo.DataSet"); return data; } }
public async Task<NoteType> GetNoteType( int id ) { using ( SqlConnection con = new SqlConnection( constring ) ) { string query = "select nt.NoteTypeID,s.SubName,nt.DisplayName,nt.ColorCode,nt.DisplayOrder,nt.Bold,nt.Italic from NoteTypes nt " + " inner join Subreddits s on s.SubredditID = nt.SubredditID" + " where NoteTypeID = @id"; NoteType ntype = ( await con.QueryAsync<NoteType>( query, new { @id } ) ).First(); return ntype; } }
public async Task<IEnumerable<NoteType>> GetNoteTypesForSubs( IEnumerable<string> subredditNames ) { using ( SqlConnection con = new SqlConnection( constring ) ) { string query = "select nt.NoteTypeID,s.SubName,nt.DisplayName,nt.ColorCode,nt.DisplayOrder,nt.Bold,nt.Italic from NoteTypes nt " + " inner join Subreddits s on s.SubredditID = nt.SubredditID" + " where s.SubName in @subs and nt.Disabled = 0"; return await con.QueryAsync<NoteType>( query, new { subs = subredditNames } ); } }
public async Task<IEnumerable<string>> GetUsersWithNotes( IEnumerable<string> subnames ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select distinct n.AppliesToUsername " + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID " + " where s.SubName in @subnames"; return await conn.QueryAsync<string>( query, new { subnames } ); } }
public async Task<IEnumerable<TestData>> AllAsync() { using (SqlConnection connection = new SqlConnection(AppConfiguration.ConnectionString)) { connection.Open(); IEnumerable<TestData> result = await connection.QueryAsync<TestData>(getAllCommandText); return result; } }
public async Task<bool> UserHasNotes(IEnumerable<string> subnames, string username ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select count(*) " + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID " + " where s.SubName in @subnames and n.AppliesToUsername = @username"; int count = (await conn.QueryAsync<int>( query, new { subnames, username } )).FirstOrDefault(); return count > 0; } }
public async Task<IEnumerable<Note>> GetNotesForSubs( IEnumerable<string> subnames, IEnumerable<string> users ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp, n.ParentSubreddit " + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID " + " where s.SubName in @subnames and n.AppliesToUsername in @users"; return await conn.QueryAsync<Note>( query, new { subnames, users } ); } }
public async Task<IEnumerable<Login>> GetLogins() { IEnumerable<Login> logins; using (var conn = new SqlConnection(_connectionString)) { logins = await conn.QueryAsync<Login>("select * from dbo.Login"); } return logins; }
public async Task<IEnumerable<UploadDto>> GetUploads(int parentId) { var q = @" SELECT * FROM [dbo].[Upload] WHERE ParentId=@parentId"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); return await con.QueryAsync<UploadDto>(q,new { parentId }); } }
public async Task<Note> GetNoteByID( int id ) { using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp, n.ParentSubreddit " + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID " + " where n.NoteID = @noteid"; return ( await conn.QueryAsync<Note>( query, new { noteid = id } ) ).Single(); } }
public async Task<AlbumTrack> GetAlbumTrack(long albumTrackId) { using (var connection = new SqlConnection(_connectionString)) { var cmd = new CommandDefinition(SqlStatements.SelectAlbumTrack, new { id = albumTrackId }); await connection.OpenAsync(); var tracks = await connection.QueryAsync<AlbumTrack>(cmd); return tracks.FirstOrDefault(); } }
public async Task<Artist> GetArtist(long artistId) { using (var connection = new SqlConnection(_connectionString)) { var cmd = new CommandDefinition(SqlStatements.SelectArtist, new { id = artistId }); await connection.OpenAsync(); var artists = await connection.QueryAsync<Artist>(cmd); return artists.FirstOrDefault(); } }
/// <summary> /// Pobiera dane z tabeli ObcLatarnia /// </summary> /// <param name="SelectedWiatr"></param> public async Task <List <ObcLatarnia> > ZaladujLatarniaAsync(string SelectedWiatr) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("BazaOWS"))) { var output = await connection.QueryAsync <ObcLatarnia>( $"select * from ObcLatarnia where StrefaWiatrowa = '{SelectedWiatr}'"); return(output.ToList()); } }
/// <summary> /// Pobiera dane z tabeli SlupyNarozne /// </summary> public async Task <List <Slupy> > ZaladujSlupyNarozneAsync() { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("BazaOWS"))) { var output = await connection.QueryAsync <Slupy>( $"select * from SlupyNarozne"); return(output.ToList()); } }
public async Task<DirtbagSettings> GetBotSettings(string subName ) { using (SqlConnection conn = new SqlConnection( connstring ) ) { string query = @" select s.DirtbagUrl, s.DirtbagUsername, s.DirtbagPassword FROM Subreddits s WHERE s.SubName = @subName "; return ( await conn.QueryAsync<DirtbagSettings>( query, new { subName } ) ).FirstOrDefault(); } }
/// <summary> /// Create a role /// </summary> /// <param name="role">The role to create</param> public async Task Insert(Role role) { using (var sqlConnection = new SqlConnection(applicationDatabaseConfiguration.GetConnectionString())) { await sqlConnection.OpenAsync(); var id = await sqlConnection.QueryAsync<int>(RoleTsql.Insert, role); role.Id = id.Single(); } }
public async Task<EmailTemplateDto> GetEmailTemplate(string name) { var q = @"SELECT Name,EmailBody,EmailSubject as Subject FROM EmailTemplate WITH (NOLOCK) WHERE Name=@name"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); var teams = await con.QueryAsync<EmailTemplateDto>(q, new { @name = name }); return teams.FirstOrDefault(); } }
public async Task<Blog> GetAsync() { var sql = $"SELECT * FROM [Blog];"; using(var connection = new SqlConnection(ConnectionString)) { var objs = await connection.QueryAsync<Blog>(sql); return objs.FirstOrDefault(); } }
/// <summary> /// Pobiera dane z tabeli NaciagPodstawowy /// </summary> public async Task <List <NaciagPodstawowy> > ZaladujNaciagPodstawowyAsync() { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("BazaOWS"))) { var output = await connection.QueryAsync <NaciagPodstawowy>( $"select * from NaciagPodstawowy "); return(output.ToList()); } }
public async Task<IEnumerable<NameValueItem>> GetPriorities() { using (var con = new SqlConnection(ConnectionString)) { con.Open(); var projects = await con.QueryAsync<NameValueItem>("SELECT * from Priority WITH (NOLOCK)"); return projects; } }
private async Task<Artist> GetArtistByName(string artistName) { using (var connection = new SqlConnection(_connectionString)) { var cmd = new CommandDefinition(SqlStatements.SelectArtistByName, new { name = artistName }); await connection.OpenAsync(); var artists = await connection.QueryAsync<Artist>(cmd); return artists.FirstOrDefault(); } }
/// <summary> /// Get all the roles /// </summary> /// <returns>A list of role names</returns> public async Task<List<Role>> GetRoles() { using (var sqlConnection = new SqlConnection(applicationDatabaseConfiguration.GetConnectionString())) { await sqlConnection.OpenAsync(); var roleEntities = await sqlConnection.QueryAsync<Role>(RoleTsql.GetAll); return roleEntities.ToList(); } }
public async Task<int> SaveUpload(UploadDto uploadDto) { var q = @"INSERT INTO [dbo].[Upload](Filename,Type,Url,CreatedDate,CreatedById,ParentId) VALUES(@fileName,@Type,@Url,@CreatedDate,@CreatedById,@ParentId);SELECT CAST(SCOPE_IDENTITY() as int)"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); var ss = await con.QueryAsync<int>(q,uploadDto); return ss.Single(); } }
/// <summary> /// Pobiera dane z tabeli ObliczoneSlupy /// </summary> public async Task <List <ObliczoneSlupy> > ZaladujTabeleAsync(string nrProjektu) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("BazaOWS"))) { if (nrProjektu == null || nrProjektu == "") { var output = await connection.QueryAsync <ObliczoneSlupy>( $"select * from ObliczoneSlupy").ConfigureAwait(false); return(output.ToList()); } else { var output = await connection.QueryAsync <ObliczoneSlupy>( $"select * from ObliczoneSlupy where NrProjektu = '{nrProjektu}'").ConfigureAwait(false); return(output.ToList()); } } }
public async Task<IEnumerable<Reason>> GetReasons() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString)) { return (await connection.QueryAsync("select Id, Description, Fields from Reasons")).Select(a => new Reason { Id = a.Id, Description = a.Description, Fields = FieldsXmlHelpers.FieldInfoFromXml((string)a.Fields).ToArray() }); } }
public async Task<UserAccountDto> GetUser(string email) { var q = @"SELECT [ID],[FirstName] as Name ,[EmailAddress],Password,[Avatar] as GravatarUrl ,[DefaultTeamID] FROM [dbo].[User] WHERE EmailAddress=@id"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); var com = await con.QueryAsync<UserAccountDto>(q, new { @id = email }); return com.FirstOrDefault(); } }
public async Task<IEnumerable<UserDto>> GetSubscribers(int teamId, string notificationTypeCode) { const string q = @"SELECT U.ID,U.FirstName AS Name,U.EmailAddress FROM UserNotificationSubscription UNS WITH (NOLOCK) JOIN [User] U WITH (NOLOCK) ON U.ID=UNS.UserID JOIN NotificationType NT WITH (NOLOCK) ON NT.Id=UNS.NotificationTypeID WHERE NT.Code=@notificationTypeCode AND TeamId=@teamId"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); return await con.QueryAsync<UserDto>(q, new { notificationTypeCode, teamId }); } }
public async Task<Note> AddNoteForUser( Note anote ) { //anote.AppliesToUsername = anote.AppliesToUsername.ToLower(); using ( SqlConnection conn = new SqlConnection( connstring ) ) { string query = "insert into Notes(NoteTypeID,SubredditID,Submitter,Message,AppliesToUsername, n.Url, n.Timestamp) " + " values (@NoteTypeID,(select SubredditID from Subreddits where SubName = @SubName),@Submitter,@Message,@AppliesToUsername, @Url, @Timestamp);" + " select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp " + " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID " + " where n.NoteID = cast(SCOPE_IDENTITY() as int) "; Note insertedNote = ( await conn.QueryAsync<Note>( query, new { anote.NoteTypeID, anote.SubName, anote.Submitter, anote.Message, anote.AppliesToUsername, anote.Url, anote.Timestamp } ) ).Single(); return insertedNote; } }
public async Task<DbSchema> BuildSchemaAsync() { if (schema != null) return schema; List<DbObjectInfo> objectInfoList = null; List<DbObjectReferenceRow> referenceTable = null; //get reference table from sql using (IDbConnection conn = new SqlConnection(sqlConnString)) { objectInfoList = (await conn.QueryAsync<DbObjectInfo>(getQuery_SpData())).ToList(); referenceTable = (await conn.QueryAsync<DbObjectReferenceRow>(getQuery_SpReferenceTable())).ToList(); } //refTable complete return schema = new DbSchema { Objects = objectInfoList, References = referenceTable, }; }
public async Task <User> GetUserByIdAsync(int id) { string Connectionstring = "Data Source=.\\SQLExpress;Initial Catalog=DapperTest;Integrated Security=True"; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring)) { await sqlConnection.OpenAsync(); var sqlM = @"SELECT * from threads order by activities desc"; var resultM = await sqlConnection.QueryAsync <User>(sqlM); return(resultM.FirstOrDefault()); } }
private async static Task QueryAsync() { using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=DapperTryDb;Integrated Security=true;Asynchronous Processing=True")) { await conn.OpenAsync(); using (SqlTransaction trans = conn.BeginTransaction()) { IEnumerable<Person> people = await conn.QueryAsync<Person>("SELECT * FROM People WHERE Age = @Age", new { Age = 17 }, trans); foreach (Person person in people) { Console.WriteLine(person); } } } }
public async Task<int> SaveTeamMemberRequest(AddTeamMemberRequestVM teamMemberRequest) { var q = @"INSERT INTO TeamMemberRequest(EmailAddress,TeamId,ActivationCode,CreatedByID,CreatedDate) VALUES(@email,@teamId,@a,@userId,@dt);;SELECT CAST(SCOPE_IDENTITY() as int)"; using (var con = new SqlConnection(ConnectionString)) { var a = Guid.NewGuid().ToString("n").Replace("-", ""); con.Open(); var p = await con.QueryAsync<int>(q, new { @teamId = teamMemberRequest.TeamId, @email = teamMemberRequest.EmailAddress, @a = a, @userId = teamMemberRequest.CreatedById, @dt = DateTime.Now }); return p.First(); } }
public async Task <IEnumerable <Author> > GetAllAuthor() { return(await _context.QueryAsync <Author>("SELECT * FROM dbo.Authors where isDelete=0")); }
public async Task <IEnumerable <Comment> > GetAllComment() { return(await _context.QueryAsync <Comment>("SELECT * FROM dbo.Comments WHERE isDelete=0")); }
public async Task <IEnumerable <Post> > GetAllPost() { return(await _context.QueryAsync <Post>(@"EXEC GetAllPostList")); }
public async Task<Album> GetAlbum(long albumId) { Album albumAggregate; using(var connection = new SqlConnection(_connectionString)) { var cmd = new CommandDefinition(SqlStatements.SelectAlbumAggregate, new { albumId = albumId }); await connection.OpenAsync(); var albums = await connection.QueryAsync<Album, Artist, AlbumArt, Album>(cmd, (album, artist, art) => { album.Artist = artist; album.AlbumArt = art; return album; }); albumAggregate = albums.FirstOrDefault(); if (albumAggregate == null) return null; cmd = new CommandDefinition(SqlStatements.SelectAlbumTracksByAlbum, new { albumId = albumId }); var tracks = await connection.QueryAsync<AlbumTrack>(cmd); var trackList = tracks.OrderBy(track => track.TrackNumber) .Select(track => new Track { Id = track.Id, Name = track.Name, DuationMs = track.DurationMs }) .ToList(); albumAggregate.TrackList = trackList; } return albumAggregate; }
public async Task<IEnumerable<Album>> GetAllAlbumsDapperAsync() { List<Album> albums; using (var con = new SqlConnection(this.connectionString)) { con.Open(); var watch = new Stopwatch(); watch.Start(); var result = await con.QueryAsync<Album, Genre, Artist, Album>( "[dbo].[HoleAlleAlben]", (album, genre, artist) => { album.Genre = genre; album.Artist = artist; return album; }, commandType: CommandType.StoredProcedure, splitOn: "GenreId, ArtistId"); albums = result.ToList(); watch.Stop(); this.logger.LogInformation("SQL query {0} was done in {1} ms.", new object[] { "[dbo].[HoleAlleAlben] Dapper.net Async", watch.ElapsedMilliseconds}); con.Close(); } return albums; }