■ 연결 문자열 설정을 사용해 대량 데이터 추가시 속도를 향상시키는 방법을 보여준다.
▶ TestModel.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
namespace TestProject { /// <summary> /// 테스트 모델 /// </summary> public class TestModel { //////////////////////////////////////////////////////////////////////////////////////////////////// Property ////////////////////////////////////////////////////////////////////////////////////////// Public #region 컬럼 01 - COLUMN01 /// <summary> /// 컬럼 01 /// </summary> public string COLUMN01 { get; set; } #endregion #region 컬럼 02 - COLUMN02 /// <summary> /// 컬럼 02 /// </summary> public string COLUMN02 { get; set; } #endregion #region 컬럼 03 - COLUMN03 /// <summary> /// 컬럼 03 /// </summary> public string COLUMN03 { get; set; } #endregion #region 컬럼 04 - COLUMN04 /// <summary> /// 컬럼 04 /// </summary> public string COLUMN04 { get; set; } #endregion #region 컬럼 05 - COLUMN05 /// <summary> /// 컬럼 05 /// </summary> public string COLUMN05 { get; set; } #endregion #region 컬럼 06 - COLUMN06 /// <summary> /// 컬럼 06 /// </summary> public string COLUMN06 { get; set; } #endregion #region 컬럼 07 - COLUMN07 /// <summary> /// 컬럼 07 /// </summary> public string COLUMN07 { get; set; } #endregion #region 컬럼 08 - COLUMN08 /// <summary> /// 컬럼 08 /// </summary> public string COLUMN08 { get; set; } #endregion #region 컬럼 09 - COLUMN09 /// <summary> /// 컬럼 09 /// </summary> public string COLUMN09 { get; set; } #endregion #region 컬럼 10 - COLUMN10 /// <summary> /// 컬럼 10 /// </summary> public string COLUMN10 { get; set; } #endregion } } |
▶ RandomStringHelper.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
using System; using System.Text; namespace TestProject { /// <summary> /// 임의 문자열 헬퍼 /// </summary> public static class RandomStringHelper { //////////////////////////////////////////////////////////////////////////////////////////////////// Field ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Public #region Field /// <summary> /// 숫자만 /// </summary> public static readonly string OnlyDigit = "9"; /// <summary> /// 소문자만 /// </summary> public static readonly string OnlyLowerCharacter = "z"; /// <summary> /// 대문자만 /// </summary> public static readonly string OnlyUpperCharacter = "Z"; /// <summary> /// 숫자와 대문자 /// </summary> public static readonly string DigitANdUpperCharacter = "9Z"; /// <summary> /// 숫자와 소문자 /// </summary> public static readonly string DigitAndLowerCharacter = "9z"; /// <summary> /// 모든 문자 /// </summary> public static readonly string AllCharacter = "9Zz"; #endregion //////////////////////////////////////////////////////////////////////////////////////////////////// Method ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Public #region 임의 문자열 구하기 - GetRandomString(length, randomType) /// <summary> /// 임의 문자열 구하기 /// </summary> /// <param name="length">길이</param> /// <param name="randomType">임의 타입</param> /// <returns>임의 문자열</returns> public static string GetRandomString(int length, string randomType = "9") { StringBuilder result = new StringBuilder(); int digitStart = 48; int digitEnd = 57; int upperCharacterStart = 65; int upperCharacterEnd = 90; int lowerCharacterStart = 97; int lowerCharacterEnd = 122; int start = 0; int end = 0; Random random = new Random(DateTime.Now.Millisecond); for(int i = 1; i <= length; i++) { string nextRandomType = randomType; if(randomType.Length >= 2) { Random _ = new Random(DateTime.Now.Millisecond); int next = 1; switch(randomType.Length) { case 2 : next = random.Next(1, 3); if(next == 2) { if(randomType == "9Z") { next = 3; } else if(randomType == "9z") { next = 2; } } break; case 3 : next = random.Next(1, 4); break; } switch(next) { case 1 : nextRandomType = "9"; break; case 2 : nextRandomType = "z"; break; case 3 : nextRandomType = "Z"; break; } } switch(nextRandomType) { case "Z" : start = upperCharacterStart; end = upperCharacterEnd; break; case "z" : start = lowerCharacterStart; end = lowerCharacterEnd; break; case "9" : start = digitStart; end = digitEnd; break; } result.Append(char.ConvertFromUtf32(random.Next(start, end + 1))); } return result.ToString(); } #endregion } } |
▶ Program.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
using System; using System.Collections.Generic; using System.Data.SQLite; using System.Diagnostics; using System.IO; using Dapper; namespace TestProject { /// <summary> /// 프로그램 /// </summary> class Program { //////////////////////////////////////////////////////////////////////////////////////////////////// Field ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Private #region Field /// <summary> /// 테이블 생성 SQL /// </summary> private static string _createTableSQL = @" CREATE TABLE test ( COLUMN01 TEXT ,COLUMN02 TEXT ,COLUMN03 TEXT ,COLUMN04 TEXT ,COLUMN05 TEXT ,COLUMN06 TEXT ,COLUMN07 TEXT ,COLUMN08 TEXT ,COLUMN09 TEXT ,COLUMN10 TEXT ); "; /// <summary> /// 삽입 SQL /// </summary> private static string _insertSQL = @" INSERT INTO Test ( COLUMN01 ,COLUMN02 ,COLUMN03 ,COLUMN04 ,COLUMN05 ,COLUMN06 ,COLUMN07 ,COLUMN08 ,COLUMN09 ,COLUMN10 ) VALUES ( @COLUMN01 ,@COLUMN02 ,@COLUMN03 ,@COLUMN04 ,@COLUMN05 ,@COLUMN06 ,@COLUMN07 ,@COLUMN08 ,@COLUMN09 ,@COLUMN10 ) "; #endregion //////////////////////////////////////////////////////////////////////////////////////////////////// Method ////////////////////////////////////////////////////////////////////////////////////////// Static //////////////////////////////////////////////////////////////////////////////// Private #region 로그 쓰기 - WriteLog(source) /// <summary> /// 로그 쓰기 /// </summary> /// <param name="source">소스 문자열</param> private static void WriteLog(string source) { Console.WriteLine($"[{DateTime.Now:HH:mm:ss}] {source}"); } #endregion #region 프로그램 시작하기 - Main() /// <summary> /// 프로그램 시작하기 /// </summary> private static void Main() { string filePath = "test.db3"; if(File.Exists(filePath)) { File.SetAttributes(filePath, FileAttributes.Normal); File.Delete(filePath); } WriteLog("추가할 데이터 생성을 시작합니다."); List<TestModel> sourceList = new List<TestModel>(); for(int i = 0; i < 100_000; i++) { TestModel test = new() { COLUMN01 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN02 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN03 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN04 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN05 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN06 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN07 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN08 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN09 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter), COLUMN10 = RandomStringHelper.GetRandomString(10, RandomStringHelper.AllCharacter) }; sourceList.Add(test); } WriteLog("추가할 데이터 생성을 종료합니다."); string connectionString = $"Data Source={filePath};Version=3;Synchronous=OFF;Journal Mode=OFF;"; using(SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Execute(_createTableSQL); WriteLog($"데이터 추가를 시작합니다 : {sourceList.Count:#,##0}건"); Stopwatch watch = new Stopwatch(); watch.Start(); connection.Execute(_insertSQL, sourceList); watch.Stop(); WriteLog("데이터 추가를 종료합니다."); WriteLog($"데이터 추가 경과 시간 : {watch.Elapsed}"); } } #endregion } } |
