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));
 }
 }
}

UI Controls (edit)

UI-Bootstrap Datepicker

http://embed.plnkr.co/OVh5h7GJYkthVwCUkZwJ/

Using AngularJS

https://dalelotts.github.io/angularjs-bootstrap-datetimepicker/

https://codepen.io/jagathish/pen/EZwqPM

Using HTML, Support Input

https://www.sitepoint.com/finding-date-picker-input-solution-bootstrap/

https://codepen.io/SitePoint/pen/RNryzy

SQL Server + CSharp (C#)

http://csharp.net-informations.com/

https://www.c-sharpcorner.com/

https://www.c-sharpcorner.com/technologies/windows-forms

https://csharp.hotexamples.com/

https://github.com/Fast-Forward-llc/FFLib/

AngularJS

https://www.codeproject.com/Articles/808213/Developing-a-Large-Scale-Application-with-a-Single

SQL Server

https://www.codeproject.com/Articles/6936/Paging-of-Large-Resultsets-in-ASP-NET

https://blog.sqlauthority.com/sql-server-interview-questions-and-answers/

DBProviderFactory

https://blog.codeinside.eu/2016/12/31/dbproviderfactory-write-database-agnostic-adonet-code/

Data Access Layer - Rick Strahl

A general purpose utility and helper library for .NET development

https://github.com/RickStrahl/Westwind.Utilities

Data Access Layer - Manh Nguyen

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            StringBuilder stringBuilder = new StringBuilder();
            DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");

            using (IDbConnection dbConnection = dbProviderFactory.CreateConnection())
            {
                dbConnection.ConnectionString = "Server=MANHNV\\SQL;database=master;Integrated Security=SSPI;";

                dbConnection.Open();

                try
                {
                    using (IDbCommand dbCommand = dbConnection.CreateCommand())
                    {
                        dbCommand.CommandType = CommandType.Text;
                        dbCommand.CommandText = "SELECT servicename, service_account FROM sys.dm_server_services";

                        using (IDataReader dataReader = dbCommand.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                stringBuilder.Append(System.Environment.NewLine);
                                stringBuilder.Append("Service Name".PadRight(45));
                                stringBuilder.Append(dataReader[0]);
                                stringBuilder.Append(System.Environment.NewLine);
                                stringBuilder.Append("Service Account".PadRight(45));
                                stringBuilder.Append(dataReader[1]);
                                stringBuilder.Append(System.Environment.NewLine);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    stringBuilder.Length = 0;
                    stringBuilder.Append(ex.ToString());
                }
            }

            MessageBox.Show(stringBuilder.ToString());
        }

        public static bool TestTransaction()
        {
            //http://www.zentut.com/sql-tutorial/sql-sample-database/
            //https://dotnetstories.wordpress.com/2011/11/09/a-thorough-look-into-transactions-in-sql-server/
            /*
             *  USE AdventureWorks;
             *  GO
             *  BEGIN TRANSACTION;
             *
             *  BEGIN TRY
             *      -- Generate a constraint violation error.
             *      DELETE FROM Production.Product
             *      WHERE ProductID = 980;
             *  END TRY
             *  BEGIN CATCH
             *      SELECT
             *          ERROR_NUMBER() AS ErrorNumber
             *          ,ERROR_SEVERITY() AS ErrorSeverity
             *          ,ERROR_STATE() AS ErrorState
             *          ,ERROR_PROCEDURE() AS ErrorProcedure
             *          ,ERROR_LINE() AS ErrorLine
             *          ,ERROR_MESSAGE() AS ErrorMessage;
             *
             *      IF @@TRANCOUNT > 0
             *          ROLLBACK TRANSACTION;
             *  END CATCH;
             *
             *  IF @@TRANCOUNT > 0
             *      COMMIT TRANSACTION;
             *  GO
             */
            DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");

            using (IDbConnection dbConnection = dbProviderFactory.CreateConnection())
            {
                dbConnection.ConnectionString = "Server=MANHNV\\SQL;database=master;Integrated Security=SSPI;";

                dbConnection.Open();

                IDbTransaction dbTransaction = dbConnection.BeginTransaction();

                try
                {
                    using (IDbCommand dbCommand = dbConnection.CreateCommand())
                    {
                        dbCommand.Transaction = dbTransaction;
                        dbCommand.CommandType = CommandType.Text;
                        dbCommand.CommandText = "INSERT INTO Table1 (Id, Data) VALUES (@ID, @DT)";
                        dbCommand.CreateParameter().AddWithValue("@ID", 1);
                        dbCommand.CreateParameter().AddWithValue("@DT", 1);
                        dbCommand.ExecuteNonQuery();
                    }

                    using (IDbCommand dbCommand = dbConnection.CreateCommand())
                    {
                        dbCommand.Transaction = dbTransaction;
                        dbCommand.CommandType = CommandType.Text;
                        dbCommand.CommandText = "INSERT INTO Table2 (Id, Data) VALUES (@ID, @DT)";
                        dbCommand.CreateParameter().AddWithValue("@ID", 1);
                        dbCommand.CreateParameter().AddWithValue("@DT", 1);
                        dbCommand.ExecuteNonQuery();
                    }

                    dbTransaction.Commit();
                }
                catch (Exception)
                {
                    dbTransaction.Rollback();
                }
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            StringBuilder stringBuilder = new StringBuilder();

            try
            {
                //SQL Server connection strings
                //https://www.connectionstrings.com/sql-server/
                //https://www.connectionstrings.com/all-sql-server-connection-string-keywords/
                //https://stackoverflow.com/questions/20558043/the-right-connection-string-for-remote-sql-server-for-c-sharp
                //https://www.dofactory.com/reference/connection-strings
                string usr = "sa";
                string pwd = "123456a@";
                string srv = $"MANHNV\\SQL";
                int port = 1433;
                int connectTimeout = 30;
                string connStr = $"Data Source={srv};Uid={usr};Pwd={pwd};Connect Timeout={connectTimeout};";
                string myConnStr = $"Data Source=10.255.3.156,{port};Uid={usr};Pwd={pwd};Connect Timeout={connectTimeout};";
                string serviceName = string.Empty;
                string serviceAccount = string.Empty;
                using (var conn = new SqlConnection(connStr))
                {
                    string cmdText = $"SELECT servicename, service_account FROM sys.dm_server_services";
                    using (var cmd = new SqlCommand(cmdText, conn))
                    {
                        conn.Open();
                        var sqlDataReader = cmd.ExecuteReader();
                        while (sqlDataReader.Read())
                        {
                            serviceName = sqlDataReader.GetString(0);
                            serviceAccount = sqlDataReader.GetString(1);

                            stringBuilder.Append(System.Environment.NewLine);
                            stringBuilder.Append("Service Name".PadRight(45));
                            stringBuilder.Append(serviceName);
                            stringBuilder.Append(System.Environment.NewLine);
                            stringBuilder.Append("Service Account".PadRight(45));
                            stringBuilder.Append(serviceAccount);
                            stringBuilder.Append(System.Environment.NewLine);
                        }
                    }
                }
                MessageBox.Show(stringBuilder.ToString());
            }
            catch (Exception ex)
            {
                stringBuilder.Length = 0;
                stringBuilder.Append(ex.ToString());
                MessageBox.Show(ex.ToString());
            }
        }
    }

    public static class MyExtensions
    {
        public static void AddWithValue(this IDbDataParameter command, string parameterName, object parameterValue)
        {
            command.ParameterName = parameterName;
            command.Value = parameterValue;
        }
    }
}