iBatis - mapping generation - 2

iBatis requires mapping and generating xml for it can also be automated:

ALTER PROCEDURE [usp_TableToiBatisXML]
/*
Created by Michael Safro based on Cade Bryant's code.
*/
@table_name SYSNAME,
@model_name VARCHAR(200),
@prefix varchar(10)
AS
SET NOCOUNT ON
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp SELECT 1,''
-- beginning of sqlMap
INSERT INTO @temp SELECT 2,''
INSERT INTO @temp SELECT 3,''
INSERT INTO @temp SELECT 4,''
INSERT INTO @temp SELECT 5,''
INSERT INTO @temp SELECT 6,'' -- start of the result maps
INSERT INTO @temp SELECT 7,'' -- start of map
INSERT INTO @temp SELECT 8,''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 9,'' -- end of map
INSERT INTO @temp SELECT 10,'' -- end of maps...

INSERT INTO @temp SELECT 11,'' -- start of statements

-- start of select procedure
INSERT INTO @temp SELECT 12,''
INSERT INTO @temp SELECT 13, @prefix+'_'+@table_name+'_PR'
INSERT INTO @temp SELECT 14, ''
-- end of select procedure

-- start of update procedure --
INSERT INTO @temp SELECT 15,''
INSERT INTO @temp SELECT 16, @prefix+'_'+@table_name+'_PU'
INSERT INTO @temp SELECT 17, ''
-- end of update procedure --

-- start of insert procedure --
INSERT INTO @temp SELECT 18,''
INSERT INTO @temp SELECT 19, @prefix+'_'+@table_name+'_PI'
INSERT INTO @temp SELECT 20, ''
-- end of insert procedure --

-- start of delete procedure --
INSERT INTO @temp SELECT 21,''
INSERT INTO @temp SELECT 22, @prefix+'_'+@table_name+'_PD'
INSERT INTO @temp SELECT 23, ''
-- end of delete procedure --

INSERT INTO @temp SELECT 24, ''

-- start of parameter maps --
INSERT INTO @temp SELECT 25, ''

-- start of ParamSelect --
INSERT INTO @temp SELECT 26, ''
INSERT INTO @temp SELECT 27, ' INSERT NECESSARY KEYS '
INSERT INTO @temp SELECT 28, ''
-- end of ParamSelect

-- start of ParamUpdate --
INSERT INTO @temp SELECT 29, ''
INSERT INTO @temp SELECT 30, ''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 31, ''
-- end of ParamUpdate

-- start of ParamInsert --
INSERT INTO @temp SELECT 32, ''
INSERT INTO @temp SELECT 33, ''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 34, ''
-- end of ParamInsert

-- start of ParamSelect --
INSERT INTO @temp SELECT 35, ''
INSERT INTO @temp SELECT 36, ' INSERT NECESSARY KEYS '
INSERT INTO @temp SELECT 37, ''
-- end of ParamSelect

INSERT INTO @temp SELECT 38, ''

INSERT INTO @temp SELECT 39,'' -- end of sqlMap

DECLARE @code varchar(8000)
DECLARE TableCol Cursor FOR SELECT code FROM @temp ORDER BY sort
Open TableCol
FETCH NEXT FROM TableCol INTO @code
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @code + CHAR(13)
FETCH NEXT FROM TableCol INTO @code
END
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

iBatis - mapping generation

I am still new to iBatis, but find it an attractive ORM mapping technology. One of the most labor intensive tasks when mapping is to create actual maps, classes and UI based on the tables. While there are few good examples of how to do this for instance here:
I think that iBatis class/map/UI generation may have its own specifics... So I created a few scripts. Below is class generation:

