Oracle DataReader Util (edit)

• DataReader
• IsDBNull & DBNull.Value
• Reflection

c# - Reflection - get attribute name and value on property - Stack Overflow

c# - Null safe way to get values from an IDataReader - Stack Overflow

Code Sample

// NuGet Package Name : Oracle.ManagedDataAccess.Client
// NuGet Package Version : 19.11.0
// Descrption : ODP.NET, Managed Driver is a 100% native code .NET Framework driver for Oracle Database. No additional Oracle Client software is required to be installed to connect to Oracle Database.
// File version : 4.122.19.1
// Folder : \packages\Oracle.ManagedDataAccess.19.11.0\lib\net40\Oracle.ManagedDataAccess.dll

using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;

namespace OracleDataReaderUtil
{
 internal class Program
 {
 private static string OracleConnString = "DATA SOURCE=192.168.0.1/ORCL;USER ID=SYS;PASSWORD=123456;PERSIST SECURITY INFO=True;";

 private static void Main(string[] args)
 {
 ProcessOracleDbDataReader();

 Console.Write("Press any key to exit...");
 Console.ReadLine();
 }

 private static void ProcessOracleDbDataReader()
 {
 var iRowCount = 0;

 var stopwatch = new Stopwatch();

 var dto = new MyDto();

 var sbSummary = new StringBuilder();
 var sbGenDtoClass = new StringBuilder();
 var sbAssignStatement = new StringBuilder();

 var sql = "SELECT \"Extent1\".\"PHASE_SCENARIO_ID\" AS \"PHASE_SCENARIO_ID\", \"Extent1\".\"SID\" AS \"SID\", \"Extent1\".\"ORDER_CODE\" AS \"ORDER_CODE\", \"Extent1\".\"COMPANY_SID\" AS \"COMPANY_SID\", \"Extent1\".\"PERIOD_START_DATE\" AS \"PERIOD_START_DATE\", \"Extent1\".\"PERIOD_END_DATE\" AS \"PERIOD_END_DATE\", \"Extent1\".\"CONSOLIDATION_STATUS\" AS \"CONSOLIDATION_STATUS\", \"Extent1\".\"DOC_ACCESS_CONTROL_FLAG\" AS \"DOC_ACCESS_CONTROL_FLAG\", \"Extent1\".\"FINANCE_INSPECTION_ALLOW\" AS \"FINANCE_INSPECTION_ALLOW\", \"Extent1\".\"PRICING_AVALABLE\" AS \"PRICING_AVALABLE\", \"Extent1\".\"BPO_ROLE_ID\" AS \"BPO_ROLE_ID\", \"Extent1\".\"ENGLISH_FONT_FLG\" AS \"ENGLISH_FONT_FLG\", \"Extent1\".\"ENGLISH_FONT_FAMILY_NAME\" AS \"ENGLISH_FONT_FAMILY_NAME\", \"Extent1\".\"FRONTLOAD_EXECUTION_STATUS\" AS \"FRONTLOAD_EXECUTION_STATUS\", \"Extent1\".\"DESCRIPTION\" AS \"DESCRIPTION\", \"Extent1\".\"USER_COMMENT\" AS \"USER_COMMENT\", \"Extent1\".\"UPDATE_DATE\" AS \"UPDATE_DATE\", \"Extent1\".\"UPDATE_USER_SID\" AS \"UPDATE_USER_SID\", \"Extent1\".\"ENTRY_DATE\" AS \"ENTRY_DATE\", \"Extent1\".\"ENTRY_USER_SID\" AS \"ENTRY_USER_SID\", \"Extent1\".\"PERIOD\" AS \"PERIOD\", \"Extent1\".\"ACCESS_USER_SET_FLAG\" AS \"ACCESS_USER_SET_FLAG\", \"Extent1\".\"EDINET_CODE\" AS \"EDINET_CODE\", \"Extent1\".\"FISCAL_END_DATE\" AS \"FISCAL_END_DATE\", \"Extent2\".\"SID\" AS \"SID1\", \"Extent2\".\"ORDER_SID\" AS \"ORDER_SID\", \"Extent2\".\"JOB_TYPE\" AS \"JOB_TYPE\", \"Extent2\".\"REVISION_NUMBER\" AS \"REVISION_NUMBER\", \"Extent2\".\"UPDATE_DATE\" AS \"UPDATE_DATE1\", \"Extent2\".\"UPDATE_USER_SID\" AS \"UPDATE_USER_SID1\", \"Extent2\".\"ENTRY_DATE\" AS \"ENTRY_DATE1\", \"Extent2\".\"ENTRY_USER_SID\" AS \"ENTRY_USER_SID1\", \"Extent3\".\"SID\" AS \"SID2\", \"Extent3\".\"JOB_SID\" AS \"JOB_SID\", \"Extent3\".\"IDENTIFICATION\" AS \"IDENTIFICATION\", \"Extent3\".\"EDIT_AUTHORITY\" AS \"EDIT_AUTHORITY\", \"Extent3\".\"CHECKOUT_DATE\" AS \"CHECKOUT_DATE\", \"Extent3\".\"CHECKOUT_USER_SID\" AS \"CHECKOUT_USER_SID\", \"Extent3\".\"UPDATE_DATE\" AS \"UPDATE_DATE2\", \"Extent3\".\"UPDATE_USER_SID\" AS \"UPDATE_USER_SID2\", \"Extent3\".\"ENTRY_DATE\" AS \"ENTRY_DATE2\", \"Extent3\".\"ENTRY_USER_SID\" AS \"ENTRY_USER_SID2\", \"Extent3\".\"EDIT_AUTHORITY_LOCK_FLAG\" AS \"EDIT_AUTHORITY_LOCK_FLAG\", \"Extent3\".\"DISPLAY_UPDATE_DATE\" AS \"DISPLAY_UPDATE_DATE\", \"Extent3\".\"DISPLAY_UPDATE_USER_SID\" AS \"DISPLAY_UPDATE_USER_SID\", \"Extent4\".\"SID\" AS \"SID3\", \"Extent4\".\"DOC_SID\" AS \"DOC_SID\", \"Extent4\".\"TITLE\" AS \"TITLE\", \"Extent4\".\"INDEX_TYPE\" AS \"INDEX_TYPE\", \"Extent4\".\"AUDIT_TYPE\" AS \"AUDIT_TYPE\", \"Extent4\".\"PARENT_INDEX_SID\" AS \"PARENT_INDEX_SID\", \"Extent4\".\"DISPLAY_ORDER\" AS \"DISPLAY_ORDER\", \"Extent4\".\"DISPLAY_LEVEL\" AS \"DISPLAY_LEVEL\", \"Extent4\".\"FILE_DIV_FLAG\" AS \"FILE_DIV_FLAG\", \"Extent4\".\"OUTPUT_FLAG\" AS \"OUTPUT_FLAG\", \"Extent4\".\"ACCOMPANIMENT_FLAG\" AS \"ACCOMPANIMENT_FLAG\", \"Extent4\".\"UPDATE_DATE\" AS \"UPDATE_DATE3\", \"Extent4\".\"UPDATE_USER_SID\" AS \"UPDATE_USER_SID3\", \"Extent4\".\"ENTRY_DATE\" AS \"ENTRY_DATE3\", \"Extent4\".\"ENTRY_USER_SID\" AS \"ENTRY_USER_SID3\", \"Extent4\".\"INDEXNUM_FLAG\" AS \"INDEXNUM_FLAG\", \"Extent4\".\"LENTICULARBRACKETS_FLAG\" AS \"LENTICULARBRACKETS_FLAG\" FROM \"OM_ORDER_INFO\" \"Extent1\" LEFT OUTER JOIN \"OM_JOB_INFO\" \"Extent2\" ON \"Extent1\".\"SID\" = \"Extent2\".\"ORDER_SID\" LEFT OUTER JOIN \"OM_DOCUMENT_INFO\" \"Extent3\" ON \"Extent2\".\"SID\" = \"Extent3\".\"JOB_SID\" LEFT OUTER JOIN \"DC_INDEX_INFO\" \"Extent4\" ON \"Extent3\".\"SID\" = \"Extent4\".\"DOC_SID\" WHERE (\"Extent3\".\"SID\" = :p__linq__0)";

 using (var conn = new OracleConnection(OracleConnString))
 {
 conn.Open();

 using (var cmd = new OracleCommand(sql, conn))
 {
 var param = cmd.CreateParameter();
 param.ParameterName = ":p__linq__0";
 param.Value = 4879975843;
 param.DbType = DbType.Int64;
 cmd.Parameters.Add(param);

 using (var reader = cmd.ExecuteReader())
 {
 stopwatch.Start();
 DataTable schemaTable = reader.GetSchemaTable();
 stopwatch.Stop();
 sbSummary.AppendLine($"{nameof(reader.GetSchemaTable)} takes {stopwatch.ElapsedMilliseconds} miliseconds.");
 stopwatch.Reset();

 Debug.WriteLine(schemaTable.ToString());

 //Gets a customized view of the table that may include a filtered view, or a cursor position.
 DataView defaultView = reader.GetSchemaTable().DefaultView;

 Debug.WriteLine(defaultView.ToString());

 stopwatch.Start();
 var colNames = schemaTable.Rows.Cast<DataRow>().Select(row => row["ColumnName"] as string).ToArray();
 stopwatch.Stop();
 sbSummary.AppendLine($"schemaTable.Rows.Cast..ColumnName takes {stopwatch.ElapsedMilliseconds} miliseconds.");
 stopwatch.Reset();

 stopwatch.Start();
 var colDataTypes = schemaTable.Rows.Cast<DataRow>().Select(row => (row["DataType"] as Type).Name).ToArray();
 sbSummary.AppendLine($"schemaTable.Rows.Cast..DataType takes {stopwatch.ElapsedMilliseconds} miliseconds.");
 stopwatch.Stop();
 stopwatch.Reset();

 stopwatch.Start();
 sbGenDtoClass.AppendLine($"public class MyDto");
 sbGenDtoClass.AppendLine("{");
 for (int j = 0; j < colNames.Length; j++)
 {
 //Generate Dto class
 sbGenDtoClass.AppendLine($"\tpublic {colDataTypes[j]} {colNames[j]} " + "{ get; set; }");

 //Generate Assign Statement
 sbAssignStatement.AppendLine($"if(colNames.Contains(\"{colNames[j]}\") && !reader.IsDBNull(\"{colNames[j]}\"))");
 sbAssignStatement.AppendLine("{");
 sbAssignStatement.AppendLine($"\tdto.{colNames[j]} = reader.GetValueOrDefault<{colDataTypes[j]}>(\"{colNames[j]}\");");
 sbAssignStatement.AppendLine("}");
 }
 sbGenDtoClass.AppendLine("}");
 stopwatch.Stop();
 sbSummary.AppendLine($"Build Dto class & Assign statement takes {stopwatch.ElapsedMilliseconds} miliseconds.");
 stopwatch.Reset();

 stopwatch.Start();
 while (reader.Read())
 {
 if (colNames.Contains("PHASE_SCENARIO_ID") && !reader.IsDBNull("PHASE_SCENARIO_ID"))
 {
 dto.PHASE_SCENARIO_ID = reader.GetValueOrDefault<Int32>("PHASE_SCENARIO_ID");
 }
 if (colNames.Contains("SID") && !reader.IsDBNull("SID"))
 {
 dto.SID = reader.GetValueOrDefault<Int64>("SID");
 }
 if (colNames.Contains("ORDER_CODE") && !reader.IsDBNull("ORDER_CODE"))
 {
 dto.ORDER_CODE = reader.GetValueOrDefault<String>("ORDER_CODE");
 }
 if (colNames.Contains("COMPANY_SID") && !reader.IsDBNull("COMPANY_SID"))
 {
 dto.COMPANY_SID = reader.GetValueOrDefault<Int64>("COMPANY_SID");
 }
 if (colNames.Contains("PERIOD_START_DATE") && !reader.IsDBNull("PERIOD_START_DATE"))
 {
 dto.PERIOD_START_DATE = reader.GetValueOrDefault<DateTime>("PERIOD_START_DATE");
 }
 if (colNames.Contains("PERIOD_END_DATE") && !reader.IsDBNull("PERIOD_END_DATE"))
 {
 dto.PERIOD_END_DATE = reader.GetValueOrDefault<DateTime>("PERIOD_END_DATE");
 }
 if (colNames.Contains("CONSOLIDATION_STATUS") && !reader.IsDBNull("CONSOLIDATION_STATUS"))
 {
 dto.CONSOLIDATION_STATUS = reader.GetValueOrDefault<Int32>("CONSOLIDATION_STATUS");
 }
 if (colNames.Contains("DOC_ACCESS_CONTROL_FLAG") && !reader.IsDBNull("DOC_ACCESS_CONTROL_FLAG"))
 {
 dto.DOC_ACCESS_CONTROL_FLAG = reader.GetValueOrDefault<Int16>("DOC_ACCESS_CONTROL_FLAG");
 }
 if (colNames.Contains("FINANCE_INSPECTION_ALLOW") && !reader.IsDBNull("FINANCE_INSPECTION_ALLOW"))
 {
 dto.FINANCE_INSPECTION_ALLOW = reader.GetValueOrDefault<Int16>("FINANCE_INSPECTION_ALLOW");
 }
 if (colNames.Contains("PRICING_AVALABLE") && !reader.IsDBNull("PRICING_AVALABLE"))
 {
 dto.PRICING_AVALABLE = reader.GetValueOrDefault<Int16>("PRICING_AVALABLE");
 }
 if (colNames.Contains("BPO_ROLE_ID") && !reader.IsDBNull("BPO_ROLE_ID"))
 {
 dto.BPO_ROLE_ID = reader.GetValueOrDefault<Int32>("BPO_ROLE_ID");
 }
 if (colNames.Contains("ENGLISH_FONT_FLG") && !reader.IsDBNull("ENGLISH_FONT_FLG"))
 {
 dto.ENGLISH_FONT_FLG = reader.GetValueOrDefault<Int16>("ENGLISH_FONT_FLG");
 }
 if (colNames.Contains("ENGLISH_FONT_FAMILY_NAME") && !reader.IsDBNull("ENGLISH_FONT_FAMILY_NAME"))
 {
 dto.ENGLISH_FONT_FAMILY_NAME = reader.GetValueOrDefault<String>("ENGLISH_FONT_FAMILY_NAME");
 }
 if (colNames.Contains("FRONTLOAD_EXECUTION_STATUS") && !reader.IsDBNull("FRONTLOAD_EXECUTION_STATUS"))
 {
 dto.FRONTLOAD_EXECUTION_STATUS = reader.GetValueOrDefault<Int16>("FRONTLOAD_EXECUTION_STATUS");
 }
 if (colNames.Contains("DESCRIPTION") && !reader.IsDBNull("DESCRIPTION"))
 {
 dto.DESCRIPTION = reader.GetValueOrDefault<String>("DESCRIPTION");
 }
 if (colNames.Contains("USER_COMMENT") && !reader.IsDBNull("USER_COMMENT"))
 {
 dto.USER_COMMENT = reader.GetValueOrDefault<String>("USER_COMMENT");
 }
 if (colNames.Contains("UPDATE_DATE") && !reader.IsDBNull("UPDATE_DATE"))
 {
 dto.UPDATE_DATE = reader.GetValueOrDefault<DateTime>("UPDATE_DATE");
 }
 if (colNames.Contains("UPDATE_USER_SID") && !reader.IsDBNull("UPDATE_USER_SID"))
 {
 dto.UPDATE_USER_SID = reader.GetValueOrDefault<Int64>("UPDATE_USER_SID");
 }
 if (colNames.Contains("ENTRY_DATE") && !reader.IsDBNull("ENTRY_DATE"))
 {
 dto.ENTRY_DATE = reader.GetValueOrDefault<DateTime>("ENTRY_DATE");
 }
 if (colNames.Contains("ENTRY_USER_SID") && !reader.IsDBNull("ENTRY_USER_SID"))
 {
 dto.ENTRY_USER_SID = reader.GetValueOrDefault<Int64>("ENTRY_USER_SID");
 }
 if (colNames.Contains("PERIOD") && !reader.IsDBNull("PERIOD"))
 {
 dto.PERIOD = reader.GetValueOrDefault<String>("PERIOD");
 }
 if (colNames.Contains("ACCESS_USER_SET_FLAG") && !reader.IsDBNull("ACCESS_USER_SET_FLAG"))
 {
 dto.ACCESS_USER_SET_FLAG = reader.GetValueOrDefault<Int16>("ACCESS_USER_SET_FLAG");
 }
 if (colNames.Contains("EDINET_CODE") && !reader.IsDBNull("EDINET_CODE"))
 {
 dto.EDINET_CODE = reader.GetValueOrDefault<String>("EDINET_CODE");
 }
 if (colNames.Contains("FISCAL_END_DATE") && !reader.IsDBNull("FISCAL_END_DATE"))
 {
 dto.FISCAL_END_DATE = reader.GetValueOrDefault<DateTime>("FISCAL_END_DATE");
 }
 if (colNames.Contains("SID1") && !reader.IsDBNull("SID1"))
 {
 dto.SID1 = reader.GetValueOrDefault<Int64>("SID1");
 }
 if (colNames.Contains("ORDER_SID") && !reader.IsDBNull("ORDER_SID"))
 {
 dto.ORDER_SID = reader.GetValueOrDefault<Int64>("ORDER_SID");
 }
 if (colNames.Contains("JOB_TYPE") && !reader.IsDBNull("JOB_TYPE"))
 {
 dto.JOB_TYPE = reader.GetValueOrDefault<String>("JOB_TYPE");
 }
 if (colNames.Contains("REVISION_NUMBER") && !reader.IsDBNull("REVISION_NUMBER"))
 {
 dto.REVISION_NUMBER = reader.GetValueOrDefault<Int32>("REVISION_NUMBER");
 }
 if (colNames.Contains("UPDATE_DATE1") && !reader.IsDBNull("UPDATE_DATE1"))
 {
 dto.UPDATE_DATE1 = reader.GetValueOrDefault<DateTime>("UPDATE_DATE1");
 }
 if (colNames.Contains("UPDATE_USER_SID1") && !reader.IsDBNull("UPDATE_USER_SID1"))
 {
 dto.UPDATE_USER_SID1 = reader.GetValueOrDefault<Int64>("UPDATE_USER_SID1");
 }
 if (colNames.Contains("ENTRY_DATE1") && !reader.IsDBNull("ENTRY_DATE1"))
 {
 dto.ENTRY_DATE1 = reader.GetValueOrDefault<DateTime>("ENTRY_DATE1");
 }
 if (colNames.Contains("ENTRY_USER_SID1") && !reader.IsDBNull("ENTRY_USER_SID1"))
 {
 dto.ENTRY_USER_SID1 = reader.GetValueOrDefault<Int64>("ENTRY_USER_SID1");
 }
 if (colNames.Contains("SID2") && !reader.IsDBNull("SID2"))
 {
 dto.SID2 = reader.GetValueOrDefault<Int64>("SID2");
 }
 if (colNames.Contains("JOB_SID") && !reader.IsDBNull("JOB_SID"))
 {
 dto.JOB_SID = reader.GetValueOrDefault<Int64>("JOB_SID");
 }
 if (colNames.Contains("IDENTIFICATION") && !reader.IsDBNull("IDENTIFICATION"))
 {
 dto.IDENTIFICATION = reader.GetValueOrDefault<String>("IDENTIFICATION");
 }
 if (colNames.Contains("EDIT_AUTHORITY") && !reader.IsDBNull("EDIT_AUTHORITY"))
 {
 dto.EDIT_AUTHORITY = reader.GetValueOrDefault<Int64>("EDIT_AUTHORITY");
 }
 if (colNames.Contains("CHECKOUT_DATE") && !reader.IsDBNull("CHECKOUT_DATE"))
 {
 dto.CHECKOUT_DATE = reader.GetValueOrDefault<DateTime>("CHECKOUT_DATE");
 }
 if (colNames.Contains("CHECKOUT_USER_SID") && !reader.IsDBNull("CHECKOUT_USER_SID"))
 {
 dto.CHECKOUT_USER_SID = reader.GetValueOrDefault<Int64>("CHECKOUT_USER_SID");
 }
 if (colNames.Contains("UPDATE_DATE2") && !reader.IsDBNull("UPDATE_DATE2"))
 {
 dto.UPDATE_DATE2 = reader.GetValueOrDefault<DateTime>("UPDATE_DATE2");
 }
 if (colNames.Contains("UPDATE_USER_SID2") && !reader.IsDBNull("UPDATE_USER_SID2"))
 {
 dto.UPDATE_USER_SID2 = reader.GetValueOrDefault<Int64>("UPDATE_USER_SID2");
 }
 if (colNames.Contains("ENTRY_DATE2") && !reader.IsDBNull("ENTRY_DATE2"))
 {
 dto.ENTRY_DATE2 = reader.GetValueOrDefault<DateTime>("ENTRY_DATE2");
 }
 if (colNames.Contains("ENTRY_USER_SID2") && !reader.IsDBNull("ENTRY_USER_SID2"))
 {
 dto.ENTRY_USER_SID2 = reader.GetValueOrDefault<Int64>("ENTRY_USER_SID2");
 }
 if (colNames.Contains("EDIT_AUTHORITY_LOCK_FLAG") && !reader.IsDBNull("EDIT_AUTHORITY_LOCK_FLAG"))
 {
 dto.EDIT_AUTHORITY_LOCK_FLAG = reader.GetValueOrDefault<Int16>("EDIT_AUTHORITY_LOCK_FLAG");
 }
 if (colNames.Contains("DISPLAY_UPDATE_DATE") && !reader.IsDBNull("DISPLAY_UPDATE_DATE"))
 {
 dto.DISPLAY_UPDATE_DATE = reader.GetValueOrDefault<DateTime>("DISPLAY_UPDATE_DATE");
 }
 if (colNames.Contains("DISPLAY_UPDATE_USER_SID") && !reader.IsDBNull("DISPLAY_UPDATE_USER_SID"))
 {
 dto.DISPLAY_UPDATE_USER_SID = reader.GetValueOrDefault<Int64>("DISPLAY_UPDATE_USER_SID");
 }
 if (colNames.Contains("SID3") && !reader.IsDBNull("SID3"))
 {
 dto.SID3 = reader.GetValueOrDefault<Int64>("SID3");
 }
 if (colNames.Contains("DOC_SID") && !reader.IsDBNull("DOC_SID"))
 {
 dto.DOC_SID = reader.GetValueOrDefault<Int64>("DOC_SID");
 }
 if (colNames.Contains("TITLE") && !reader.IsDBNull("TITLE"))
 {
 dto.TITLE = reader.GetValueOrDefault<String>("TITLE");
 }
 if (colNames.Contains("INDEX_TYPE") && !reader.IsDBNull("INDEX_TYPE"))
 {
 dto.INDEX_TYPE = reader.GetValueOrDefault<Int32>("INDEX_TYPE");
 }
 if (colNames.Contains("AUDIT_TYPE") && !reader.IsDBNull("AUDIT_TYPE"))
 {
 dto.AUDIT_TYPE = reader.GetValueOrDefault<Int32>("AUDIT_TYPE");
 }
 if (colNames.Contains("PARENT_INDEX_SID") && !reader.IsDBNull("PARENT_INDEX_SID"))
 {
 dto.PARENT_INDEX_SID = reader.GetValueOrDefault<Int64>("PARENT_INDEX_SID");
 }
 if (colNames.Contains("DISPLAY_ORDER") && !reader.IsDBNull("DISPLAY_ORDER"))
 {
 dto.DISPLAY_ORDER = reader.GetValueOrDefault<Int32>("DISPLAY_ORDER");
 }
 if (colNames.Contains("DISPLAY_LEVEL") && !reader.IsDBNull("DISPLAY_LEVEL"))
 {
 dto.DISPLAY_LEVEL = reader.GetValueOrDefault<Int32>("DISPLAY_LEVEL");
 }
 if (colNames.Contains("FILE_DIV_FLAG") && !reader.IsDBNull("FILE_DIV_FLAG"))
 {
 dto.FILE_DIV_FLAG = reader.GetValueOrDefault<Int16>("FILE_DIV_FLAG");
 }
 if (colNames.Contains("OUTPUT_FLAG") && !reader.IsDBNull("OUTPUT_FLAG"))
 {
 dto.OUTPUT_FLAG = reader.GetValueOrDefault<Int16>("OUTPUT_FLAG");
 }
 if (colNames.Contains("ACCOMPANIMENT_FLAG") && !reader.IsDBNull("ACCOMPANIMENT_FLAG"))
 {
 dto.ACCOMPANIMENT_FLAG = reader.GetValueOrDefault<Int16>("ACCOMPANIMENT_FLAG");
 }
 if (colNames.Contains("UPDATE_DATE3") && !reader.IsDBNull("UPDATE_DATE3"))
 {
 dto.UPDATE_DATE3 = reader.GetValueOrDefault<DateTime>("UPDATE_DATE3");
 }
 if (colNames.Contains("UPDATE_USER_SID3") && !reader.IsDBNull("UPDATE_USER_SID3"))
 {
 dto.UPDATE_USER_SID3 = reader.GetValueOrDefault<Int64>("UPDATE_USER_SID3");
 }
 if (colNames.Contains("ENTRY_DATE3") && !reader.IsDBNull("ENTRY_DATE3"))
 {
 dto.ENTRY_DATE3 = reader.GetValueOrDefault<DateTime>("ENTRY_DATE3");
 }
 if (colNames.Contains("ENTRY_USER_SID3") && !reader.IsDBNull("ENTRY_USER_SID3"))
 {
 dto.ENTRY_USER_SID3 = reader.GetValueOrDefault<Int64>("ENTRY_USER_SID3");
 }
 if (colNames.Contains("INDEXNUM_FLAG") && !reader.IsDBNull("INDEXNUM_FLAG"))
 {
 dto.INDEXNUM_FLAG = reader.GetValueOrDefault<Int16>("INDEXNUM_FLAG");
 }
 if (colNames.Contains("LENTICULARBRACKETS_FLAG") && !reader.IsDBNull("LENTICULARBRACKETS_FLAG"))
 {
 dto.LENTICULARBRACKETS_FLAG = reader.GetValueOrDefault<Int16>("LENTICULARBRACKETS_FLAG");
 }

 iRowCount += 1;
 }
 stopwatch.Stop();
 sbSummary.AppendLine($"reader.Read() takes {stopwatch.ElapsedMilliseconds} miliseconds.");
 stopwatch.Reset();
 }
 }
 }

 Debug.WriteLine(dto.ToString());

 sbSummary.AppendLine($"Total rows: {iRowCount}");

 if (sbGenDtoClass.Length > 0)
 {
 File.WriteAllText("MyDto.txt", sbGenDtoClass.ToString(), Encoding.UTF8);
 Process.Start(@"C:\Program Files\Notepad++\notepad++.exe", "MyDto.txt");
 }

 if (sbAssignStatement.Length > 0)
 {
 File.WriteAllText("Statement.txt", sbAssignStatement.ToString(), Encoding.UTF8);
 Process.Start(@"C:\Program Files\Notepad++\notepad++.exe", "Statement.txt");
 }

 if (sbSummary.Length > 0)
 {
 File.WriteAllText("Output.txt", sbSummary.ToString(), Encoding.UTF8);
 Process.Start(@"C:\Program Files\Notepad++\notepad++.exe", "Output.txt");
 }
 }
 }

