Genera Mapeo

SET nocount  ON
SET CONCAT_NULL_YIELDS_NULL off;
Declare @TableName varchar(250);
Declare @Entity varchar(max);
Declare @Propertys varchar(max);
Declare @Property varchar(max);
Declare @Types varchar(max);
Declare @Type varchar(max);
Declare @TableAux table(id int identity(1,1), columnname varchar(250) , typecolumn varchar(250), isnullable varchar(5),tamanio varchar(5) );
Declare @i int;
Declare @Count int;
Declare @Columnname varchar(250);
Declare @Columntype varchar(250);
Declare @isnullable varchar(5);
Declare @Columns varchar(max);

Declare @RowData varchar(max);
Declare @RowDataAux varchar(max);
Declare @LenRowData int;

set @TableName = 'H_laboral';
set @i = 1;

Insert into @TableAux
select column_name,data_type,is_nullable,character_maximum_length from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName;
set @Count = @@ROWCOUNT;

--Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'H_Laboral';

while (@i <= @Count)
begin
 select @Columnname = columnname, @Columntype = typecolumn, @isnullable = isnullable from @TableAux where id = @i;

 if(@Columntype = 'varchar')
 Begin
    Set @Columntype = 'string'
 End

 if(@Columntype = 'datetime')
 Begin
    Set @Columntype = 'DateTime'
 End

 if(@Columntype = 'numeric')
 Begin
    Set @Columntype = 'Double'
 End

 if(@Columntype = 'bit')
 Begin
    Set @Columntype = 'bool'
 End

 if(@Columntype = 'smallint')
 Begin
    Set @Columntype = 'Int16'
 End

 if @i = 1
    begin
        set @Columns = @Columns + @Columnname
    end
    else
    begin
        set @Columns = @Columns +  CHAR(13) + ',' + @Columnname
    end
   
  if(@Columntype='string')
 begin
     set @Property = @Columnname
         set @Type = @Columntype    
        set @LenRowData = (SELECT DATALENGTH(@RowData))

        if @LenRowData >= 7900
        begin
            --Esta variable solo se llena si la la variable @RowData alcanza su limite de carcateres
            set @RowDataAux = 'CONTINUA AQUI...' + CHAR(13) + @RowDataAux + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? null : dataReader["' + @Property + '"].ToString();' + CHAR(13)
        end
        else
        begin
            set @RowData = @RowData + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? null : dataReader["' + @Property + '"].ToString();' + CHAR(13)
        end       
 end   
 else
 begin
     if(@isnullable='YES')
     begin
        set @Property = @Columnname
         set @Type = @Columntype + '?'                     
        set @LenRowData = (SELECT DATALENGTH(@RowData))

        if @LenRowData >= 7900
        begin
            --Esta variable solo se llena si la la variable @RowData alcanza su limite de carcateres
            set @RowDataAux = 'CONTINUA AQUI...' + CHAR(13) + @RowDataAux + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? default(' + @Type + ') : ' + SUBSTRING(@Type, 1, Len(@Type) - 1) + '.Parse(dataReader["' + @Property + '"].ToString());' + CHAR(13)
        end
        else
        begin
            set @RowData = @RowData + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? default(' + @Type + ') : ' + SUBSTRING(@Type, 1, Len(@Type) - 1) + '.Parse(dataReader["' + @Property + '"].ToString());' + CHAR(13)
        end       
     end
     else
     begin
         set @Property = @Columnname
         set @Type = @Columntype
        set @LenRowData = (SELECT DATALENGTH(@RowData))

        if @LenRowData >= 7900
        begin
            --Esta variable solo se llena si la la variable @RowData alcanza su limite de carcateres
            set @RowDataAux = 'CONTINUA AQUI...' + CHAR(13) + @RowDataAux + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? default(' + @Type + ') : ' + @Type + '.Parse(dataReader["' + @Property + '"].ToString());' + CHAR(13)
        end
        else
        begin
            set @RowData = @RowData + LOWER(@TableName) + '.' + @Property + ' = dataReader.IsDBNull(dataReader.GetOrdinal("' + @Property + '")) ? default(' + @Type + ') : ' + @Type + '.Parse(dataReader["' + @Property + '"].ToString());' + CHAR(13)
        end           
     end
 end

--set @Types = @Types + @Type + CHAR(13)
--set @Propertys = @Propertys + @Property + CHAR(13)
set @i = @i + 1;
end;

--print @Types
--print @Propertys
print LOWER(@TableName) + ' = new '
print @RowData
print @RowDataAux