Author: Jeff Tucker
A user-defined function that formats the columns of a table, view, stored procedure, etc. into separate columns of code such as C# properties, Wilson ORMapper xml, and more
-- Code
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[GetTableAsCode]') AND xtype in (N'FN', N'IF', N'TF')
)
BEGIN
DROP FUNCTION [dbo].[GetTableAsCode]
END
GO
CREATE FUNCTION [dbo].[GetTableAsCode]
(
@tableName AS VARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
SELECT
ColumnName = m.[ColumnName]
,ColumnOrder = m.[ColumnOrder]
,PropertyName = m.[PropertyName]
,PropertyCode = '
private ' + m.[CSharpType] + ' ' + m.[PrivateProperty] + ';
public ' + m.[CSharpType] + ' ' + m.[PropertyName] + '
{
get { return ' + m.[PrivateProperty] + '; }
set { ' + m.[PrivateProperty] + ' = value; }
}'
,WilsonORMMappingCode = ' <attribute member="' + m.[PrivateProperty] + '" field="' + m.[ColumnName] + '" alias="' + m.[PropertyName] + '" />'
,WilsonORMHelperGet = ' case "' + m.[PrivateProperty] + '": return this.' + m.[PrivateProperty] + ';'
,WilsonORMHelperSet = ' case "' + m.[PrivateProperty] + '": this.' + m.[PrivateProperty] + ' = (' + m.[CSharpType] + ')value; break;'
,ReadReader = CASE
WHEN (m.[TypeName] = 'bit') THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? false : reader.GetBoolean(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] = 'tinyint') THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? (Int16)0 : reader.GetInt16(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] = 'int') THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? (int)0 : reader.GetInt32(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] = 'bigint') THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? (Int64)0 : reader.GetInt64(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] = 'float') THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? (Double)0 : reader.GetDouble(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] IN ('decimal', 'money', 'smallmoney')) THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? (decimal)0 : reader.GetString(' + m.[ReaderPosition] + ');')
WHEN (m.[TypeName] IN ('datetime', 'smalldatetime')) THEN ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? DateTime.Now : reader.GetDateTime(' + m.[ReaderPosition] + ');')
ELSE ('item.' + m.[PropertyName] + ' = reader.IsDBNull(' + m.[ReaderPosition] + ') ? string.Empty : reader.GetString(' + m.[ReaderPosition] + ');') END
,MaxColumnWidth = 'UNION SELECT ' + m.[ColumnName] + ', MAX(LEN([' + m.[ColumnName] + '])) FROM [' + @tableName + ']'
FROM
(SELECT
ColumnName = c.[name]
,PropertyName = REPLACE(c.[name], '@', '')
,PrivateProperty = '_' + LOWER(SUBSTRING(c.[name], 1, 1)) + SUBSTRING(c.[name], 2, LEN(c.[name]) - 1)
,ReaderPosition = CONVERT(VARCHAR, c.[colorder]-1)
,ColumnOrder = c.[colorder]
,Size = c.[prec]
,TypeName = t.[name]
,CSharpType = CASE
WHEN (t.[name]='bit') THEN ('Boolean')
WHEN (t.[name]='tinyint') THEN ('Int16')
WHEN (t.[name]='int') THEN ('Int32')
WHEN (t.[name]='bigint') THEN ('Int64')
WHEN (t.[name]='float') THEN ('double')
WHEN (t.[name] IN ('decimal', 'money', 'smallmoney')) THEN ('decimal')
WHEN (t.[name]='datetime') THEN ('DateTime')
WHEN (t.[name] IN ('nvarchar', 'varchar', 'sysname', 'sql_variant')) THEN ('String')
ELSE ('String') END
FROM
[syscolumns] c
INNER JOIN [sysobjects] o ON c.id=o.id
INNER JOIN [systypes] t ON c.[usertype] = t.[usertype]
WHERE o.[name] = @tableName) AS m
)
-- Usage
SELECT * FROM GetTableAsCode('sysobjects') ORDER BY ColumnOrder
-- Notes
-- This is provided as is.
-- KNOWN ISSUES:
-- The ReadReader column index only works if your Reader and the TableAsCode table are sorted by column order
-- The CSharp data types aren't complete or always the best choice, but they get me close.
-- I need to make some columns to address other common tasks.
-- If anyone wants to extend it or can help improve it, that would be great!
