TSqlCodeGen

TSqlCodeGen is a simple template based SQL script code generator which generates code using table structure. Provide your target table and template to the TSqlCodeGen and it will generate code you for based on your template. The tool is very handy if you are dealing with a table with a large number of columns.

TSqlCodeGen - Template based SQL script code generator

TSqlCodeGen - Template based SQL script code generator

How To

Open TSqlCodeGen script in the SQL Management Studio, provide your table name and template, press F5 to execute TSqlCodeGen and have your code generated in result Pane. Copy generated code from the result pane and paste in your project.

TSqlCodeGen - Introduction

TSqlCodeGen - Introduction

You can create you own TSqlCodeGen template using following tags.

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

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

{sap}     : Separator Start Tag
{/sap}    : Separator End Tag

Usage

To understand TSqlCodeGen usage, let’s use a basic template which will generate a simple list of columns appended after the table name.

1
2
3
4
SET @Template = '
** Generate simple list of table columns saprated by comma /v1.0
$table: {loop}$field{sap}, {/sap} {/loop}
'

Generated Code:

1
Categories: CategoryID, CategoryName, Description, Picture

TSqlCodeGen - Usage With Comma

TSqlCodeGen - Usage With Comma

Example 1: Create a UPDATE Stored Procedure

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
** Generate a basic update stored procedure based on table columns /v1.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

Generated Code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE [dbo].[usp_UpdateCategories]
  @CategoryID = INT,
  @CategoryName = NVARCHAR(15),
  @Description = NTEXT,
  @Picture = IMAGE
AS
BEGIN

  UPDATE dbo.Categories WITH (ROWLOCK)
  SET 
    CategoryID = @CategoryID,
    CategoryName = @CategoryName,
    Description = @Description,
    Picture = @Picture
  WHERE --TODO

  RETURN -1
END

Example 2: Create a C# entity class

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
** Create a basic C# entity class /v1.0
using System;
using System.Collections.Generic;
using System.Text;

namespace NorthWind
{
  public class $table
    {{loop}
    public $type $field { get; set; }{/loop}
  }
}

Generated Code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
using System;
using System.Collections.Generic;
using System.Text;

namespace NorthWind
{
  public class Order
  {
    public Int32 OrderID { get; set; }
    public String CustomerID { get; set; }
    public Int32 EmployeeID { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime RequiredDate { get; set; }
    public DateTime ShippedDate { get; set; }
    public Int32 ShipVia { get; set; }
    public Double Freight { get; set; }
    public String ShipName { get; set; }
    public String ShipAddress { get; set; }
    public String ShipCity { get; set; }
    public String ShipRegion { get; set; }
    public String ShipPostalCode { get; set; }
  }
}

GIT Repository:
TSqlCodeGen

0 Comments