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


namespace framework_library
{
    public class constructor
    {
        public static DataSet GetTables()
        {
            string strQuery = String.Empty;
            DataSet result = new DataSet();
            SqlCommand command = new SqlCommand();
            try
            {
                //begin transaction
                command = dataTier.CommandMethods.BeginSQLCommand();
                //build query
                strQuery += "SELECT Name As tableName FROM sysobjects ";
                strQuery += "Where type = 'U' ";
                strQuery += "ORDER BY name ";
                command.CommandText = strQuery;
                //return dataset
                result = dataTier.ReturnMethods.ReturnDataSet(ref command);
                //convert to list
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
            }
            return result;
        }


        /// <summary>
        /// DataSet of tables
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static DataSet GetTables(ref SqlCommand command)
        {
            string strQuery = String.Empty;
            DataSet result = new DataSet();
            try
            {
                //build query
                strQuery += "SELECT Name As tableName FROM sysobjects ";
                strQuery += "Where type = 'U' ";
                strQuery += "ORDER BY name ";
                command.CommandText = strQuery;
                //return dataset
                result = dataTier.ReturnMethods.ReturnDataSet(ref command);
                //convert to list
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }
        /// <summary>
        /// Get Tables Fields
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>ArrayList</returns>
        public static ArrayList GetTableFields(string tableName, ref SqlCommand command, bool includeComputed = false)
        {
            ArrayList result = new ArrayList();
            string strQuery = String.Empty;
            DataSet data = new DataSet();
            try
            {
                //build query
                strQuery += "SELECT o1.[name] As fieldName, o3.[name] As dataType, o1.prec As [dataLength]  ";
                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] <> 'sysname' ";
                if (!includeComputed)
                    strQuery += "AND o1.iscomputed <> 1 ";
                strQuery += "ORDER BY o1.[name]";
                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)
            {
                throw ex;
            }

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


}