Create classes (class .vb) from a table created in MS SQL Server

Question

Create classes (class .vb) from a table created in MS SQL Server

Navigation

#1 by (4 votes)

4

I have a database created in MS SQL Server with different tables in it. What I'm trying to do is generate from this model "import" in some way to Visual Studio 2013 (Visual Basic - Framework 4.0) and generate classes automatically.

For example I have the table Usuario with the fields:

ID Bigint
Nombre Varchar(255)
Apellido varchar(255)

What should generate me in my VB project would be a class: Usuario.vb (class) with the attributes:

dim ID as long
dim Nombre as String
dim Apellido as String

This was a comment that is heard that it was possible to do. But I could not find the form, in some places I read that maybe it was possible to use Entity Framework but I could not do it.

How could I achieve it?

clase sql-server vb.net

asked by lucasalderete 10.02.2017 в 20:57

source

1 answer

Extract text between two words How to copy the data of one table from one database into another database with an identical table?

score 4 · Accepted Answer

Run the following script in SQL Server Management Studio to get the class from the result.

----------------------------------
-- DW: 11/04/2011
-- This script will build a class file with 
-- private fields and public properties from
-- the table specified in the @TableName variable.
----------------------------------

DECLARE @TableName varchar(50)
SET @TableName = 'Usuario'

SET NOCOUNT ON; -- Hide row count so printed output is not affected

DECLARE @DataTypeName varchar(50)
DECLARE @NewLine char
DECLARE @ColumnName varchar(50)
DECLARE @DataType varchar(50)
DECLARE @FieldName varchar(50)

--SET @NewLine = char(13)

-- Start Output
--PRINT '' + @NewLine;
PRINT 'Public Class ' + @TableName;
PRINT '';
PRINT '#Region "Declarations"';
PRINT '';

-- Declarations
DECLARE DeclarationCursor CURSOR SCROLL FOR 
    SELECT
        columns.name [ColumnName],
        CASE 
            WHEN columns.system_type_id = 34    THEN 'Byte[]'
            WHEN columns.system_type_id = 35    THEN 'String'
            WHEN columns.system_type_id = 36    THEN 'System.Guid'
            WHEN columns.system_type_id = 48    THEN 'Byte'
            WHEN columns.system_type_id = 52    THEN 'Short'
            WHEN columns.system_type_id = 56    THEN 'Integer'
            WHEN columns.system_type_id = 58    THEN 'System.DateTime'
            WHEN columns.system_type_id = 59    THEN 'float'
            WHEN columns.system_type_id = 60    THEN 'Decimal'
            WHEN columns.system_type_id = 61    THEN 'System.DateTime'
            WHEN columns.system_type_id = 62    THEN 'double'
            WHEN columns.system_type_id = 98    THEN 'Object'
            WHEN columns.system_type_id = 99    THEN 'String'
            WHEN columns.system_type_id = 104   THEN 'Boolean'
            WHEN columns.system_type_id = 106   THEN 'Decimal'
            WHEN columns.system_type_id = 108   THEN 'Decimal'
            WHEN columns.system_type_id = 122   THEN 'Decimal'
            WHEN columns.system_type_id = 127   THEN 'long'
            WHEN columns.system_type_id = 165   THEN 'Byte[]'
            WHEN columns.system_type_id = 167   THEN 'String'
            WHEN columns.system_type_id = 173   THEN 'Byte[]'
            WHEN columns.system_type_id = 175   THEN 'string'
            WHEN columns.system_type_id = 189   THEN 'Long'
            WHEN columns.system_type_id = 231   THEN 'String'
            WHEN columns.system_type_id = 239   THEN 'String'
            WHEN columns.system_type_id = 241   THEN 'String'
            WHEN columns.system_type_id = 241   THEN 'String'
        END [DataType]
FROM              sys.tables tables
    INNER JOIN    sys.schemas schemas ON (tables.schema_id = schemas.schema_id )
    INNER JOIN    sys.columns columns ON (columns.object_id = tables.object_id) 
WHERE
    tables.name = @TableName
ORDER BY 
    columns.object_id ASC;


OPEN DeclarationCursor;

FETCH NEXT FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = '_' + LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)
    PRINT '    Private ' + @FieldName + ' As ' + @DataType;

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END
PRINT ''; 
PRINT '#End Region';
PRINT ''; 
PRINT '#Region "Properties"';
PRINT '';

FETCH FIRST FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = '_' + LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)

    PRINT '    Public Property ' + @ColumnName + ' As ' + @DataType;
    PRINT '        Get';
    PRINT '            Return ' + @FieldName;
    PRINT '        End Get';
    PRINT '        Set';
    PRINT '            ' + @FieldName + ' = value';
    PRINT '        End Set';
    PRINT '    End Property';
    PRINT ''; 

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END

PRINT ''; 
PRINT '#End Region';
PRINT ''; 


DECLARE @FIELDS varchar(max)
SET @FIELDS = ''

FETCH FIRST FROM DeclarationCursor 
INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FieldName = LOWER(SUBSTRING(@ColumnName, 1,1)) + SUBSTRING(@ColumnName, 2, LEN(@ColumnName)-1)
    SET @FIELDS = @FIELDS + 'byval ' + @FieldName + ' as ' + @DataType + ', '

    FETCH NEXT FROM DeclarationCursor 
    INTO @ColumnName, @DataType;
END

SET @FIELDS = SUBSTRING(@FIELDS, 1, (LEN(@FIELDS) - 1))

PRINT '#Region "Constructors"';
PRINT '';
PRINT '    Public Sub New()';
PRINT '        ';
PRINT '    End Sub';
PRINT '';
PRINT '    Public Sub New(' + @FIELDS + ')'
PRINT '        Me.New()';
PRINT '    End Sub';
PRINT '';
PRINT '#End Region';
PRINT ''; 

CLOSE DeclarationCursor;
DEALLOCATE DeclarationCursor;

-- End of Class
PRINT 'End Class';

If you want to generate for another table, you only have to update the value of @TableName .

Reference