MSJO.kr- appsettings.json
- Student.cs (모델)
- Global.cs
- IStudentService
- StudentService.cs
- HomeController.cs
- Startup.cs
- Reference
대용량의 데이터를 한 번에 입력, 수정, 삭제할 때 대부분의 개발자는 ‘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.SqlServer와 RepoDb.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?}");
});
}
}
}