using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;

public class DBAbstraction
{
    //db settings
    private string cString = null;
    private string query = null;
    //error results
    private ArrayList errorArray = new ArrayList();
    //result information
    int resultRows = 0;
    int resultColumns = 0;

    //accessors
    public ArrayList errors
    {
        get { return errorArray; }
    }
    public string connectionString
    {
        set { cString = value; }
    }
    public string queryString
    {
        set { query = value; }
        get { return query; }
    }
    public int rows
    {
        get { return resultRows; }
    }
    public int columns
    {
        get { return resultColumns; }
    }

    public DBAbstraction() { }

    public string[,] execute()
    {
        string[,] emptyArray = new string[0, 0];
        //verify we have what we need .. if not set error conditions
        if (cString == null)
        {
            addError("Connection string null.", new Exception());
        }
        if (query == null)
        {
            addError("Query null.", new Exception());
        }
        if (errorArray.Count > 0)
        {
            return emptyArray;
        }

        //  initialize variables
        DataSet resultset = new DataSet();
        OleDbConnection conn = new OleDbConnection(cString);
        OleDbCommand myAccessCommand = new OleDbCommand();
        OleDbDataAdapter results = new OleDbDataAdapter();

        //do query
        try
        {
            myAccessCommand = new OleDbCommand(query, conn);
        }
        catch (Exception ex)
        {
            addError("Unable to start OleDbCommand.", ex);
            return emptyArray;
        }
        try
        {
            results = new OleDbDataAdapter(myAccessCommand);
        }
        catch(Exception ex)
        {
            addError("Query Failed.", ex);
            return emptyArray;
        }
        try
        {
            results.Fill(resultset);
            results.Dispose();  //release OleDbDataAdapter memory
        }
        catch (Exception ex)
        {
            addError("Error filling DataSet.", ex);
            return emptyArray;
        }

        /*    Catch empty result sets before hitting the dataset portion         */
        try
        {
            int test = resultset.Tables[0].Rows.Count;
        }
        catch (Exception ex)
        {
            //empty resultset so just return
            resultRows = 0;
            resultColumns = 0;
            return emptyArray;
        }

        //set properties with rows and fields
        resultRows = resultset.Tables[0].Rows.Count;
        resultColumns = resultset.Tables[0].Columns.Count;

        //C#'s lack of support for multidimensional associative arrays sucks donkey butt
        string[,] dataArray = new string[resultset.Tables[0].Rows.Count, resultset.Tables[0].Columns.Count];
        for (int i = 0; i < resultset.Tables[0].Rows.Count; i++)
        {
            for (int k = 0; k < resultset.Tables[0].Columns.Count; k++)
            {
                dataArray[i, k] = Convert.ToString(resultset.Tables[0].Rows[i][k]);
            }
        }

        resultset.Dispose();  //release DataSet memory
        return dataArray;
    }

    //utilities
    private void addError(string error, Exception ex)
    {
        string[,] logerror = new string[,] {
                { error, ex.Message, ex.StackTrace }
            };
        errorArray.Add(logerror);
    }

    public void anExample()
    {
        /*          here's an example of using this class             */
        string youroutputformat = null;

        //instantiate the query class
        DBAbstraction query = new DBAbstraction();
        //pass connection paramters
        query.connectionString = System.Configuration.ConfigurationManager.AppSettings.Get("web.config.stored.connectionstring");
        //pass select statement
        query.queryString = "SELECT * FROM mytable";
        //execute the query and get the result array
        string[,] result = query.execute();
        //optional.... get rows and columns
        int rows = query.rows;
        int columns = query.columns;

        //check for an error condition
        if (query.errors.Count > 0)
        {
            youroutputformat += "Error condition encountered!<br><br>";
            foreach (string[,] test in query.errors)
            {
                youroutputformat += test[0, 0] + "<br>" + test[0, 1] + "<br><br>"
                    + test[0, 2] + "<br><br>";
            }
            return;
        }

        //iterate through the results
        youroutputformat += "<Center><table style='border: 1px solid black; border-collapse: collapse;'>";
        for (int row = 0; row < rows; row++)
        {
            youroutputformat += "<tr>";
            for (int field = 0; field < columns; field++)
            {
                youroutputformat += "<td>" + result[row, field] + "</td>";
            }
            youroutputformat += "</tr>";
        }
        youroutputformat += "</table></center>";
        return;
    }
}