Saturday 14 May 2011

CRUD ASP.NET and SQL Script Generator

This is the first post in a little while (up-to-my-eyes lately) - anyway, let's get down to business.

Today (like many other days) I find myself wiring the four basic functions of persistent storage – Create, Read, Update and Delete (CRUD) – into my ASP.NET forms application. It's integral to what we do as developers...but it's tedious...really tedious...and so rather than bob along in this arid sea of tranquillity I wondered if there was something that could do this for me? The answer is 'yes' there probably is – but that might not be as flexible, simple (or half as much fun) as rolling my own little ASP.NET and SQL Script Generator.

What I wanted was a really simple way of typing in the field names, selecting the desired controls and having the create table, the stored procedures and the ASP.NET controls all created for me; then I wanted to be able to save the definition and load it all back in again if I needed to make any changes or if I wanted to come back to it later - and I wanted to do all of this in under a day! Thankfully, developing a CRUD ASP.NET and SQL script generator turned out to be a whole lot easier than I imagined, by catering to ASP.NET with a dash of XML and a sprinkle of XSLT it was baked in next to no time.

The interface allows you to input a table name and select a template (this points to an XSLT template to create the output). Clicking on the 'Load Sample' link below the 'Definition' heading will load a sample definition along with its output.

Once you enter a definition into the grid, either by typing it in or by uploading one that you created earlier, just select a template and click on the generate files button to see the SQL and ASP.NET created automatically; then simply execute the SQL script against your database and copy the ASP.NET into your page.


This works by converting your input into XML and then translating it using XSLT templates in just a few lines of code; the same XML methods allow you to download or upload the XML description and regenerate your scripts just as easily.

You can use this online at http://code.zyky.com/crudgenerator/ or download the source code and add your own templates. Give it a go, it's saved me oodles of time already.

Demo

View a live demo at http://code.zyky.com/crudgenerator/

Code

Download the Source Code