TinySQL - Getting Started

Coordinator
Oct 11, 2007 at 11:20 PM
Edited Oct 11, 2007 at 11:41 PM
Open TinySQL script in SQL Management Studio, provide table name and template, press F5 to execute TinySQL and you will have code generated in result pane. Now you just have to copy generated code from result pane and paste in your project.

You can create you own template using below tags as a 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}