script to create default class in VB.NET

,

The following script creates a class to access the default stored procs created by a previous script I submitted called script to create default stored procs.

Please note that this will only work correctly if the stored procs created by my earlier script (script to create default stored procedures) have been created.



Have fun

/* author Peter Livesey
date 17/2/2006
purpose will generate a class with insert update and delete capability on a 
        single table where the stored procs were created by the stored proc creation script
*/



/*first we need the table name*/
SET NOCOUNT ON
DECLARE @TABLENAME  VARCHAR(50)
SET @TABLENAME = 'customers'
/* do not tiouch code beyond this point*/
DECLARE @CRLF char(2)
DECLARE @TAB  char(2)
SET @CRLF = char(13) + char (10)
SET @TAB = '  '





DECLARE @PARAMETERLIST varchar(8000)
SET @PARAMETERLIST = ''

/* get the fieldclassstring*/
DECLARE @FIELDCLASSSTRING  varchar(8000)
SET @FIELDCLASSSTRING=''

DECLARE  @fields TABLE
        (
        name  sysname,
        type  varchar(50),
        length int,
        NULLABLE bit
        )
insert into @fields
SELECT sc.name , st.name , sc.length
FROM
	syscolumns sc
JOIN
	systypes st
	ON(
		sc.xtype = st.xusertype
	)
	
WHERE
	sc.id = OBJECT_ID(@TABLENAME)

ORDER BY
	sc.colorder





SELECT @FIELDCLASSSTRING =   @FIELDCLASSSTRING +  @CRLF + @tab + @tab + 'Public Class ' + name +
      @CRLF + @tab + @tab + @tab + 'Public Const Name As String = "' + name + '"' +
      @CRLF + @tab + @tab + @tab+ 'Public Const Type As sqlDBType = SqlDbType.' + type  +
      @CRLF + @tab + @tab + @tab+ 'Public Const Size As Integer = ' + CONVERT (varchar(10),length)  +
      @CRLF + @tab + @tab+     'End Class'
FROM @fields


/**********************************************
get the type of ID
**********************************************/
DECLARE @IDENTITY bit
SET @IDENTITY = 0


/*check to see whether the key field is an identity*/
IF (exists (SELECT '*'
            FROM
            	syscolumns sc
            JOIN
            	systypes st
            	ON(
            		sc.xtype = st.xusertype
            	)
            	
            WHERE
            	sc.id = OBJECT_ID(@TABLENAME)
              and autoval IS NOT NULL
            )
    )
BEGIN
   SET @IDENTITY= 1
END
DECLARE @GUID bit

SET @GUID = 0
IF (exists (SELECT '*'
            FROM
            	syscolumns sc
            JOIN
            	systypes st
            	ON
            		sc.xtype = st.xusertype
             JOIN 
	            sysindexes si
            ON
	            sc.id = si.id
	            and sc.colid = si.indid
            	
            WHERE
            	sc.id = OBJECT_ID(@TABLENAME)
              and st.name ='uniqueidentifier'
              and indid= 1
            )
    )
BEGIN
   SET @GUID= 1
END