 public class MyDto
 {
 public Int32 PHASE_SCENARIO_ID { get; set; }
 public Int64 SID { get; set; }
 public String ORDER_CODE { get; set; }
 public Int64 COMPANY_SID { get; set; }
 public DateTime PERIOD_START_DATE { get; set; }
 public DateTime PERIOD_END_DATE { get; set; }
 public Int32 CONSOLIDATION_STATUS { get; set; }
 public Int16 DOC_ACCESS_CONTROL_FLAG { get; set; }
 public Int16 FINANCE_INSPECTION_ALLOW { get; set; }
 public Int16 PRICING_AVALABLE { get; set; }
 public Int32 BPO_ROLE_ID { get; set; }
 public Int16 ENGLISH_FONT_FLG { get; set; }
 public String ENGLISH_FONT_FAMILY_NAME { get; set; }
 public Int16 FRONTLOAD_EXECUTION_STATUS { get; set; }
 public String DESCRIPTION { get; set; }
 public String USER_COMMENT { get; set; }
 public DateTime UPDATE_DATE { get; set; }
 public Int64 UPDATE_USER_SID { get; set; }
 public DateTime ENTRY_DATE { get; set; }
 public Int64 ENTRY_USER_SID { get; set; }
 public String PERIOD { get; set; }
 public Int16 ACCESS_USER_SET_FLAG { get; set; }
 public String EDINET_CODE { get; set; }
 public DateTime FISCAL_END_DATE { get; set; }
 public Int64 SID1 { get; set; }
 public Int64 ORDER_SID { get; set; }
 public String JOB_TYPE { get; set; }
 public Int32 REVISION_NUMBER { get; set; }
 public DateTime UPDATE_DATE1 { get; set; }
 public Int64 UPDATE_USER_SID1 { get; set; }
 public DateTime ENTRY_DATE1 { get; set; }
 public Int64 ENTRY_USER_SID1 { get; set; }
 public Int64 SID2 { get; set; }
 public Int64 JOB_SID { get; set; }
 public String IDENTIFICATION { get; set; }
 public Int64 EDIT_AUTHORITY { get; set; }
 public DateTime CHECKOUT_DATE { get; set; }
 public Int64 CHECKOUT_USER_SID { get; set; }
 public DateTime UPDATE_DATE2 { get; set; }
 public Int64 UPDATE_USER_SID2 { get; set; }
 public DateTime ENTRY_DATE2 { get; set; }
 public Int64 ENTRY_USER_SID2 { get; set; }
 public Int16 EDIT_AUTHORITY_LOCK_FLAG { get; set; }
 public DateTime DISPLAY_UPDATE_DATE { get; set; }
 public Int64 DISPLAY_UPDATE_USER_SID { get; set; }
 public Int64 SID3 { get; set; }
 public Int64 DOC_SID { get; set; }
 public String TITLE { get; set; }
 public Int32 INDEX_TYPE { get; set; }
 public Int32 AUDIT_TYPE { get; set; }
 public Int64 PARENT_INDEX_SID { get; set; }
 public Int32 DISPLAY_ORDER { get; set; }
 public Int32 DISPLAY_LEVEL { get; set; }
 public Int16 FILE_DIV_FLAG { get; set; }
 public Int16 OUTPUT_FLAG { get; set; }
 public Int16 ACCOMPANIMENT_FLAG { get; set; }
 public DateTime UPDATE_DATE3 { get; set; }
 public Int64 UPDATE_USER_SID3 { get; set; }
 public DateTime ENTRY_DATE3 { get; set; }
 public Int64 ENTRY_USER_SID3 { get; set; }
 public Int16 INDEXNUM_FLAG { get; set; }
 public Int16 LENTICULARBRACKETS_FLAG { get; set; }
 }

