TinySQL Code Generator

Tiny is small and handy script which can quickly generate consistent code snippet that you can paste in your project. It basically operates by reading schema of provided table and applying template to each column of table in order to generate code snippet.

With Tiny, you can create complete or specific portion of busis class, busis service, or data access layer. You can also generate repetitive code snippet like storing all DataReader columns into respective property of busis object, or passing all object properties to Pmeter in data access layer, or creating stored procedures for a table or creating simple update or insert statement in stored procedure etc.

Tiny is part of my daily development practice. I am using early version of Tiny for 2 years and found myself more productive with this tool. I usually keep Management Stu open (like most Microsoft application developers) all the time with a dedicated query pane for Tiny. Whenever I need to write any repetitive code based on schema of any table, I simply provide table name, tweak template per my requirement and execute the script. I then y and paste generated code in my project. Yes, I do require making mi changes sometime in generated code to make it usable in my project but still Tiny serves its purpose.

Tiny is definitely not a replacement of sophisticated code generation tools like CodeSmith, TierDeveloper, LLBLGen etc. Those tools are very advance and can generate code for whole project including user interface, busis layer, data access layers and even store procedures. Tiny can only generate basic code snippet that you can use in your existing code or project.

Download Link:

Implementation of Tiny is very simple and straight forward. It reads schema den of all columns of provided table and template; it then render code by rotating loop for each column. Variable you have to take care in Tiny are:

@TableName: table name for which you want to generate code
@PrintTableName: how above table name should play in generated code. You may have table “tblCategories” but you want “Category” for class name.
@Template: Template for generated code

Open Tiny in Management Stu, provide table name and template, press F5 to execute Tiny and you will have generated code in result pane. you just have to y generated code from result pane and paste in your project.

To make customization of template more flexible, I have used few tags as placeholder for rendering respective entity. These tags are:

$table : Table name
$field : Column Name
$type : .NET Data Type
$default : .NET Default Value
$sp_type : Data Type
$length : Column Max Length

{loop} : Start Loop
{/loop} : End Loop

Suppose I want to generate snippet which has list of all columns of provided table. So value of Tiny variables will be:

SET @TableName = 'tblCategory'
SET @PrintTableName = 'Category'
SET @Template = '
** generate simple column list /v1.0
$table: {loop}$field, {/loop}
'

It will generate code something like this:

Category: Id, Language, ParentId, Name, SafeName, Right, Left, IsEnabled, playOrder, Color,

Note: ft line of template is comment portion which can be used as template description, author, template version, additional comment etc.

You can play with Tiny to explorer more about this tool.

Ok, so here are some practical examples of templates which explain the basic usage of Tiny:

1. Listing all fields of provided table.

$table Fields:
{loop}$field,
{/loop}

This template will generate list of field in septe li.

2. Creating Simple SELECT statement:

SELECT {loop}$field, {/loop}
FROM $table

As I mentioned before, you may require mi modification sometime in generated code to use it in your project. above case, you have to manual remove last comma from SELECT.

3. Creating UPDATE statement

UPDATE $table
SET {loop}$field = @$field,
{/loop}

Again, you require removing last comma from generated code.

4. Creating Update Stored Procedure:

** generate Data Provider: Update/v1.0
CREATE PROCEDURE [dbo].[usp_Update$table]{loop}
@$field = $sp_type,{/loop}
AS
BEGIN

UPDATE dbo.$table WITH (ROWLOCK)
SET {loop}
$field = @$field,{/loop}
WHERE --T

RETURN -1
END

5. Creating simple insert statement:

** generate simple insert statement/v1.0
INSERT INTO $table ({loop}[$field],{/loop}
VALUES ({loop}@$field,{/loop}

6. , let’s use Tiny to generate code snippet in C# (or VB.NET) which assign entity values to respective pmeters of stored procedure.

** generate Data Provider: Update Function/v1.0
Database database = new Database(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.$table_Update", _useStoredProcedure);
{loop}database.AddInPmeter(commandWrapper, "@$field", DbType.$type, entity.$field );
{/loop}

7. And as last example, here is template to generate busis entity in C#.

Like last example, you can even create complete busis service or data access layer using Tiny.

I hope you will find this tool useful. Please post your comment and back .