Welcome to Particletree!

Introduction

Database programming is like a confused little monkey. Puddlin’ on the floor and screaming at the walls, it’s a tedious little thing that’s prone to memory leaks and lots of errors and mistakes. To the dismay of anyone hoping to avoid a headache, database programming is ridiculously redundant and incredibly boring. Because web development loves its little monkey sooooo much, it’s unfortunately here to stay.

Today, however, we’re going to show a little love to the thing we all love to hate, and go over a small, clean and reusable class to help us in all our projects. This class will improve our relationship with programming databases in the following ways:

• First, it will place all of your database code in a single file. If you ever need to alter a function, the change carries through to the entire project.

• Second, it makes your life easier by handling INSERT and UPDATE string syntax for you. Because most errors occur while modifying a database, it’s just nice to have a little help. Hopefully, you will never encounter a missing apostrophe ever again.

• Third, and most importantly, it’s going to remove most of your redundant code. This class can also handle the creation and deletion of objects and you’ll ever need to get out your old project to find out how to do “that database thing you did.”

The Code

The database class that follows consists of 4 functions and is portable across most databases (however, it’s only been tested on SQL Server and MySQL). Because we here at Particletree are code agnostic about these things, we got it in three languages for you today.

• Download PHP Version
• Download ASP Version
• Download VB.NET Version

Implementation

1) Include the class in your file.

//PHP
include "cDatabase.php";

.

//ASP
<!--#include file="cDatabase.asp" -->

.

//.NET
Imports projectName.cDatabase

2) If you are following along with us, you’ll need to create a simple database table. We’re going to name it “tPersons” and it’ll have the following 4 fields: - personID - firstName - lastName - age

3) Change the connection string at the top of the class to match your database. If your using PHP or .NET, this is where the object is created.

//PHP
$cDB = new cDatabase(host, user, password, database);

.

//ASP
sMysqlConn = "Your connection string here!"

.

//.NET
Dim cDB as New cDatabase(connString)

4) And you’re done. Now you can call the functions listed below.

sqlInsert

This is used to get data into your database. To call the sqlInsert function, you’ll need to create two arrays, and a reference to those arrays and our demo table called ‘tPersons’.

//PHP
$names = array("firstName", "lastName", "age");
$values = array("ryan", "campbell", "22");
$cDB->sqlInsert($names, $values, "tPersons");

.

//ASP
dataNames = array("firstName", "lastName", "age")
dataValues = array("Ryan", "Campll", "22")
sqlInsert dataNames, dataValues, "tPerson"

.

//.NET
dim dataNames() as String = {"firstName", "lastName", "age"}
dim dataValues() as String = {"Ryan", "Campll", "22"}
cDB.sqlInsert(dataNames, dataValues, "tPerson")

The first array consists of strings naming the database field you wish to have inserted. The second array contains the string values corresponding to the first array. Order is important here, the positions in the arrays must match.

sqlUpdate

As you may know, sql UPDATE statements modify existing records in a database. ‘sqlUpdate()’ works almost identical to ‘sqlInsert()’ above, except it adds a fourth paramter called condition. The condition is simply a string stating the sql condition statement a record must meet in order to be affected by the update.

//PHP
$names = array("lastName");
$values = array("campbell");
$cDB->sqlUpdate($names, $values, "tPersons", "WHERE firstName = 'Ryan'");

.

//ASP
dataNames = array("lastName")
dataValues = array("Campbell")
sqlUpdate dataNames, dataValues, "tPerson", "WHERE firstName = 'Ryan'"

.

//.NET
dim dataNames() as String = {"lastName"}
dim dataValues() as String = {"Campbell"}
cDB.sqlUpdate(dataNames, dataValues, "tPerson", "WHERE firstName = 'Ryan'")

Also note that the arrays do not need to contain values for all of the fields. If you do not reference a field in the array, it will not be affected by the update. In the example above, only records with a first name of “Ryan” will have their last name updated to “Campbell”.

ExecuteReader

My favorite function in the class. ‘ExecuteReader()’ will return a recordset of all matching records in a SELECT query. You do not have to create any objects, or worry about the execution of the query. Pass in a string with a properly formatted sql select statement, and a recordset is returned for you to do as you will. Here’s an example:

//PHP
$rs = $cDB->ExecuteReader("SELECT * FROM tPersons");
while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
  echo $row['firstName'];
}

.

//ASP
rs = ExecuteReader("SELECT * FROM tPerson " & "WHERE firstName = 'Ryan'")'Write the first and last name if the recordset is not empty.
if not rs.EOF then 
  response.write rs("firstName") & " " & rs("lastName")
