An Interface To A DataBase


Here is a database interface that I use extensively with MySql projects.
This API converts all of the database types to string on retrieval which makes object persistence routine and negates the need for 3rd party mapping tools. No matter how you have described the fields of your database tables when you use this API to retrieve the data you will be presented with an array of strings and when you write data back to the database field values enclosed in single quotes are converted by the database engine into the database field type.
There are only four routines necessary...Connect(), Disconnect(), ExecImm(), and ExecWithResponse(). Connect and Disconnect are self explanatory. ExecImm is used when there are no results expected, eg Insert, Update, and Delete sql statements.

ExecWithResponse returns the results of a query in an arraylist. The first element is the number of field names returned in the second through nth element. Element n+1 contains the value for the first column of row 1, element 2n+1 contains the value for the first column of row 2.
So, if you executed the SQL statement "SELECT MAX(test_score) FROM student" then the returned arraylist (al) would have the max value of the test_score column in element al[2].

al[0] = 1
al[1] = "test_score"
al[2] = "the score"

And if you executed the SQL statement "SELECT name, MAX(test_score) FROM student" then the returned arraylist would look like ...

al[0] = 2
al[1] = "name"
al[2] = "test_score"
al[3] = "the_students_name"
al[4] = "the_students_score"

And if you executed the SQL statement "SELECT name, test_score FROM student" then the returned arraylist would look like ...

al[0] = 2
al[1] = "name"
al[2] = "test_score"
al[3] = student_1_name
al[4] = student_1_score
al[5] = student_2_name
al[6] = student_2_score

...

Here is most of the code .....

    using System;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Data.Odbc;					// using MySql
    using System.Text;
    using System.Web;
    using System.Web.UI;

    namespace perspage
    {
	    public static class DB
	    {
		    // ---------------------------------------------------------------------
		    //		execute a command without a response
		    //		set msg if no rows are effected
		    // ---------------------------------------------------------------------
		    public static bool ExecImm(string stmt)
		    {
			    bool rc = true;
			    int nrows;

			    OdbcCommand cmd = new OdbcCommand("", Usr_cnctn);
			    cmd.CommandText = stmt;
			    try
			    {
				    nrows = cmd.ExecuteNonQuery();
				    if (nrows <= 0)
				    {
					    rc = false;
					    Debug.WriteLine("No rows affected");
				    }
			    }
			    catch (OdbcException se)
			    {
				    Log(stmt + " : " + se.Message);
				    rc = false;
			    }
			    return rc;
		    }

		    // ---------------------------------------------------------------------
		    //  execute a query and read the response
		    //  return the response in an array list whose format is ...
		    //      al[0] the number of columns in each row returned
		    //      al[1] ... [i] the names of the columns
		    //      al[j] ... [k] the values of each row, col
		    //	 If there is no data (al[0]+1) will = al.Count
		    //
		    //	The correct reader 'GET' routine is called by using the 
		    // type of the cell
		    // ---------------------------------------------------------------------
		    public static ArrayList ExecWithResp(string stmt)
		    {
			    ArrayList al = new ArrayList();
			    ArrayList ty = new ArrayList();
			    int ncols;
			    string dbtyp = "";

			    try
			    {
				    OdbcCommand cmd = new OdbcCommand("", Usr_cnctn);
				    cmd.CommandText = stmt;
				    OdbcDataReader rdr = cmd.ExecuteReader();
				    ncols = rdr.FieldCount;							
				    al.Add(ncols);
				    ty.Add(ncols);

				    // --- extract col names and db data types ---
				    for (int i = 0; i < ncols; i++)
				    {
					    al.Add(rdr.GetName(i));							
					    ty.Add(rdr.GetDataTypeName(i));					
				    }

				    // --- extract col data for each row ---
				    while (rdr.Read())
				    {
					    for (int i = 0; i < ncols; i++)
					    {
						    if (rdr.IsDBNull(i) == true) 
						    {
							    al.Add("");
							    continue;
						    }
						    dbtyp = rdr.GetDataTypeName(i).ToLower();
						    switch (dbtyp)
						    {
							    case "char":
							    case "nchar":
								    object ob = rdr.GetValue(i);
								    string s0 = Convert.ToString(ob);
								    al.Add(s0.Trim());
								    break;

							    case "text":
							    case "varchar":
							    case "ntext":
							    case "nvarchar":
								    al.Add(rdr.GetString(i));
								    break;

							    case "int":
								    al.Add(rdr.GetInt32(i).ToString());
								    break;

							    case "smallint":
								    al.Add(rdr.GetInt16(i).ToString());
								    break;

							    case "tinyint":
								    al.Add(rdr.GetByte(i).ToString());
								    break;

							    case "bigint":
								    al.Add(rdr.GetInt64(i).ToString());
								    break;

							    case "bit":
								    al.Add(rdr.GetBoolean(i).ToString());
								    break;

							    case "money":
							    case "smallmoney":
							    case "numeric":
							    case "decimal":
								    al.Add(rdr.GetDecimal(i).ToString());
								    break;

							    case "real":
							    case "float":
								    ob = rdr.GetValue(i);
								    Double dbl = Convert.ToDouble(ob);
								    al.Add(dbl.ToString());
								    break;

							    case "datetime":
							    case "date":
							    case "time":
							    case "datetime2":
							    case "smalldatetime":
							    case "datetimeoffset":
								    al.Add(rdr.GetDateTime(i).ToString());
								    break;

							    case "cursor":
							    case "timestamp":
							    case "uniqueidentifier":
							    case "xml":
							    case "hierarchyid":
							    case "Odbc_variant":
							    case "table":
								    al.Add(rdr.GetDataTypeName(i));
								    break;
						    }
					    }
				    }
				    rdr.Close();
			    }
			    catch (Exception se)
			    {
				    Log(stmt + " : " + se.Message);
				    al.Add(0);
			    }
			    return al;
		    }
	    }
    }