DROP PROCEDURE Tabla_Clase


USE [BBDD]
GO

--****** Object:  StoredProcedure [dbo].[Tabla_ClasePropiedades_VB]  
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Jorge Sánchez>
-- Create date: <05/10/2013>
-- Description:	<Generación de Código Fuente de VB NET con las propiedades de una clase a partir de una tabla de SQL>
-- =============================================
CREATE PROCEDURE [dbo].[PROC_Tabla_ClasePropiedades_VB]
	-- ***** PARAMETROS
	@Tabla CHAR(50)
AS
BEGIN


IF NOT EXISTS(SELECT name FROM sysobjects WHERE sysobjects.name = @Tabla)
PRINT 'ERROR !!! LA TABLA : [' + LTRIM(RTRIM(@Tabla)) + '] NO EXISTE'
RETURN -1


DECLARE @IdCampo INT,@Campo CHAR(50),@Tipo CHAR(50),@Longitud INT --,@Permite_Nulo CHAR(10),@Tabla_Externa CHAR(50),
DECLARE @Prefijo char(1),@sTipo char(10)

DECLARE Campos CURSOR
    FOR SELECT DISTINCT
syscolumns.colid AS IdCampo, syscolumns.name AS Campo, systypes.name AS Tipo, 
syscolumns.length AS Longitud
FROM         
sysobjects sysobjects_1 RIGHT OUTER JOIN
sysforeignkeys ON sysobjects_1.id = sysforeignkeys.rkeyid RIGHT OUTER JOIN
syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON sysforeignkeys.fkeyid = syscolumns.id AND 
sysforeignkeys.fkey = syscolumns.colid LEFT OUTER JOIN
sysindexkeys ON sysobjects.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
WHERE  
sysobjects.name = @Tabla AND systypes.name <> 'sysname'
ORDER BY syscolumns.colid

OPEN Campos



FETCH NEXT FROM Campos
INTO @IdCampo,@Campo,@Tipo,@Longitud;

WHILE @@FETCH_STATUS = 0
BEGIN
-- ****************************** PRINT DE LAS PROPIEDADES ****************************************

-- *********>> PREFIJO DE LA PROPIEDAD
SELECT @Prefijo = 'x'
SELECT @sTipo = 'xxxxx'

IF @Tipo = 'char' OR @Tipo = 'nchar' OR @Tipo = 'varchar' BEGIN SELECT @Prefijo = 's';SELECT @sTipo ='String' END
IF @Tipo = 'int' OR @Tipo = 'Numeric' BEGIN SELECT @Prefijo = 'i';SELECT @sTipo ='Integer' END
IF @Tipo = 'bit' BEGIN SELECT @Prefijo = 'b';SELECT @sTipo ='Boolean' END
IF @Tipo = 'Date' OR @Tipo = 'Datetime' BEGIN SELECT @Prefijo = 'f';SELECT @sTipo ='Date' END

SELECT @Prefijo = '_'

-- ************** GENERAMOS LA PROPIEDAD
PRINT 'Private '+ @Prefijo+LTRIM(RTRIM(@Campo)) + ' as ' + @sTipo
PRINT 'Public Property ' + LTRIM(RTRIM(@Campo)) + '() As ' + @sTipo
PRINT '	Get'
PRINT '		Return '+ @Prefijo+LTRIM(RTRIM(@Campo))
PRINT '	End Get'
PRINT '	Set(ByVal value As ' + LTRIM(RTRIM(@sTipo)) + ')'
PRINT '		'+ @Prefijo+LTRIM(RTRIM(@Campo)) + ' = value'
PRINT '	End Set'
PRINT 'End Property'
PRINT ''
PRINT ''


-- *************************************************************************************************


FETCH NEXT FROM Campos
INTO @IdCampo,@Campo,@Tipo,@Longitud;

END
CLOSE Campos;
DEALLOCATE Campos;

END

GO