 public static class DataReaderExtension
 {
 public static bool IsDBNull(this IDataReader dataReader, string columnName)
 {
 return dataReader[columnName] == DBNull.Value;
 }

 public static T GetValueOrDefault<T>(this IDataRecord row, string fieldName)
 {
 int ordinal = row.GetOrdinal(fieldName);
 return row.GetValueOrDefault<T>(ordinal);
 }

 public static T GetValueOrDefault<T>(this IDataRecord row, int ordinal)
 {
 return (T)(row.IsDBNull(ordinal) ? default(T) : row.GetValue(ordinal));
 }
 }
}

Folder Explorer (C#) (edit)

What Are System Environment Variables?

Environment variables are strings that save information about the entire environment in your system. These string values are dynamic and they can affect the way your system will behave on. Environment variables can be classified into two main types:

System Variables: They affect the entire system whatever the current user is. They are defined by Windows and saved in the registry. You need to be an administrator to be able to modify them. You usually need to restart your computer to make these changes effective.

User Variables: They affect the current environment of the current system user. They can be deleted, modified, and added by any system user. They are used by Windows setup, by some programs, and by users. Changes to these variables are saved to the registry and be effective immediately.

Program.cs

using System;
using System.Linq;
using System.Security.Principal;

 class Program
 {
 private static void Main(string[] args)
 {
 Console.WriteLine("%UserName%: " + Environment.GetEnvironmentVariable("UserName"));

 //Getting machine and user information
 Console.WriteLine("Machine Information");
 Console.WriteLine("======================");
 Console.WriteLine("Machine Name: " + Environment.MachineName);
 Console.WriteLine("OS Version: " + Environment.OSVersion);
 Console.WriteLine("System Directory: " + Environment.SystemDirectory);
 Console.WriteLine("User Name: " + Environment.UserName);
 Console.WriteLine("Version: " + Environment.Version);
 Console.WriteLine("Current Directory: " + Environment.CurrentDirectory);
 Console.WriteLine();

 // Get all logical hard drives
 string[] drives = Environment.GetLogicalDrives();
 Console.WriteLine("======================");
 Console.WriteLine("Available drives:");
 foreach (string drive in drives)
 {
 Console.WriteLine(drive);
 }

 Console.WriteLine("%APPDATA%: " + System.Environment.ExpandEnvironmentVariables("%APPDATA%"));
 Console.WriteLine("%PROGRAMDATA%: " + System.Environment.ExpandEnvironmentVariables("%PROGRAMDATA%"));

 //Local Environment Variables
 Console.WriteLine("%USERNAME%: " + System.Environment.ExpandEnvironmentVariables("%USERNAME%"));
 Console.WriteLine("%USERPROFILE%: " + System.Environment.ExpandEnvironmentVariables("%USERPROFILE%"));
 Console.WriteLine("%ALLUSERSPROFILE%: " + System.Environment.ExpandEnvironmentVariables("%ALLUSERSPROFILE%"));
 Console.WriteLine("%APPDATA%: " + System.Environment.ExpandEnvironmentVariables("%APPDATA%"));
 Console.WriteLine("%TEMP%: " + System.Environment.ExpandEnvironmentVariables("%TEMP%"));
 Console.WriteLine("%TMP%: " + System.Environment.ExpandEnvironmentVariables("%TMP%"));

 //System Environment Variables
 Console.WriteLine("%COMPUTERNAME%: " + System.Environment.ExpandEnvironmentVariables("%COMPUTERNAME%"));
 Console.WriteLine("%OS%: " + System.Environment.ExpandEnvironmentVariables("%OS%"));
 Console.WriteLine("%PATH%: " + System.Environment.ExpandEnvironmentVariables("%PATH%"));
 Console.WriteLine("%HOMEPATH%: " + System.Environment.ExpandEnvironmentVariables("%HOMEPATH%"));
 Console.WriteLine("%SYSTEMDRIVE%: " + System.Environment.ExpandEnvironmentVariables("%SYSTEMDRIVE%"));
 Console.WriteLine("%SYSTEMROOT%: " + System.Environment.ExpandEnvironmentVariables("%SYSTEMROOT%"));
 Console.WriteLine("%WINDIR%: " + System.Environment.ExpandEnvironmentVariables("%WINDIR%"));

 //Others
 Console.WriteLine("%ALLUSERSPROFILE%: " + System.Environment.ExpandEnvironmentVariables("%ALLUSERSPROFILE%"));
 Console.WriteLine("%CommonProgramFiles%: " + System.Environment.ExpandEnvironmentVariables("%CommonProgramFiles%"));
 Console.WriteLine("%CommonProgramFiles(x86)%: " + System.Environment.ExpandEnvironmentVariables("%CommonProgramFiles(x86)%"));
 Console.WriteLine("%CommonProgramW6432%: " + System.Environment.ExpandEnvironmentVariables("%CommonProgramW6432%"));

 Console.WriteLine("%ProgramFiles%: " + System.Environment.ExpandEnvironmentVariables("%ProgramFiles%"));
 Console.WriteLine("%CommonProgramFiles%: " + System.Environment.ExpandEnvironmentVariables("%CommonProgramFiles%"));

 string ServerPath = "My Software";
 if (System.Environment.Is64BitOperatingSystem)
 {
 //Is64Bit
 ServerPath = System.IO.Path.Combine(System.Environment.ExpandEnvironmentVariables("%ProgramFiles(x86)%"), ServerPath);
 }
 else
 {
 //Is32Bit
 ServerPath = System.IO.Path.Combine(System.Environment.ExpandEnvironmentVariables("%ProgramFiles%"), ServerPath);
 }

 Console.WriteLine("My Software: " + ServerPath);

 Console.WriteLine("Path.AltDirectorySeparatorChar: " + string.Format("'{0}'", System.IO.Path.AltDirectorySeparatorChar)); // => "/"
 Console.WriteLine("Path.DirectorySeparatorChar: " + string.Format("'{0}'", System.IO.Path.DirectorySeparatorChar)); // => "\"
 Console.WriteLine("Path.PathSeparator: " + string.Format("'{0}'", System.IO.Path.PathSeparator)); // => ";"
 Console.WriteLine("Path.VolumeSeparatorChar: " + string.Format("'{0}'", System.IO.Path.VolumeSeparatorChar)); // => ":"

 string[] ServerPath_Folders = ServerPath.Split(new char[] { '\\' }, StringSplitOptions.RemoveEmptyEntries); // is safer for path
 for (int i = 0; i < ServerPath_Folders.Length; i++)
 {
 Console.WriteLine(string.Format("ServerPath_Folders[{0}]: ", i) + ServerPath_Folders[i]);
 }

 WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent();

 if (windowsIdentity == null)
 {
 throw new InvalidOperationException("WindowsIdentity is null");
 }

 string domainName = windowsIdentity.Name.Split(System.IO.Path.DirectorySeparatorChar).First();
 string nameWithoutDomain = windowsIdentity.Name.Split(System.IO.Path.DirectorySeparatorChar).Last();

 Console.WriteLine("Domain Name: " + domainName);
 Console.WriteLine("Name With Domain: " + windowsIdentity.Name);
 Console.WriteLine("Name Without Domain: " + nameWithoutDomain);

 Console.ReadLine();
 }
 }