1 ALTER PROCEDURE UTIL_GenerateClassBody
  2 (
  3 	@TableName varchar(100)
  4 )
  5 AS
  6 
  7 SET NOCOUNT ON
  8 
  9 PRINT 'Public Class ' + @TableName
 10 PRINT ''
 11 
 12 DECLARE @FieldsTable         -- Временная таблица для выборки полей данной таблицы
 13     table 
 14         (FieldName varchar(50), 
 15         FieldType varchar(50))
 16 
 17 DECLARE @FieldName varchar(50)
 18 DECLARE @FieldType varchar(50)
 19 
 20 DECLARE @PropertyType varchar(50)
 21 
 22 DECLARE @LoopVar bit
 23 SET @LoopVar = 1
 24 WHILE @LoopVar = 1
 25 	BEGIN
 26 
 27 		-- Получаем название и тип очередного поля
 28 		SELECT @FieldName = syscolumns.name, 
 29 	            @FieldType = systypes.name
 30 	        FROM syscolumns INNER JOIN sysobjects
 31 	            ON syscolumns.id = sysobjects.id
 32 	                INNER JOIN systypes 
 33 	            ON syscolumns.xtype = systypes.xtype
 34 	        WHERE   sysobjects.name = @TableName AND
 35 	            syscolumns.name NOT IN (SELECT FieldName FROM @FieldsTable)
 36 
 37 
 38 	        IF @@ROWCOUNT > 0 
 39 	            BEGIN
 40 	                INSERT INTO @FieldsTable(FieldName, FieldType)
 41 	                VALUES(@FieldName, @FieldType)
 42 
 43 			-- Заменяем пользовательский тип на реальный тип ms sql
 44 
 45 			SELECT @FieldType = name FROM systypes 
 46 			INNER JOIN 
 47 			(  SELECT xtype FROM systypes WHERE systypes.name = @FieldType )  AS realtypes
 48 			ON systypes.xtype = realtypes.xtype AND systypes.xtype = systypes.xusertype
 49 
 50 
 51 			-- Вычисляем VB тип данных
 52 
 53 			SET @PropertyType = 'Object'
 54 
 55 			IF @FieldType = 'bigint' OR @FieldType = 'int' OR 
 56 				@FieldType = 'smallint' OR @FieldType = 'tinyint'
 57 				SET @PropertyType = 'Integer'
 58 
 59 			IF @FieldType = 'char' OR @FieldType = 'nchar' OR 
 60 				@FieldType = 'ntext' OR @FieldType = 'nvarchar' OR
 61 				@FieldType = 'sql_var' OR @FieldType = 'text' OR
 62 				@FieldType = 'timestamp' OR @FieldType = 'uniqueidentifier' OR
 63 				@FieldType = 'varchar' 
 64 
 65 				SET @PropertyType = 'String'
 66 
 67 
 68 			IF @FieldType = 'datetime' OR @FieldType = 'smalldatetime'
 69 
 70 				SET @PropertyType = 'DateTime'
 71 
 72 			IF @FieldType = 'decimal' OR @FieldType = 'money' OR 
 73 				@FieldType = 'smallmoney' OR @FieldType = 'numeric'
 74 
 75 				SET @PropertyType = 'Decimal'
 76 
 77 			IF @FieldType = 'bit' 
 78 				SET @PropertyType = 'Boolean'
 79 
 80 			IF @FieldType = 'float' 
 81 
 82 				SET @PropertyType = 'Float'
 83 
 84 			IF @FieldType = 'real' 
 85 
 86 				SET @PropertyType = 'Double'
 87 
 88 
 89 			-- Генерируем код
 90 			PRINT '	Private _' + @FieldName + ' As ' + @PropertyType
 91 			PRINT '	Public Property ' + @FieldName + ' As ' + @PropertyType
 92 			PRINT '		Get'
 93 			PRINT '			Return _' + @FieldName
 94 			PRINT '		End Get '
 95 			PRINT '		Set(ByVal Value As ' + @PropertyType + ')'
 96 			PRINT '			_' + @FieldName + ' = Value' 
 97 			PRINT '		End Get '
 98 			PRINT '	End Property '
 99 			PRINT ''
100 
101 			SET @LoopVar = 1
102 	            END
103 		ELSE
104 			SET @LoopVar = 0
105 		
106 	END
107 
108 PRINT '	Public Sub New()'
109 PRINT '	End Sub'
110 PRINT 'End Class '
111 
112 GO
113 
114 --EXECUTE UTIL_GenerateClassBody 'Company'