using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using edu.jhu.Cas.Util;
namespace edu.jhu.Cas.Test {
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class DataReaderTest {

		public DataReaderTest(string server,string mydb) {
			MyDbConString = mydb;
			ServerConString = server;
		}

		public const int COM_LIMIT = 200;
		public string MyDbConString;
		public string ServerConString;
		/*
		public void RunThread(string tableName,string qry){
			ThreadDataReader tdr = new ThreadDataReader(tableName,qry,COM_LIMIT);
			tdr.Run();
		}
		*/
		public void Run(string tableName,string qry){
			DateTime start = DateTime.Now;
//			Console.WriteLine("START: "+start);
			
			string myConnString = ServerConString;
			
			string mySelectQuery = qry;

			SqlConnection myConnection = new SqlConnection(myConnString);
			SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
			myConnection.Open();
			SqlDataReader myReader;
			//Console.WriteLine("RUNNING QUERY");
			myCommand.CommandTimeout = 0;
			myReader = myCommand.ExecuteReader();
			// Always call Read before accessing data.
			
			string mydbConString = MyDbConString;
			SqlConnection mydbCon = new SqlConnection(mydbConString);
			mydbCon.Open();
			SqlTransaction trans = mydbCon.BeginTransaction();

			bool madeTable = false;
			SqlCommand insertCommand = new SqlCommand();
			insertCommand.Connection = mydbCon;
			insertCommand.CommandTimeout = 0;
			
			if(true){
				try{
					insertCommand.CommandText = "drop table "+tableName;
					insertCommand.Transaction = trans;
					insertCommand.ExecuteNonQuery();
				}catch(Exception e){}
			}
						
			//Console.WriteLine("LOADING RESULTS");
			string insertQry = "";
			int comCount = 0;
			float ReadStart,WriteStart;
			float ReadSum = 0,WriteSum = 0;
			int rowCnt=0;
			ReadStart = DateTime.Now.Second;
			while (myReader.Read()) {
			
				if(!madeTable){
//					Console.Write("CREATE TABLE...");
					MakeTable(tableName,mydbCon,myReader,insertCommand,trans);
					madeTable = true;
//					Console.WriteLine("COMPLETE");
				}
				string line = "insert into "+tableName+" values(";
				for(int x=0;x<myReader.FieldCount;x++){
					line+="@"+myReader.GetName(x);
				//	args["@"+myReader.GetName(x)] = myReader.GetValue(x);
					insertCommand.Parameters["@"+myReader.GetName(x)].Value =myReader.GetValue(x);
					if(x<(myReader.FieldCount - 1))
						line+=",";
				}
				ReadSum+= DateTime.Now.Second - ReadStart;
				line+=")";
				rowCnt++;
				
				WriteStart = DateTime.Now.Second;
				insertCommand.CommandText = line;
				
				insertCommand.Transaction = trans;
			//	foreach(Object key in args.Keys)
			//		insertCommand.Parameters[key.ToString()].Value = args[key];
					
				insertCommand.ExecuteNonQuery();
				line = "";
				comCount++;
				
				if(comCount > COM_LIMIT){
					comCount = 0;
					Console.Write(".");
					trans.Commit();
					trans = mydbCon.BeginTransaction();
				}
				WriteSum+= DateTime.Now.Second - WriteStart;
				ReadStart = DateTime.Now.Second;
				
			}
//			Console.WriteLine("FINAL COMMIT");
			trans.Commit();
			Console.WriteLine();
			
			
			// always call Close when done reading.
			myReader.Close();
			// Close the connection when done with it.
			myConnection.Close();
			mydbCon.Close();
//			Console.WriteLine("ELAPSED: "+(start - DateTime.Now).ToString());
//			Console.WriteLine("AVG READ: "+(ReadSum / rowCnt).ToString());
//			Console.WriteLine("AVG WRITE: "+(WriteSum / rowCnt).ToString());
        }
		public void SPRun(string tableName,string qry){
			DateTime start = DateTime.Now;
//			Console.WriteLine("START: "+start);
			
			string myConnString = ServerConString;
			
			string mySelectQuery = qry;

			SqlConnection myConnection = new SqlConnection(myConnString);
			SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
			myConnection.Open();
			SqlDataReader myReader;
			//Console.WriteLine("RUNNING QUERY");
			myCommand.CommandTimeout = 0;
			myReader = myCommand.ExecuteReader();
			// Always call Read before accessing data.
			
			string mydbConString = MyDbConString;
			SqlConnection mydbCon = new SqlConnection(mydbConString);
			mydbCon.Open();
			SqlTransaction trans = mydbCon.BeginTransaction();

			bool madeTable = false,madeSP = false;
			SqlCommand insertCommand = new SqlCommand();
			insertCommand.Connection = mydbCon;
			insertCommand.CommandTimeout = 0;
			
			if(true){
				try{
					insertCommand.CommandText = "drop table "+tableName;
					insertCommand.Transaction = trans;
					insertCommand.ExecuteNonQuery();
				}catch(Exception e){}
			}
						
			//Console.WriteLine("LOADING RESULTS");
			string insertQry = "";
			int comCount = 0;
			float ReadStart,WriteStart;
			float ReadSum = 0,WriteSum = 0;
			int rowCnt=0;
			ReadStart = DateTime.Now.Second;
			string sp_name = "";
			bool prepared = false;
			while (myReader.Read()) {
			
				if(!madeTable){
//					Console.Write("CREATE TABLE...");
					MakeTable(tableName,mydbCon,myReader,insertCommand,trans);
					madeTable = true;
//					Console.WriteLine("COMPLETE");
				}
				if(!madeSP){
					sp_name = MakeSP(tableName,mydbCon,myReader,trans);
					madeSP = true;
				}
				string line = "exec "+sp_name+" ";
				for(int x=0;x<myReader.FieldCount;x++){
					string v = myReader.GetValue(x).ToString();
					object o = myReader.GetSqlValue(x);
					v = "@"+myReader.GetName(x);
					//if(o is SqlString)
					//	v = "'"+v+"'";
					line+=v;
					if(x<(myReader.FieldCount - 1))
						line+=",";
					insertCommand.Parameters["@"+myReader.GetName(x)].Value =myReader.GetValue(x);
				}
				ReadSum+= DateTime.Now.Second - ReadStart;
			
				rowCnt++;
				
				WriteStart = DateTime.Now.Second;
				insertCommand.CommandText = line;
				
				insertCommand.Transaction = trans;
				if(!prepared){
					insertCommand.Prepare();
					prepared = true;
				}
			//	foreach(Object key in args.Keys)
			//		insertCommand.Parameters[key.ToString()].Value = args[key];
//					Console.WriteLine(line);
				insertCommand.ExecuteNonQuery();
				line = "";
				comCount++;
				
				if(comCount > COM_LIMIT){
					comCount = 0;
					Console.Write(".");
					trans.Commit();
					trans = mydbCon.BeginTransaction();
				}
				WriteSum+= DateTime.Now.Second - WriteStart;
				ReadStart = DateTime.Now.Second;
				
			}
//			Console.WriteLine("FINAL COMMIT");
			insertCommand.CommandText = "drop procedure "+sp_name;
			insertCommand.Transaction = trans;
			insertCommand.ExecuteNonQuery();
			
			trans.Commit();
					
			
			// always call Close when done reading.
			myReader.Close();
			// Close the connection when done with it.
			myConnection.Close();
			mydbCon.Close();
//			Console.WriteLine("ELAPSED: "+(start - DateTime.Now).ToString());
//			Console.WriteLine("AVG READ: "+(ReadSum / rowCnt).ToString());
//			Console.WriteLine("AVG WRITE: "+(WriteSum / rowCnt).ToString());
		}

		public void MakeTable(string tableName, SqlConnection mydbCon, SqlDataReader srcReader,SqlCommand insertCommand,SqlTransaction trans){
			string qry = "CREATE TABLE "+tableName+" ( ";
			for(int x=0; x<srcReader.FieldCount;x++){
				qry+="["+srcReader.GetName(x)+"] ";
				qry+=srcReader.GetDataTypeName(x);
				insertCommand.Parameters.Add("@"+srcReader.GetName(x),srcReader.GetSqlValue(x));
				if(x<(srcReader.FieldCount - 1))
					qry+=",";
			}
			qry+=" ) ";
			SqlCommand com = new SqlCommand(qry,mydbCon);
			//try{
		
		com.Transaction = trans;
			com.ExecuteNonQuery();
			//}catch(Exception e){}
		}
		
		public string MakeSP(string tableName, SqlConnection mydbCon, SqlDataReader srcReader,SqlTransaction trans){
			string sp_name = tableName+"_tmp";
			
			string qry = " CREATE PROCEDURE "+sp_name+" ";
			string arg = "a";
			for(int x=0; x<srcReader.FieldCount;x++){
				qry+="@"+arg;
				qry+=" "+srcReader.GetDataTypeName(x);
				//insertCommand.Parameters.Add("@"+srcReader.GetName(x),srcReader.GetSqlValue(x));
				if(x<(srcReader.FieldCount - 1))
					qry+=",";
				arg = "a"+x.ToString();
			}	
			qry+=" as insert into "+tableName+" values(";
				arg = "a";
				for(int x=0; x<srcReader.FieldCount;x++){
					qry+="@"+arg;
					if(x<(srcReader.FieldCount - 1))
						qry+=",";
					arg = "a"+x.ToString();
				}
				qry+=");";

			SqlCommand com = new SqlCommand(qry,mydbCon);
			//try{
	//Console.WriteLine(qry);
			com.Transaction = trans;
			com.ExecuteNonQuery();
			//}catch(Exception e){}
			
			return sp_name;
		}
	}
}
