using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
/**
  $Id: SqlFactory.cs 111295 2010-05-12 02:49:19Z nli $
  $Revision: 111295 $
  $Date: 2010-05-11 22:49:19 -0400 (Tue, 11 May 2010) $
  */
namespace edu.jhu.Cas.Util{
	public class SqlFactory{
		public static string JOBS_STRING = ConfigurationSettings.AppSettings["SqlConnection.String"]!=null?
			ConfigurationSettings.AppSettings["SqlConnection.String"]:
			"Initial Catalog=BatchAdmin; Data Source = sdssdr1; User ID = *****; Password=*****;";
		public const string VERSION = "$Revision: 111295 $";
		public static Object scalar(SqlJob job, string statement){
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                return scalar(tmp, job, statement);
            }
		}
		public static Object scalar(SqlConnection con,SqlJob job,string statement){
			con.Open();
			SqlCommand command = new SqlCommand(statement, con);
			job.addParameters(command);
			command.Prepare();
			job.addParameterValues(command);
			Object o = command.ExecuteScalar();
			con.Close();
			return o;
		}
		public static Object scalar(string qry){
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                return scalar(tmp, qry);
            }
		}
		public static Object scalar(SqlConnection con,string statement){
			con.Open();
			SqlCommand command = new SqlCommand(statement, con);
			Object o = command.ExecuteScalar();
			con.Close();
			return o;
		}
		public static void runQuery(string statement,string conString){
            using (SqlConnection con = new SqlConnection(conString)) {
                con.Open();
                SqlCommand command = new SqlCommand(statement, con);
                command.ExecuteNonQuery();
                con.Close();
            }
		}
		public static void insert(SqlJob job, string statement){
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                insert(tmp, job, statement);
            }
		}
		public static void insert(SqlConnection con,SqlJob job,string statement){
			con.Open();
            SqlTransaction st = con.BeginTransaction(IsolationLevel.RepeatableRead);
            using (SqlCommand command = new SqlCommand(statement, con, st)) {
                job.addParameters(command);
                job.addParameterValues(command);
                command.Prepare();
                command.ExecuteNonQuery();
                st.Commit();
            }            
		}
		public static void insert(string qry){
            using (SqlConnection con = new SqlConnection(JOBS_STRING)) {
                con.Open();
                SqlCommand command = new SqlCommand(qry, con);
                command.ExecuteNonQuery();
                con.Close();
            }
		}
		public static DataSet select(SqlJob job, string statement){
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                return select(tmp,job, statement, new DataSet());
            }
		}
		public static DataSet select(SqlJob job, string statement, DataSet dataSet){
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                return select(tmp, job, statement, dataSet);
            }
		}
		public static DataSet select(SqlConnection con,SqlJob job,
			string statement,DataSet dataSet){
			con.Open();
			SqlCommand command = new SqlCommand(statement, con);
			job.addParameters(command);
			command.Prepare();
			job.addParameterValues(command);
			SqlDataAdapter adapter = new SqlDataAdapter();
			adapter.SelectCommand = command;
			adapter.Fill(dataSet);
			con.Close();
			return dataSet;
		}
		public static DataSet select(string qry){
			return userSelect(qry,JOBS_STRING);
		}
		public static DataSet userSelect(string qry, string connectionString){
            using (SqlConnection con = new SqlConnection(connectionString)) {
                con.Open();
                SqlCommand command = new SqlCommand(qry, con);
                command.Prepare();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
		}
        public static DataSet select_param(string qry, string[] paramNames,SqlDbType[] types,int[] sizes, object[] paramValues) {
            if (paramNames.Length != types.Length || paramNames.Length != paramValues.Length || paramNames.Length != sizes.Length)
                throw new CasJobsException("All arrays must be the same length");

            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                SqlCommand com = new SqlCommand(qry, tmp);
                com.Connection.Open();
                for (int i = 0; i < paramNames.Length; i++) {
                    com.Parameters.Add(paramNames[i], types[i]);
                    com.Parameters[paramNames[i]].Value = paramValues[i];
                    com.Parameters[paramNames[i]].Size = sizes[i];
                }
                com.Prepare();

                SqlDataAdapter adap = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                try {
                    adap.Fill(ds);
                    return ds;
                }
                finally {
                    com.Connection.Close();
                }
            }
        }
        public static void nonselect_param(string qry, string[] paramNames, SqlDbType[] types, int[] sizes, object[] paramValues) {
            if (paramNames.Length != types.Length || paramNames.Length != paramValues.Length || paramNames.Length != sizes.Length)
                throw new CasJobsException("All arrays must be the same length");
            using (SqlConnection tmp = new SqlConnection(JOBS_STRING)) {
                SqlCommand com = new SqlCommand(qry, tmp);
                com.Connection.Open();
                for (int i = 0; i < paramNames.Length; i++) {
                    com.Parameters.Add(paramNames[i], types[i]);
                    com.Parameters[paramNames[i]].Value = paramValues[i];
                    com.Parameters[paramNames[i]].Size = sizes[i];
                }
                com.Prepare();
                com.ExecuteNonQuery();
            }
        }
        public static DataTable RunSQL(ReaderRapper reader,int maxBytes) {
			int totalBytes = 0;
			bool memError = false;
            DataTable dataTable = new DataTable();
			try   {
				do{
					System.Threading.Thread.Sleep(new TimeSpan(SYSTEM.TICKWAIT));
					// Create new data table
					DataTable schemaTable = reader.GetSchemaTable();
					if ( schemaTable != null ){
						// A query returning records was executed
						for ( int i = 0; i < schemaTable.Rows.Count; i++ ){
							DataRow dataRow = schemaTable.Rows[ i ];
							// Create a column name that is unique in the data table
							string columnName = ( string )dataRow[ "ColumnName" ] ;
							// Add the column definition to the data table
							DataColumn column = new DataColumn( columnName, ( Type )dataRow[ "DataType" ] );
							dataTable.Columns.Add( column );
						}
						// Fill the data table we just created
						while ( reader.Read() ){
							DataRow dataRow = dataTable.NewRow();
							for ( int i = 0; i < reader.FieldCount; i++ ){
								dataRow[ i ] = reader.GetValue( i );
								//Console.WriteLine(dataRow[i].GetType()+"\tTotalBytes: "+totalBytes);
								switch(dataRow[i].GetType().ToString()){
									case "System.String"://min string size is 20
										totalBytes+=20+dataRow[i].ToString().Length;
										break;
									case "System.Int32":
										totalBytes+=4;
										break;
									case "System.Int64":
										totalBytes+=8;
										break;
									case "System.DateTime":
										totalBytes+=8;
										break;
									case "System.DBNull":
										totalBytes+=1;
										break;
									case "System.Single":
										totalBytes+=4;
										break;
									case "System.Double":
										totalBytes+=8;
										break;
									case "System.Decimal":
										totalBytes+=16;
										break;
									case "System.Char":
										totalBytes+=2;
										break;
									case "System.Int16":
										totalBytes+=2;
										break;
									case "System.Byte":
										totalBytes+=1;
										break;
									case "System.Sbyte":
										totalBytes+=1;
										break;
									case "System.Boolean":
										totalBytes+=1;
										break;
								}
							}
							dataTable.Rows.Add( dataRow );
							if(totalBytes >= maxBytes){
								memError = true;
								reader.Dispose();
								break;
							}
						}
					}
					else {
						// No records were returned
						//DataColumn column = new DataColumn( reader.RowsAffected );
						DataColumn column = new DataColumn();
						dataTable.Columns.Add(column);
						DataRow dataRow = dataTable.NewRow();
						dataRow[0] = reader.RecordsAffected;
						dataTable.Rows.Add( dataRow );
					}
				}
				while (reader.NextResult());
			}
			catch(Exception e){
				if(memError) throw new OutOfMemoryException("Query results exceed memory limitations.   Please revise your query to return less information, or use MyDB");
				else throw;
			}
			finally {
				if(reader!=null){
					//if(!reader.IsClosed)
						//while(reader.Read()){}//flush out (might help connections closing, don't remove
					//reader.Close();
				}
			}
			return dataTable;
		}
	}
}
/**
 * $Log$
 * Revision 1.18  2008/03/26 19:11:11  nli
 * job execution now cancels properly when encoutering a loading error
 * added quickjobcleanup service
 *
 * Revision 1.17  2007/10/26 03:06:41  nli
 * --fixed a bug in SqlFactory.runQuery that was preventing new user mydb creation
 *
 * Revision 1.16  2007/10/25 18:24:32  nli
 * sqlconnection disposal audit
 *
 * Revision 1.15  2007/10/23 22:31:13  nli
 * tentative fix for
 * kb 928365
 *
 * Revision 1.14  2007/05/04 21:10:02  nli
 * columns are now assigned random names in the event of a naming conflict
 *
 * Revision 1.13  2007/04/27 21:54:35  nli
 * basic notes functionality
 *
 * Revision 1.12  2007/04/26 20:35:39  nli
 * added 'local' view type to mydbindex
 * schema change:
 * groups ->
 *   -- add privs column, varchar(200)
 *
 * admin.aspx allows group search/update
 *
 * Revision 1.11  2007/02/02 17:23:34  thakar
 * Changed "excede" to "exceed" (spelling).
 *
 * Revision 1.10  2007/01/02 19:29:39  nli
 * failed user account creation should now display an appropriate error message.
 *
 * Revision 1.9  2006/09/14 16:34:32  nli
 * minor cosmetic changes
 *
 * Revision 1.8  2006/04/25 17:47:31  nli
 * --query memory limitations for quick queries should now function as intended
 *
 * Revision 1.7  2006/03/24 20:21:25  nli
 * --jobservice multi-instance safe?
 *
 * Revision 1.6  2006/02/20 23:56:24  nli
 * --mydbindex revision (sorting)
 * --mydbcontent revision (cosmetic)
 *
 * Revision 1.5  2005/10/31 19:32:24  nli
 * --HasIntoClause method redone, now quicker
 *
 * Revision 1.4  2005/03/14 20:36:21  nli
 * --added a very small pause between reader executes (shortjob reads, long job inserts).  Total waiting time for 1 million executes should be 10 seconds. This is set in SYSTEM.TICKWAIT.
 * --some more cursor support in editing.
 * --removed 'n users are using casjobs' message of dubious accuracy.
 *
 * Revision 1.3  2005/01/19 22:02:24  womullan
 *  Mulitple bugs fixed - old failed jobs in output removed, better subjetc for email, eail for output nolonger says failed when it worked.
 *
 * Revision 1.2  2004/09/02 18:48:40  nli
 * --unit tests
 * --massive debugging, many changes to execution
 * --new help page
 *
 * Revision 1.1.1.1  2004/04/27 16:38:31  cvsuser
 * import CasJobs to FERMI
 *
 * Revision 1.2  2004/03/12 21:15:32  nli
 * datareader, table link, etc
 *
 * Revision 1.1.1.1  2004/02/18 16:38:28  nli
 * new version
 *
 * Revision 1.2  2004/02/13 16:28:20  nli
 * plot images sort
 *
 * Revision 1.1.1.1  2004/01/25 18:16:58  nli
 * first
 *
 * Revision 1.1.1.1  2004/01/20 20:59:57  nli
 * new
 *
 * Revision 1.12  2003/08/15 18:28:40  nli
 * basic job filtering
 *
 * Revision 1.11  2003/08/06 19:11:21  nli
 * lordy lordy!, short query cancel works and cleans up too.  Don't fuck it up.
 *
 * Revision 1.10  2003/08/06 17:02:55  nli
 * cancel kinda works
 *
 * Revision 1.9  2003/08/05 21:45:10  nli
 * allyourbase
 *
 * Revision 1.8  2003/07/02 20:29:53  nli
 * --full lazy creation mydb support
 * --changed some *.aspx so non ms cronies can run it
 * --failed submitJob should set failed status
 *
 * Revision 1.7  2003/06/26 21:36:45  nli
 * added mydb creation support.   Core methods are in casusers
 *
 * Revision 1.6  2003/06/26 19:13:06  nli
 * new webmethod in CasUsers called HasMyDB(wsid)
 *
 * Revision 1.5  2003/06/24 17:20:15  nli
 * some stuff in procrast added.  will finish... later
 *
 * Revision 1.4  2003/06/23 20:36:57  nli
 *
 * asdfasdfasdfCVS: ----------------------------------------------------------------------
 *
 * Revision 1.3  2003/06/20 18:33:40  nli
 * added submitShortJob method in CasJobs
 *
 * Revision 1.2  2003/06/19 21:58:33  nli
 * casusers complete, but utterly untested
 *
 * Revision 1.1  2003/06/19 20:27:01  nli
 * arranging stuff
 *
 * Revision 1.5  2003/06/19 19:38:41  womullan
 *  added back sqlfac
 *
 * Revision 1.3  2003/06/19 18:52:08  nli
 *
 * blah blah cvs
 *
 */
