SQL Helper
If you’re still one of the few that don't use DeKlarit you'll probably like this tool. I called it SQL Helper, and basically what it does is create the SQL statements you need in order to INSERT, UPDATE, DELETE or SELECT your own objects to a database.
I've been having this idea for a long time now, but it was a few weeks/months ago that my friend Alvarenga introduced to me the PropertyInfo class and it all became clear then.
There are only a few rules you must be careful with in order to use this tool. First of all there’s a naming convention, there's always one, and I'll explain how it works with an example.
Let's say you have to manage Clients. You'll probably have a ‘Client’ class with properties like FirstName, LastName, BirthDate, and so on. In order to get the appropriate SQL statements you'll have to name a table ‘Client’ like your class, and all the columns in that table must be name as the class' attributes. So, for our example, you'll also have a ‘Class’ table with FirstName, LastName and BirthDate as columns/attributes.
If you created that scenario you'll be able to call all the functions from the DDLStatement class.
For the UPDATE, DELETE and SELECT statements there are two functions. One of them gets the object you want the sentence to be created from, and the second parameter is the name of the attribute that holds the primary key in your database. In short, the 'WHERE' part of the sentence.
But if you're starting from scratch you can make your domain classes inherit from 'IDKeyObject'. This is an abstract class that it only has one attribute, 'ID', which is the one I assume is your PK. So, if you pass an instance of any derived class there's no need to tell the PK attribute.
Give it a try, and let me know how can I improve it. It's only been tested in SQL Server, but I think I'm using standard ANSII SQL, so, it should work for every DBMS.
Well, actually there's a function of the DDLStatement class which returns the CREATE TABLE statement to create a table from a class, and the data types I use there are the ones from SQL Server, I guess I could have parameterized than :(
Posts
