This SQL script will create the code needed to put into a VB class to use with the Microsoft Data Application blocks.
Two other scripts are needed in this code
f_4P_columnType
f_4P_columnTypeVB
-- =============================================
-- v 1.5
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_class'
AND type = 'P')
DROP PROCEDURE sp_class
GO
CREATE PROCEDURE sp_class
-- sp_class '_4P_groups_INS'
@vchrProcName varchar(50)
AS
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @vchrVBFieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(1000)
declare @paramListVB varchar(1000)
declare @paramListHelper varchar(1000)
declare @paramListHelper3 varchar(1000)
declare @vchrMethodName varchar(50)
set @output = ''
select
@paramList = '',
@paramListVB = '',
@paramListHelper = '',
@paramListHelper3 = '',
@vchrMethodName = substring(@vchrProcName,5,99)
declare curFields CURSOR for
select p.name,
dbo.f_4P_columnType (p.xtype,length, [xprec] , scale),
dbo.f_4P_columnTypevb (p.xtype)
from sysobjects o
join syscolumns p on p.id = o.id
where o.name = @vchrProcName
OPEN curFields
FETCH NEXT FROM curFields INTO @paramName, @fieldType, @fieldTypeVB--,@paramName
WHILE @@FETCH_STATUS = 0
BEGIN
set @vchrVbFieldName = case when @paramName like <a href="mailto:'@vchr%'">'@vchr%'</a> then replace(@paramName,'@vchr','str') else replace(@paramName,'@','') end
--build a param list
--build a VB param list
set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @vchrVBFieldName + ' As ' + @fieldTypeVB
--build a sqlhelper param list
set @paramListHelper = @paramListHelper + ', _ ' + char(13) + char(10) + space(10) + 'New SqlParameter("' + @paramName + '", ' + @vchrVBFieldName + ')'
--build a sqlhelper3 param list
set @paramListHelper3 = @paramListHelper3 + char(13) + char(10) +
space(10) + 'db.AddInParameter(dbCommand,"' + @paramName + '", DbType.String,' + @vchrVBFieldName + ')'
FETCH NEXT FROM curFields INTO @paramName, @fieldType,@fieldTypeVB
END
CLOSE curFields
DEALLOCATE curFields
if @paramListVB > ''
set @paramListVB = stuff(@paramListVB,1,2,'')
print 'This procedure creates several different types of classes, choose the one that you need'
print ''
print 'For the Microsoft Data Access Blocks 3.0:'
print ''
print 'Sub ' + @vchrMethodName + '(' + @paramListVB + ') '
print ' Try'
print ' Dim db As Database = DatabaseFactory.CreateDatabase()'
print ' Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand("' + @vchrProcName + '")' + @paramListHelper3
print ''
print ' db.ExecuteNonQuery(dbCommand)'
print ' Catch ex As Exception'
print ' Throw New Exception(ex.Message, ex.InnerException)'
print ' End Try'
print 'End Sub'
print ''
print 'For the Microsoft Data Access Blocks 2.0:'
print ''
print 'Public Sub ' + @vchrMethodName + '(' + @paramListVB + ') '
print ' SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'
print ' '
print 'Public Function ' + @vchrMethodName + '(' + @paramListVB + ') as sqldatareader'
print ' return SqlHelper.ExecuteReader(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'
print ''
go
grant exec on sp_class to public