/* get id name*/
DECLARE @IDNAME varchar(100)
DECLARE @IDTYPE varchar(100)
DECLARE @IDVBTYPE varchar (100)
DECLARE @IDVBPREFIX varchar(3)
SELECT  @IDNAME = b.name ,@IDTYPE = st.name,
@IDVBTYPE =CASE st.name
		WHEN 'bigint' 		THEN 'long'
		WHEN 'binary' 		THEN 'object'
		WHEN 'bit' 		THEN 'boolean'
		WHEN 'char' 		THEN 'str'
		WHEN 'datetime' 	THEN 'date'
		WHEN 'decimal' 		THEN 'double'
		WHEN 'float' 		THEN 'real'
		WHEN 'identity' 	THEN 'int'
		WHEN 'image' 		THEN 'object'
		WHEN 'int' 		THEN 'integer'
		WHEN 'money' 		THEN 'double'
		WHEN 'nchar' 		THEN 'string'
		WHEN 'ntext' 		THEN 'string'
		WHEN 'numeric' 		THEN 'double'
		WHEN 'nvarchar' 	THEN 'string'
		WHEN 'real' 		THEN 'single'
		WHEN 'smalldatetime' 	THEN 'date'
		WHEN 'smallint' 	THEN 'integer'
		WHEN 'smallmoney' 	THEN 'double'
		WHEN 'sql_variant' 	THEN 'object'
		WHEN 'text' 		THEN 'string'
		WHEN 'timestamp' 	THEN 'object'
		WHEN 'tinyint' 		THEN 'integer'
		WHEN 'uniqueidentifier'	THEN 'guid'
		WHEN 'varbinary' 	THEN 'object'
		WHEN 'varchar' 		THEN 'string'
	END,

@IDVBPREFIX =
CASE st.name
		WHEN 'bigint' 		THEN 'int'
		WHEN 'binary' 		THEN 'obj'
		WHEN 'bit' 		THEN 'bln'
		WHEN 'char' 		THEN 'str'
		WHEN 'datetime' 	THEN 'dt'
		WHEN 'decimal' 		THEN 'dbl'
		WHEN 'float' 		THEN 'dbl'
		WHEN 'identity' 	THEN 'int'
		WHEN 'image' 		THEN 'obj'
		WHEN 'int' 		THEN 'int'
		WHEN 'money' 		THEN 'dbl'
		WHEN 'nchar' 		THEN 'str'
		WHEN 'ntext' 		THEN 'str'
		WHEN 'numeric' 		THEN 'dbl'
		WHEN 'nvarchar' 	THEN 'str'
		WHEN 'real' 		THEN 'sng'
		WHEN 'smalldatetime' 	THEN 'dt'
		WHEN 'smallint' 	THEN 'int'
		WHEN 'smallmoney' 	THEN 'dbl'
		WHEN 'sql_variant' 	THEN 'obj'
		WHEN 'text' 		THEN 'str'
		WHEN 'timestamp' 	THEN 'obj'
		WHEN 'tinyint' 		THEN 'int'
		WHEN 'uniqueidentifier'	THEN 'gui'
		WHEN 'varbinary' 	THEN 'obj'
		WHEN 'varchar' 		THEN 'str'
	END
FROM 
	syscolumns b
INNER JOIN 
	sysindexes si
ON
	b.id = si.id
	and b.colid = si.indid
INNER  JOIN
   systypes st
ON
   b.xtype = st.xusertype
	
where 
  b.id = OBJECT_ID(@TABLENAME)and
  indid = 1

/***********************************************/

PRINT 'Imports System'
PRINT 'Imports System.Web'
PRINT 'Imports System.Data'
PRINT 'Imports System.Data.SqlClient'
PRINT 'Imports System.IO'
PRINT 'Imports System.Xml'

PRINT @CRLF
PRINT 'Namespace DATA'
PRINT @CRLF
PRINT @CRLF
PRINT  @TAB +   'Public Class ' + @TABLENAME  
PRINT @CRLF
PRINT @CRLF

PRINT '''*******************************************************************************'
   
PRINT '''  Class Name:   ' + @TABLENAME  
    
PRINT '''   Description: The class provides all database access functionallity.'
    
PRINT '''   Written By:  Peter Livesey'
    