end if
rs.close

.

//.NET
Dim rs As SqlDataReader
rs = cDB.sqlSelect("SELECT * FROM tPersons")        
If rs.Read Then
   response.write(rs("firstName"))
End Ifrs.Close()
rs = Nothing

What’s also nice about this function is that it minimizes the chance memory leaks.

ExecuteNonQuery

‘ExecuteNonQuery’ executes a query that doesn’t return any data. The “NonQuery” part is actually a misnomer and comes from a function that Microsoft introduced in .NET. I liked it so much that I ported to PHP and ASP here. One way to use this function is to remove data from a database. Basically, DELETE statements are just strings that need to be executed. Here is how we would execute a DELETE statement using ‘ExecuteNonQuery()’.

//PHP
$cDB->ExecuteNonQuery("DELETE tPerson WHERE firstName = 'Ryan'");

.

//ASP
ExecuteNonQuery("DELETE tPerson WHERE firstName = 'Ryan'")

.

//.NET
cDB.ExecuteNonQuery("DELETE tPerson WHERE firstName = 'Ryan'")

Depending on the language you’re using, ‘ExecuteNonQuery’ can save you up to 6 lines of code each time you call it.

How It Works

So now that you know how to call the functions, let’s look at what’s under the hood.

sqlInsert

//PHP
function sqlInsert($dataNames, $dataValues, $tableName)
{
    $sqlNames = "INSERT INTO " . $tableName . "(";
    for($x = 0; $x < count($dataNames); $x++) {
        if($x != (count($dataNames) - 1)) {
            $sqlNames = $sqlNames . $dataNames[$x] . ", ";
            $sqlValues = $sqlValues . "'" . $dataValues[$x] . "', ";
        }
        else {
            $sqlNames = $sqlNames . $dataNames[$x] . ") VALUES(";
            $sqlValues = $sqlValues . "'" . $dataValues[$x] . "')";
        }
    }
    echo $sqlNames . $sqlValues;
    ExecuteNonQuery($sqlNames . $sqlValues);
}

.

//ASP
function sqlInsert(dataNames, dataValues, tableName)
   sqlNames = "INSERT INTO " & tableName & "("
   for x = 0 to ubound(dataNames)
      if x <> ubound(dataNames) then
         sqlNames = sqlNames & dataNames(x) & ", "
         sqlValues = sqlValues & "'" & dataValues(x) & "', "
      else
         sqlNames = sqlNames & dataNames(x) & ") VALUES
         sqlValues = sqlValues & "'" & dataValues(x) & "')"
      end if
   next   ExecuteNonQuery(sqlNames & sqlValues)
end function

.

//.NET
Public Sub sqlInsert(ByVal dataNames(), ByVal dataValues(), ByVal tableName)Dim sqlNames As String
Dim sqlValues As String
Dim x As IntegersqlNames = "INSERT INTO " & tableName & "("
For x = 0 To UBound(dataNames)
    If x <> UBound(dataNames) Then
        sqlNames = sqlNames &     Replace(dataNames(x), "'", "''") & ", "
        sqlValues = sqlValues & "'" & Replace(dataValues(x), "'", "''") & "', "
    Else
        sqlNames = sqlNames & Replace(dataNames(x), "'", "''") & ") VALUES("
        sqlValues = sqlValues & "'" & Replace(dataValues(x), "'", "''") & "')"
    End If
NextExecuteQuery(sqlNames & sqlValues)End Sub

Basically, ‘sqlInsert()’ loops through the name / value paired arrays, and builds the sql string. The resulting string will appear in the following proper syntax:

INSERT INTO tPersons(firstName, lastName, age) 
VALUES('Ryan', 'Campll', '22')

Also note the call to ‘ExecuteNonQuery()’ which executes the query. We’ll look at this function at the end.

sqlUpdate

//PHP
function sqlUpdate($dataNames, $dataValues, $tableName, $condition)
{
    $sql = "UPDATE " . $tableName . " SET ";
    for($x = 0; $x < count($dataNames); $x++) {
        if($x != (count($dataNames) - 1)) {
            $sql = $sql . $dataNames[$x] . "= '" . $dataValues[$x] . "', ";
        }
        else {
            $sql = $sql . $dataNames[$x] . "= '" . $dataValues[$x] . "' ";
        }
    }
    $sql = $sql . $condition;
    echo $sql;
    ExecuteNonQuery($sql)
}

.

