For the New Year, I’ve added a new habit. Each work day, I’ll review what programming improvement I learned that day. I’ll write them up here, as a way for me to remember and share with other developers (and also, maybe get some feedback from other developers that will help me improve even more).
I’m currently developing a .Net library to let developers write SQL queries, without writing SQL. They’ll call the library’s methods, and it will build them a SQLCommand object.
The SQLCommand object’s CommandText will be written appropriately for the database they specify. Currently, only MS SQL 2008 is available; however, I plan to add several other common databases. The SQLCommand object will also be parameterized, to prevent SQL injection problems.
This project is a spin-off from the IDNORM project I’ve been experimenting with.
A client recently wanted the ability to deploy their web application against multiple database back-ends (depending on what their client uses).
At first, I built something that had multiple SQL statements – one for each database type. However, one night, while working on the IDNORM project, I realized I could take parts of that to build what they needed. Fortunately, the queries they have aren’t very complex, and the IDNORM code could handle them.
Now, I’m trying to see if I can build a more robust library to handle complex queries (the WHERE clauses can get tricky), and be useful to other programmers.
Anyway, on to the improvement of the day.
One of my concerns was that not all methods were required to be called before building the SQLCommand object.
A programmer could possibly create a DeleteQuery object and never set a WHERE condition for it. That would create a query that would delete all the rows in the specified table – which may have been on oversight on the developer’s part.
So, I’ve added a new method to the DeleteQuery and UpdateQuery objects – ApplyToAllRows().
If the developer doesn’t add any WHERE conditions to their query – by calling the Where(columnName, comparator, value) method – then they need to explicitly call ApplyToAllRows(). Without calling one of these methods, the library will raise an exception when building the SQLCommand object.
This will make the library much safer to use.
Today’s lesson: When writing library code, ensure you don’t leave ways for users to inadvertently do something dangerous.