16# 프로시저에서 DataTable 대신 사용 방법.

C#에 있는 DataTable은 너무 많은 정보를 들고 있어 무겁기에 비슷하지만 더 가벼운 형식으로 구현.

MySqlTable Class

public class MySqlTable<T> 
    {
        public List<string> Columns
        {
            get;
            set;
        }
 
        public string AddColumn
        {
            set
            {
                if (Columns.Contains(value) == true)
                    new ArgumentException("이미 등록된 컬럼값 입니다.");
 
                Columns.Add(value);
            }
        }
 
        public List<List<T>> Rows
        {
            get;
            set;
        }
 
        public Int32 Count
        {
            get
            {
                return Rows.Count;
            }
        }
 
        public T this[int index]
        {
            get
            {
                return Rows[0][index];
            }
        }
 
        public T this[int row, int index]
        {
            get
            {
                return Rows[row][index];
            }
        }
 
        public T this[int row, string name]
        {
            get
            {
                Int32 index = Columns.FindIndex(t => t == name);
 
                if (index == -1)
                    new ArgumentException("해당 Column이 존재하지 않습니다.");
 
                return Rows[row][index];
            }
        }
 
        public MySqlTable()
        {
            Columns = new List<string>();
            Rows = new List<List<T>>();
        }
 
        public void CreateTable(IDataReader reader)
        {
            try
            {
                Columns = new List<string>(reader.FieldCount);
 
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    AddColumn = reader.GetName(i);
                }
 
                while (reader.Read())
                {
                    List<T> row = new List<T>(Columns.Count);
 
                    for (int i = 0; i < Columns.Count; i++)
                    {
                        if (reader.GetValue(i) == DBNull.Value)
                            row.Add(default(T));
                        else
                            row.Add((T)reader.GetValue(i));
                    }
 
                    Rows.Add(row);
                }
            }
            catch (Exception ex)
            {
                new DataException("CreateTable : " + ex.Message);
                //new ArgumentException("해당 Column이 존재하지 않습니다.");
            }
        }

MySqlManager Class

private static readonly Dictionary<string, object> emptyParameter = new Dictionary<string, object>(0);
public static MySqlTable<T> ExecuteMySqlTable<T>(string connectionString, string procedureName, out Int32 resultCode)
{
    return ExecuteMySqlTableImpl<T>(connectionString, procedureName, emptyParameter, out resultCode);
}
public static MySqlTable<T> ExecuteMySqlTable<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)
{
    return ExecuteMySqlTableImpl<T>(connectionString, procedureName, paramsList, out resultCode);
}
 
[MethodImpl(MethodImplOptions.AggressiveInlining)]
private static MySqlTable<T> ExecuteMySqlTableImpl<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)
{
    MySqlTable<T> mysqlTable = new MySqlTable<T>();
    resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;
 
    using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
    using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))
    {
        sqlCommand.CommandTimeout = ServiceConfig.REQUEST_DB_TIMEOUT_SECOND;
        sqlCommand.CommandType = CommandType.StoredProcedure;
 
        foreach (KeyValuePair<string, object> pair in paramsList)
        {
            sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);
        }
 
        try
        {
            sqlConnection.Open();
 
            using (IDataReader r = sqlCommand.ExecuteReader())
            {
                if (r.Read())
                    resultCode = (Int32)r.GetInt32(0);
 
                if(r.NextResult() == true)
                    mysqlTable.CreateTable(r);
            }
        }
        catch (Exception ex)
        {
            resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;
            WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error List<T> ExecuteReader - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));
        }
    }
 
    return mysqlTable;
}

사용 예.

Dictionary<string, object> paramList = new Dictionary<string, object>
{
    ["$user_key"] = requestPacket.UserKey,
    ["$uuid"] = requestPacket.UserUUID,
};
 
MySqlTable<object> readerList = MySqlManager.ExecuteMySqlTable<object>(ServiceConfig.db_ConnectionString, MySqlProcedureName.sp_UserStageScore, paramList, out Int32 resultCode);
responsePacket.ResultNumber = resultCode;