PRINT '''   Date:        7 dec 2005'
    
PRINT '''*******************************************************************************'
PRINT @CRLF
PRINT @CRLF
PRINT @CRLF
PRINT @tab + '''make this a static class by making a private constructor'
PRINT @tab + 'Private Function ' + @TABLENAME  + '() As Object'
PRINT @tab + 'End Function'




 PRINT @TAB +'Public Class Field'          

PRINT @FIELDCLASSSTRING

 PRINT @TAB + 'End Class  '' field'   /* end of field class*/
      


/* now I need to write the insert procedure*/
/* work out the parameterlist for the insert proc*/

DECLARE @spname varchar(128)

SET @spname = 'usp_insert'+ @TABLENAME


SELECT
	 @PARAMETERLIST = @PARAMETERLIST + 
'ByVal ' + 

	CASE st.name
		WHEN 'bigint' 		THEN 'vnt'
		WHEN 'binary' 		THEN 'vnt'
		WHEN 'bit' 		THEN 'bln'
		WHEN 'char' 		THEN 'str'
		WHEN 'datetime' 	THEN 'dt'
		WHEN 'decimal' 		THEN 'vnt'
		WHEN 'float' 		THEN 'dbl'
		WHEN 'identity' 	THEN 'lng'
		WHEN 'image' 		THEN 'vnt'
		WHEN 'int' 		THEN 'lng'
		WHEN 'money' 		THEN 'cry'
		WHEN 'nchar' 		THEN 'str'
		WHEN 'ntext' 		THEN 'str'
		WHEN 'numeric' 		THEN 'vnt'
		WHEN 'nvarchar' 	THEN 'str'
		WHEN 'real' 		THEN 'sng'
		WHEN 'smalldatetime' 	THEN 'dt'
		WHEN 'smallint' 	THEN 'int'
		WHEN 'smallmoney' 	THEN 'cry'
		WHEN 'sql_variant' 	THEN 'vnt'
		WHEN 'text' 		THEN 'str'
		WHEN 'timestamp' 	THEN 'vnt'
		WHEN 'tinyint' 		THEN 'byt'
		WHEN 'uniqueidentifier'	THEN 'gui'
		WHEN 'varbinary' 	THEN 'vnt'
		WHEN 'varchar' 		THEN 'str'
	END
	+ REPLACE(REPLACE(sc.name, '_', ' '), ' ', '')
	+ ' As ' +
	CASE st.name
		WHEN 'bigint' 		THEN 'intger'
		WHEN 'binary' 		THEN 'Object'
		WHEN 'bit' 		THEN 'Boolean'
		WHEN 'char' 		THEN 'String'
		WHEN 'datetime' 	THEN 'Date'
		WHEN 'decimal' 		THEN 'real'
		WHEN 'float' 		THEN 'Double'
		WHEN 'identity' 	THEN 'Long'
		WHEN 'image' 		THEN 'object'
		WHEN 'int' 		THEN 'Long'
		WHEN 'money' 		THEN 'String'
		WHEN 'nchar' 		THEN 'String'
		WHEN 'ntext' 		THEN 'String'
		WHEN 'numeric' 		THEN 'String'
		WHEN 'nvarchar' 	THEN 'String'
		WHEN 'real' 		THEN 'Single'
		WHEN 'smalldatetime' 	THEN 'Date'
		WHEN 'smallint' 	THEN 'Integer'
		WHEN 'smallmoney' 	THEN 'Currency'
		WHEN 'sql_variant' 	THEN 'object'
		WHEN 'text' 		THEN 'String'
		WHEN 'timestamp' 	THEN 'object'
		WHEN 'tinyint' 		THEN 'Byte'
		WHEN 'uniqueidentifier'	THEN 'GUID'
		WHEN 'varbinary' 	THEN 'object'
		WHEN 'varchar' 		THEN 'String'
		ELSE 'UNKNOWN'
	END
	+ ', _' + @CRLF
FROM
	syscolumns sc
JOIN
	systypes st
ON
	sc.xtype = st.xusertype
LEFT JOIN 
	sysindexes si
ON
	sc.id = si.id
  and colid = indid
WHERE
	sc.id = OBJECT_ID(@TABLENAME)
  and autoval IS NULL
ORDER BY
	sc.colorder


/* now create the stored proc paramters*/
DECLARE @SPParameters varchar(8000)
set @SPParameters = ''

SELECT @SPParameters = @SPParameters + @tab + @tab +
'.Add(New SqlParameter(' 
	+ '"@" & Field.' + REPLACE(sc.name,'@','') + '.Name ,Field.' + REPLACE(sc.name,'@','') + '.Type,Field.' + REPLACE(sc.name,'@','') + '.size)).value =' +
	CASE st.name
		WHEN 'bigint' 		THEN 'vnt'
		WHEN 'binary' 		THEN 'vnt'
		WHEN 'bit' 		THEN 'bln'
		WHEN 'char' 		THEN 'str'
		WHEN 'datetime' 	THEN 'dt'
		WHEN 'decimal' 		THEN 'vnt'
		WHEN 'float' 		THEN 'dbl'
		WHEN 'identity' 	THEN 'lng'
		WHEN 'image' 		THEN 'vnt'
		WHEN 'int' 		THEN 'lng'
		WHEN 'money' 		THEN 'cry'
		WHEN 'nchar' 		THEN 'str'
		WHEN 'ntext' 		THEN 'str'
		WHEN 'numeric' 		THEN 'vnt'
		WHEN 'nvarchar' 	THEN 'str'
		WHEN 'real' 		THEN 'sng'
		WHEN 'smalldatetime' 	THEN 'dt'
		WHEN 'smallint' 	THEN 'int'
		WHEN 'smallmoney' 	THEN 'cry'
		WHEN 'sql_variant' 	THEN 'vnt'
		WHEN 'text' 		THEN 'str'
		WHEN 'timestamp' 	THEN 'vnt'
		WHEN 'tinyint' 		THEN 'byt'
		WHEN 'uniqueidentifier'	THEN 'gui'
		WHEN 'varbinary' 	THEN 'vnt'
		WHEN 'varchar' 		THEN 'str'
		ELSE 'str'
	END
	+
	REPLACE(REPLACE(SUBSTRING(sc.name, 2, LEN(sc.name) - 1), '_', ' '), ' ', '')
  +@CRLF
	

FROM
	syscolumns sc
INNER JOIN
	systypes st
	ON(
		sc.xtype = st.xusertype
	)
WHERE
	sc.id = OBJECT_ID('usp_insert' +@TABLENAME)-- and st.status = 0
ORDER BY
	sc.colorder





/* rip of last comma */
SET @PARAMETERLIST = LEFT(@PARAMETERLIST,LEN(@PARAMETERLIST) - 5) + ' _' 
PRINT  @tab + 'Public Shared Function Insert' + @TABLENAME  + ' _'
PRINT  @tab + '(  _'
PRINT @PARAMETERLIST
PRINT @tab + ') as string'
/* now we write the body of this function*/
PRINT @CRLF + @CRLF
PRINT @tab + ''' open connection' 
PRINT @tab + 'Dim cn As SqlConnection'
PRINT @tab + 'Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")'
PRINT @CRLF + @CRLF
PRINT @tab +  'cn = New SqlConnection(strConn)'
PRINT @CRLF
PRINT @tab + '''create the command object'
PRINT @tab + 'Dim strCommand As String = "usp_Insert' + @tablename + '"'
PRINT @tab + 'Dim scmd As New SqlCommand(strCommand, cn)'
PRINT @tab + 'scmd.CommandType = CommandType.StoredProcedure'
PRINT @CRLF
PRINT @tab + ''' Add all the required SQL parameters.'
PRINT @tab + 'With scmd.Parameters'
PRINT @SPParameters
PRINT @tab + 'End With'
PRINT @tab + 'Try'
PRINT @tab + @tab +'cn.Open()'
PRINT @tab + @tab +'scmd.ExecuteNonQuery()'
PRINT @tab + @tab +'Return "" '' return no errors'
PRINT @tab + 'Catch exp As Exception'
PRINT @tab + @tab +'Return exp.Message'
PRINT @tab + 'Finally'
PRINT @tab + @tab +'cn.Close()'
PRINT @tab + 'End Try'
PRINT 'end function ''Insert' + @TABLENAME 




