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.