using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;

using System.Data.SqlClient;

using System.Reflection;

namespace EvoSoft_Constructor
{
    class Constructor
    {
       

        public static DataSet GetTables()
        {
            string strQuery = String.Empty;
            DataSet result = new DataSet();
            SqlCommand command = new SqlCommand();
            try
            {
                //begin transaction
                command = dataTier.TransactionalMethods.BeginSQLTransaction();
                //build query
                strQuery += "SELECT Name As tableName FROM sysobjects ";
                strQuery += "Where type IN ('U','V') "; //JasR 2015-04-18 added views
                strQuery += "ORDER BY name ";
                command.CommandText = strQuery;
                //return dataset
                result = dataTier.ReturnMethods.ReturnDataSet(ref command);
                //convert to list
            }
            catch (Exception ex)
            {
                command.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                dataTier.TransactionalMethods.CompleteSQLTransaction(ref command);
            }
            return result;
        }

        /// <summary>
        /// Get Tables Fields
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>ArrayList</returns>
        public static ArrayList GetTableFields(string tableName)
        {
            ArrayList result = new ArrayList();
            string strQuery = String.Empty;
            DataSet data = new DataSet();
            SqlCommand command = new SqlCommand();
            try
            {
                //begin transaction
                command = dataTier.TransactionalMethods.BeginSQLTransaction();
                //build query
                strQuery += "SELECT o1.[name] As fieldName, o3.[name] As dataType ";
                strQuery += "FROM [sysColumns] o1 ";
                strQuery += "INNER JOIN [sysObjects] o2 ON o1.[id] = o2.[id] ";
                strQuery += "INNER JOIN [sysTypes] o3 ON o1.[xtype] = o3.[xtype] ";
                strQuery += "WHERE o2.[name] = '" + tableName + "' And o3.[name] NOT IN ('sysname','tSessionId','tSessionItemLong') AND o3.[name] <> 'tAppName' ";
                command.CommandText = strQuery;
                //return dataset
                data = dataTier.ReturnMethods.ReturnDataSet(ref command);
                //convert to list
                result = utils.ConvertDataTableToList(data.Tables[0], typeof(oTable));
            }
            catch (Exception ex)
            {
                command.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                dataTier.TransactionalMethods.CompleteSQLTransaction(ref command);
            }
            return result;
        }

        /// <summary>
        /// Get Tables Fields
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>ArrayList</returns>
        public static ArrayList GetCustomFields(string query)
        {
            ArrayList result = new ArrayList();
            string strQuery = String.Empty;
            DataSet data = new DataSet();
            SqlConnection connection = dataTier.ReturnMethods.Connection("conn");
            connection.Open();
            SqlCommand command = new SqlCommand("ObjectCreatorProc", connection);

            try
            {
                command.CommandType = CommandType.StoredProcedure;
                //Add the Parameters
                command.Parameters.AddWithValue("@Query", query);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                //Fill the DataSet
                adapter.Fill(data);
                //Dispose the Objects
                adapter.Dispose();                
                //convert to list
                result = utils.ConvertDataTableToList(data.Tables[0], typeof(oTable));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Dispose();
                connection.Close();
            }

            return result;
        }
    }


}