An Interface To A DataBase
Here is a database interface that I use extensively.
I have used it in both MySql and MS SQL 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 coltains 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 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 mysql
{
public static class DB
{
private static string InitialCatalog = "use mine";
private static string cns = "";
private static OdbcConnection Usr_cnctn = new OdbcConnection();
// ---------------------------------------------------------------------
// connect / disconnect
//
// this uses a connection string stored in a global place
// the web.config file can be used by un-commenting code following
// ---------------------------------------------------------------------
public static Exception Connect()
{
// --- get connection string from web.config ---
//NameValueCollection appnvc =
System.Web.Configuration.WebConfigurationManager.AppSettings;
//Glob.cns = appnvc["_cns"];
try
{
Usr_cnctn = new OdbcConnection(Glob.cns);
Usr_cnctn.Open();
OdbcCommand cmd = new OdbcCommand(InitialCatalog, Usr_cnctn);
cmd.ExecuteNonQuery();
}
catch (Exception se)
{
Log(cns + " : " + se.Message);
return (se);
}
return (null);
}
public static void DisConnect()
{
try
{
Usr_cnctn.Close();
}
catch (OdbcException se)
{
Log(se.Message);
}
}
// ---------------------------------------------------------------------
// 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; // # of cols in a row
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)); // col names
ty.Add(rdr.GetDataTypeName(i)); // col data type
}
// --- extract col data for each row ---
while (rdr.Read())
{
for (int i = 0; i < ncols; i++)
{
if (rdr.IsDBNull(i) == true) // special case for null
{
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;
}
// ---------------------------------------------------------------------
// Given an arraylist as defined above
// return a dictionary of the first row
// ---------------------------------------------------------------------
public static Dictionary AlToDic(ArrayList al)
{
Dictionary dic = new Dictionary(50);
int ncols = (int)al[0];
for (int i = 1; i <= ncols; i++)
{
string name = ((string)al[i]).ToLower();
string valu = ((String)(al[i + ncols]));
dic.Add(name, valu);
}
return dic;
}
// -----------------------------------------------------------------
// log into the log table
// ignore errors
// -----------------------------------------------------------------
public static void Log(string msg)
{
// To Be Implemented on a per client basis
}
}
}