Writing a SQL generator, What Should I Read Up On?

Question

I am soon going to be writing a component that takes metadata and generates dynamic SQL from it. Mostly we're talking SELECT, INSERT, UPDATE, DELETE stuff but I suppose it's possible to have some CREATE/ALTER TABLE statements in there too.

I'm assured that no existing ORM solution fits the bill but otherwise the details on what where and how are still a little hazy.

I can write a SQL statement same as any other developer and have used NHibernate a little in the past but I know next to nothing of the theory behind databases or ORMs. I'd like to get my prep-work in so I'm looking for any suggested reading or code to go through.

By the way my coding environment is .NET (C#) and SQL Server.

Answer 1

For SQL Server stuff there is pretty much no one on the planet who has the chops of Itzik Ben-Gan. He has several books on T-SQL itself and does an excellent job of getting you into theory and the inner workings of the language as well as the engine.

http://www.solidq.com/insidetsql/

Just to give you an idea, most people who work with SQL Server have come across the ubiquitous blog of Pinal Dave, this post from his blog about Itzik is a pretty good testimony on this guy.

http://blog.sqlauthority.com/2009/11/01/sql-authority-news-advanced-t-sql-with-itzik-ben-gan-solid-quality-mentors/

Answer 2

Here are a few SQL generation and validation resources for you:

• SQLCK: http://code.google.com/p/sqlck/

• Squiggle-SQL: http://code.google.com/p/squiggle-sql/

• Mimer SQL Validator: http://developer.mimer.com/validator/