2
\$\begingroup\$

I wrote a simple C# application that connects to ODBC DB and pull the first N Rows from each table in a List. Then saves the query output to a separate text file, also in the case where there is an error with any of the tables, then all the tables names will be printed into another text file. The code is running and does what it is supposed to, but I believe I can make it better and faster.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Odbc;
using System.Data;
using System.Data.Common;
using System.IO;

namespace ODBC_Connection
{
    class Program
    {
        static  void Main(string[] args)
        {
            //defining variables
            string splitter = "|";
            string tblName;
            List<string> lines = new List<string>();
            List<string> tables = new List<string>();
            List<string> failed = new List<string>();
            StreamWriter sw;
            StreamReader sr;


            StreamWriter swFail = new StreamWriter(@"C:\failedTB.txt");

            //Read tables names and store them in a list "tables"
            sr = new StreamReader(@"H:\Tables.txt");

            while (true)
            {
                tblName = sr.ReadLine();
                if (tblName == null)
                {
                    break;
                }
                tables.Add(tblName);
            }

            //Establish ODBC Connection 

            OdbcConnection DbConnection = new OdbcConnection("UID= ID; PWD= PWD; DSN=ODBC");

            try
            {
                DbConnection.Open();
            }
            catch (OdbcException ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();

            }

            using (DbConnection)
            {
                //Create command
                OdbcCommand cmd = DbConnection.CreateCommand();


                //For each table in the list, pull the first 1000 rows and store each  
                //in separate text file
                foreach (var table in tables)
                {
                    Console.WriteLine("Processing.. " + table);

                    cmd.CommandText = "Select * From " + table + " LIMIT 1000";
                    cmd.CommandTimeout = 600; //Timeout is increased

                    try {

                        DbDataReader reader = cmd.ExecuteReader();

                        //Check if table has data
                        if (reader.HasRows)
                        {
                            StringBuilder sb = new StringBuilder();
                            Object[] items = new Object[reader.FieldCount];

                            //Get Column names and add to lines 
                            if (reader.Read())
                            {
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    sb.Append(reader.GetName(i));
                                    sb.Append(splitter);
                                }
                                lines.Add(sb.ToString());
                                sb.Clear();
                            }

                            //while there is data
                            while (reader.Read())
                            {
                                reader.GetValues(items);

                                foreach (var item in items)
                                {
                                    sb.Append(item.ToString());
                                    sb.Append("|");
                                }
                                lines.Add(sb.ToString());
                                sb.Clear();
                            }
                        }

                        reader.Close();

                        //Create a new file for each table 
                        sw = new StreamWriter(@"C:\"+table+".txt");
                        foreach (var line in lines)
                        {
                            sw.WriteLine(line);
                        }
                        lines.Clear();
                        sw.Close();
                    }

                    //if query is not successful/does not exist add table name to a list
                    catch (OdbcException ex)
                    {
                        failed.Add(table);
                        Console.WriteLine(ex.Message + " " + table);

                    }
                }

                //Print out every table name that has failed into a single file
                foreach (var item in failed)
                {
                    swFail.WriteLine(item);
                }

                //Closing files and connection
                swFail.Close();
                DbConnection.Close();
                Console.WriteLine("Done!");
                Console.ReadLine();
            }

        }
    }
}

My questions/requests would be:

  • Is there a way to not type my ID and Password when configuring the ODBC connection?
  • Would be there any better way to write the query output to separate text files?

Also any notes or suggestions are welcomed :)

\$\endgroup\$
1
\$\begingroup\$

Nitpicks aside it is time for separation of concerns. There are at least 3 distinct operations that should be extracted to methods. Those being: reading the table file, Get data from a specific table from the database and Write the data to a file.

But before code let me address other code issues. You read lines from the file like this:

while (true)
{
   tblName = sr.ReadLine();
   if (tblName == null)
   {
       break;
   }
   tables.Add(tblName);
}

However a common strategy would be to do this:

