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

Oracle and MySQL Data Access (edit)

POCO Class Generator - @manhng

• DBeaver
• MySQL + MySqlConnector
• Oracle + Oracle.ManagedDataAccess (version 18.3.0)
• Dapper (version 1.50.2) + Dapper.Oracle
• StringBuilder -> Generate Code
• Entity Framework 6 with Oracle: Oracle.ManagedDataAccess.EntityFramework (version 18.3.0)

Oracle Comments for Table

How to get comments for table & column from oracle DB from its metadata? - Stack Overflow

SELECT table_name,
  column_name,
  comments
FROM dba_col_comments
WHERE owner ='orcl'
AND table_name='User';

Oracle Comments for Column

How to get comments for table & column from oracle DB from its metadata? - Stack Overflow

SELECT *
FROM user_col_comments;

SELECT *
FROM user_tab_comments;

List table columns in Oracle database

List table columns in Oracle database - Oracle Data Dictionary Queries (dataedo.com)

select col.column_name 
 from sys.all_tab_columns col 
 inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name 
 where col.owner = 'NORTHWIND' AND col.table_name = 'CUSTOMER' 
 order by col.owner, col.table_name, col.column_id;

select col.column_id, col.owner as schema_name, col.table_name, col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable from sys.all_tab_columns col inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name -- excluding some Oracle maintained schemas where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') order by col.owner, col.table_name, col.column_id;

Other

select col.column_name 
 from sys.all_tab_columns col 
 inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name 
 where col.owner = 'NORTHWIND' AND col.table_name = 'CUSTOMER' 
 order by col.owner, col.table_name, col.column_id;

select col.column_id, col.owner as schema_name, col.table_name, col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable from sys.dba_tab_columns col inner join sys.dba_tables t on col.owner = t.owner and col.table_name = t.table_name -- excluding some Oracle maintained schemas where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') order by col.owner, col.table_name, col.column_id;

Oracle

Oracle Parameterized Queries for the .NET Developer - CodeProject

Table Definitions

First point is that there are obvious differences on how equivalent data types are defined in the table definition. Using these two tables, I will expand on the pitfalls when writing code for database operations.

The first pitfalls are in the connections strings. For SQL Server, connecting to the database is a relatively simple affair. Oracle connections are very different. For one, Oracle connections can be done in two different ways, with the tnsnames.ora file or without. The tsanames.ora file contains the server IP address, the server port, and the Oracle service to connect to.

Lấy tất cả dữ liệu của một bảng

Trying to load an entire table in c# using Oracle.ManagedDataAccess - Stack Overflow

List table columns in MySQL database

List table columns in MySQL database - MySQL Data Dictionary Queries (dataedo.com)

php - Get table column names in MySQL? - Stack Overflow

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Or to get column names with comma in a line:

SELECT group_concat(COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Best way

php - MySQL query to get column names? - Stack Overflow

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';