Create class from SQL-Server database table

[Kareninstructor]

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


Continue reading...