ALTER PROCEDURE [usp_TableToClass]
/*
Created by Cade Bryant's.
Modified by Michael Safro to
generate VB.NET class code for a table
and fields/properties for each column.

*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class _' + @table_name + CHAR(13) + CHAR(10) + 'Inherits Helper' + CHAR(13) + CHAR(10) + 'Implements IComparable(Of _' + @table_name + ')'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region'+ CHAR(34) + 'Constructors' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public sub new ()'
+ CHAR(13) + CHAR(10) + CHAR(9) + ''
+ CHAR(13) + CHAR(10) + CHAR(9) + 'end sub'

INSERT INTO @temp
SELECT 4, '#end region' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region' + CHAR(34) + 'Private Fields' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
+ '_' + COLUMN_NAME + ' as ' + CHAR(9) +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'Nullable (Of integer) = nothing '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'Nullable(Of DateTime) = nothing '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) = nothing '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable(Of decimal) = nothing '
ELSE 'object '
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#end region' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region' + CHAR(34) + 'Public Properties' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public property ' +
+ COLUMN_NAME + ' as ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN ' Nullable (Of integer) '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'Nullable (Of DateTime) '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable(Of decimal) '
ELSE 'object '
END +
CHAR(13) + CHAR(10) + CHAR(9) + '' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get' + CHAR(13) + CHAR(10) + 'return _' + COLUMN_NAME + CHAR(13) + CHAR(10) + ' end get' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set (byval value as ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN ' Nullable(Of integer)'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN ' Nullable(Of DateTime) '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable (Of decimal) '
ELSE 'object '
END
+' )' + CHAR(13) + CHAR(10) + '_' + COLUMN_NAME + ' = value' + CHAR(13) + CHAR(10) + 'end set' +
CHAR(13) + CHAR(10) + CHAR(9) + ' end property' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#end region'

INSERT INTO @temp
SELECT 11, '#Region "CRUD Procedures"'

INSERT INTO @temp
SELECT 13, 'Public Function Obtain(ByVal p'+ @table_name +' as Integer) as IList (Of _'+ @table_name +')' + CHAR(13)+CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'return Mapper.QueryForList(Of _' + @table_name + ')("SelectFrom'+@table_name+'")'+CHAR(13)+CHAR(10)+
+ CHAR(39)+'INSERT NECESSARY KEYS' + +CHAR(13)+CHAR(10)+
+ 'End Function'

INSERT INTO @temp
SELECT 14, 'Public Sub Delete(ByVal p'+ @table_name +' as Integer)' + CHAR(13)+CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Delete("Delete'+@table_name+'")'+CHAR(13)+CHAR(10)+
+ CHAR(39)+'INSERT NECESSARY KEYS' + +CHAR(13)+CHAR(10)+
+'End Sub'

-- start of update sub --
INSERT INTO @temp
SELECT 15, 'Public Sub Update()' + CHAR(13)+ CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Update("Update'+@table_name+'",Me)'+ CHAR(13)+ CHAR(10)+
+'End Sub'
-- end of update sub --


-- start of Insert sub --
INSERT INTO @temp
SELECT 16, 'Public Sub Insert()' + CHAR(13)+ CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Insert("Insert'+@table_name+'",Me)'+ CHAR(13)+ CHAR(10)+
+'End Sub'
-- end of Insert sub --

INSERT INTO @temp
SELECT 17, '#End Region'


-- start of Insert sub --
INSERT INTO @temp
SELECT 16, 'Public Function CompareTo(ByVal other as _'+@table_name+') as Integer Implements System.IComparable (Of _'+@table_name+').CompareTo' + CHAR(13)+ CHAR(10)+

+'End Function'
-- end of Insert sub --


INSERT INTO @temp SELECT 18, CHAR(13) + CHAR(10) + ' end class'
-- end of code

DECLARE @code varchar(8000)
DECLARE TableCol Cursor FOR SELECT code FROM @temp ORDER BY sort
Open TableCol
FETCH NEXT FROM TableCol INTO @code
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @code + CHAR(13)
FETCH NEXT FROM TableCol INTO @code
END
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

Start of my technology blogging

Tempted by the http://www.continuouslylearning.blogspot.com/, belonging to Perry McKenzie, whom I have an honor to work with, I decided to start my own little tech blog.