//ASP
function sqlUpdate(dataNames, dataValues, tableName, condition)
   sql = "UPDATE " & tablename & " SET "
   for x = 0 to ubound(dataNames)
      if x <> ubound(dataNames) then
         sql = sql & dataNames(x) & "= '" & dataValues(x) & "', "
      else
         sql = sql & dataNames(x) & "= '" & dataValues(x) & "' "
      end if
   next
   sql = sql & condition
   ExecuteNonQuery(sql)
end function

.

//.NET
Public Sub sqlUpdate(ByVal dataNames(), ByVal dataValues(), ByVal tableName, ByVal condition)Dim sql As String
Dim x As Integersql = "UPDATE " & tableName & " SET "
For x = 0 To UBound(dataNames)
    If x <> UBound(dataNames) Then
        sql = sql & dataNames(x) & "= '" & Replace(dataValues(x), "'", "''") & "', "
    Else
        sql = sql & dataNames(x) & "= '" & Replace(dataValues(x), "'", "''") & "' "
    End If
Next
sql = sql & conditionExecuteQuery(sql)End Sub

‘sqlUpdate()’ loops through the name / value paired arrays, and builds the sql string. The resulting string will appear in proper syntax:

UPDATE tPersons 
SET lastName = 'Campbell'
WHERE firstName = 'Ryan'

ExecuteReader

//PHP
function ExecuteReader($query){
    $conn = mysql_connect($this->host, $this->user, $this->password);
    mysql_select_db ($this->database);
    $rs = mysql_query($query,$conn);
    mysql_close($conn);
    return $rs;
} 

.

//ASP
function ExecuteReader(sql)
  Set oConn = Server.CreateObject("ADODB.Connection")  oConn.Open sMysqlConn
  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.CursorLocation = 3
  rs.Open sql, oConn, 3, 4  
  set rs.ActiveConnection = Nothing
  Set ExecuteReader = rs
  oConn.Close
  Set oConn = Nothing
  Set rs = Nothing
end function

.

//.NET
Public Function sqlSelect(ByVal sQuery As String) As SqlDataReader    Dim conn As SqlConnection
    Dim db As SqlCommand
    Dim rs As SqlDataReader    conn = New SqlConnection(eConn)
    conn.Open()
    db = New SqlCommand(sQuery, conn)
    rs = db.ExecuteReader(CommandBehavior.CloseConnection)
    Return rsEnd Function

The key line to point out here (Does not apply to PHP) is the following:

//PHP
This excerpt applies to ASP and .NET only.

.

//ASP
set rs.ActiveConnection = Nothing

.

//.NET
rs = db.ExecuteReader(CommandBehavior.CloseConnection)

Normally you may try to return a recordset before everything has been properly closed. This line allows you to close all database connections, but keep the recordset active. This prevents memory leaks from occuring. When you call this function, you are given a handle on the object that allows you to close it anywhere in your code.

ExecuteNonQuery

//PHP
function ExecuteNonQuery($sql){
    $conn = mysql_connect($this->host, $this->user, $this->password);
    mysql_select_db ($this->database);
    $rs = mysql_query($sql,$conn);
    settype($rs, "null");
    mysql_close($conn);
}

.

//ASP
sub ExecuteNonQuery(sql)
  set db = Server.CreateObject("ADODB.Connection")
  db.open sMysqlConn
  set rs = db.execute(sql)
  set rs = nothing
  db.close
  set db = nothing
end sub

.

//.NET
Public Sub ExecuteQuery(ByVal sQuery As String)    Dim conn As SqlConnection
    Dim db As SqlCommand    conn = New SqlConnection(eConn)
    conn.Open()
    db = New SqlCommand(sQuery, conn)
    db.ExecuteNonQuery()    conn.Close()
    db.Dispose()End Sub

This function is referenced by ‘sqlInsert()’ and ‘sqlUpdate()’. This is a core function because it creates and destroys the objects needed to execute any database operation. I spoke about redundancy before: These are the 6 lines you would write whenever you wanted to communicate with a database, and now they are consolidated into one function.

PHP Users: Note that you have to use the appropriate function for your database (ie: mysql_close vs mssql_close) and modify the connection string in this function.

Addendum

Obviously, these classes can easily be enhanced / expanded upon and so if you do so in any way, I’d love to update them and give you credit for it. As always, comments and suggestions are always welcome. Let me also point out that I intentionally left out error handling to make the tutorial easier to understand. Be sure that you’re also error checking your database activity. Because this tutorial covers 3 different languages, I didn’t get a chance to really dive into the particular quirks of each language. If you have any questions, please let me know.

Additional Help

If you need to touch up on your database skills check out the following resources for each language:

• PHP : The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

• ASP : Introduction to ActiveX Data Objects

• .NET : Efficiently Iterating Through Results from a Database Query using ADO.NET