Pull From Oracle into CSV File

Question

I have written this code in C# to connect to an Oracle Database, omitting the tnsnames.ora file, then place results from a query into a CSV File.

Is there more efficient way to do this? (Such as reading in the data into a DataReader rather than a DataTable)

Please review and tell me what you would change.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using System.Configuration;
using System.Data;
using FileHelpers;
using System.IO;

namespace oracle_test
{
class Program
{
    static void Main(string[] args)
    {
        OraTest ot = new OraTest();
        ot.Connect();
    }

    class OraTest
    {        
        public void Connect()
        {
            var constr = new OracleConnectionStringBuilder()
            {
                DataSource = @"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.100.10)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ONE10)))",
                UserID = "100",
                Password = "100",
            }.ConnectionString;

            using (var con = new OracleConnection(constr))
            {
                string prfbalqry = "select * from ati where be = 0";                 

                OracleCommand cmd = new OracleCommand(prfbalqry, con);
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();

                DataTable pbResults = new DataTable();
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                oda.Fill(pbResults);

                StringBuilder sb = new StringBuilder();
                IEnumerable<string> columnNames = pbResults.Columns.Cast<DataColumn>().Select(column => column.ColumnName);
                sb.AppendLine(string.Join(",", columnNames));

                foreach (DataRow row in pbResults.Rows)
                {
                    IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
                    sb.AppendLine(string.Join(",", fields));
                }

                File.WriteAllText(@"C:\Financial Reporting\Output Path\pbresults.csv", sb.ToString());
            }
        }
    }
}
}

Answer 1

OraTest is not a useful name. Call it OracleToCSVWriter or something like that.

---

Mind the naming of variables etc: oda is not very descriptive - adapter would maybe be better.

---

Hardcoded username/password is rarely a good idea.

---

Why do you convert to string in this line?:

IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));

I think the below would be sufficient:

sb.AppendLine(string.Join(",", row.ItemArray));

---

Even for such a small piece of code it is a good idea to split it up in meaningful parts (maybe: private string GetConnectionString() {...}, private DataTable LoadData(OracleConnection connection) {...}, WriteData(DataTable data) {...})

---

You should dispose OracleCommand, OracleDataAdapteras well as DataTablejust as you dispose OracleConnection.

---

When writing to file, I would write directly to a stream, instead of first collect all data in memory:

    using (StreamWriter writer = new StreamWriter("<Path>", false, Encoding.Unicode))
    {
      foreach (DataRow row in table.Rows)
      {
        writer.WriteLine(string.Join(", ", row.ItemArray));
      }
    }

---

That said You should be aware that when writing a CSV file, string and other data may contain commas (,) and that could break the column layout, so you'll have to encapsulate strings in quotation marks, which will complicate the writing a little bit. A way to go could be something like:

        writer.WriteLine(string.Join(", ", row.ItemArray.Select(i => i is string? $"\"{i}\"" : i)));

or maybe:

writer.WriteLine(string.Join(", ", row.ItemArray.Select(i => $"\"{i}\"")));

(Not fully tested!)

Answer 2

DataTable has a lot of overhead. Use DataReader.

StringBuilder does not have a lot of overhead but for scalability I would write to text file line by line.