-- DROP PROCEDURE [dbo].[Tabla_Modelo_EF_VB]

-- EXEC Tabla_Modelo_EF_VB 'Tabla'

USE [BD]
GO

/****** Object:  StoredProcedure [dbo].[Tabla_Modelo_EF_VB]    Script Date: 03/28/2014 22:20:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_Tabla_Modelo_EF_VB]
	@Tabla CHAR(50)
AS
BEGIN

DECLARE @IdCampo INT,@Campo CHAR(50),@Tipo CHAR(50),@Longitud INT,@IdKey INT ,@Permite_Nulo INT--,@Tabla_Externa CHAR(50),
DECLARE @sTipo char(10),@Anotaciones Varchar(500)

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

OPEN Campos


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

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

-- *********>> TIPOS
SELECT @sTipo = 'xxxxx'

IF @Tipo = 'char' OR @Tipo = 'nchar' OR @Tipo = 'varchar' BEGIN SELECT @sTipo ='string' END
IF @Tipo = 'int' OR @Tipo = 'Numeric' BEGIN SELECT @sTipo ='Integer' END
IF @Tipo = 'bit' BEGIN SELECT @sTipo ='Boolean' END
IF @Tipo = 'Date' OR @Tipo = 'Datetime' BEGIN SELECT @sTipo ='Date' END


--- *********>> ANOTACIONES

SELECT @Anotaciones = ''
IF @IdKey IS NOT NULL BEGIN SELECT @Anotaciones = @Anotaciones + '<Key()> _' + CHAR(13) END
IF @IdKey IS NOT NULL BEGIN SELECT @Anotaciones = @Anotaciones + '<DatabaseGenerated(DatabaseGeneratedOption.None)> _' + CHAR(13) END
IF @Permite_Nulo = 0 BEGIN SELECT @Anotaciones = @Anotaciones + '<Required(ErrorMessage:="El Campo ' + LTRIM(RTRIM(@Campo)) + ' es Obligatorio")> _' + CHAR(13) END
SELECT @Anotaciones = @Anotaciones + '<Display(Name:=" ' + LTRIM(RTRIM(@Campo)) + ' ")> _' + CHAR(13)
IF @Tipo = 'char' OR @Tipo = 'nchar' OR @Tipo = 'varchar' BEGIN 
	SELECT @Anotaciones = @Anotaciones + '<MaxLength(' + CONVERT(VARCHAR,@Longitud) + ')> _'
END
 

-- ************** GENERAMOS LA PROPIEDAD
PRINT @Anotaciones
PRINT 'Public Property ' + LTRIM(RTRIM(@Campo)) + ' As ' + @sTipo
PRINT ''
--PRINT ''
-- *************************************************************************************************


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

END
CLOSE Campos;
DEALLOCATE Campos;

END

GO