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