The Visual Basic.Net doesn’t contain the refactoring which is one of the major advantages for the C# programmers. Often I miss the Encapsulate Field feature in VB.Net which helps us for geneting public properties of a class and associated Get & Set methods. Initially I had a thought of doing a code fragment in VB.Net for parsing private data members and automatically generating public properties along with their Set and Get methods.

The hardcore requirement of encapsulation (in Re-factoring) of private members with public properties  comes in the Entity class in MVC design patterns. So, I decided to have code for generating class members with reference to the table in the SQL Server for which the entity class is meant for. The below code is my implementation of that idea as a stored procedure.

The code first checks for the existence of the procedure and if exists, it drops the procedure. So, please make sure that there is no such a procedure exist. Kindly remember that the procedure only covers primary data types and you may have to modify it to include the other datatypes. Also enclose the code with Public Class ClassName and End Class statements

--/*********************************************************
--Procedure		:	sp_Generate_Properties
--
--Purpose		:	To generate vb.net Properties from existing Table Columns.
--
--Parameter		:	Table Name
--
--Written by	:	Manivannan.S
--
--Test script	:	EXEC sp_Generate_Properties 'Employee'
--*/

PRINT 'Checking for the existence of this procedure'
 -- If  the procedure already exists
IF (SELECT OBJECT_ID('sp_Generate_Properties','P')) IS NOT NULL
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_Generate_Properties
END
GO

CREATE PROC sp_generate_Properties
(
-- Use this parameter to specify a table name
-- for which the Properties will be generated
@table_name varchar(776)
)
AS
BEGIN
SET NOCOUNT ON
select '
       Private _' + COLUMN_NAME + ' As ' + CASE DATA_TYPE
       WHEN 'smallint' THEN 'int16' WHEN 'int' THEN 'integer'
       WHEN 'tinyint' THEN 'int16' WHEN 'bigint' THEN 'int64'
       WHEN 'float' THEN 'Single' WHEN 'nvarchar' THEN 'string'
       WHEN 'varchar' THEN 'string' WHEN  'text' THEN 'string'
       WHEN 'ntext' THEN 'string' WHEN 'bit' THEN 'boolean'
       ELSE DATA_TYPE  END  +'
	   Public Property ' + COLUMN_NAME + ' As ' + CASE DATA_TYPE
	   WHEN 'smallint' THEN 'int16' WHEN 'int' THEN 'integer'
	   WHEN 'tinyint' THEN 'int16' WHEN 'bigint' THEN 'int64'
	   WHEN 'float' THEN 'Single' WHEN 'nvarchar' THEN 'string' WHEN
	   'varchar' THEN 'string' WHEN 'text' THEN
	   'string' WHEN 'ntext' THEN 'string' WHEN 'bit' THEN 'boolean'
	   ELSE DATA_TYPE  END + '
	       Get
               Return _' + COLUMN_NAME + '
	       End Get
           Set(ByVal value As ' + CASE DATA_TYPE
       WHEN 'smallint' THEN 'int16' WHEN 'int' THEN 'integer'
       WHEN 'tinyint' THEN 'int16' WHEN 'bigint' THEN 'int64'
       WHEN 'float' THEN 'Single' WHEN 'nvarchar' THEN 'string'
       WHEN 'varchar' THEN 'string' WHEN 'text' THEN 'string'
       WHEN  'ntext' THEN 'string' WHEN 'bit' THEN 'boolean'
       ELSE DATA_TYPE  END + ')
               _' + COLUMN_NAME + ' = value
           End Set
       End Property'
   from INFORMATION_SCHEMA.columns
   where TABLE_NAME =@table_name
PRINT '''Done'
END