generateTableVoClass.sql

/* ----------------------------------------------------------------------------
Simple tsql C# vo class generator

Pass in the name of the table to create a class for, and the name for your
resulting class

Assumptions : 
- This script dumps a C# class representation of a sql database table

- MS tsql datatypes were used for C# converstion
see : http://msdn.microsoft.com/en-us/library/ms131092.aspx

Usage :
1.Run it
	EXECUTE generateTableVoClass 'YourTableName', 'yourtableclass'

2.Copy generated rows into your .cs class file

---------------------------------------------------------------------------- */

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'generateTableVoClass' 
)
   DROP PROCEDURE generateTableVoClass
GO

CREATE PROCEDURE generateTableVoClass
	(
		@tbl_name varchar(100), 
		@class_name varchar( 100 )
	)
AS

-------------------------------------------------------------------------------

	---------

	SELECT	'public class ' + @class_name AS 'copy all rows to your CS class file'

	---------
	UNION ALL 

	SELECT	'{'

	---------
	UNION ALL 

	SELECT	char(10) + char(10) + char(10) + char(10) + 
			'public '
			+
			CASE DATA_TYPE
				WHEN 'bigint' THEN 'Int64'
				WHEN 'bit' THEN 'Boolean'
				WHEN 'char' THEN 'string'
				WHEN 'date' THEN 'DateTime'
				WHEN 'datetime' THEN 'DateTime'
				WHEN 'decimal' THEN 'Decimal'
				WHEN 'float' THEN 'Double'
				WHEN 'int' THEN 'Int32'
				WHEN 'money' THEN 'Decimal'
				WHEN 'nchar' THEN 'string'
				WHEN 'ntext' THEN 'string'
				WHEN 'numeric' THEN 'Decimal'
				WHEN 'real' THEN 'Single'
				WHEN 'rowversion' THEN 'Byte[]'
				WHEN 'smallint' THEN 'Int16'
				WHEN 'smallmoney' THEN 'Decimal'
				WHEN 'sql_variant' THEN 'Object'
				WHEN 'text' THEN 'string'
				WHEN 'time' THEN 'DateTime'
				WHEN 'timestamp' THEN 'DateTime'
				WHEN 'tinyint' THEN 'Byte'
				WHEN 'uniqueidentifier' THEN 'Guid'
				WHEN 'varbinary ' THEN 'Byte[]'
				WHEN 'varchar' THEN 'string'
				
				ELSE DATA_TYPE

			END
			+ ' ' +
			COLUMN_NAME
			+ ' ' +
			CASE DATA_TYPE
				WHEN 'bigint' THEN ' = 0;'
				WHEN 'bit' THEN ' = new Boolean();'
				WHEN 'char' THEN ' = string.Empty;'
				WHEN 'date' THEN ' = new DateTime();'
				WHEN 'datetime' THEN ' = new DateTime();'
				WHEN 'decimal' THEN ' = 0.0;'
				WHEN 'float' THEN ' = 0.0;'
				WHEN 'int' THEN ' = 0;'
				WHEN 'money' THEN ' = 0.0;'
				WHEN 'nchar' THEN ' = string.Empty;'
				WHEN 'ntext' THEN ' = string.Empty;'
				WHEN 'numeric' THEN ' = 0;'
				WHEN 'real' THEN ' = 0;'
				WHEN 'rowversion' THEN ' = new Byte();'
				WHEN 'smallint' THEN ' = 0;'
				WHEN 'smallmoney' THEN ' = 0.0;'
				WHEN 'sql_variant' THEN ' = new Object();'
				WHEN 'text' THEN ' = string.Empty;'
				WHEN 'time' THEN ' = new DateTime();'
				WHEN 'timestamp' THEN ' = new DateTime();'
				WHEN 'tinyint' THEN ' = new Byte();'
				WHEN 'uniqueidentifier' THEN ' = new Guid();'
				WHEN 'varbinary ' THEN ' = new Byte();'
				WHEN 'varchar' THEN ' = string.Empty;'
				
				ELSE ' = string.Empty;'

			END


	FROM	INFORMATION_SCHEMA.COLUMNS

	WHERE	TABLE_NAME = @tbl_name

	---------
	UNION ALL 


	SELECT '}'


-------------------------------------------------------------------------------

GO

-- sample usage
-- EXECUTE generateTableVoClass 'DevTable', 'devtable'