/Program.cs

/*

 * SQL_CLI - A simple command line client for processing SQL files, against a given DSN

 * Copyright (C) 2005 Lokkju, Inc

 * 

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 * 

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 * 

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

 *

 * @version 1.0

 * @author Lokkju

 * 

 */





using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Odbc;

using System.Data.OleDb;

using System.IO;



namespace ODBC_CLI

{

	class Program

	{

		static void Main(string[] args)

		{

			string dbc = "";

			string fname = "";

			string ctype = "";

			if (args.Length < 3)

			{

				usage();

			}

			else

			{

				ctype = args[0];

				dbc = args[1];

				fname = args[2];

				switch (ctype.ToLower())

				{

					case "ole":

						do_cli_ole(dbc, fname);

						break;

					case "odbc":

						do_cli_odbc(dbc, fname);

						break;

				}

				

			}

		}

		static void do_cli_ole(string strDbc, string strFname)

		{

			File.Delete(strFname + ".err");

			StreamWriter errlog = File.CreateText(strFname + ".err");

			if (!file_exists(strFname))

			{

				System.Console.WriteLine("Fatal Error: The specified sql command file does not exist.");

				return;

			}

			if (!dbc_exists_ole(strDbc))

			{

				System.Console.WriteLine("Fatal Error: The specified Connection String is invalid.");

				return;

			}

			OleDbConnection dbc = new OleDbConnection(strDbc);

			StreamReader sqlf = File.OpenText(strFname);

			try

			{

				dbc.Open();

				OleDbCommand cmd;

				int i = 0;

				TimeSpan tmr = new TimeSpan();

				Int64 tic = DateTime.Now.Ticks;

				OleDbTransaction tr = dbc.BeginTransaction();

				int n = 1000;

				while (sqlf.Peek() >= 0)

				{

					//if (i < 39100) { sqlf.ReadLine(); i++; continue; }

					if ((i > n))

					{

						n += 1000;

						tr.Commit();

						tr.Dispose();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

						tmr = new TimeSpan(DateTime.Now.Ticks - tic);

						Console.Write("\r\n");

						Console.WriteLine("i:" + i + "\t\tTime:" + tmr.TotalSeconds);

					}

					string cmdtxt = "";

					string ln = sqlf.ReadLine();

					cmdtxt = ln;

					if (ln.StartsWith("--")) { Console.Write("\r\nLine " + i + ":\tSkipping (--)\r\n"); i++; continue; }

					else if (ln.Trim().Length < 5) { Console.Write("\r\nLine " + i + ":\tSkipping (<5)\r\n"); i++; continue; }

					else if (ln.StartsWith("SET")) { Console.Write("\r\nLine " + i + ":\tSkipping (SET)\r\n"); i++; continue; }

					else if (ln.StartsWith("GO")) { Console.Write("\r\nLine " + i + ":\tSkipping (GO)\r\n"); i++; continue; }



					//else if (!ln.StartsWith("INSERT")) { Console.Write("Line " + i + ":\tInvalid SQL Statement\r\n"); i++; continue; }

					//else { cmdtxt += ln; }

					cmd = dbc.CreateCommand();

					try

					{

						cmd.CommandText = ln;

						cmd.CommandTimeout = 3;

						cmd.Transaction = tr;

						cmd.ExecuteNonQuery();

						cmd.Dispose();

						Console.Write(".");

					}

					catch (OleDbException oex)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + " (this transaction lost!)\r\n" + cmdtxt + "\r\n" + oex.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\tOLE Error (this transaction lost!)\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					catch (System.AccessViolationException ave)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + "\r\n" + cmdtxt + "\r\n" + ave.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\t Unknown Error\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					catch (Exception ex)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + "\r\n" + cmdtxt + "\r\n" + ex.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\t Unknown Error\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					finally

					{

						i++;

					}



				}

				tr.Commit();



			}

			catch (OleDbException oex)

			{

				Console.WriteLine(oex.ToString());

			}

			catch (AccessViolationException ave)

			{

				Console.WriteLine(ave.ToString());

			}

			catch (Exception oex)

			{

				Console.WriteLine(oex.ToString());

			}

			finally

			{

				dbc.Close();

				sqlf.Close();

				errlog.Flush();

				errlog.Close();

			}

		}