/* now we need the update proc*/
/* rip of last comma */
PRINT  @tab + 'Public Shared Function Update' + @TABLENAME  + ' _'
PRINT  @tab + '(  _'
PRINT @PARAMETERLIST
PRINT @tab + ') as string'
/* now we write the body of this function*/
PRINT @CRLF + @CRLF
PRINT @tab + ''' open connection' 
PRINT @tab + 'Dim cn As SqlConnection'
PRINT @tab + 'Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")'
PRINT @CRLF + @CRLF
PRINT @tab +  'cn = New SqlConnection(strConn)'
PRINT @CRLF
PRINT @tab + '''create the command object'
PRINT @tab + 'Dim strCommand As String = "usp_Update' + @tablename + '"'
PRINT @tab + 'Dim scmd As New SqlCommand(strCommand, cn)'
PRINT @tab + 'scmd.CommandType = CommandType.StoredProcedure'
PRINT @CRLF
PRINT @tab + ''' Add all the required SQL parameters.'
PRINT @tab + 'With scmd.Parameters'
PRINT @SPParameters
PRINT @tab + 'End With'
PRINT @tab + 'Try'
PRINT @tab + @tab +'cn.Open()'
PRINT @tab + @tab +'scmd.ExecuteNonQuery()'
PRINT @tab + @tab +'Return "" '' return no errors'
PRINT @tab + 'Catch exp As Exception'
PRINT @tab + @tab +'Return exp.Message'
PRINT @tab + 'Finally'
PRINT @tab + @tab +'cn.Close()'
PRINT @tab + 'End Try'
PRINT 'end function ''update' + @TABLENAME 




/* now we need the update proc*/
/* rip of last comma */
PRINT  @tab + 'Public Shared Function Delete' + @TABLENAME  + ' _'
PRINT  @tab + '(  _'
PRINT @IDVBPrefix + @IDname + ' as ' + @IDVBTYPE + ' _'
PRINT @tab + ') as string'
/* now we write the body of this function*/
PRINT @CRLF + @CRLF
PRINT @tab + ''' open connection' 
PRINT @tab + 'Dim cn As SqlConnection'
PRINT @tab + 'Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")'
PRINT @CRLF + @CRLF
PRINT @tab +  'cn = New SqlConnection(strConn)'
PRINT @CRLF
PRINT @tab + '''create the command object'
PRINT @tab + 'Dim strCommand As String = "usp_Delete' + @tablename + '"'
PRINT @tab + 'Dim scmd As New SqlCommand(strCommand, cn)'
PRINT @tab + 'scmd.CommandType = CommandType.StoredProcedure'
PRINT @CRLF
PRINT @tab + ''' Add all the required SQL parameters.'
PRINT @tab + 'With scmd.Parameters'
PRINT @TAB + @TAB + '.add(New SqlParameter("@" & Field.' + @IDNAME + '.Name, Field.' + @IDNAME + '.Type, Field.' + @IDNAME + '.size)).value = ' + @IDVBPREFIX + @IDNAME

      
PRINT @tab + 'End With'
PRINT @tab + 'Try'
PRINT @tab + @tab +'cn.Open()'
PRINT @tab + @tab +'scmd.ExecuteNonQuery()'
PRINT @tab + @tab +'Return "" '' return no errors'
PRINT @tab + 'Catch exp As Exception'
PRINT @tab + @tab +'Return exp.Message'
PRINT @tab + 'Finally'
PRINT @tab + @tab +'cn.Close()'
PRINT @tab + 'End Try'
PRINT 'end function ''delete' + @TABLENAME 


PRINT @CRLF
PRINT 'End Class ''' + @TABLENAME  /*end of main class*/        
PRINT 'End NameSpace '' data'

Rate

Share

Share

Rate