stickleprojects
Level: Moderator
Registered: 09-09-2002
Posts: 1014
|
VB code generation TSQL scripts for SQL2k
Here's a couple of useful SPs for you:
USP_Admin_CreateVBTAbleClass outputs the code for property gets/lets and a loadfromrs function for any given table in a database
USP_Admin_CreateVBCode outputs the code to execute an SP
The latter is my prefered way, but it provides a good example of CRUD.
comments welcome:
Kieron
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc USP_Admin_CreateVBTableClass (
@DBName varchar(128),
@TableName varchar(128)
)
AS
set nocount on
if not exists (select * from information_schema.tables where table_name='VBTypelookup')
BEGIN
CREATE TABLE [VBTypeLookup] (
[DBTypeLike] [varchar] (128),
[Prefix] [varchar] (10) ,
[VBDataType] [varchar] (20) ,
[VBDBType] [varchar] (20)
)
insert into vbtypelookup values ('%char%','str','String','adVarChar')
insert into vbtypelookup values ('%int%','lng','Long','adInteger')
insert into vbtypelookup values ('%bit%','bln','Boolean','adInteger')
insert into vbtypelookup values ('%date%','dte','Date','adDBDate')
insert into vbtypelookup values ('%money%','sng','Single','adVarChar')
END
create table #t (
ColName varchar(128),
ColDataType varchar(128),
AllowNulls bit,
VBPropName varchar(128),
VBVarName varchar(128),
VBDataType varchar(128)
)
DECLARE @SQL nvarchar(2000)
set @sql='select column_name, data_type ,
case is_nullable
when ''YES'' then 1
else 0
end
from ' + @dbname + '.information_schema.columns
where table_name=''' + @tablename + '''
order by ordinal_position'
insert into #t (colname, coldatatype, allownulls)
Exec sp_executesql @sql
DECLARE @fc int
SELECT @fc = count(*) from #t
if @fc=0
begin
select 'No columns found'
return
end
update t SET
t.vbdatatype = vb.VBdatatype,
VBPropName = colname,
VBVarName = vb.prefix + colname
from
#t t
inner join vbtypelookup vb
on t.coldatatype like vb.dbtypelike
declare @code TABLE(RowID int identity(0,1),
Indent int default(0),
Code varchar(1000)
)
DECLARE @i int
set @i=0
-- Var declarations
insert into @code (indent, code)
select @i, 'Private m_' + vbVarName + ' AS ' + VBDataType from #t
insert into @code (indent, code)
values (@i,'')
-- Property get's
insert into @code (indent, code)
select @i, 'public property get ' + VBPropName + ' () AS ' + VBDataType + '
' + VBPropname + ' = m_' + vbVarName + '
end property'
FROM #t
-- Property let's
insert into @code (indent, code)
select @i, 'public property let ' + VBPropName + ' (NewValue AS ' + VBDataType + ')
m_' + VBVarName + '= NewValue
end property'
FROM #t
-- LoadFromRS
insert into @code (code) values ('public function LoadFromRS (objRS as recordset) as Boolean')
set @i=1
insert into @code (indent,code) values (@i, '')
insert into @code (indent,code) values (@i, 'if not objrs is nothing then')
set @i=@i+1
insert into @code (indent,code) values (@i, 'if objrs.state = adStateOpen then')
set @i=@i+1
insert into @code (indent,code) values (@i, 'if not objRS.EOF then')
set @i=@i+1
insert into @code (indent, code)
select @i, 'm_' + vbVarName + ' = objRS("' + colname + '") & ""'
from #t
set @i=@i-1
insert into @code (indent,code) values (@i, 'end if')
set @i=@i-1
insert into @code (indent,code) values (@i, 'end if')
set @i=@i-1
insert into @code (indent,code) values (@i, 'end if')
insert into @code (code) values ('end function')
-- output the code
select replicate(char(9),indent) + code from @code order by rowid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
Usage:
| exec USP_Admin_CreateVBTableClass 'Northwind','Region' |
if not exists (select * from information_schema.tables where table_name='VBTypelookup')
BEGIN
CREATE TABLE [VBTypeLookup] (
[DBTypeLike] [varchar] (128),
[Prefix] [varchar] (10) ,
[VBDataType] [varchar] (20) ,
[VBDBType] [varchar] (20)
)
insert into vbtypelookup values ('%char%','str','String','adVarChar')
insert into vbtypelookup values ('%int%','lng','Long','adInteger')
insert into vbtypelookup values ('%bit%','bln','Boolean','adInteger')
insert into vbtypelookup values ('%date%','dte','Date','adDBDate')
insert into vbtypelookup values ('%money%','sng','Single','adVarChar')
END
|
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_StartsWith]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_StartsWith]
GO
create function fn_StartsWith (
@SearchIn varchar(1000),
@SearchFor varchar(100)
) RETURNS int
AS
BEGIN
DECLARE @Ret int
set @ret=0
if left(@searchin,len(@searchfor))=@searchfor
set @ret=1
RETURN @ret
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_Admin_CreateVBCode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_Admin_CreateVBCode]
GO
CREATE PROC USP_Admin_CreateVBCode (
@spname varchar(128),
@IsFunction int = 1,
@DBName varchar(128) = 'contact',
@VBProcName varchar(128) = '',
@VBScope varchar(128) = 'Public',
@ReturnType varchar(128) = 'Recordset',
@UseInsertCodeTemplate int =0 ,
@UseCountOfTemplate int=0,
@UseDeleteTemplate int=0,
@DefaultReturnValue varchar(128) = '',
@KeyFieldName varchar(128)=null,
@DBConnectionVariableName varchar(128)=null
)
AS
DECLARE @SQL nvarchar(4000)
SELECT
@DBConnectionVariableName = isnull(@DBConnectionVariableName,'g_ConCMS'),
@IsFunction = isnull(@IsFunction,1),
@DBName = isnull(@DBName,'contact'),
@VBProcName = isnull(@VBProcName,''),
@VBScope = isnull(@VBScope,'Public'),
@ReturnType = isnull(@ReturnType,'Recordset'),
@UseInsertCodeTemplate = isnull(@UseInsertCodeTemplate,0),
@UseCountOfTemplate = isnull(@UseCountOfTemplate,0)
IF Isnull(@SPName,'')=''
BEGIN
raiserror('ERROR: no spname specified',0,1)
return
END
SELECT @IsFunction = isnull(@IsFunction,1),
@DBName = isnull(@DBname, 'contact'),
@VBProcName = isnull(@VBProcName,''),
@VBScope = isnull(@VBScope,'public'),
@ReturnType = isnull(@ReturnType,'Recordset')
IF dbo.fn_StartsWith(@SPname,'USP_Update')>0
BEGIN
SET @UseInsertCodeTemplate=1
SET @ReturnType='Boolean'
END
IF dbo.fn_StartsWith(@SPname,'USP_GetCountOf')>0
BEGIN
SET @UseCountofTemplate=1
SET @ReturnType='Long'
SET @IsFunction=1
END
IF dbo.fn_StartsWith(@SPname,'USP_Delete')>0
BEGIN
SET @UseDeleteTemplate=1
SET @ReturnType='Long'
SET @IsFunction=1
END
IF Isnull(@DefaultReturnValue,'')='' AND @ReturnType='Long'
SET @DefaultReturnValue='-1'
SET @SQL = '
if not exists (select * from ' + @DBName + '.information_schema.routines
where specific_name = ''' + @spname + ''')
select '' WArning. SP not found in ' + @DBName + 'contact db'''
exec sp_executesql @SQL
DECLARE @VBDeclaration varchar(1000)
DECLARE @KeyFieldVBName varchar(128)
IF len(isnull(@VBProcName,''))=0 SET @VBProcName = replace(@SPName,'USP_','')
if @IsFunction=1 AND LEN(ISNULL(@ReturnType,''))=0 SET @ReturnType='Recordset'
SET @VBDeclaration = @VBScope + ' ' +
CASE WHEN @IsFunction=1 THEN 'function' else 'sub' end + ' ' +
@VBProcName + ' (%PARAMS%)' +
CASE WHEN @IsFunction=1 THEN ' AS ' + @ReturnType else '' end
CREATE TABLE #plist (rowid int identity(0,1),
ParamName varchar(128),
ParamType varchar(128),
paramdirection varchar(128),
VBName varchar(128),
VBType varchar(128),
DBType varchar(128),
ByVal int)
SET @SQL = 'select Parameter_name,
data_type, parameter_mode
from ' + @DBName + '.information_schema.parameters p
where specific_name = ''' + @spName + '''
order by ordinal_position'
print @SQL
insert into #plist (paramname, ParamType, paramdirection)
EXEC (@SQL)
-- update the vb types
update p SET
dbtype= VBDBType,
vbname = lk.prefix + substring(paramname,2,999),
vbtype = lk.vbdatatype,
ByVal = 1
FROM
#plist p
left outer join VBTypeLookup lk on p.paramtype like lk.DBTypeLike
update #plist set byval = 0 where paramdirection in ('INOUT','OUT')
if exists (select * from #plist where vbtype is null)
begin
SELECT distinct 'Unsupported data type' as message, * from #plist
where vbtype is null
return
end
-- Byref the first param (ie. rowid)
IF @UseInsertCodeTemplate = 1
BEGIN
IF @KeyfieldName is null
SELECT @Keyfieldname = paramname FROM #plist WHERE RowID=0
update #plist set ByVal=0
WHERE paramname = @keyfieldname
END
-- construct the params
DECLARE @Params varchar(1000)
SET @Params = ''
SELECT @Params = @Params + (
CASE WHEN len(@Params)=0 THEN '' ELSE ', ' END) +
case when byval=1 then 'byval' else 'byref' end + ' ' +
VBName + ' AS ' + VBType
FROM #plist
DECLARE @tblCode TABLE (RowID int identity(0,1),
Code varchar(1000),
indent int default(0)
)
insert into @tblcode (code) values ( REPLACE(@VBDeclaration,'%PARAMS%',@params))
DECLARE @i int
SET @i=1
-- replace the declaration
if exists (SELECT * FROM #plist)
insert into @tblcode (code, indent) values ( 'dim objParams as new clsCMSDataParams ',@i)
insert into @tblcode (code, indent) values ( 'dim objParam as clsCMSDataParam ',@i)
if exists (select * from #plist where paramdirection in ('OUT','INOUT'))
insert into @tblcode (code, indent) values ( 'dim objOutParams as new clsCMSDataParams ',@i)
insert into @tblcode (code, indent) values ( 'dim objRS as RecordSet ',@i)
insert into @tblcode (code, indent) values ( '',@i)
if exists (select * from #plist where paramdirection in ('IN','INOUT'))
BEGIN
insert into @tblcode (code, indent) values ( 'with objParams',@i)
SET @i=@i+1
insert into @tblcode (code, indent)
SELECT '.AddItem .CreateItem("' +
substring(paramname,2,999) + '", ' + dbtype + ', ' +
case when vbtype='boolean' then 'abs(' + vbname + ')' else vbname end +
')',@i
FROM #plist
SET @i=@i-1
insert into @tblcode (code, indent) values ( 'end with',@i)
end
DECLARE @ExecuteStatement varchar(1000)
SET @ExecuteStatement = 'Set objRs = GetData(' + @DBConnectionVariableName + ', "' + @SPName +
'"'
IF EXISTS (SELECT * FROM #plist WHERE paramdirection in ('IN','INOUT'))
SET @ExecuteStatement = @ExecuteStatement + ', objParams.vNames, objParams.vValues, objParams.vTypes'
if exists (select * from #plist where paramdirection in ('OUT','INOUT'))
SET @ExecuteStatement = @ExecuteStatement + ', objOutputParams:=objOutParams'
SET @ExecuteStatement = @ExecuteStatement + ')'
insert into @tblcode (code, indent) values (@executestatement,@i)
-- process the output parameters
insert into @tblcode (code, indent) values ( '',@i)
insert into @tblCode (code, indent)
select vbname + ' = objOutParams("' + substring(paramname,2,999) + '").value', @i
from #plist
WHERE paramdirection in ('OUT','INOUT')
if @IsFunction =0
BEGIN
seT @i=@i-1
insert into @tblcode (code, indent) values ( 'end sub',0)
END
ELSE
BEGIN
IF @UseCountofTemplate = 1 or @UseDeleteTemplate=1
BEGIN
-- default return value
insert into @tblcode (code, indent) values ( @vbprocname + ' = ' + @DefaultReturnValue,@i)
insert into @tblcode (code, indent) values ( 'if not objRS.eof then ',@i)
SET @i=@i+1
insert into @tblcode (code, indent) values ( @vbprocname + ' = val(objRS("RC"))',@i)
SET @i=@i-1
insert into @tblcode (code, indent) values ( 'end if',@i)
END
ELSE
BEGIN
IF @UseInsertCodeTemplate = 0
BEGIN
insert into @tblcode (code, indent) values ( 'set ' + @vbprocname + ' = objRS',@i)
END
ELSE
BEGIN
insert into @tblcode (code, indent) values ( 'if not objRS.eof then ',@i)
set @i=@i+1
insert into @tblcode (code, indent) values ( @vbprocname + ' = true',@i)
SELECT @KeyfieldVBName = VBName FROM #plist where paramname = @KeyFieldName
if left(@keyfieldname,1)='@'
SET @KeyfieldName = substring(@keyfieldname,2,999)
insert into @tblcode (code, indent) values ( 'if ' + @keyfieldvbname + '<=0 then ' + @keyfieldvbname + '= objrs("' + @KeyFieldName + '")',@i)
set @i=@i-1
insert into @tblcode (code, indent) values ( 'end if',@i)
END
END
seT @i=@i-1
insert into @tblcode (code, indent) values ( 'end function',0)
END
select replicate(char(9),indent) + code from @tblcode order by rowid
GO
|
____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)
|
