using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.SqlServer.Server;

/// <summary>
/// Batabase info goes in this class
/// </summary>
public static class SqlControls
{
    #region global variables....
    string registry = null;
    string appName = "ASPBulletin";
    string asppShortName = "ASPBulletin";
    string database = null;
    bool multiserver = false;
    ArrayList shutdownQueries = new ArrayList();
    SqlCommand comm = null;
    bool SendError = true;
    string errorMessage = null;
    string sqlErrorMessage = null;
    bool lockTable = false;
    int queryCount = 0;
    #endregion

    #region database variables....
    // Caches the connection string
    private readonly static string dbConnectionString;
    #endregion
    
    /// <summary>
    /// Default Constructor
    /// </summary>
    static SqlControls()
    {
        dbConnectionString = ConfigurationManager.ConnectionStrings["ASPBulletinConnection"].ConnectionString;
	}

    /// <summary>
    /// Checks to see if registry is an object or not
    /// </summary>
    /// <param name="registry"></param>
    /// <returns></returns>
    public static string Database(ref string registry)
    {
        if (registry == null)
        {
            return "Registry object isn't an object";
        }
        else
        {
            this.registry = registry;
        }
    }

    /// <summary>
    /// Returns the connection string for the ASPBulletin database
    /// </summary>
    public static string DBConnectionString
    {
        get
        {
            return dbConnectionString;
        }
    }

    /// <summary>
    /// Create a sql command with database info to connection
    /// </summary>
    /// <returns></returns>
    public static SqlCommand CreateCommand()
    {
        // Obtain the database connection string
        string connectionString = DBConnectionString;
        // Obtain a database specific connection object
        SqlConnection conn = new SqlConnection(connectionString);
        // Create a database specific command object
        SqlCommand comm = conn.CreateCommand();
        // Return the initialized command object
        return comm;
    }

    /// <summary>
    /// Execute an INSERT Query
    /// </summary>
    /// <param name="table">Name of the table into which data should be inserted</param>
    /// <param name="fields">Comma-seperated list of the fields to affect</param>
    /// <param name="values">SQL Values</param>
    /// <returns></returns>
    public static string queryInsert( string table, string fields, ref object[] values)
    {
        return string.Format("INSERT INTO {0} {1} VALUES( {2} )", table, fields, values);
    }

    /// <summary>
    /// Returns the number of rows contained within a query result set
    /// </summary>
    /// <param name="query">The query result we are deling with</param>
    /// <returns></returns>
    public static int numberRows(string query)
    {
        int numberofrows = -1;
        SqlCommand comm = new SqlCommand(query);
        try
        {
            comm.Connection.Open();
            numberofrows = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex.Message;
        }
        finally
        {
            comm.Connection.Close();
        }
        // Return the numberRows
        return numberofrows;
    }

    /// <summary>
    /// Returns the number of fields contained within a query result set
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public static int numberFields(string query)
    {
        SqlCommand comm = CreateCommand();
        comm.CommandText = query;
        int numFields = -1;
        try
        {
            comm.Connection.Open();
            numFields = comm.ExecuteReader().FieldCount;

        }
        catch (Exception ex)
        {
            throw ex.Message;
        }
        finally
        {
            comm.Connection.Close();
        }
        return numFields;
    }

    /// <summary>
    /// Returns the name of a field from within a query set
    /// </summary>
    /// <param name="query">The query</param>
    /// <param name="index">The index position of the field</param>
    /// <returns></returns>
    public static string fieldName(string query, int index)
    {
        SqlCommand comm = CreateCommand();
        comm.CommandText = query;
        string value = "";
        try
        {
            comm.Connection.Open();
            SqlDataReader dr = comm.ExecuteReader();

            string location = dr.GetName(index);
            dr.Read();
            value = location.ToString();
        }
        catch (Exception ex)
        {
            throw ex.Message();
        }
        finally
        {
            comm.Connection.Close();
        }
        return value;
    }

    /// <summary>
    /// Returns the numbe of rows that was affected
    /// </summary>
    /// <param name="query"></param>
    /// <returns></returns>
    public static int affectedRows(string query)
    {
        SqlCommand comm = CreateCommand();
        comm.CommandText = query;
        int affectedRows = -1;

        try
        {
            comm.Connection.Open();
            affectedRows = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex.Message.ToString();
        }
        finally
        {
            comm.Connection.Close();
        }
        return affectedRows;
    }

    public class DatabaseStore
    {
        static string[] items = {
            "options",
            "bitfields",
            "attachmentcache",
            "forumcache",
            "usergroupcache",
            "languagecache",
            "products",
            "profilefield"
        };

        ArrayList itemArray = new ArrayList();
        object registry = null;
        object dbobject = null;
        string prefix = "";

        public static void store(ref object registry, ref object dbobject)
        {
            this.registry = registry;
            this.dbobject = dbobject;

            this.prefix = this.registry = ConfigurationManager.AppSettings["prefix"];

            if(registry == null)
            {
                string error = string.Format("<strong>DatabaseStore</strong>: {0} is not an object", this.registry);
                throw new Exception(error);
            }
            if (dbobject == null)
            {
                string error = string.Format("<strong>DatabaseStore</strong>: {0} is not an object", this.dbobject);
                throw new Exception(error);
            }
        }

        public static bool register(string title, object data)
        {
            if(title == "bitfields")
            {
                object registry = this.registry;
                foreach(data d in group)
                {
                    registry["bf_" + group] = data[" + group + "];
                }
            }
        }
    }
}