Create class from SQL-Server database table

2018-09-11

Question

_{Tuesday, September 11, 2018 12:11 PM}

The following is a helper SQL Builder for creating a simple class from a SQL-Server database table. When reading data from SQL-Server the two main choices for containers are DataTable or a Class which represents the fields in the SQL-Server table. Suppose there is a need for using a class and there are many columns in the table, A developer would then need to create a new class and add properties for each field in the table. Then there is the chance for not mapping fields to the proper type.

Alternate options are Entity Framework but here the focus is writing your own classes.

Open up SSMS (SQL-Server Management Studio), select the database in object explorer and create a new query. In the parameter @TableName place the table name there. Make sure you have selected the database properly or use a USE statement. Now run the statement and this produces a class for the table.

Test table where in this case there are several fields that don't map and are not common usage.

Unmappables are in red.

Here is a mappable table

Public Class Customers
    Public Property CustomerIdentifier As Integer
    Public Property CompanyName As String
    Public Property ContactName As String
    Public Property ContactIdentifier As Integer?
    Public Property ContactTypeIdentifier As Integer?
    Public Property Street As String
    Public Property City As String
    Public Property PostalCode As String
    Public Property CountryIdentfier As Integer?
    Public Property Phone As String
    Public Property ModifiedDate As DateTime?
    Public Property InUse As Boolean?
End Class

Here is how the classes where created (this one is for the last class Customers)

USE NorthWindAzure2
DECLARE @TableName sysname = 'Customers';
DECLARE @Result VARCHAR(MAX) = 'Public Class ' + @TableName; 

SELECT  @Result = @Result + '
    Public Property ' + ColumnName + ' As ' + ColumnType + NullableSign
FROM    ( SELECT    REPLACE(col.name, ' ', '_') ColumnName ,
                    column_id ColumnId ,
                    CASE typ.name
                      WHEN 'bigint' THEN 'Long'
                      WHEN 'binary' THEN 'Byte[]'
                      WHEN 'bit' THEN 'Boolean'
                      WHEN 'char' THEN 'String'
                      WHEN 'date' THEN 'DateTime'
                      WHEN 'datetime' THEN 'DateTime'
                      WHEN 'datetime2' THEN 'DateTime'
                      WHEN 'datetimeoffset' THEN 'DateTimeOffset'
                      WHEN 'decimal' THEN 'Decimal'
                      WHEN 'float' THEN 'Float'
                      WHEN 'image' THEN 'Byte()'
                      WHEN 'int' THEN 'Integer'
                      WHEN 'money' THEN 'Decimal'
                      WHEN 'nchar' THEN 'String'
                      WHEN 'ntext' THEN 'String'
                      WHEN 'numeric' THEN 'Decimal'
                      WHEN 'nvarchar' THEN 'String'
                      WHEN 'real' THEN 'Double'
                      WHEN 'smalldatetime' THEN 'DateTime'
                      WHEN 'smallint' THEN 'Short'
                      WHEN 'smallmoney' THEN 'Decimal'
                      WHEN 'text' THEN 'String'
                      WHEN 'time' THEN 'TimeSpan'
                      WHEN 'timestamp' THEN 'DateTime'
                      WHEN 'tinyint' THEN 'Byte'
                      WHEN 'uniqueidentifier' THEN 'Guid'
                      WHEN 'varbinary' THEN 'Byte()'
                      WHEN 'varchar' THEN 'String'
                      ELSE 'UNKNOWN_' + typ.name
                    END ColumnType ,
                    CASE WHEN col.is_nullable = 1
                              AND typ.name IN ( 'bigint', 'bit', 'date',
                                                'datetime', 'datetime2',
                                                'datetimeoffset', 'decimal',
                                                'float', 'int', 'money',
                                                'numeric', 'real',
                                                'smalldatetime', 'smallint',
                                                'smallmoney', 'time',
                                                'tinyint', 'uniqueidentifier' )
                         THEN '?'
                         ELSE ''
                    END NullableSign
          FROM      sys.columns col
                    JOIN sys.types typ ON col.system_type_id = typ.system_type_id
                                          AND col.user_type_id = typ.user_type_id
          WHERE     object_id = OBJECT_ID(@TableName)
        ) t
