# Pulling N rows from list of tables

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.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;

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

//Read tables names and store them in a list "tables"

while (true)
{
if (tblName == null)
{
break;
}
}

//Establish ODBC Connection

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

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

}

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 {

//Check if table has data
{
StringBuilder sb = new StringBuilder();

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

//while there is data
{

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

//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)
{
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!");
}

}
}
}


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 :)

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)
{
if (tblName == null)
{
break;
}
}


However a common strategy would be to do this:

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


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())
{

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


You would do this

while (reader.Read())
{
}


Also here you forgot to use splitter.

if (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;

{
.ToList();
yield return string.Join(splitter, names);

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

string line;
{
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)
{
.Select(table => {
try{
return new{
Data = GetData(DbConnection, table),
Table = table
}
}catch{
}
});

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!");
}
}
catch (OdbcException ex)
{
Console.WriteLine(ex.Message);
return;
}

}

• 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). – Adriano Repetti Jul 13 '17 at 11:42

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.

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.