[C#/DAPPER] SqlMapper 클래스 : GetRowParser 확장 메소드를 사용해 제네릭 타입 데이터의 .CSV 파일 생성하기

TestProject.zip

0.51MB

▶ MainForm.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;

using DevExpress.Utils;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Grid;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 메인 폼
    /// </summary>
    public partial class MainForm : XtraForm
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 연결
        /// </summary>
        private OleDbConnection connection;

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - MainForm()

        /// <summary>
        /// 생성자
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            this.connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb");

            this.gridView.InitializeView
            (
                true,                           // Multi Select
                GridMultiSelectMode.CellSelect, // Grid Multi Select Mode
                DrawFocusRectStyle.CellFocus,   // Draw Focus Rect Style
                true,                           // Show Indicator
                true,                           // Show Column Headers
                true,                           // Allow Column Moving
                true,                           // Allow Column Resizing
                true,                           // Allow Filter
                true,                           // Allow Sort
                false,                          // Allow Cell Merge
                EditorShowMode.Default,         // Editor Show Mode
                false                           // Editable
            );

            this.gridView.OptionsBehavior.AutoPopulateColumns = true;

            this.gridControl.DataSource = Order.GetOrderList(this.connection);

            this.gridView.BestFitColumns();

            this.createButton.Click += createButton_Click;
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Private
        //////////////////////////////////////////////////////////////////////////////// Event

        #region 생성하기 버튼 클릭시 처리하기 - createButton_Click(sender, e)

        /// <summary>
        /// 생성하기 버튼 클릭시 처리하기
        /// </summary>
        /// <param name="sender">이벤트 발생자</param>
        /// <param name="e">이벤트 인자</param>
        private void createButton_Click(object sender, EventArgs e)
        {
            string sql = "SELECT * FROM Orders";

            WriteFile(this.connection, sql, null, 500, "D:\\", "Sample");
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////// Function

        #region 헤더 라인 쓰기 - WriteHeaderLine(writer, rowDictionary)

        /// <summary>
        /// 헤더 라인 쓰기
        /// </summary>
        /// <param name="writer">스트림 라이터</param>
        /// <param name="rowDictionary">행 딕셔너리</param>
        private void WriteHeaderLine(StreamWriter writer, IDictionary<string, object> rowDictionary)
        {
            int x = 0;

            foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)
            {
                if(x > 0)
                {
                    writer.Write(",");
                }

                if(x < rowDictionary.Count - 1)
                {
                    writer.Write(keyValuePair.Key);
                }
                else
                {
                    writer.WriteLine(keyValuePair.Key);
                }

                x++;
            }
        }

        #endregion
        #region 데이터 라인 쓰기 - WriteDataLine(writer, rowDictionary)

        /// <summary>
        /// 데이터 라인 쓰기
        /// </summary>
        /// <param name="writer">스트림 라이터</param>
        /// <param name="rowDictionary">행 딕셔너리</param>
        private void WriteDataLine(StreamWriter writer, IDictionary<string, object> rowDictionary)
        {
            int x = 0;

            foreach(KeyValuePair<string, object> keyValuePair in rowDictionary)
            {
                if(x > 0)
                {
                    writer.Write(",");
                }

                object valueObject = keyValuePair.Value;

                if(valueObject == null)
                {
                    #region 값 객체가 NULL인 경우 처리한다.

                    if(x < rowDictionary.Count - 1)
                    {
                        writer.Write(string.Empty);
                    }
                    else
                    {
                        writer.WriteLine(string.Empty);
                    }

                    #endregion
                }
                else
                {
                    Type valueObjectType = valueObject?.GetType();

                    if(valueObjectType == typeof(DateTime))
                    {
                        #region 값 객체 타입이 DateTime인 경우 처리한다.

                        DateTime valueDateTime = (DateTime)valueObject;

                        if(x < rowDictionary.Count - 1)
                        {
                            writer.Write(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else
                        {
                            writer.WriteLine(valueDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                        }

                        #endregion
                    }
                    else if(valueObjectType == typeof(DateTime?))
                    {
                        #region 값 객체 타입이 DateTime?인 경우 처리한다.

                        DateTime? valueDateTime = (DateTime?)valueObject;

                        if(valueDateTime.HasValue)
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else
                            {
                                writer.WriteLine(valueDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                        }
                        else
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(string.Empty);
                            }
                            else
                            {
                                writer.WriteLine(string.Empty);
                            }
                        }

                        #endregion
                    }
                    else if(valueObjectType == typeof(string))
                    {
                        #region 값 객체 타입이 string인 경우 처리한다.

                        string valueString = (string)valueObject;

                        if(valueString == null)
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write(string.Empty);
                            }
                            else
                            {
                                writer.WriteLine(string.Empty);
                            }
                        }
                        else
                        {
                            if(x < rowDictionary.Count - 1)
                            {
                                writer.Write($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");
                            }
                            else
                            {
                                writer.WriteLine($"\"{valueString.Replace("\r", string.Empty).Replace("\n", string.Empty)}\"");
                            }
                        }

                        #endregion
                    }
                    else
                    {
                        #region 값 객체 타입이 기타인 경우 처리한다.

                        if(x < rowDictionary.Count - 1)
                        {
                            writer.Write(valueObject);
                        }
                        else
                        {
                            writer.WriteLine(valueObject);
                        }

                        #endregion
                    }
                }

                x++;
            }
        }

        #endregion
        #region 파일 쓰기 - WriteFile(connection, sql, condition, lineCountPerFile, saveDirectoryPath, fileName)

        /// <summary>
        /// 파일 쓰기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="sql">SQL</param>
        /// <param name="condition">조건</param>
        /// <param name="lineCountPerFile">파일당 라인 수</param>
        /// <param name="saveDirectoryPath">저장 디렉토리 경로</param>
        /// <param name="fileName">파일 확장자가 없는 파일명</param>
        private void WriteFile(IDbConnection connection, string sql, object condition, int lineCountPerFile, string saveDirectoryPath, string fileName)
        {
            using(IDataReader reader = connection.ExecuteReader(sql, condition))
            {
                int lineCount = 0;

                StreamWriter writer = null;

                try
                {
                    while(reader.Read())
                    {
                        int rest = lineCount % lineCountPerFile;

                        if(rest == 0)
                        {
                            writer?.Dispose();

                            int sequence = lineCount / lineCountPerFile + 1;

                            string actualFileName = $"{fileName}_{sequence}.csv";

                            string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

                            writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);
                        }

                        var rowParser = reader.GetRowParser<dynamic>();

                        var row = rowParser(reader);

                        IDictionary<string, object> rowDictionary = row as IDictionary<string, object>;

                        if(rest == 0)
                        {
                            WriteHeaderLine(writer, rowDictionary);
                        }

                        WriteDataLine(writer, rowDictionary);

                        lineCount++;
                    }

                    if(lineCount == 0)
                    {
                        string actualFileName = $"{fileName}_1.csv";

                        string actualFilePath = Path.Combine(saveDirectoryPath, actualFileName);

                        writer = new StreamWriter(actualFilePath, false, Encoding.UTF8);
                    }
                }
                finally
                {
                    writer?.Dispose();
                }
            }
        }

        #endregion
    }
}