logo MSJO.kr

CRUD using BULK Operation in ASP.NET

2020-08-19
MsJ
   

대용량의 데이터를 한 번에 입력, 수정, 삭제할 때 대부분의 개발자는 ‘for’와 같은 루프 문을 사용하여 이를 처리하는 데 처리할 ‘row’의 개수만큼 operation이 발생하므로 RDBMS가 SQL Server의 경우 Bulk Operation 기법을 사용하여 한 번에 처리하도록 해야 한다. 또는 Table 변수를 사용하여 한 번에 모든 데이틀 넘겨주고 데이터베이스에서는 이를 한 번의 로직으로 처리하도록 유도해야 한다(ASP.NET MVC, Delete Multiple Rows With Checkbox).

소스 예제는 ‘Thumb IKR - Programming Examples’의 유튜브 강좌를 참고하였고 일부 소스는 수정하였다. VS2019에서 ASP.NET Core 웹 애플리케이션(MVC) 프로젝트를 생성하고 기본값을 그대로 사용하였으며 변경된 부분의 코드 위주로 아래에 소소를 나열하였다. 추가한 nuget package는 RepoDb.SqlServerRepoDb.SqlServer.BulkOperations이다(repodb.net).

appsettings.json
{
  "ConnectionStrings": {
    "testdb": "Server=아이피주소;Database=디비;User Id=아이디;Password=패스워드;"
  }
}
Student.cs (모델)
namespace BulkCRUD.Models
{
    public class Student
    {
        public int StudentID { get; set; }
        public string Name { get; set; }
        public string Roll { get; set; }
    }
}
Global.cs
namespace BulkCRUD.Common
{
    public static class Global
    {
        public static string ConnectionString { get; set; }
    }
}

IStudentService
using BulkCRUD.Models;
using System.Collections.Generic;

namespace BulkCRUD.IService
{
    public interface IStudentService
    {
        string BulkSave(List<Student> oStudents);
        string BulkUpdate(List<Student> oStudents);
        string BulkDelete(List<Student> oStudents);
        string BulkMerge(List<Student> oStudents);
    }
}
StudentService.cs
using BulkCRUD.IService;
using BulkCRUD.Models;
using System.Collections.Generic;
using BulkCRUD.Common;
using Microsoft.Data.SqlClient;
using RepoDb;

namespace BulkCRUD.Service
{
    public class StudentService : IStudentService
    {
        public string BulkSave(List<Student> oStudents)
        {
            using var connenction = new SqlConnection(Global.ConnectionString);
            var totalRows = connenction.BulkInsert(oStudents);
            return totalRows > 0 ? "Saved" : "Failed";
        }

        public string BulkUpdate(List<Student> oStudents)
        {
            using var connenction = new SqlConnection(Global.ConnectionString);
            var totalRows = connenction.BulkUpdate(oStudents);
            return totalRows > 0 ? "Updated" : "Failed";
        }

        public string BulkDelete(List<Student> oStudents)
        {
            using var connenction = new SqlConnection(Global.ConnectionString);
            var totalRows = connenction.BulkDelete(oStudents);
            return totalRows > 0 ? "Deleted" : "Failed";
        }

        public string BulkMerge(List<Student> oStudents)
        {
            using var connenction = new SqlConnection(Global.ConnectionString);
            var totalRows = connenction.BulkMerge(oStudents);
            return totalRows > 0 ? "Merged" : "Failed";
        }
    }
}
HomeController.cs
using BulkCRUD.IService;
using BulkCRUD.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;

namespace BulkCRUD.Controllers
{
    public class HomeController : Controller
    {
        private readonly IStudentService _studentService;

        public HomeController(IStudentService studentService)
        {
            _studentService = studentService;
        }

        public IActionResult Index()
        {
            var message = BulkSave();
            //BulkUpdate();
            //BulkMerge();
            //BulkDelete();

            ViewData["Message"] = message;
            return View();
        }

        private string BulkSave()
        {
            var oStudents = new List<Student>();
            const int nTotalStudent = 2000;

            for (var i = 1; i <= nTotalStudent; i++)
            {
                oStudents.Add(new Student()
                {
                    StudentID = i,
                    Name = "학생-" + i,
                    Roll = "Roll-" + i
                });
            }

            var message = _studentService.BulkSave(oStudents);
            return message;
        }

        //private string BulkUpdate()
        //{
        //    var oStudents = new List<Student>
        //    {
        //        new Student() {StudentID = 3, Name = "홍길동", Roll = "11111"},
        //        new Student() {StudentID = 4, Name = "홍길서", Roll = "22222"},
        //        new Student() {StudentID = 5, Name = "홍길남", Roll = "33333"},
        //        new Student() {StudentID = 6, Name = "홍길북", Roll = "44444"}
        //    };

        //    var message = _studentService.BulkUpdate(oStudents);
        //    return message;
        //}

        //private string BulkDelete()
        //{
        //    var oStudents = new List<Student>
        //    {
        //        new Student() {StudentID = 3},
        //        new Student() {StudentID = 4},
        //        new Student() {StudentID = 5},
        //        new Student() {StudentID = 2}
        //    };

        //    var message = _studentService.BulkDelete(oStudents);
        //    return message;
        //}

        //private string BulkMerge()
        //{
        //    var oStudents = new List<Student>
        //    {
        //        new Student() {StudentID = 3, Name = "홍길동1", Roll = "A11111"},
        //        new Student() {StudentID = 4, Name = "홍길서2", Roll = "B22222"},
        //        new Student() {StudentID = 5, Name = "홍길남3", Roll = "C33333"},
        //        new Student() {StudentID = 6, Name = "홍길북4", Roll = "D44444"}
        //    };

        //    var message = _studentService.BulkMerge(oStudents);
        //    return message;
        //}
    }
}
Startup.cs
using BulkCRUD.Common;
using BulkCRUD.IService;
using BulkCRUD.Service;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace BulkCRUD
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();

            Global.ConnectionString = Configuration.GetConnectionString("testdb");
            services.AddScoped<IStudentService, StudentService>();
            RepoDb.SqlServerBootstrap.Initialize();
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}
Reference

Prεv   Nεxt
Content
Search     RSS Feed     BY-NC-ND