Blogs - sp_CreateVBClassForATable

DEVSHED Blog
As always, I welcome your comments!
sp_CreateVBClassForATable

Dec 10
Written by: Steve Gray
12/10/2010 12:56 PM
The is code that will create a VB class based on a table. There are helper scripts needed, find them here
Code Snippet
 IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_CreateVBClassForATable' 
    AND    type = 'P')
    DROP PROCEDURE sp_CreateVBClassForATable
GO
 
CREATE PROCEDURE sp_CreateVBClassForATable 
-- sp_CreateVBClassForATable 'POP10300'
 
--sp_sps
 
    @vchrTable varchar(255)
    
AS
 
set nocount on
 
DECLARE 
    @FS INT ,
    @RC INT ,
    @vchrLine varchar(1000),
    @vchrSP_Prefix varchar(10),
    @vchrSelectByIDSuffix varchar(20),
    @vchrDeleteSuffix varchar(20),
    @vchrUpdateSuffix varchar(20),
    @vchrInsertSuffix varchar(20),
    @vchrSelectByIDProc varchar(255),
    @vchrUpdateProc varchar(255),
    @vchrInsertProc varchar(255),
    @vchrDeleteProc varchar(255),
    @vchrDataClass varchar(255)
 
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--    IMPORTANT - EDIT THIS TEXT
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT 
    @vchrSP_Prefix = 'FP_',
    @vchrSelectByIDSuffix = '_SEL_byID',
    @vchrDeleteSuffix = '_DEL',
    @vchrUpdateSuffix = '_UPD',
    @vchrInsertSuffix = '_INS',
    @vchrDataClass = 'SPs' + '.'
 
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- END EDITING
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
--===================================================================================
--begin processing
--===================================================================================
 
--write the class header
print 'Public Class ' + @vchrTable
  print '#Region ' + char(34) + 'Properties'+ char(34)
--=======================================================
--write the fields
--=======================================================
DECLARE @vchrField varchar(255),
    @vchrFieldType varchar(255)
 
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR 
    SELECT  c.Name ,
            dbo.f_4P_columnTypeVB(c.xtype)
        from sysobjects o (nolock)
            join syscolumns c (nolock) on o.id = c.id    
        where o.[name] = @vchrTable
            and o.xtype = 'U'
        order by c.colorder
 
OPEN curName
WHILE 1=1 
BEGIN
    FETCH NEXT FROM curName INTO @vchrField,@vchrFieldType
    if @@fetch_status <> 0 begin
        break
    end 
 
    --write the field declaration
    print   '    Public ' + @vchrField + ' as ' + @vchrFieldType
 
END
CLOSE curName
DEALLOCATE curName 
print '#End Region'
--=======================================================
-- end fields
--=======================================================
 
 
select 
    @vchrSelectByIDProc = @vchrSP_Prefix + @vchrTable + @vchrSelectByIDSuffix,
    @vchrUpdateProc = @vchrSP_Prefix + @vchrTable + @vchrUpdateSuffix,
    @vchrInsertProc = @vchrSP_Prefix + @vchrTable + @vchrInsertSuffix,
    @vchrDeleteProc = @vchrSP_Prefix + @vchrTable + @vchrDeleteSuffix
 
--=======================================================
--write the 'new' subroutine
--=======================================================
--print a blank line
print ''
 
exec sp_NEW @vchrSelectByIDProc, @vchrTable,@vchrDataClass
 
--=======================================================
--write the 'save' subroutine
--=======================================================
--print a blank line
print ''
 
exec sp_SAVE @vchrUpdateProc, @vchrTable,@vchrDataClass
 
--=======================================================
--write the class footer
--=======================================================
   print'End Class ' + char(39) + @vchrTable
 
 
 
 
go
 
grant exec on sp_CreateVBClassForATable to public
 
--sp_sps
 
Trackback Print
Tags:
Categories:
Location: Blogs Parent Separator
DEVSHED Blogs Child Separator
SQL
0 comment(s) so far...
As always, I welcome your comments!