		static void do_cli_odbc(string strDbc,string strFname) {

			File.Delete(strFname + ".err");

			StreamWriter errlog = File.CreateText(strFname + ".err");

			if (!file_exists(strFname)) {

				System.Console.WriteLine("Fatal Error: The specified sql command file does not exist.");

				return;

			}

			if (!dbc_exists(strDbc)) {

				System.Console.WriteLine("Fatal Error: The specified DSN does not exist.");

				return;				

			}

			OdbcConnection dbc = new OdbcConnection(strDbc);

			StreamReader sqlf = File.OpenText(strFname);

			try

			{

				dbc.ConnectionTimeout = 3;

				dbc.Open();

				OdbcCommand cmd;

				int i = 0;

				TimeSpan tmr = new TimeSpan();

				Int64 tic = DateTime.Now.Ticks;

				OdbcTransaction tr = dbc.BeginTransaction();

				int n = 1000;

				while (sqlf.Peek() >= 0)

				{

					//if (i < 39100) { sqlf.ReadLine(); i++; continue; }

					if ((i > n))

					{

						n += 1000;

						tr.Commit();

						tr.Dispose();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

						tmr = new TimeSpan(DateTime.Now.Ticks - tic);

						Console.Write("\r\n");

						Console.WriteLine("i:" + i + "\t\tTime:" + tmr.TotalSeconds);

					}

					string cmdtxt = "";

					string ln = sqlf.ReadLine();

					if (ln.StartsWith("--")) { Console.Write("\r\nLine " + i + ":\tInvalid SQL Statement (--)\r\n"); i++; continue; }

					else if (ln.Length < 5) { Console.Write("\r\nLine " + i + ":\tInvalid SQL Statement (<5)\r\n"); i++; continue; }

					//else if (!ln.StartsWith("INSERT")) { Console.Write("Line " + i + ":\tInvalid SQL Statement\r\n"); i++; continue; }

					//else { cmdtxt += ln; }

					cmd = dbc.CreateCommand();

					try

					{

						cmd.CommandText = ln;

						cmd.CommandTimeout = 3;

						cmd.Transaction = tr;

						cmd.ExecuteNonQuery();

						cmd.Dispose();

						Console.Write(".");

					}

					catch (OdbcException oex)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + "\r\n" + cmdtxt + "\r\n" + oex.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\tODBC Error\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					catch (System.AccessViolationException ave)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + "\r\n" + cmdtxt + "\r\n" + ave.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\t Unknown Error\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					catch (Exception ex)

					{

						cmd.Dispose();

						errlog.Write("Error:\tLine Number " + i + "\r\n" + cmdtxt + "\r\n" + ex.ToString() + "\r\n\r\n");

						Console.Write("\r\nLine " + i + ":\t Unknown Error\r\n");

						errlog.Flush();

						dbc.Close();

						dbc.Open();

						tr = dbc.BeginTransaction();

					}

					finally

					{

						i++;

					}

					

				}

				tr.Commit();

				

			}

			catch (OdbcException oex)

			{

				Console.WriteLine(oex.ToString());

			}

			catch (AccessViolationException ave)

			{

				Console.WriteLine(ave.ToString());

			}

			catch (Exception oex)

			{

				Console.WriteLine(oex.ToString());

			}

			finally

			{

				dbc.Close();

				sqlf.Close();

				errlog.Flush();

				errlog.Close();

			}

		}

		static void usage()

		{

			System.Console.WriteLine("Usage: odbc_cli [odbc|ole] [DSN] [filename]");

		}

		static bool file_exists(string fname)

		{

			return System.IO.File.Exists(fname);

		}

		static bool dbc_exists(string dbc)

		{

			System.Data.Odbc.OdbcConnection ddbc = new OdbcConnection(dbc);

			try

			{

				ddbc.Open();

			}

			catch (OdbcException oex)

			{

				System.Console.WriteLine(oex.ToString());

				return false;

			}

			finally

			{

				ddbc.Close();

			}

			return true;

		}

		static bool dbc_exists_ole(string dbc)

		{

			System.Data.OleDb.OleDbConnection ddbc = new OleDbConnection(dbc);

			try

			{

				ddbc.Open();

			}

			catch (OleDbException oex)

			{

				System.Console.WriteLine(oex.ToString());

				return false;

			}

			finally

			{

				ddbc.Close();

			}

			return true;

		}

	}

}