In a previous article, I showed how to create a class which returns your recordset as an array using the GetRows function. In this article, I will expand on the class, adding two more functions: one that adds a new record to your table, and one that edits a row in your table.
Expanded Class
We will be adding these new properties/methods to our class:
| Database | |
|---|---|
| Properties | +Output : String |
| Methods | +AddNew(in table : String, in fields : Array, in values : Array) : Boolean +Update(in table : String, in id : Double, in fields : Array, in values : Array) : Boolean |
The AddNew method loops through the array of the fields passed to it, and for each one assigns it the value in the matching array of the values. The table to add a new row is passed as a parameter, but if you are only making changes to one table you can hard code that in the method. A nice feature with this method, is that after the addition of the row, you may wish to return any fields that are created automatically, like the creation time or the ID. For each value you want returned, save it locally within the class after the update, and then create a Get Property for it. You can see an example in the class of returning the ID field.
The Update method also loops through the fields and updates them with their corresponding values. I select what row to update based on a table and an ID that is passed in. Then I create the query.
Using AddNew and Output
The code above shows a very common scenario in a database INSERT: create a new record and get back the newly created primary key (ID). Let’s go through the code. First, we create two local arrays: one holds the field names of the row in the table we are inserting, and the other the actual values we are inserting. These could come for example from a form post. Then we create an instance of the class, and we call the AddNew method, passing the necessary parameters to it. One of those parameters is the table name we are inserting to; change this to your needs. The Output property returns the newly created primary field (ID) of the row.
Using Update
Again, we create two arrays, one containing the fields to be updated and the other containing the values to be updated to.
Conclusion: complete Class code
| Database | |
|---|---|
| Properties | +Output : String |
| Methods | -Class_Initialize() +GetArray(in strQuery : String) : Array +AddNew(in table : String, in fields : Array, in values : Array) : Boolean +Update(in table : String, in id : Double, in fields : Array, in values : Array) : Boolean -Class_Terminate() |
