Walking on water and developing software from a specification are easy if both are frozen.

– Edward V Berard

I just released TinySQL code generator v2.0 with few bug fixes and a new feature of using separator tag for loop statement. You can now use {sap}{/sap} tags to place your separators inside loop.

Here is the example of using {sap}{/sap} tags in TinySQL template:

** Generate DATA Provider: UPDATE/v2.0
CREATE PROCEDURE [dbo].[usp_Update$table]{loop}
	@$field = $sp_type{sap},{/sap}{/loop}
AS
BEGIN
UPDATE dbo.$table WITH (ROWLOCK)
SET {loop}$field = @$field{sap},{/sap}
	{/loop}
WHERE --TODO
RETURN -1
END

And generated code from above template will be like:

CREATE PROCEDURE [dbo].[usp_UpdateAction]
	@Id = NUMERIC,
	@Action = VARCHAR(250),
	@ProjectId = NUMERIC,
	@IsNextAction = BIT,
	@IsWaitingFor = BIT,
	@WaitingForNotes = VARCHAR(1000),
	@RemindOn = DATETIME,
	@IsDefer = BIT,
	@DeferDate = DATETIME,
	@IsDone = BIT,
	@Sequence = INT
AS
BEGIN
UPDATE dbo.Action WITH (ROWLOCK)
SET Id = @Id,
	Action = @Action,
	ProjectId = @ProjectId,
	IsNextAction = @IsNextAction,
	IsWaitingFor = @IsWaitingFor,
	WaitingForNotes = @WaitingForNotes,
	RemindOn = @RemindOn,
	IsDefer = @IsDefer,
	DeferDate = @DeferDate,
	IsDone = @IsDone,
	Sequence = @Sequence
WHERE --TODO
RETURN -1
END

Download: TinySQL v2.0

You can also read my previous blog to learn more about TinySQL tool.
TinySQL Code Generator

Hope you will find this tool useful.

TinySQL is small and handy SQL 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 TinySQL, you can create complete or specific portion of business class, business service, or data access layer. You can also generate repetitive code snippet like storing all DataReader columns into respective property of business object, or passing all object properties to SqlParameter in data access layer, or creating stored procedures for a table or creating simple update or insert statement in stored procedure etc.

TinySQL is part of my daily development practice. I am using early version of TinySQL for 2 years now and found myself more productive with this tool. I usually keep SQL Management Studio open (like most Microsoft application developers) all the time with a dedicated query pane for TinySQL. 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 copy and paste generated code in my project. Yes, I do require making minor changes sometime in generated code to make it usable in my project but still TinySQL serves its purpose.

TinySQL 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, business layer, data access layers and even store procedures. TinySQL can only generate basic code snippet that you can use in your existing code or project.

Download Link: TinySQL

Implementation of TinySQL is very simple and straight forward. It reads schema definition 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 TinySQL are:

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

Open TinySQL in SQL Management Studio, provide table name and template, press F5 to execute TinySQL and you will have generated code in result pane. Now you just have to copy 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  : SQL 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 TinySQL 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, DisplayOrder, Color,

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

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

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

1. Listing all fields of provided table.

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

This template will generate list of field in separate lines.

2. Creating Simple SELECT statement:

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

As I mentioned before, you may require minor modification sometime in generated code to use it in your project. Like in 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 --TODO
 
	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. Now, let’s use TinySQL to generate code snippet in C# (or VB.NET) which assign entity values to respective parameters of stored procedure.

** generate DATA Provider: UPDATE FUNCTION/v1.0
SqlDatabase DATABASE = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(DATABASE, "dbo.$table_Update", _useStoredProcedure);
{loop}DATABASE.AddInParameter(commandWrapper, "@$field", DbType.$type, entity.$field );
{/loop}

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

** generate basic business entity/v1.0
#region Using Directives
 
using System;
using System.ComponentModel;
using System.Collections;
using System.Xml.Serialization;
using System.Runtime.Serialization;
 
#endregion
 
namespace MyNameSpace.Entities
{
    ///<summary>
    /// An object representation of the "$table" table.
    ///</summary>
    [Serializable, DataObject]
    public class $table
    {
        #region Variable Declarations
		{loop}
		private $type _$field;{/loop}
 
        #endregion Variable Declarations
 
        #region Constructors
 
        public $table()
        {
		 {loop}_$field = $default;
		{/loop}
		}
 
        #endregion
 
        #region Properties
		{loop}
		public $type $field
        {
            get
            {
                return this._$field;
            }
            set
            {
                this._$field = value;
            }
		}
		{/loop}
        #endregion
    }
}

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

I hope you will find this tool useful. Please do let me know if you found any bug or feature request.

UPDATE:
Please take latest version from TinySQL v2.0

Here is another good reason of why product testing is always important.

Look like someone did last minute adjustment before delivery.

If anyone asks me which keystroke combination I used most while computing, I would unhesitatingly say its Alt-SpaceBar (yes! it’s not even Alt-F4 for closing window).

I am not sure if this key combination is generally used by most users, but I love this combination as it allows me to close, minimize, maximize or even restore any window using keyboard and therefore I have another reason of not using mouse. Now I can always keep my hand on keyboard even if I need to do some activities which can only possible using mouse and I no more require to shift my hand from keyboard to mouse and then again from mouse to keyboard just to minimize or maximize window.

Here is the Alt-SpaceBar keystroke combination list:
Alt-SpaceBar + c – Closing Window
Alt-SpaceBar + n – Minimizing Window
Alt-SpaceBar + x – Maximizing Window
Alt-SpaceBar + Enter – Restore Window

Like for closing a window, simply press Alt and SpaceBar which will open a control popup menu and then press c. You will feel more comforting in pressing these keys as they are more closer to you as compare to F4 which is that top of the keyboard.

It might seem bit awkward and complex key combination first few times (as I was feeling at initial stage) but with regular use, I am sure you will also love this key stroke combination.