string tblName;
while ((tblName = sr.ReadLine()) != null)
{
    tables.Add(tblName);
}

Like other users pointed out you are reading a single line to a string builder. Did you consider using string.Join instead. Instead of doing this

while (reader.Read())
{
  reader.GetValues(items);

  foreach (var item in items)
  {
      sb.Append(item.ToString());
      sb.Append("|");
  }
  lines.Add(sb.ToString());
  sb.Clear();
}

You would do this

while (reader.Read())
{
    reader.GetValues(items);
    lines.Add(string.Join("|", items));
}

Also here you forgot to use splitter.

if (reader.Read())
{
  /**/
}

while (reader.Read())

You have a bug here. Didn't you forget to write the first line to the file?

You only have one input. Therefore you can get it from standard input instead of an hard-coded file path.

Program < "H:\Tables.txt"

"Select * From " + table + " LIMIT 1000";

SQL injection on my way! (I am not going to address it)

Consider my solution to be a pseudo algorithm as I didn't test it:

private readonly string splitter = ",";

private static IEnumerable<string> GetData(OdbcConnection connection, string table){
    var cmd = connection.CreateCommand();
    cmd.CommandText = "Select * From " + table + " LIMIT 1000";
    cmd.CommandTimeout = 600;

    var reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        var names = Enumerable.Range(0, reader.FieldCount)
            .Select(i => reader.GetName(i))
            .ToList();
        yield return string.Join(splitter, names);

        Object[] items = new Object[reader.FieldCount];
        while (reader.Read())
        {
            reader.GetValues(items);
            yield return string.Join(splitter, items);
        }
    }
}

private static void WriteToFile(string path, IEnumerable<string> lines){
    using(var writer = new StreamWriter(path)){
        foreach (var line in lines)
        {
            writer.WriteLine(line);
        }
        writer.Close();
    }
}

private static IEnumerable<string> ReadLines(StreamReader reader){
    string line;
    while ((line = reader.ReadLine()) != null)
    {
        yield return line;
    }
}

static void Main(string[] args)
{
    List<string> failed = new List<string>();

    try
    {
        StreamWriter swFail = new StreamWriter(@"C:\failedTB.txt");
        OdbcConnection DbConnection = new OdbcConnection("UID= ID; PWD= PWD; DSN=ODBC");
        DbConnection.Open();

        using (DbConnection)
        {
            var data = ReadLines(Console.In)
                .Select(table => {
                    try{
                        return new{ 
                            Data = GetData(DbConnection, table),
                            Table = table
                        }
                    }catch{
                        failed.Add(table);
                    }
                });


            foreach (var table in data)
            {
                WriteToFile(@"C:\"+table.Table+".txt", table.Data);
            }

            //Print out every table name that has failed into a single file
            foreach (var item in failed)
            {
                swFail.WriteLine(item);
            }

            //Closing files and connection
            swFail.Close();
            Console.WriteLine("Done!");
            Console.ReadLine();
        }
    }
    catch (OdbcException ex)
    {
        Console.WriteLine(ex.Message);
        Console.ReadLine();
        return;
    }

}
\$\endgroup\$
  • 1
    \$\begingroup\$ Just three things: commands and readers should be disposed, when there is using then no need to call Close() and OP usage of using is little bit weird (to me). \$\endgroup\$ – Adriano Repetti Jul 13 '17 at 11:42
0
\$\begingroup\$

Is there a way to not type my ID and Password when configuring the ODBC connection?

You could write the ID and Password in a seperate file and load it on intialize. If you want further protection you can also encrypt the password or if you got a static ip address you could save a file on your server with the ID and Password and whitelist this ip address to load the file.

\$\endgroup\$
0
\$\begingroup\$

If you are going to write it out to file one line at a time then why write it to lines? Just write it out to file directly. Or put it all in one SB and write it out in one call. This not efficient.

For that matter why List tables? Just read and process.

Why does the program not exit in the first catch.

The program does not type in ID and Password.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.