ORDER BY ColumnId;

SET @Result = @Result + '
End Class';

PRINT @Result;

Summary

It's not perfect e.g. does not understand a few field types but could be altered to do so with some additional coding. I've been using this in a C# version and thought it might be good to create this VB.NET version to share with the community.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

All replies (7)

_{Tuesday, September 11, 2018 1:46 PM}

Screenshot in SSMS

_{Tuesday, September 11, 2018 1:52 PM}

From Visual Studio. Create a text file with .sql extension instead of .txt.

In the query windows connect

Brings up the following, enter the server name.

Select the database

Add the SQL as per the primary post in this thread and set the table name, execute with the right green arrow above.

_{Tuesday, September 11, 2018 3:01 PM}

Hello,

I've been using this in a C# version and thought it might be good to create this VB.NET version to share with the community.

It's will be Ok if you use it for your self.

But as soon as you propose this for community - you violate one of the US patents.

Look on situation with IronSpeed,

By the way - it's very problematic to implement code generation using T-Sql.

Very hard to understand what you dealing with and how to modify the code.

Specially, if you produce more than one type "class with properties".

Somewhere in the archive I have generator for full application - it contain more than 600 templates(assume the code you provided are just one of them) and this is an addition app. 70-80Mb of sources for metadata analysis.

And... xmmm...

You should wrap your Table_5 into NorthWindAzure1.dbo namespace.

You also need collection to store Table_5 items

You also need functionality for CRUD...

You also need...

At least there is a reason to use T4 for text generation.

Best regards,

Andrey

_{Tuesday, September 11, 2018 3:14 PM}

Can you provide a link and specifics to one of the US patents?

In short the SQL is a starting point, never meant to replace T4 templates (if I were too I would had mentioned the following Visual Studio add-in) and never did I indicate anything about CRUD operations, that is beyond the scope of the information I provided.

In regards to

You should wrap your Table_5 into NorthWindAzure1.dbo namespace. - feel free to do so
You also need collection to store Table_5 items - beyond the scope of this SQL, this is all meant to allow a developer to consider classes with simple data structures to move away from DataTable containers. Start moving pass this and many developers will be lost.

_{Tuesday, September 11, 2018 3:33 PM}

>Can you provide a link and specifics to one of the US patents?

Guys refer to:

http://www.google.com/patents/US7062502

>feel free to do so

Thanks - I have better tool for this - getting full code for class, crud for several types of databases and... relatively easy extendable.Adding template to get result similar to yours will take few minutes and will handle all user defined types.

Best regards,

Andrey

_{Tuesday, September 11, 2018 4:14 PM}

What I read (yes this is a very long page) from reading the first page there is nothing indicating a SQL-Statement but instead as hightlighted indicates "Computer software" and "utility software" were the script is not software but instead a script which can run from the command line, a database editor, SSMS, Visual Studio where all of them execute what you ask.

>Thanks - I have better tool for this

Then this post was never intended for you, it's intended for those who might want to move away from DataTable containers. Over the past 10 years the majority of people who ask data related questions here are not dealing with relational data and are not expereinced with property notifications and so forth, this post was purely an effort to provide some motication. The average developer or hobbist may need to take baby steps to get to EF and similar ways to interact with data. With that said I've said my peace and stand by this.

_{Tuesday, September 11, 2018 5:19 PM}

>this is a very long page

Basically, what are claimed by the patent owner - any option which are used to create UI (or parts) based on extraction of metadata from database.

In your case - you will require to prove that offered method ether:

- not extracted metadata - what is impossible as you definitely use metadata

- not targeted for any kind of UI element - what you can't prove if patent owner want you to prove.

>was never intended for you

Those for whom you are targeted the post most likely would not understand what it is about.

Best regards,

Andrey

שתף באמצעות

Create class from SQL-Server database table

Question

All replies (7)

משאבים נוספים