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<br>
#region Using Directives</p>
<p>using System;<br>
using System.ComponentModel;<br>
using System.Collections;<br>
using System.Xml.Serialization;<br>
using System.Runtime.Serialization;</p>
<p>#endregion</p>
<p>namespace MyNameSpace.Entities<br>
{<br>
///<summary><br>
/// An object representation of the “$table” table.<br>
///</summary><br>
[Serializable, DataObject]<br>
public class $table<br>
{<br>
#region Variable Declarations<br>
{loop}<br>
private $type _$field;{/loop}</p>
<p> #endregion Variable Declarations</p>
<p> #region Constructors</p>
<p> public $table()<br>
{<br>
{loop}_$field = $default;<br>
{/loop}<br>
}</p>
<p> #endregion</p>
<p> #region Properties<br>
{loop}<br>
public $type $field<br>
{<br>
get<br>
{<br>
return this._$field;<br>
}<br>
set<br>
{<br>
this._$field = value;<br>
}<br>
}<br>
{/loop}<br>
#endregion<br>
}<br>
}
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 post your comment and feedback here.