05_11 세가지 형태의 repository 구현

VideoRepositoryAdoAsync.cs 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace VideoAppCore.Models
{
    public class VideoRepositoryAdoAsync : IVideoRepositoryAsync
    {
        private readonly string _connectionString;

        public VideoRepositoryAdoAsync(string connectionString)
        {
            this._connectionString = connectionString;
        }
        public async Task<Video> AddVideoAsync(Video model)
        {

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                const string query =
                    "Insert Into Videos(Title, Url, Name, Company, CreatedBy) 
                    	Values(@Title, @Url, @Name, @Company, @CreatedBy);" +
                    "Select Cast(SCOPE_IDENTITY() As Int);";
                SqlCommand cmd = new SqlCommand(query, con) { CommandType = CommandType.Text };

                cmd.Parameters.AddWithValue("@Title", model.Title);
                cmd.Parameters.AddWithValue("@Url", model.Url);
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@Company", model.Company);
                cmd.Parameters.AddWithValue("@CreatedBy", model.CreatedBy);

                con.Open();
                object result = await cmd.ExecuteScalarAsync();
                if (int.TryParse(result.ToString(), out int id))
                {
                    model.Id = id;
                }
                con.Close();
            }

            return model;

        }

        public async Task<List<Video>> GetVideoAsync()
        {
            List<Video> videos = new List<Video>();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                const string query = "Select * From Videos;";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text
                };

                con.Open();
                SqlDataReader dr = await cmd.ExecuteReaderAsync();
                while (dr.Read())
                {
                    Video video = new Video
                    {
                        Id = dr.GetInt32(0),
                        Title = dr["Title"].ToString(),
                        Url = dr["Url"].ToString(),
                        Name = dr["Name"].ToString(),
                        Company = dr["Company"].ToString(),
                        CreatedBy = dr["CreatedBy"].ToString(),
                        Created = Convert.ToDateTime(dr["Created"])
                    };
                    videos.Add(video);
                }
                con.Close();
            }

            return videos;
        }

        public async Task<Video> GetVideoByIdAsync(int id)
        {
            Video video = new Video();

            using (SqlConnection con = new SqlConnection(_connectionString))
            {
                const string query = "Select * From Videos Where Id = @Id";

                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text
                };

                cmd.Parameters.AddWithValue("@Id", id);

                con.Open();
                SqlDataReader dr = await cmd.ExecuteReaderAsync();
                if(dr.Read())
                {
                    video.Id = dr.GetInt32(0);
                    video.Title = dr["Title"].ToString();
                    video.Url = dr["Url"].ToString();
                    video.Name = dr["Name"].ToString();
                    video.Company = dr["Company"].ToString();
                    video.CreatedBy = dr["CreatedBy"].ToString();
                    video.Created = Convert.ToDateTime(dr["Created"]);
                }
            }

            return video;
        }

        public async Task RemoveVideoAsync(int id)
        {
            using(SqlConnection con = new SqlConnection(_connectionString))
            {
                const string query = "Delete Videos Where Id = @Id";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text
                };

                cmd.Parameters.AddWithValue("@Id", id);
                con.Open();
                await cmd.ExecuteNonQueryAsync();
                con.Close();
            }
        }

        public async Task<Video> UpdateVideoAsync(Video model)
        {
            using(SqlConnection con = new SqlConnection(_connectionString))
            {
                const string query = @"
                    Update Videos
                    Set
                        Title = @Title,
                        Url = @Url,
                        Name = @Name,
                        Company = @Company,
                        ModifiedBy = @ModifiedBy
                    Where Id = @Id";

                SqlCommand cmd = new SqlCommand(query, con) { CommandType = CommandType.Text };

                cmd.Parameters.AddWithValue("@Id", model.Id);

                cmd.Parameters.AddWithValue("@Title", model.Title);
                cmd.Parameters.AddWithValue("@Url", model.Url);
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@Company", model.Company);
                cmd.Parameters.AddWithValue("@ModifiedBy", model.ModifiedBy);

                con.Open();
                await cmd.ExecuteNonQueryAsync();
                con.Close();
            }

            return model;
        }
    }
}

 

VideoRepositoryDapperAsync.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Dapper;

namespace VideoAppCore.Models
{
    public class VideoRepositoryDapperAsync : IVideoRepositoryAsync
    {
        private readonly SqlConnection _db;

        public VideoRepositoryDapperAsync(string connectionString)
        {
            this._db = new SqlConnection(connectionString);
        }
        public async Task<Video> AddVideoAsync(Video model)
        {
            const string query =
                "Insert Into Videos(Title, Url, Name, Company, CreatedBy) 
                		Values(@Title, @Url, @Name, @Company, @CreatedBy)" +
                "Select Cast(SCOPE_IDENTITY() As Int);";

            int id = await _db.ExecuteScalarAsync<int>(query, model);

            model.Id = id;

            return model;
        }

        public async Task<List<Video>> GetVideoAsync()
        {
            const string query = "Select * From Videos;";

            var videos = await _db.QueryAsync<Video>(query);

            return videos.ToList();
        }

        public async Task<Video> GetVideoByIdAsync(int id)
        {
            const string query = "Select * From Videos Where Id = @Id";

            var video = await _db.QueryFirstOrDefaultAsync<Video>(query, new { id },
                                    commandType: CommandType.Text);

            return video;
        }

        public async Task RemoveVideoAsync(int id)
        {
            const string query = "Delete Videos Where Id = @Id";

            await _db.ExecuteAsync(query, new { id }, commandType: CommandType.Text);
        }

        public async Task<Video> UpdateVideoAsync(Video model)
        {
            const string query = @"
                    Update Videos
                    Set
                        Title = @Title,
                        Url = @Url,
                        Name = @Name,
                        Company = @Company,
                        ModifiedBy = @ModifiedBy
                    Where Id = @Id";

            await _db.ExecuteAsync(query, model);

            return model;
        }
    }
}

 

VideoRepositoryEfCoreAsync.cs 

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace VideoAppCore.Models
{
    public class VideoRepositoryEfCoreAsync : IVideoRepositoryAsync
    {
        private readonly VideoDbContext _context;

        public VideoRepositoryEfCoreAsync(VideoDbContext context)
        {
            this._context = context;
        }
        public async Task<Video> AddVideoAsync(Video model)
        {
            _context.Videos.Add(model);
            await _context.SaveChangesAsync();
            return model;
        }

        public async Task<List<Video>> GetVideoAsync()
        {
            return await _context.Videos.ToListAsync();
        }

        public async Task<Video> GetVideoByIdAsync(int id)
        {
            return await _context.Videos.
                                        Where(v => v.Id == id).
                                        SingleOrDefaultAsync();
        }

        public async Task RemoveVideoAsync(int id)
        {
            var video = await _context.Videos.
                                        Where(v => v.Id == id).
                                        SingleOrDefaultAsync();
            if ( video != null)
            {
                _context.Videos.Remove(video);
                await _context.SaveChangesAsync();
            }
        }

        public async Task<Video> UpdateVideoAsync(Video model)
        {
            _context.Entry(model).State = EntityState.Modified;
            await _context.SaveChangesAsync();
            return model;
        }
    }
}

 

 

Startup.cs파일의 ConfigureServices메서드에 다음코드 추가하기. 

services.AddTransient<IVideoRepositoryAsync, VideoRepositoryEfCoreAsync>();
            
services.AddDbContext<VideoDbContext>(options =>
    options.UseSqlServer(
        Configuration.GetConnectionString("DefaultConnection")));