It is currently Thu Mar 28, 2024 2:27 pm



Reply to topic  [ 1 post ] 
c# database abstraction class 
Author Message
Felix Rex
User avatar

Joined: Fri Mar 28, 2003 6:01 pm
Posts: 16646
Location: On a slope
Reply with quote
Post c# database abstraction class
ok... I finally wrote something cool in c#. I hate c#'s implementation of database querying. I also hate the fact that you can't do multidimensional associated arrays (you can do one-dimensional ones, but bleh).

But I REALLY got tired of the fact that data coming back from a query was a pain in the ass to work with. Just like PHP, it's in its own little special format instead of something handy like, say, a multidimensional array. PHP has a cool extension (3rd party) call adoDB that addresses PHP's shortcomings, but I don't know of anything similar for c#. So I wrote something. Granted, it's fairly simple. But it's a start.

Instead of returning some sort of retarded data object, it returns a multidimensional array of database results. Any error messages are returned as an arrayList. It's all wrapped into a class you instantiate in code.

The code:
Code:
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;
    }
}


Usage example is in the final method. (which may, of course, be removed from the class). In my case the output format is html for use on a web page, but there's nothing to stop this code from being used in any c# app.

_________________
They who can give up essential liberty to obtain a little temporary safety, deserve neither liberty nor safety.


Mon May 14, 2007 7:47 am
Profile WWW
Display posts from previous:  Sort by  
Reply to topic   [ 1 post ] 

Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by STSoftware.