locked
vb.net classes - create from SQL? RRS feed

  • Question

  • User1123965013 posted

    I've read a few things that suggest VB.NET classes can be automatically generated from an SQL database to save on coding effort.

    Does anyone know of a good tool that can be mapped to my existing ecommerce SQL database design for all tables, PK's, FK's, etc... and give me  head start on the class code?

    I've heard Visio might be able to do this using UML diagrams and reverse engineering, but wondering if there is a better package out there or perhaps a better method to achieve class code.

    Wednesday, February 17, 2010 7:41 PM

Answers

  • User-319574463 posted

    Running this stored procedure against my ToDo table by

    EXEC csp_MakeSelect 'ToDo', 'VB'

    gives a Select stored procedure and the following:

      ''' <summary>
      '''    Helper class for ToDoSelect
      '''    20Feb2010Autogenerated
      ''' </summary>
      Public Class ToDoSelect
        Dim m_iId As Integer
        Dim m_sDateAdded As String
        Dim m_sDateDone As String
        Dim m_bItemDone As Boolean
        Dim m_sItemText As String
        Dim m_sItemTitle As String
      #Region "Id "
        ''' <summary>
        ''' Gets or sets Identity integer primary key
        ''' </summary>
        Public Property Id AS Integer 
          Get
            Return m_iId
          End Get
          Set(ByVal Value As Integer)
            m_iId = value
          End Set
        End Property
      #End Region 
      #Region "DateAdded "
        ''' <summary>
        ''' Gets or sets Date/time row added
        ''' </summary>
        Public Property DateAdded AS String 
          Get
            Return m_sDateAdded
          End Get
          Set(ByVal Value As String)
            m_sDateAdded = value
          End Set
        End Property
      #End Region 
      #Region "DateDone "
        ''' <summary>
        ''' Gets or sets Date item marked as done
        ''' </summary>
        Public Property DateDone AS String 
          Get
            Return m_sDateDone
          End Get
          Set(ByVal Value As String)
            m_sDateDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemDone "
        ''' <summary>
        ''' Gets or sets 0 = To do, 1 = Done
        ''' </summary>
        Public Property ItemDone AS bool 
          Get
            Return m_bItemDone
          End Get
          Set(ByVal Value As bool)
            m_bItemDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemText "
        ''' <summary>
        ''' Gets or sets Text to to do item
        ''' </summary>
        Public Property ItemText AS String 
          Get
            Return m_sItemText
          End Get
          Set(ByVal Value As String)
            m_sItemText = value
          End Set
        End Property
      #End Region 
      #Region "ItemTitle "
        ''' <summary>
        ''' Gets or sets Title of to do item
        ''' </summary>
        Public Property ItemTitle AS String 
          Get
            Return m_sItemTitle
          End Get
          Set(ByVal Value As String)
            m_sItemTitle = value
          End Set
        End Property
      #End Region 
      End Class

      ''' <summary>
      '''    Helper class for ToDoSelect
      '''    20Feb2010Autogenerated
      ''' </summary>
      Public Class ToDoSelect
        Dim m_iId As Integer
        Dim m_sDateAdded As String
        Dim m_sDateDone As String
        Dim m_bItemDone As Boolean
        Dim m_sItemText As String
        Dim m_sItemTitle As String
      #Region "Id "
        ''' <summary>
        ''' Gets or sets Identity integer primary key
        ''' </summary>
        Public Property Id AS Integer 
          Get
            Return m_iId
          End Get
          Set(ByVal Value As Integer)
            m_iId = value
          End Set
        End Property
      #End Region 
      #Region "DateAdded "
        ''' <summary>
        ''' Gets or sets Date/time row added
        ''' </summary>
        Public Property DateAdded AS String 
          Get
            Return m_sDateAdded
          End Get
          Set(ByVal Value As String)
            m_sDateAdded = value
          End Set
        End Property
      #End Region 
      #Region "DateDone "
        ''' <summary>
        ''' Gets or sets Date item marked as done
        ''' </summary>
        Public Property DateDone AS String 
          Get
            Return m_sDateDone
          End Get
          Set(ByVal Value As String)
            m_sDateDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemDone "
        ''' <summary>
        ''' Gets or sets 0 = To do, 1 = Done
        ''' </summary>
        Public Property ItemDone AS bool 
          Get
            Return m_bItemDone
          End Get
          Set(ByVal Value As bool)
            m_bItemDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemText "
        ''' <summary>
        ''' Gets or sets Text to to do item
        ''' </summary>
        Public Property ItemText AS String 
          Get
            Return m_sItemText
          End Get
          Set(ByVal Value As String)
            m_sItemText = value
          End Set
        End Property
      #End Region 
      #Region "ItemTitle "
        ''' <summary>
        ''' Gets or sets Title of to do item
        ''' </summary>
        Public Property ItemTitle AS String 
          Get
            Return m_sItemTitle
          End Get
          Set(ByVal Value As String)
            m_sItemTitle = value
          End Set
        End Property
      #End Region 
      End Class



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2010 11:55 AM

All replies

  • User-952121411 posted

     

    I've heard Visio might be able to do this using UML diagrams and reverse engineering, but wondering if there is a better package out there or perhaps a better method to achieve class code.

    The last edition I am aware of 'Visio for Enterprise Architects' was only available in Visio 2003 and would reverse engineer code into VS.NET 2003 only.  Since that is quite an old IDE at this point I am not sure how helpful it would be.  In fact, several years ago most of the peers around me that gave it a shot in engineering the code said it was spotty at best, however that is 2nd hand information.  That may explain though why no new version has come out since.  If anyone knows differently, please add to this.  Here is a link with some additional info as well:

    http://blogs.msdn.com/visio/archive/2006/12/05/visio-2007-standard-and-professional.aspx

    Wednesday, February 17, 2010 11:08 PM
  • User-319574463 posted

    In the CommonData solution at http://commondata.codeplex.com there are scripts for generating TSQL from SQL tables. 

    One of them generates either a C# or VB.NET data help[er class.


    Saturday, February 20, 2010 11:46 AM
  • User-319574463 posted

    ALTER Procedure [dbo].[csp_MakeSelect] 
    (
      @TABLENAME NVARCHAR(255)= 'xyzzy',
      @LANG      CHAR(2) = ''  -- VB or C# (default set in configration section)
    ) AS
    -- Purpose:
    --   Create Select script from table definition
    -- Copyright (C) 2000, 2003, 2004, 2005, 2006, 2007, 2008, 2009 Clive Chinery
    -- 
    -- This library is free software; you can redistribute it and/or
    -- modify it under the terms of the GNU Lesser General Public
    -- License as published by the Free Software Foundation; either
    -- version 2.1 of the License, or (at your option) any later version.
    -- 
    -- This library is distributed in the hope that it will be useful,
    -- but WITHOUT ANY WARRANTY; without even the implied warranty of
    -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    -- Lesser General Public License for more details.
    -- 
    -- You should have received a copy of the GNU Lesser General Public
    -- License along with this library; if not, write to the Free Software
    -- Foundation, Inc., 59 Temple Place, Suite 330, 
    --- Boston, MA  02111-1307  USA
    -- Notes:
    --   1) Run from current database
    --      by entering csp_MakeSelect TABLENAME XX
    --      where XX is either VB or C# (can be defaulted)
    --   2) The TABLENAME is case insensitive, however the table must exist
    --   3) Id and TIMESTAMP columns are specially handled
    --   4) Tables with Id column will have a where clause using 
    --      the Id column - this normally suffices. 
    -- Changes:
    -- 27Jan2009 Clive Make C# wrapper code StyleCop compliant
    -- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
    -- 24Jun2006 Clive Make VB/C# switchable
    -- 09Oct2005 Clive Update documentation
    -- 19Sep2005 Clive Extend to output XML documentation
    -- 01Sep2005 Clive Extend to cover more data types
    -- 09Jun2005 Clive Make c# variant of vb.net
    -- 14Oct2000 Clive - Original coding
    SET NOCOUNT ON
    DECLARE @colid      INT
    DECLARE @ColName    VARCHAR(80)
    DECLARE @ColLength  INT
    DECLARE @COUNT_ROW  INT
    DECLARE @HasIdent   INT
    DECLARE @RowCount   INT
    DECLARE @RowIndex   INT
    DECLARE @PrintLine  VARCHAR(256)
    DECLARE @SpName     VARCHAR(80)
    DECLARE @HelperName VARCHAR(80)
    DECLARE @SpUpper    VARCHAR(80)
    DECLARE @DateWork   VARCHAR(16)
    DECLARE @TN         VARCHAR(80)
    DECLARE @SetValue   VARCHAR(80)
    DECLARE @USER       VARCHAR(32)
    DECLARE @xprec      INT
    DECLARE @xscale     INT
    DECLARE @DOM_NAME   VARCHAR(50)
    DECLARE @SpPrefix   VARCHAR(10)
    DECLARE @SET_DB     VARCHAR(50)
    DECLARE @ObPrefix   VARCHAR(10)
    DECLARE @VarPrefix  VARCHAR(10)
    DECLARE @ALLOWED    BIT
    DECLARE @DefLanguage CHAR(2)
    DECLARE @Separator2 VARCHAR(1)
    DECLARE @ArgName    VARCHAR(80)
    DECLARE @COL_NAME   VARCHAR(80)
    DECLARE @HAS_LAST   BIT
    DECLARE @Type       VARCHAR(32)
    DECLARE @Length     INT
    DECLARE @SqlDbType  VARCHAR(20) 
    DECLARE @COMMENT    VARCHAR(132)
    DECLARE @PREFIX     VARCHAR(1)
    DECLARE @ModPrefix  VARCHAR(10)
    DECLARE @USEVAR     BIT
    DECLARE @USETNAME   BIT
    SELECT  @LANG = RTRIM(UPPER(LTRIM(@LANG)))
    SELECT  @ALLOWED = 0
    IF @LANG = 'CS' SELECT  @LANG = 'C#'
    IF @LANG = 'VB' SELECT  @ALLOWED = 1
    IF @LANG = 'C#' SELECT  @ALLOWED = 1
    --  Configuration start
    SELECT  @SpPrefix = 'usp_'          -- Set to desired prefix for stored procedures
    SELECT  @DOM_NAME = 'DALETH' + '\'  -- Set to your domain name
    -- Set @SET_DB to name of SP to set permissions or to blank
    SELECT  @SET_DB   = 'asp_dbPermissions'
    SELECT  @ObPrefix = 'x'             -- Prefix for object variables
    SELECT  @DefLanguage = 'C#'         -- Default language C# or VB
    SELECT  @Separator2  = ''           -- Separator between Tablename and action
    SELECT  @ModPrefix   = 'm_'         -- Module level prefix
    SELECT  @USEVAR      = 1            -- set to 1 for VS2008 C#, otherwise 0
    SELECT  @USETNAME    = 0            -- 1=Use Table name in wrapper name
    --  Configuration end
    IF @ALLOWED = 0 SET @LANG = @DefLanguage
    IF @TABLENAME ='xyzzy' 
    OR NOT EXISTS (SELECT * FROM sysobjects 
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
    BEGIN
      PRINT '--Use csp_MakeSelect TABLENAME (XX) to generate select S.P. '
      PRINT ' for TABLENAME and XX is the language for Wrapper VB or C#'
      PRINT '--or  csp_MakeSelect garbage to get this help text!'
      RETURN
    END 
    SELECT @TN = name FROM sysobjects 
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
    DECLARE @DocTable TABLE 
      Comment VARCHAR(128),
      Colname VARCHAR(128),
      Coltype VARCHAR(128),
      Cstatus INT,
      ColId   INT,
      Cid     INT,
      length  INT,
      xprec   INT,
      xscale  INT
    INSERT INTO @DocTable (Comment, Colname, Coltype, Cstatus, 
      ColId, Cid, length, xprec, xscale)
    SELECT COALESCE(CONVERT(VARCHAR(130),P.value), 
      '[Undocumented in Database]') AS Comment,  
      C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, 
      C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale
      FROM syscolumns C 
      JOIN sys.tables O ON C.id = O.object_id
      JOIN systypes   U ON C.xusertype = U.xusertype
      LEFT OUTER JOIN SYS.extended_properties P  ON P.minor_id =  C.COLID  AND C.id = P.major_id 
      WHERE O.name =  @TN AND U.name <> 'timestamp'
      ORDER BY C.colid
    SELECT @RowCount=0
    DECLARE xArgs CURSOR FOR
      SELECT Colname FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @ColName
    WHILE   @@FETCH_STATUS=0
    BEGIN
      SELECT @RowCount = @RowCount+1
      FETCH  xArgs INTO @ColName
    END
    CLOSE      xArgs
    DEALLOCATE xArgs   
    SELECT @SpName = @SpPrefix + RTRIM(@TN) + @Separator2 + 'Select'
    SELECT @HelperName = RTRIM(@TN) + 'Select'
    SELECT @ColLength= MAX(DATALENGTH(CONVERT(VARCHAR(64),Colname))) 
      FROM @DocTable
      WHERE Cstatus<>128 
    SELECT @SpUpper = UPPER(@SpName)
    IF EXISTS(select * from sysobjects 
    where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
    SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
    ELSE
      SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName
    PRINT  @PrintLine
    PRINT  '('
    SELECT @HasIdent=COUNT(*) FROM @DocTable
     WHERE Cstatus=128
    SELECT  @HAS_LAST = 0
    SELECT  @RowIndex = 0
    IF      @ColLength < 16 SELECT @ColLength = 16
    DECLARE xArgs CURSOR FOR 
    SELECT '@' + Colname, Coltype, length, xprec, xscale
      FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
    WHILE   @@FETCH_STATUS = 0
    BEGIN ----
      SELECT @RowIndex=@RowIndex + 1
      SELECT @ArgName = SUBSTRING(@COL_NAME 
           + '                                    ',1,@ColLength + 2)
      SELECT @Type    = UPPER(@Type)
      SELECT @PrintLine = '  ' + @ArgName + @Type
      IF @Type='char'
        SELECT @PrintLine=@PrintLine 
             + ' (' + RTRIM(convert(varchar,@Length))  + ')'
      IF @Type = 'VARCHAR' BEGIN
        IF @Length = -1
          SELECT @PrintLine = @PrintLine + ' (MAX)'
        ELSE
          SELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length))  + ')'
        END
      IF @Type = 'NCHAR'
        SELECT @PrintLine=@PrintLine + ' (' 
             + RTRIM(convert(varchar,@Length/2))  + ')'
      IF @Type = 'NVARCHAR' BEGIN
        IF @Length = -1
          SELECT @PrintLine = @PrintLine + ' (MAX)'
        ELSE
          SELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length / 2))  + ')'
        END
      IF (@Type='decimal') OR (@Type='numeric')
        SELECT @PrintLine = @PrintLine 
             + ' (' + RTRIM(convert(varchar,@xprec)) 
               + ','  + RTRIM(convert(varchar,@xscale)) + ')'   
      IF RTRIM(LTRIM(@ArgName)) <> '@Id'
        SELECT @PrintLine = @PrintLine + ' OUTPUT'  
      IF @RowIndex < @RowCount 
        SELECT @PrintLine = @PrintLine + ','
      PRINT @PrintLine
      FETCH xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE xArgs
    DEALLOCATE xArgs
    PRINT  ') AS'
    PRINT '--  Purpose:'
    SELECT @PrintLine='--    Select record on ' + @TN + ' table'
    PRINT  @PrintLine
    PRINT  '--  Parameters:'      
    DECLARE xArgs CURSOR FOR 
    SELECT Colname FROM @DocTable
    WHERE Cstatus = 128 
      ORDER BY ColId     
    OPEN  xArgs
    FETCH xArgs INTO @ArgName 
    WHILE @@FETCH_status=0
    BEGIN
      SELECT @ArgName=SUBSTRING(@ArgName 
           + '                            ',1,@ColLength + 2)
      SELECT @PrintLine='--    ' + @ArgName + '-'
      PRINT @PrintLine
      FETCH xArgs INTO @ArgName 
    END
    CLOSE  xArgs
    DEALLOCATE xArgs          
    PRINT  '--  History:   '
    SELECT @USER = system_user
    IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
      SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
    END  
    SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
    SELECT @DateWork=substring(@DateWork,1,2) 
         + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
    SELECT @PrintLine='--  ' + @DateWork + ' '      
    SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
    PRINT  @PrintLine
    PRINT  'SET NOCOUNT ON'      
    SELECT @PrintLine='SELECT ' 
    PRINT  @PrintLine     
    SELECT @RowCount=COUNT(*) 
      FROM @DocTable 
      WHERE Colname NOT IN ('Id')
    DECLARE xArgs CURSOR FOR 
     SELECT  Colname FROM @DocTable
      WHERE Colname NOT IN ('Id')        
      ORDER BY ColId
    SELECT @RowIndex=0
    OPEN   xArgs
    FETCH  xArgs INTO @ArgName
    WHILE  @@FETCH_STATUS=0
    BEGIN
      SELECT @RowIndex=@RowIndex + 1
      SELECT @ArgName=SUBSTRING(@ArgName + '           ',1,@ColLength+2)
      SELECT @PrintLine = '  @' + @ArgName + ' = ' + @TN 
           + '.' + RTRIM(@ArgName)
      IF @RowIndex <> @RowCount 
      SELECT @PrintLine=@PrintLine + ','
      PRINT @PrintLine
      FETCH xArgs INTO @ArgName
    END
    CLOSE      xArgs
    DEALLOCATE xArgs    
    SELECT @RowCount = COUNT(*) FROM @DocTable
    -- Where clause missing
    IF @RowCount = 0 BEGIN
      PRINT  '-- You MUST amend the following line to correctly identify the row to update'
        ---------
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE SOMECOLUMN = @SOMEVALUE'
        PRINT  @PrintLine        
        END
      ELSE BEGIN
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE '
        PRINT  @PrintLine      
        SELECT @RowCount = COUNT(*) FROM @DocTable
         WHERE Cstatus = 128
        IF @RowCount = 0 BEGIN
          PRINT '-- THERE IS NO Identity ROW - assuming first row'
          DECLARE xArgs CURSOR FOR 
          SELECT  Colname + '=@' + Colname 
          FROM @DocTable
          ORDER BY ColId
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          PRINT '  ' + @TN + '.' + @SetValue
          CLOSE      xArgs
          DEALLOCATE xArgs
          END
        ELSE BEGIN
          DECLARE xArgs CURSOR FOR 
          SELECT  Colname + '=@' + Colname
          FROM @DocTable
          WHERE Cstatus = 128
          ORDER BY ColId
          SELECT @RowIndex=0
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          WHILE @@FETCH_STATUS=0
          BEGIN
            SELECT @RowIndex=@RowIndex + 1
            SELECT @PrintLine='  ' + @TN + '.' + @SetValue
            IF @RowIndex <> @RowCount   
              SELECT @PrintLine=@PrintLine + ' AND'
            PRINT @PrintLine
            FETCH xArgs INTO @SetValue
          END
          CLOSE      xArgs
          DEALLOCATE xArgs
        END
      END 
    PRINT  'RETURN'
    PRINT  '-------------- this is the end ----------------' 
    PRINT  '/* Data Access code follows:'
    IF @LANG = 'VB' BEGIN
      PRINT  '#Region " Using "'
      PRINT  'Imports System'
      PRINT  'Imports System.Data'
      PRINT  'Imports System.Data.SqlClient'
      PRINT  '#End Region'
      END
    ELSE BEGIN
      PRINT  '#region " Using "'
      PRINT  'using System;'
      PRINT  'using System.Data;'
      PRINT  'using System.Data.SqlClient;'
      PRINT  '#endregion'
      END
    IF @LANG = 'VB' BEGIN
      PRINT  '    #Region " SelectRecord  "'
      PRINT  '    '''''' <summary>'
      PRINT  '    '''''' Select record on ' + @TN
      PRINT  '    '''''' </summary>'
      PRINT  '    '''''' <param name="id">Id of record to fetch</param>'
      PRINT  '    '''''' <returns>' + @HelperName 
             + ' containing data for selected row</returns>'
      END
    ELSE BEGIN
     PRINT  '    #region " Select  "'
      PRINT  '    /// <summary>'
      PRINT  '    /// Select record on ' + @TN
      PRINT  '    /// </summary>'
      PRINT  '    /// <param name="id">Id of record to fetch</param>'
      PRINT  '    /// <returns>' + @HelperName 
             + ' containing data for selected row</returns>'
      END
    SELECT  @RowIndex = 0
    SELECT @RowCount=COUNT(*) FROM @DocTable
    IF @LANG = 'VB' BEGIN
      IF @USETNAME = 1
        PRINT  '    Public Function SelectRecord' + @TN + '(ByRef id AS Integer) AS ' + @HelperName
      ELSE
        PRINT  '    Public Function SelectRecord(ByRef id AS Integer) AS ' + @HelperName
      PRINT  '      Dim ' + @ObPrefix + @HelperName + ' As ' + @HelperName + ' = new ' + @HelperName + '()  '
      PRINT  '      Dim connect As String = CommonData.GetConnection()'
      PRINT  '      Dim sqlConnection As SqlConnection = new SqlConnection(connect)'
      PRINT  '      Dim sqlCommand As SqlCommand = new SqlCommand("' + @SpName + '", sqlConnection)'
      PRINT  '      Try'
      PRINT  '        sqlCommand.CommandType = CommandType.StoredProcedure'
      END
    ELSE BEGIN
      IF @USETNAME = 1
        PRINT  '    public static ' + @HelperName + ' Select' + @TN + '(int id) ' 
      ELSE
        PRINT  '    public static ' + @HelperName + ' Select(int id) ' 
      PRINT  '    {'
      IF @USEVAR = 0 BEGIN
        PRINT  '      ' + @HelperName + ' ' + @ObPrefix + '' + @HelperName + ' = new ' + @HelperName + '();  '
        PRINT  '      string connect = CommonData.ConnectionString;'
        PRINT  '      SqlConnection sqlConnection = new SqlConnection(connect);'
        PRINT  '      SqlCommand sqlCommand = new SqlCommand("' + @SpName + '", sqlConnection);'
        END
      ELSE BEGIN
        PRINT  '      var ' + dbo.ufn_CamelCase(@HelperName) + ' = new ' + @HelperName + '();  '
        PRINT  '      var connect = CommonData.ConnectionString;'
        PRINT  '      var sqlConnection = new SqlConnection(connect);'
        PRINT  '      var sqlCommand = new SqlCommand("' + @SpName + '", sqlConnection);'
        END
      PRINT  '      try'
      PRINT  '      {'
      PRINT  '        sqlCommand.CommandType = CommandType.StoredProcedure;'
      END
    SELECT @RowIndex = 0
    DECLARE xArgs CURSOR FOR 
    SELECT DISTINCT '@' + Colname, Coltype, length, ColId, xprec, xscale
      FROM @DocTable
      ORDER BY ColId 
    OPEN  xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    WHILE @@FETCH_STATUS=0
    BEGIN
      SELECT @SqlDbType = @Type  -- Default assignment
      IF @Type='bigint'         SELECT @SqlDbType = 'BigInt'
      IF @Type='bit'            SELECT @SqlDbType = 'Bit'
      IF @Type='int'            SELECT @SqlDbType = 'Int'
      IF @Type='decimal'        SELECT @SqlDbType = 'Decimal'
      IF @Type='numeric'        SELECT @SqlDbType = 'Decimal'
      IF @Type='datetime'       SELECT @SqlDbType = 'DateTime'
      IF @Type='char'           SELECT @SqlDbType = 'Char' 
      IF @Type='varchar'        SELECT @SqlDbType = 'VarChar'
      IF @Type='nchar'          SELECT @SqlDbType = 'NChar'
      IF @Type='nvarchar'       SELECT @SqlDbType = 'NVarChar' 
      IF @Type='ntext'          SELECT @SqlDbType = 'NText'
      IF @Type='text'           SELECT @SqlDbType = 'Text'
      IF @Type='real'           SELECT @SqlDbType = 'Real'
      IF @Type='money'          SELECT @SqlDbType = 'Money'
      IF @Type='float'          SELECT @SqlDbType = 'Float'
      IF @Type='smalldatetime'  SELECT @SqlDbType = 'SmallDateTime'
      IF @Type='smallint'      SELECT @SqlDbType = 'SmallInt'
      IF @Type='smallmoney'     SELECT @SqlDbType = 'SmallMoney'
      IF @Type='xml'            SELECT @SqlDbType = 'Xml'
      -- more type conversions here !
      SELECT @PrintLine='        sqlCommand.Parameters.Add("' 
          + @COL_NAME + '", SqlDbType.'
      SELECT @PrintLine=@PrintLine + @SqlDbType
      IF @Type='char'     
        SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length)) 
      IF @Type='varchar' 
      SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length)) 
      IF @Type='nchar'    
        SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length/2)) 
      IF @Type='nvarchar' 
        SELECT @PrintLine=@PrintLine + ', ' 
        + RTRIM(convert(varchar,@Length/2)) 
      IF @LANG = 'VB' 
        SELECT @PrintLine=@PrintLine + ')' 
      ELSE
        SELECT @PrintLine=@PrintLine + ');'  
      PRINT @PrintLine   
      IF (@Type='decimal') OR (@Type='numeric') BEGIN
      IF @LANG = 'VB' BEGIN
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec) 
        ELSE  
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
        PRINT @PrintLine
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)
        ELSE    
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
        END
      ELSE BEGIN
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec)
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
        PRINT @PrintLine
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)
        ELSE    
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
        END   
        PRINT @PrintLine
      END     
      SELECT @RowIndex = @RowIndex + 1
      IF @LANG = 'VB' BEGIN
        IF @RowIndex = 1 
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Value = id'
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters("'  + @COL_NAME + '").Direction = ParameterDirection.Output'
        END
      ELSE BEGIN
        IF @RowIndex = 1 
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Value = id;'
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Direction = ParameterDirection.Output;'
        END
      PRINT @PrintLine
      FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    END  
    CLOSE      xArgs
    DEALLOCATE xArgs
    IF @LANG = 'VB' BEGIN
      PRINT  '        sqlCommand.Connection.Open()'
      PRINT  '        sqlCommand.ExecuteNonQuery()'
      END
    ELSE BEGIN
      PRINT  '        sqlCommand.Connection.Open();'
      PRINT  '        sqlCommand.ExecuteNonQuery();'
      END 
    DECLARE xArgs CURSOR FOR 
    SELECT DISTINCT Colname, Coltype, length, ColId, xprec, xscale
    FROM @DocTable ORDER BY ColId
    OPEN  xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      IF @RowIndex > 0 BEGIN
        SELECT @SqlDbType = 'NullToString' 
        IF @Type='bit'            SELECT @SqlDbType = 'NullToBoolean'
        IF @Type='int'            SELECT @SqlDbType = 'NullToInteger'
        ---
        IF @Type='smallint'       SELECT @SqlDbType = 'NullToInteger16'
        IF @Type='bigint'         SELECT @SqlDbType = 'NullToInteger64'
        IF @Type='decimal'        SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='numeric'        SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='money'          SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='smallmoney'     SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='datetime'       SELECT @SqlDbType = 'NullToDateString'
        IF @Type='smalldatetime'  SELECT @SqlDbType = 'NullToDateString'
        -----
        IF @Type='real'           SELECT @SqlDbType = 'NullToDouble' 
        IF @Type='char'           SELECT @SqlDbType = 'NullToString' 
        IF @Type='varchar'        SELECT @SqlDbType = 'NullToString'
        IF @Type='nchar'          SELECT @SqlDbType = 'NullToString'
        IF @Type='nvarchar'       SELECT @SqlDbType = 'NullToString' 
        IF @Type='text'           SELECT @SqlDbType = 'NullToString' 
        IF @Type='ntext'          SELECT @SqlDbType = 'NullToString'
        IF @Type='float'          SELECT @SqlDbType = 'NullToDouble'
        IF @LANG = 'VB' BEGIN
          SELECT @PrintLine = '        ' + @ObPrefix + '' + @HelperName  + '.' + @COL_NAME + ' = CommonData.'
          SELECT @PrintLine = @PrintLine + @SqlDbType + '('  + @ObPrefix + 'SqlCommand.Parameters("@'
          SELECT @PrintLine = @PrintLine + @COL_NAME + '").Value)'
          END
        ELSE BEGIN 
          SELECT @PrintLine = '        ' + dbo.ufn_CamelCase(@HelperName)  + '.' + @COL_NAME + ' = CommonData.'
          SELECT @PrintLine = @PrintLine + @SqlDbType + '(sqlCommand.Parameters["@'
          SELECT @PrintLine = @PrintLine + @COL_NAME + '"].Value);'
        END
        PRINT @PrintLine
        END
      FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    END
    CLOSE      xArgs
    DEALLOCATE xArgs
    IF @LANG = 'VB' BEGIN
      PRINT  '      Catch ex As Exception'
      PRINT  '        const Message As String = "Failure calling ' + @SpName + '"'
      PRINT  '        CommonData.WriteEventError(ex, Message)'
      PRINT  '        throw new DalGeneralException(Message, ex)'
      PRINT  '      Finally '
      PRINT  '        sqlCommand.Connection.Close()'
      PRINT  '        sqlCommand.Dispose()'
      PRINT  '        sqlConnection.Dispose()'
      PRINT  '      End Try'
      PRINT  '      Return ' + @ObPrefix + '' + @HelperName   
      PRINT  '    End Function'
      PRINT  '    #End Region'
      END
    ELSE BEGIN
      PRINT  '    }' 
      PRINT  '      catch (Exception ex)'
      PRINT  '      {'
      PRINT  '        const string Message = "Failure calling ' + @SpName + '";'
      PRINT  '        CommonData.WriteEventError(ex, Message);'  
      PRINT  '        throw new DalGeneralException(Message, ex);'
      PRINT  '      }'
      PRINT  '      finally '
      PRINT  '      {'
      PRINT  '        if (sqlCommand.Connection != null) ' 
      PRINT  '        {'
      PRINT  '          sqlCommand.Connection.Close();'
      PRINT  '        }'
      PRINT  '        sqlCommand.Dispose();'
      PRINT  '        sqlConnection.Dispose();'
      PRINT  '      }'
      PRINT  '      return ' + dbo.ufn_CamelCase(@HelperName) + ';'
      PRINT  '    }'
      PRINT  '    #endregion'
      END
    PRINT  '*/'
    PRINT  '/* Helper class code follows:'
    IF @LANG = 'VB' BEGIN
      --PRINT  '  #region " Helper class:' + @HelperName + '"'
      PRINT  '  '''''' <summary>'
      PRINT  '  ''''''    Helper class for ' + @HelperName
      PRINT  '  ''''''    ' + @DateWork + 'Autogenerated'
      PRINT  '  '''''' </summary>'
      PRINT  '  Public Class ' + @HelperName
      END
    ELSE BEGIN
      --PRINT  '  #region " Helper class:"' + @HelperName
      PRINT  '  /// <summary>'
      PRINT  '  ///    Helper class for ' + @HelperName
      PRINT  '  ///    ' + @DateWork + 'Autogenerated'
      PRINT  '  /// </summary>'
      PRINT  '  public class ' + @HelperName
      PRINT  '  {'
      END
    SELECT  @COMMENT = 'Not Documented Yet'
    SELECT  @RowIndex = 0
    DECLARE xArgs CURSOR FOR 
    SELECT Comment, Colname, Coltype, length, xprec, xscale
      FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, 
            @xprec, @xscale
    WHILE @@FETCH_STATUS=0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      IF @RowIndex > 0 BEGIN -- Generate id data member
        SELECT @SqlDbType = @Type  -- Default assignment
        -----------
        IF @Type='bigint'         SELECT @SqlDbType = 'Int64'
        IF @Type='smallint'       SELECT @SqlDbType = 'Int16'
        IF @Type='bit'            SELECT @SqlDbType = 'bool'
        IF @Type='int'            SELECT @SqlDbType = 'int'
        IF @Type='decimal'        SELECT @SqlDbType = 'decimal'
        IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
        ---------
        IF @Type='datetime'       SELECT @SqlDbType = 'string'
        IF @Type='char'           SELECT @SqlDbType = 'string' 
        IF @Type='varchar'        SELECT @SqlDbType = 'string'
        IF @Type='nchar'          SELECT @SqlDbType = 'string'
        IF @Type='nvarchar'       SELECT @SqlDbType = 'string' 
        IF @Type='text'           SELECT @SqlDbType = 'string' 
        IF @Type='ntext'          SELECT @SqlDbType = 'string'
        IF @Type='smallmoney'     SELECT @SqlDbType = 'decimal'
        IF @Type='money'          SELECT @SqlDbType = 'decimal'
        IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
        IF @Type='real'           SELECT @SqlDbType = 'double' 
        IF @Type='float'          SELECT @SqlDbType = 'double' 
        SELECT @VarPrefix = 'x'  -- Default assignment
        IF @Type='real'           SELECT @VarPrefix = 'd' 
        IF @Type='float'          SELECT @VarPrefix = 'd' 
        IF @Type='smallint'       SELECT @VarPrefix = 'i'
        IF @Type='smalldatetime'  SELECT @VarPrefix = 's'
        IF @Type='bigint'         SELECT @VarPrefix = 'l'
        IF @Type='bit'            SELECT @VarPrefix = 'b'
        IF @Type='int'            SELECT @VarPrefix = 'i'
        IF @Type='decimal'        SELECT @VarPrefix = 'c'
        IF @Type='numeric'        SELECT @VarPrefix = 'c'
        IF @Type='money'          SELECT @VarPrefix = 'c'
        IF @Type='smallmoney'     SELECT @VarPrefix = 'c'
        -----
        IF @Type='datetime'       SELECT @VarPrefix = 's'
        IF @Type='char'           SELECT @VarPrefix = 's' 
        IF @Type='varchar'        SELECT @VarPrefix = 's'
        IF @Type='nchar'          SELECT @VarPrefix = 's'
        IF @Type='nvarchar'       SELECT @VarPrefix = 's' 
        IF @Type='text'           SELECT @VarPrefix = 's' 
        IF @Type='ntext'          SELECT @VarPrefix = 's'
        IF @LANG = 'VB' BEGIN
          IF @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'    
          IF @SqlDbType = 'string'  SELECT @SqlDbType = 'String'
          IF @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'  
          IF @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'   
          IF @SqlDbType = 'bool'    SELECT @SqlDbType = 'Boolean'       
          PRINT  '    Dim ' + @ModPrefix +  @VarPrefix  +  @COL_NAME + ' As ' + @SqlDbType     
          END 
        ELSE BEGIN
          IF @USEVAR = 0 BEGIN
            PRINT  '    ' + @SqlDbType + '    ' + @ModPrefix + @VarPrefix + @COL_NAME + ';'  
            END  
          END
      END 
      FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE   xArgs
    SELECT  @RowIndex = 0
    OPEN    xArgs
    FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, 
            @xprec, @xscale
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      SELECT @PREFIX = 'x'  -- Default assignment
      IF @Type='bigint'     SELECT @PREFIX = 'l'
      IF @Type='bit'        SELECT @PREFIX = 'b'
      IF @Type='int'        SELECT @PREFIX = 'i'
      --------
      IF @Type='smallint'   SELECT @PREFIX = 'i'
      IF @Type='decimal'    SELECT @PREFIX = 'c'
      IF @Type='numeric'        SELECT @PREFIX = 'c'
      IF @Type='datetime'       SELECT @PREFIX = 's'
      IF @Type='smalldatetime'  SELECT @PREFIX = 's'
      IF @Type='char'           SELECT @PREFIX = 's' 
      IF @Type='varchar'    SELECT @PREFIX = 's'
      IF @Type='nchar'      SELECT @PREFIX = 's'
      IF @Type='nvarchar'   SELECT @PREFIX = 's' 
      IF @Type='text'       SELECT @PREFIX = 's'
      IF @Type='ntext'      SELECT @PREFIX = 's'
      IF @Type='smallmoney' SELECT @PREFIX = 'c'
      IF @Type='money'      SELECT @PREFIX = 'c'
      IF @Type='real'       SELECT @PREFIX = 'd'
      IF @Type='float'      SELECT @PREFIX = 'd'
      SELECT @SqlDbType = 'string'  -- Default assignment
      IF @Type='real'       SELECT @SqlDbType = 'double' 
      IF @Type='float'      SELECT @SqlDbType = 'double' 
      IF @Type='bigint'     SELECT @SqlDbType = 'Int64'
      IF @Type='smallint'   SELECT @SqlDbType = 'Int16'
      IF @Type='bit'        SELECT @SqlDbType = 'bool'
      IF @Type='int'        SELECT @SqlDbType = 'int'
      ----------
      IF @Type='decimal'    SELECT @SqlDbType = 'decimal'
      IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
      IF @Type='datetime'       SELECT @SqlDbType = 'string'
      IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
      IF @Type='char'           SELECT @SqlDbType = 'string' 
      IF @Type='varchar'        SELECT @SqlDbType = 'string'
      IF @Type='nchar'      SELECT @SqlDbType = 'string'
      IF @Type='nvarchar'   SELECT @SqlDbType = 'string' 
      IF @Type='text'       SELECT @SqlDbType = 'string' 
      IF @Type='ntext'      SELECT @SqlDbType = 'string'
      IF @Type='smallmoney' SELECT @SqlDbType = 'decimal'
      IF @Type='money'      SELECT @SqlDbType = 'decimal'
      SELECT @PrintLine = ''    
      IF @LANG = 'VB' BEGIN
        IF @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'    
        IF @SqlDbType = 'string'  SELECT @SqlDbType = 'String'
        IF @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'  
        IF @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'        
        PRINT '  #Region "' + @COL_NAME + ' "' 
        PRINT '    '''''' <summary>'
        PRINT '    '''''' Gets or sets ' + @COMMENT
        PRINT '    '''''' </summary>'
        PRINT '    Public Property ' + @COL_NAME + ' AS ' + @SqlDbType + ' '
        -----------
        PRINT '      Get'
        PRINT '        Return ' + @ModPrefix + @PREFIX + @COL_NAME
        PRINT '      End Get'
        PRINT '      Set(ByVal Value As ' + @SqlDbType + ')'
        PRINT '        ' + @ModPrefix + + @PREFIX + @COL_NAME + ' = value'
        PRINT '      End Set'
        PRINT '    End Property' 
        PRINT '  #End Region '
        END
      ELSE BEGIN
        PRINT '    /// <summary>'
        IF @Type='bit' 
          PRINT '    /// Gets or sets a value indicating whether ' + @COMMENT
        ELSE
          PRINT '    /// Gets or sets ' + @COMMENT
        PRINT '    /// </summary>'
        IF @USEVAR = 0 BEGIN
          PRINT '    public ' + @SqlDbType + ' '  + @COL_NAME
          PRINT '    {'
          PRINT '      get { return ' + @ModPrefix + @PREFIX + @COL_NAME + ';}'
          PRINT '      set { ' + @ModPrefix + @PREFIX + @COL_NAME + ' = value; }'
          PRINT '    }' 
          PRINT ''
          END
        ELSE BEGIN   
          PRINT '    public ' + @SqlDbType + ' '  + @COL_NAME + ' { get; set; }'
          IF @RowIndex < @RowCount 
            PRINT ''
          END
        END  
      FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE     xArgs
    DEALLOCATE xArgs 
    IF @LANG = 'VB' BEGIN
      PRINT  '  End Class'
      END
    ELSE BEGIN
      PRINT  '  }'
      END
    PRINT  '*/'
    PRINT  'GO'
    PRINT  @SET_DB
    PRINT  'GO'
    RETURN
    -------------- this is the end ----------------

    CREATE Procedure [dbo].[csp_MakeSelect] 
    (
      @TABLENAME NVARCHAR(255)= 'xyzzy',
      @LANG      CHAR(2) = ''  -- VB or C# (default set in configration section)
    ) AS
    -- Purpose:
    --   Create Select script from table definition
    -- Copyright (C) 2000, 2003, 2004, 2005, 2006, 2007, 2008, 2009 Clive Chinery
    -- 
    -- This library is free software; you can redistribute it and/or
    -- modify it under the terms of the GNU Lesser General Public
    -- License as published by the Free Software Foundation; either
    -- version 2.1 of the License, or (at your option) any later version.
    -- 
    -- This library is distributed in the hope that it will be useful,
    -- but WITHOUT ANY WARRANTY; without even the implied warranty of
    -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    -- Lesser General Public License for more details.
    -- 
    -- You should have received a copy of the GNU Lesser General Public
    -- License along with this library; if not, write to the Free Software
    -- Foundation, Inc., 59 Temple Place, Suite 330, 
    --- Boston, MA  02111-1307  USA
    -- Notes:
    --   1) Run from current database
    --      by entering csp_MakeSelect TABLENAME XX
    --      where XX is either VB or C# (can be defaulted)
    --   2) The TABLENAME is case insensitive, however the table must exist
    --   3) Id and TIMESTAMP columns are specially handled
    --   4) Tables with Id column will have a where clause using 
    --      the Id column - this normally suffices. 
    -- Changes:
    -- 27Jan2009 Clive Make C# wrapper code StyleCop compliant
    -- 11Nov2007 Clive Convert to SQL2005 Operation (n.b. this cannot reside in Master as a result)
    -- 24Jun2006 Clive Make VB/C# switchable
    -- 09Oct2005 Clive Update documentation
    -- 19Sep2005 Clive Extend to output XML documentation
    -- 01Sep2005 Clive Extend to cover more data types
    -- 09Jun2005 Clive Make c# variant of vb.net
    -- 14Oct2000 Clive - Original coding
    SET NOCOUNT ON
    DECLARE @colid      INT
    DECLARE @ColName    VARCHAR(80)
    DECLARE @ColLength  INT
    DECLARE @COUNT_ROW  INT
    DECLARE @HasIdent   INT
    DECLARE @RowCount   INT
    DECLARE @RowIndex   INT
    DECLARE @PrintLine  VARCHAR(256)
    DECLARE @SpName     VARCHAR(80)
    DECLARE @HelperName VARCHAR(80)
    DECLARE @SpUpper    VARCHAR(80)
    DECLARE @DateWork   VARCHAR(16)
    DECLARE @TN         VARCHAR(80)
    DECLARE @SetValue   VARCHAR(80)
    DECLARE @USER       VARCHAR(32)
    DECLARE @xprec      INT
    DECLARE @xscale     INT
    DECLARE @DOM_NAME   VARCHAR(50)
    DECLARE @SpPrefix   VARCHAR(10)
    DECLARE @SET_DB     VARCHAR(50)
    DECLARE @ObPrefix   VARCHAR(10)
    DECLARE @VarPrefix  VARCHAR(10)
    DECLARE @ALLOWED    BIT
    DECLARE @DefLanguage CHAR(2)
    DECLARE @Separator2 VARCHAR(1)
    DECLARE @ArgName    VARCHAR(80)
    DECLARE @COL_NAME   VARCHAR(80)
    DECLARE @HAS_LAST   BIT
    DECLARE @Type       VARCHAR(32)
    DECLARE @Length     INT
    DECLARE @SqlDbType  VARCHAR(20) 
    DECLARE @COMMENT    VARCHAR(132)
    DECLARE @PREFIX     VARCHAR(1)
    DECLARE @ModPrefix  VARCHAR(10)
    DECLARE @USEVAR     BIT
    DECLARE @USETNAME   BIT
    SELECT  @LANG = RTRIM(UPPER(LTRIM(@LANG)))
    SELECT  @ALLOWED = 0
    IF @LANG = 'CS' SELECT  @LANG = 'C#'
    IF @LANG = 'VB' SELECT  @ALLOWED = 1
    IF @LANG = 'C#' SELECT  @ALLOWED = 1
    --  Configuration start
    SELECT  @SpPrefix = 'usp_'          -- Set to desired prefix for stored procedures
    SELECT  @DOM_NAME = 'DALETH' + '\'  -- Set to your domain name
    -- Set @SET_DB to name of SP to set permissions or to blank
    SELECT  @SET_DB   = 'asp_dbPermissions'
    SELECT  @ObPrefix = 'x'             -- Prefix for object variables
    SELECT  @DefLanguage = 'VB'         -- Default language C# or VB
    SELECT  @Separator2  = ''           -- Separator between Tablename and action
    SELECT  @ModPrefix   = 'm_'         -- Module level prefix
    SELECT  @USEVAR      = 1            -- set to 1 for VS2008 C#, otherwise 0
    SELECT  @USETNAME    = 0            -- 1=Use Table name in wrapper name
    --  Configuration end
    IF @ALLOWED = 0 SET @LANG = @DefLanguage
    IF @TABLENAME ='xyzzy' 
    OR NOT EXISTS (SELECT * FROM sysobjects 
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U')
    BEGIN
      PRINT '--Use csp_MakeSelect TABLENAME (XX) to generate select S.P. '
      PRINT ' for TABLENAME and XX is the language for Wrapper VB or C#'
      PRINT '--or  csp_MakeSelect garbage to get this help text!'
      RETURN
    END 
    SELECT @TN = name FROM sysobjects 
    WHERE UPPER(name)=UPPER(@TABLENAME) and type='U'
    DECLARE @DocTable TABLE 
    ( 
      Comment VARCHAR(128),
      Colname VARCHAR(128),
      Coltype VARCHAR(128),
      Cstatus INT,
      ColId   INT,
      Cid     INT,
      length  INT,
      xprec   INT,
      xscale  INT
    ) 
    INSERT INTO @DocTable (Comment, Colname, Coltype, Cstatus, 
      ColId, Cid, length, xprec, xscale)
    SELECT COALESCE(CONVERT(VARCHAR(130),P.value), 
      '[Undocumented in Database]') AS Comment,  
      C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, 
      C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale
      FROM syscolumns C 
      JOIN sys.tables O ON C.id = O.object_id
      JOIN systypes   U ON C.xusertype = U.xusertype
      LEFT OUTER JOIN SYS.extended_properties P  ON P.minor_id =  C.COLID  AND C.id = P.major_id 
      WHERE O.name =  @TN AND U.name <> 'timestamp'
      ORDER BY C.colid
    SELECT @RowCount=0
    DECLARE xArgs CURSOR FOR
      SELECT Colname FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @ColName
    WHILE   @@FETCH_STATUS=0
    BEGIN
      SELECT @RowCount = @RowCount+1
      FETCH  xArgs INTO @ColName
    END
    CLOSE      xArgs
    DEALLOCATE xArgs   
    SELECT @SpName = @SpPrefix + RTRIM(@TN) + @Separator2 + 'Select'
    SELECT @HelperName = RTRIM(@TN) + 'Select'
    SELECT @ColLength= MAX(DATALENGTH(CONVERT(VARCHAR(64),Colname))) 
      FROM @DocTable
      WHERE Cstatus<>128 
    SELECT @SpUpper = UPPER(@SpName)
    IF EXISTS(select * from sysobjects 
    where (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))
    SELECT @PrintLine='ALTER  PROCEDURE dbo.' + @SpName
    ELSE
      SELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpName	
    PRINT  @PrintLine
    PRINT  '('
    SELECT @HasIdent=COUNT(*) FROM @DocTable
     WHERE Cstatus=128
    SELECT  @HAS_LAST = 0
    SELECT  @RowIndex = 0
    IF      @ColLength < 16 SELECT @ColLength = 16
    DECLARE xArgs CURSOR FOR 
    SELECT '@' + Colname, Coltype, length, xprec, xscale
      FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
    WHILE   @@FETCH_STATUS = 0
    BEGIN ----
      SELECT @RowIndex=@RowIndex + 1
      SELECT @ArgName = SUBSTRING(@COL_NAME 
           + '                                    ',1,@ColLength + 2)
      SELECT @Type    = UPPER(@Type)
      SELECT @PrintLine = '  ' + @ArgName + @Type
      IF @Type='char'
        SELECT @PrintLine=@PrintLine 
             + ' (' + RTRIM(convert(varchar,@Length))  + ')'
      IF @Type = 'VARCHAR' BEGIN
        IF @Length = -1
          SELECT @PrintLine = @PrintLine + ' (MAX)'
        ELSE
          SELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length))  + ')'
        END
      IF @Type = 'NCHAR'
        SELECT @PrintLine=@PrintLine + ' (' 
             + RTRIM(convert(varchar,@Length/2))  + ')'
      IF @Type = 'NVARCHAR' BEGIN
        IF @Length = -1
          SELECT @PrintLine = @PrintLine + ' (MAX)'
        ELSE
          SELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length / 2))  + ')'
        END
      IF (@Type='decimal') OR (@Type='numeric')
        SELECT @PrintLine = @PrintLine 
             + ' (' + RTRIM(convert(varchar,@xprec)) 
               + ','  + RTRIM(convert(varchar,@xscale)) + ')'   
      IF RTRIM(LTRIM(@ArgName)) <> '@Id'
        SELECT @PrintLine = @PrintLine + ' OUTPUT'  
      IF @RowIndex < @RowCount 
        SELECT @PrintLine = @PrintLine + ','
      PRINT @PrintLine
      FETCH xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE xArgs
    DEALLOCATE xArgs
    PRINT  ') AS'
    PRINT '--  Purpose:'
    SELECT @PrintLine='--    Select record on ' + @TN + ' table'
    PRINT  @PrintLine
    PRINT  '--  Parameters:'      
    DECLARE xArgs CURSOR FOR 
    SELECT Colname FROM @DocTable
    WHERE Cstatus = 128 
      ORDER BY ColId     
    OPEN  xArgs
    FETCH xArgs INTO @ArgName 
    WHILE @@FETCH_status=0
    BEGIN
      SELECT @ArgName=SUBSTRING(@ArgName 
           + '                            ',1,@ColLength + 2)
      SELECT @PrintLine='--    ' + @ArgName + '-'
      PRINT @PrintLine
      FETCH xArgs INTO @ArgName 
    END
    CLOSE  xArgs
    DEALLOCATE xArgs          
    PRINT  '--  History:   '
    SELECT @USER = system_user
    IF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGIN
      SELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))
    END  
    SELECT @DateWork=CONVERT(CHAR(11),CURRENT_TIMESTAMP,106)
    SELECT @DateWork=substring(@DateWork,1,2) 
         + SUBSTRING(@DateWork,4,3) + SUBSTRING(@DateWork,8,4)
    SELECT @PrintLine='--  ' + @DateWork + ' '      
    SELECT @PrintLine=@PrintLine + @USER + ' Original coding'
    PRINT  @PrintLine
    PRINT  'SET NOCOUNT ON'      
    SELECT @PrintLine='SELECT ' 
    PRINT  @PrintLine     
    SELECT @RowCount=COUNT(*) 
      FROM @DocTable 
      WHERE Colname NOT IN ('Id')
    DECLARE xArgs CURSOR FOR 
     SELECT  Colname FROM @DocTable
      WHERE Colname NOT IN ('Id')        
      ORDER BY ColId
    SELECT @RowIndex=0
    OPEN   xArgs
    FETCH  xArgs INTO @ArgName
    WHILE  @@FETCH_STATUS=0
    BEGIN
      SELECT @RowIndex=@RowIndex + 1
      SELECT @ArgName=SUBSTRING(@ArgName + '           ',1,@ColLength+2)
      SELECT @PrintLine = '  @' + @ArgName + ' = ' + @TN 
           + '.' + RTRIM(@ArgName)
      IF @RowIndex <> @RowCount 
      SELECT @PrintLine=@PrintLine + ','
      PRINT @PrintLine
      FETCH xArgs INTO @ArgName
    END
    CLOSE      xArgs
    DEALLOCATE xArgs    
    SELECT @RowCount = COUNT(*) FROM @DocTable
    -- Where clause missing
    IF @RowCount = 0 BEGIN
      PRINT  '-- You MUST amend the following line to correctly identify the row to update'
        ---------
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE SOMECOLUMN = @SOMEVALUE'
        PRINT  @PrintLine        
        END
      ELSE BEGIN
        SELECT @PrintLine='  FROM ' + @TN + ' WHERE '
        PRINT  @PrintLine      
        SELECT @RowCount = COUNT(*) FROM @DocTable
         WHERE Cstatus = 128
        IF @RowCount = 0 BEGIN
          PRINT '-- THERE IS NO Identity ROW - assuming first row'
          DECLARE xArgs CURSOR FOR 
          SELECT  Colname + '=@' + Colname 
          FROM @DocTable
          ORDER BY ColId
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          PRINT '  ' + @TN + '.' + @SetValue
          CLOSE      xArgs
          DEALLOCATE xArgs
          END
        ELSE BEGIN
          DECLARE xArgs CURSOR FOR 
          SELECT  Colname + '=@' + Colname
          FROM @DocTable
          WHERE Cstatus = 128
          ORDER BY ColId
          SELECT @RowIndex=0
          OPEN  xArgs
          FETCH xArgs INTO @SetValue
          WHILE @@FETCH_STATUS=0
          BEGIN
            SELECT @RowIndex=@RowIndex + 1
            SELECT @PrintLine='  ' + @TN + '.' + @SetValue
            IF @RowIndex <> @RowCount   
              SELECT @PrintLine=@PrintLine + ' AND'
            PRINT @PrintLine
            FETCH xArgs INTO @SetValue
          END
          CLOSE      xArgs
          DEALLOCATE xArgs
        END
      END 
    PRINT  'RETURN'
    PRINT  '-------------- this is the end ----------------' 
    PRINT  '/* Data Access code follows:'
    IF @LANG = 'VB' BEGIN
      PRINT  '#Region " Using "'
      PRINT  'Imports System'
      PRINT  'Imports System.Data'
      PRINT  'Imports System.Data.SqlClient'
      PRINT  '#End Region'
      END
    ELSE BEGIN
      PRINT  '#region " Using "'
      PRINT  'using System;'
      PRINT  'using System.Data;'
      PRINT  'using System.Data.SqlClient;'
      PRINT  '#endregion'
      END
    IF @LANG = 'VB' BEGIN
      PRINT  '    #Region " SelectRecord  "'
      PRINT  '    '''''' <summary>'
      PRINT  '    '''''' Select record on ' + @TN
      PRINT  '    '''''' </summary>'
      PRINT  '    '''''' <param name="id">Id of record to fetch</param>'
      PRINT  '    '''''' <returns>' + @HelperName 
             + ' containing data for selected row</returns>'
      END
    ELSE BEGIN
     PRINT  '    #region " Select  "'
      PRINT  '    /// <summary>'
      PRINT  '    /// Select record on ' + @TN
      PRINT  '    /// </summary>'
      PRINT  '    /// <param name="id">Id of record to fetch</param>'
      PRINT  '    /// <returns>' + @HelperName 
             + ' containing data for selected row</returns>'
      END
    SELECT  @RowIndex = 0
    SELECT @RowCount=COUNT(*) FROM @DocTable
    IF @LANG = 'VB' BEGIN
      IF @USETNAME = 1
        PRINT  '    Public Function SelectRecord' + @TN + '(ByRef id AS Integer) AS ' + @HelperName
      ELSE
        PRINT  '    Public Function SelectRecord(ByRef id AS Integer) AS ' + @HelperName
      PRINT  '      Dim ' + @ObPrefix + @HelperName + ' As ' + @HelperName + ' = new ' + @HelperName + '()  '
      PRINT  '      Dim connect As String = CommonData.GetConnection()'
      PRINT  '      Dim sqlConnection As SqlConnection = new SqlConnection(connect)'
      PRINT  '      Dim sqlCommand As SqlCommand = new SqlCommand("' + @SpName + '", sqlConnection)'
      PRINT  '      Try'
      PRINT  '        sqlCommand.CommandType = CommandType.StoredProcedure'
      END
    ELSE BEGIN
      IF @USETNAME = 1
        PRINT  '    public static ' + @HelperName + ' Select' + @TN + '(int id) ' 
      ELSE
        PRINT  '    public static ' + @HelperName + ' Select(int id) ' 
      PRINT  '    {'
      IF @USEVAR = 0 BEGIN
        PRINT  '      ' + @HelperName + ' ' + @ObPrefix + '' + @HelperName + ' = new ' + @HelperName + '();  '
        PRINT  '      string connect = CommonData.ConnectionString;'
        PRINT  '      SqlConnection sqlConnection = new SqlConnection(connect);'
        PRINT  '      SqlCommand sqlCommand = new SqlCommand("' + @SpName + '", sqlConnection);'
        END
      ELSE BEGIN
        PRINT  '      var ' + dbo.ufn_CamelCase(@HelperName) + ' = new ' + @HelperName + '();  '
        PRINT  '      var connect = CommonData.ConnectionString;'
        PRINT  '      var sqlConnection = new SqlConnection(connect);'
        PRINT  '      var sqlCommand = new SqlCommand("' + @SpName + '", sqlConnection);'
        END
      PRINT  '      try'
      PRINT  '      {'
      PRINT  '        sqlCommand.CommandType = CommandType.StoredProcedure;'
      END
    SELECT @RowIndex = 0
    DECLARE xArgs CURSOR FOR 
    SELECT DISTINCT '@' + Colname, Coltype, length, ColId, xprec, xscale
      FROM @DocTable
      ORDER BY ColId 
    OPEN  xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    WHILE @@FETCH_STATUS=0
    BEGIN
      SELECT @SqlDbType = @Type  -- Default assignment
      IF @Type='bigint'         SELECT @SqlDbType = 'BigInt'
      IF @Type='bit'            SELECT @SqlDbType = 'Bit'
      IF @Type='int'            SELECT @SqlDbType = 'Int'
      IF @Type='decimal'        SELECT @SqlDbType = 'Decimal'
      IF @Type='numeric'        SELECT @SqlDbType = 'Decimal'
      IF @Type='datetime'       SELECT @SqlDbType = 'DateTime'
      IF @Type='char'           SELECT @SqlDbType = 'Char' 
      IF @Type='varchar'        SELECT @SqlDbType = 'VarChar'
      IF @Type='nchar'          SELECT @SqlDbType = 'NChar'
      IF @Type='nvarchar'       SELECT @SqlDbType = 'NVarChar' 
      IF @Type='ntext'          SELECT @SqlDbType = 'NText'
      IF @Type='text'           SELECT @SqlDbType = 'Text'
      IF @Type='real'           SELECT @SqlDbType = 'Real'
      IF @Type='money'          SELECT @SqlDbType = 'Money'
      IF @Type='float'          SELECT @SqlDbType = 'Float'
      IF @Type='smalldatetime'  SELECT @SqlDbType = 'SmallDateTime'
      IF @Type='smallint'  	    SELECT @SqlDbType = 'SmallInt'
      IF @Type='smallmoney'     SELECT @SqlDbType = 'SmallMoney'
      IF @Type='xml'            SELECT @SqlDbType = 'Xml'
      -- more type conversions here !
      SELECT @PrintLine='        sqlCommand.Parameters.Add("' 
          + @COL_NAME + '", SqlDbType.'
      SELECT @PrintLine=@PrintLine + @SqlDbType
      IF @Type='char'     
        SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length)) 
      IF @Type='varchar' 
      SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length)) 
      IF @Type='nchar'    
        SELECT @PrintLine=@PrintLine + ', ' 
             + RTRIM(convert(varchar,@Length/2)) 
      IF @Type='nvarchar' 
        SELECT @PrintLine=@PrintLine + ', ' 
        + RTRIM(convert(varchar,@Length/2)) 
      IF @LANG = 'VB' 
        SELECT @PrintLine=@PrintLine + ')' 
      ELSE
        SELECT @PrintLine=@PrintLine + ');'  
      PRINT @PrintLine   
      IF (@Type='decimal') OR (@Type='numeric') BEGIN
      IF @LANG = 'VB' BEGIN
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec) 
        ELSE  
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
        PRINT @PrintLine
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)
        ELSE    
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
        END
      ELSE BEGIN
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec)
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'
        PRINT @PrintLine
        IF @LANG = 'VB'
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)
        ELSE    
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'
        END   
        PRINT @PrintLine
      END     
      SELECT @RowIndex = @RowIndex + 1
      IF @LANG = 'VB' BEGIN
        IF @RowIndex = 1 
          SELECT @PrintLine='        sqlCommand.Parameters("' + @COL_NAME + '").Value = id'
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters("'  + @COL_NAME + '").Direction = ParameterDirection.Output'
        END
      ELSE BEGIN
        IF @RowIndex = 1 
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Value = id;'
        ELSE
          SELECT @PrintLine='        sqlCommand.Parameters["' + @COL_NAME + '"].Direction = ParameterDirection.Output;'
        END
      PRINT @PrintLine
      FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    END  
    CLOSE      xArgs
    DEALLOCATE xArgs
    IF @LANG = 'VB' BEGIN
      PRINT  '        sqlCommand.Connection.Open()'
      PRINT  '        sqlCommand.ExecuteNonQuery()'
      END
    ELSE BEGIN
      PRINT  '        sqlCommand.Connection.Open();'
      PRINT  '        sqlCommand.ExecuteNonQuery();'
      END 
    DECLARE xArgs CURSOR FOR 
    SELECT DISTINCT Colname, Coltype, length, ColId, xprec, xscale
    FROM @DocTable ORDER BY ColId
    OPEN  xArgs
    FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      IF @RowIndex > 0 BEGIN
        SELECT @SqlDbType = 'NullToString' 
        IF @Type='bit'            SELECT @SqlDbType = 'NullToBoolean'
        IF @Type='int'            SELECT @SqlDbType = 'NullToInteger'
        ---
        IF @Type='smallint'       SELECT @SqlDbType = 'NullToInteger16'
        IF @Type='bigint'         SELECT @SqlDbType = 'NullToInteger64'
        IF @Type='decimal'        SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='numeric'        SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='money'          SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='smallmoney'     SELECT @SqlDbType = 'NullToDecimal'
        IF @Type='datetime'       SELECT @SqlDbType = 'NullToDateString'
        IF @Type='smalldatetime'  SELECT @SqlDbType = 'NullToDateString'
        -----
        IF @Type='real'           SELECT @SqlDbType = 'NullToDouble' 
        IF @Type='char'           SELECT @SqlDbType = 'NullToString' 
        IF @Type='varchar'        SELECT @SqlDbType = 'NullToString'
        IF @Type='nchar'          SELECT @SqlDbType = 'NullToString'
        IF @Type='nvarchar'       SELECT @SqlDbType = 'NullToString' 
        IF @Type='text'           SELECT @SqlDbType = 'NullToString' 
        IF @Type='ntext'          SELECT @SqlDbType = 'NullToString'
        IF @Type='float'          SELECT @SqlDbType = 'NullToDouble'
        IF @LANG = 'VB' BEGIN
          SELECT @PrintLine = '        ' + @ObPrefix + '' + @HelperName  + '.' + @COL_NAME + ' = CommonData.'
          SELECT @PrintLine = @PrintLine + @SqlDbType + '('  + @ObPrefix + 'SqlCommand.Parameters("@'
          SELECT @PrintLine = @PrintLine + @COL_NAME + '").Value)'
          END
        ELSE BEGIN 
          SELECT @PrintLine = '        ' + dbo.ufn_CamelCase(@HelperName)  + '.' + @COL_NAME + ' = CommonData.'
          SELECT @PrintLine = @PrintLine + @SqlDbType + '(sqlCommand.Parameters["@'
          SELECT @PrintLine = @PrintLine + @COL_NAME + '"].Value);'
        END
        PRINT @PrintLine
        END
      FETCH   xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscale
    END
    CLOSE      xArgs
    DEALLOCATE xArgs
    IF @LANG = 'VB' BEGIN
      PRINT  '      Catch ex As Exception'
      PRINT  '        const Message As String = "Failure calling ' + @SpName + '"'
      PRINT  '        CommonData.WriteEventError(ex, Message)'
      PRINT  '        throw new DalGeneralException(Message, ex)'
      PRINT  '      Finally '
      PRINT  '        sqlCommand.Connection.Close()'
      PRINT  '        sqlCommand.Dispose()'
      PRINT  '        sqlConnection.Dispose()'
      PRINT  '      End Try'
      PRINT  '      Return ' + @ObPrefix + '' + @HelperName   
      PRINT  '    End Function'
      PRINT  '    #End Region'
      END
    ELSE BEGIN
      PRINT  '    }' 
      PRINT  '      catch (Exception ex)'
      PRINT  '      {'
      PRINT  '        const string Message = "Failure calling ' + @SpName + '";'
      PRINT  '        CommonData.WriteEventError(ex, Message);'  
      PRINT  '        throw new DalGeneralException(Message, ex);'
      PRINT  '      }'
      PRINT  '      finally '
      PRINT  '      {'
      PRINT  '        if (sqlCommand.Connection != null) ' 
      PRINT  '        {'
      PRINT  '          sqlCommand.Connection.Close();'
      PRINT  '        }'
      PRINT  '        sqlCommand.Dispose();'
      PRINT  '        sqlConnection.Dispose();'
      PRINT  '      }'
      PRINT  '      return ' + dbo.ufn_CamelCase(@HelperName) + ';'
      PRINT  '    }'
      PRINT  '    #endregion'
      END
    PRINT  '*/'
    PRINT  '/* Helper class code follows:'
    IF @LANG = 'VB' BEGIN
      --PRINT  '  #region " Helper class:' + @HelperName + '"'
      PRINT  '  '''''' <summary>'
      PRINT  '  ''''''    Helper class for ' + @HelperName
      PRINT  '  ''''''    ' + @DateWork + 'Autogenerated'
      PRINT  '  '''''' </summary>'
      PRINT  '  Public Class ' + @HelperName
      END
    ELSE BEGIN
      --PRINT  '  #region " Helper class:"' + @HelperName
      PRINT  '  /// <summary>'
      PRINT  '  ///    Helper class for ' + @HelperName
      PRINT  '  ///    ' + @DateWork + 'Autogenerated'
      PRINT  '  /// </summary>'
      PRINT  '  public class ' + @HelperName
      PRINT  '  {'
      END
    SELECT  @COMMENT = 'Not Documented Yet'
    SELECT  @RowIndex = 0
    DECLARE xArgs CURSOR FOR 
    SELECT Comment, Colname, Coltype, length, xprec, xscale
      FROM @DocTable
      ORDER BY ColId
    OPEN    xArgs
    FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, 
            @xprec, @xscale
    WHILE @@FETCH_STATUS=0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      IF @RowIndex > 0 BEGIN -- Generate id data member
        SELECT @SqlDbType = @Type  -- Default assignment
        -----------
        IF @Type='bigint'         SELECT @SqlDbType = 'Int64'
        IF @Type='smallint'       SELECT @SqlDbType = 'Int16'
        IF @Type='bit'            SELECT @SqlDbType = 'bool'
        IF @Type='int'            SELECT @SqlDbType = 'int'
        IF @Type='decimal'        SELECT @SqlDbType = 'decimal'
        IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
        ---------
        IF @Type='datetime'       SELECT @SqlDbType = 'string'
        IF @Type='char'           SELECT @SqlDbType = 'string' 
        IF @Type='varchar'        SELECT @SqlDbType = 'string'
        IF @Type='nchar'          SELECT @SqlDbType = 'string'
        IF @Type='nvarchar'       SELECT @SqlDbType = 'string' 
        IF @Type='text'           SELECT @SqlDbType = 'string' 
        IF @Type='ntext'          SELECT @SqlDbType = 'string'
        IF @Type='smallmoney'     SELECT @SqlDbType = 'decimal'
        IF @Type='money'          SELECT @SqlDbType = 'decimal'
        IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
        IF @Type='real'           SELECT @SqlDbType = 'double' 
        IF @Type='float'          SELECT @SqlDbType = 'double' 
        SELECT @VarPrefix = 'x'  -- Default assignment
        IF @Type='real'           SELECT @VarPrefix = 'd' 
        IF @Type='float'          SELECT @VarPrefix = 'd' 
        IF @Type='smallint'       SELECT @VarPrefix = 'i'
        IF @Type='smalldatetime'  SELECT @VarPrefix = 's'
        IF @Type='bigint'         SELECT @VarPrefix = 'l'
        IF @Type='bit'            SELECT @VarPrefix = 'b'
        IF @Type='int'            SELECT @VarPrefix = 'i'
        IF @Type='decimal'        SELECT @VarPrefix = 'c'
        IF @Type='numeric'        SELECT @VarPrefix = 'c'
        IF @Type='money'          SELECT @VarPrefix = 'c'
        IF @Type='smallmoney'     SELECT @VarPrefix = 'c'
        -----
        IF @Type='datetime'       SELECT @VarPrefix = 's'
        IF @Type='char'           SELECT @VarPrefix = 's' 
        IF @Type='varchar'        SELECT @VarPrefix = 's'
        IF @Type='nchar'          SELECT @VarPrefix = 's'
        IF @Type='nvarchar'       SELECT @VarPrefix = 's' 
        IF @Type='text'           SELECT @VarPrefix = 's' 
        IF @Type='ntext'          SELECT @VarPrefix = 's'
        IF @LANG = 'VB' BEGIN
          IF @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'    
          IF @SqlDbType = 'string'  SELECT @SqlDbType = 'String'
          IF @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'  
          IF @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'   
          IF @SqlDbType = 'bool'    SELECT @SqlDbType = 'Boolean'       
          PRINT  '    Dim ' + @ModPrefix +  @VarPrefix  +  @COL_NAME + ' As ' + @SqlDbType     
          END 
        ELSE BEGIN
          IF @USEVAR = 0 BEGIN
            PRINT  '    ' + @SqlDbType + '    ' + @ModPrefix + @VarPrefix + @COL_NAME + ';'  
            END  
          END
      END 
      FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE   xArgs
    SELECT  @RowIndex = 0
    OPEN    xArgs
    FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, 
            @xprec, @xscale
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SELECT @RowIndex = @RowIndex + 1
      SELECT @PREFIX = 'x'  -- Default assignment
      IF @Type='bigint'     SELECT @PREFIX = 'l'
      IF @Type='bit'        SELECT @PREFIX = 'b'
      IF @Type='int'        SELECT @PREFIX = 'i'
      --------
      IF @Type='smallint'   SELECT @PREFIX = 'i'
      IF @Type='decimal'    SELECT @PREFIX = 'c'
      IF @Type='numeric'        SELECT @PREFIX = 'c'
      IF @Type='datetime'       SELECT @PREFIX = 's'
      IF @Type='smalldatetime'  SELECT @PREFIX = 's'
      IF @Type='char'           SELECT @PREFIX = 's' 
      IF @Type='varchar'    SELECT @PREFIX = 's'
      IF @Type='nchar'      SELECT @PREFIX = 's'
      IF @Type='nvarchar'   SELECT @PREFIX = 's' 
      IF @Type='text'       SELECT @PREFIX = 's'
      IF @Type='ntext'      SELECT @PREFIX = 's'
      IF @Type='smallmoney' SELECT @PREFIX = 'c'
      IF @Type='money'      SELECT @PREFIX = 'c'
      IF @Type='real'       SELECT @PREFIX = 'd'
      IF @Type='float'      SELECT @PREFIX = 'd'
      SELECT @SqlDbType = 'string'  -- Default assignment
      IF @Type='real'       SELECT @SqlDbType = 'double' 
      IF @Type='float'      SELECT @SqlDbType = 'double' 
      IF @Type='bigint'     SELECT @SqlDbType = 'Int64'
      IF @Type='smallint'   SELECT @SqlDbType = 'Int16'
      IF @Type='bit'        SELECT @SqlDbType = 'bool'
      IF @Type='int'        SELECT @SqlDbType = 'int'
      ----------
      IF @Type='decimal'    SELECT @SqlDbType = 'decimal'
      IF @Type='numeric'        SELECT @SqlDbType = 'decimal'
      IF @Type='datetime'       SELECT @SqlDbType = 'string'
      IF @Type='smalldatetime'  SELECT @SqlDbType = 'string'
      IF @Type='char'           SELECT @SqlDbType = 'string' 
      IF @Type='varchar'        SELECT @SqlDbType = 'string'
      IF @Type='nchar'      SELECT @SqlDbType = 'string'
      IF @Type='nvarchar'   SELECT @SqlDbType = 'string' 
      IF @Type='text'       SELECT @SqlDbType = 'string' 
      IF @Type='ntext'      SELECT @SqlDbType = 'string'
      IF @Type='smallmoney' SELECT @SqlDbType = 'decimal'
      IF @Type='money'      SELECT @SqlDbType = 'decimal'
      SELECT @PrintLine = ''    
      IF @LANG = 'VB' BEGIN
        IF @SqlDbType = 'int'     SELECT @SqlDbType = 'Integer'    
        IF @SqlDbType = 'string'  SELECT @SqlDbType = 'String'
        IF @SqlDbType = 'decimal' SELECT @SqlDbType = 'Decimal'  
        IF @SqlDbType = 'bit'     SELECT @SqlDbType = 'Boolean'        
        PRINT '  #Region "' + @COL_NAME + ' "' 
        PRINT '    '''''' <summary>'
        PRINT '    '''''' Gets or sets ' + @COMMENT
        PRINT '    '''''' </summary>'
        PRINT '    Public Property ' + @COL_NAME + ' AS ' + @SqlDbType + ' '
        -----------
        PRINT '      Get'
        PRINT '        Return ' + @ModPrefix + @PREFIX + @COL_NAME
        PRINT '      End Get'
        PRINT '      Set(ByVal Value As ' + @SqlDbType + ')'
        PRINT '        ' + @ModPrefix + + @PREFIX + @COL_NAME + ' = value'
        PRINT '      End Set'
        PRINT '    End Property' 
        PRINT '  #End Region '
        END
      ELSE BEGIN
        PRINT '    /// <summary>'
        IF @Type='bit' 
          PRINT '    /// Gets or sets a value indicating whether ' + @COMMENT
        ELSE
          PRINT '    /// Gets or sets ' + @COMMENT
        PRINT '    /// </summary>'
        IF @USEVAR = 0 BEGIN
          PRINT '    public ' + @SqlDbType + ' '  + @COL_NAME
          PRINT '    {'
          PRINT '      get { return ' + @ModPrefix + @PREFIX + @COL_NAME + ';}'
          PRINT '      set { ' + @ModPrefix + @PREFIX + @COL_NAME + ' = value; }'
          PRINT '    }' 
          PRINT ''
          END
        ELSE BEGIN   
          PRINT '    public ' + @SqlDbType + ' '  + @COL_NAME + ' { get; set; }'
          IF @RowIndex < @RowCount 
            PRINT ''
          END
        END  
      FETCH   xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscale
    END
    CLOSE     xArgs
    DEALLOCATE xArgs 
    IF @LANG = 'VB' BEGIN
      PRINT  '  End Class'
      END
    ELSE BEGIN
      PRINT  '  }'
      END
    PRINT  '*/'
    PRINT  'GO'
    PRINT  @SET_DB
    PRINT  'GO'
    RETURN
    -------------- this is the end ----------------



    Saturday, February 20, 2010 11:49 AM
  • User-319574463 posted

    Running this stored procedure against my ToDo table by

    EXEC csp_MakeSelect 'ToDo', 'VB'

    gives a Select stored procedure and the following:

      ''' <summary>
      '''    Helper class for ToDoSelect
      '''    20Feb2010Autogenerated
      ''' </summary>
      Public Class ToDoSelect
        Dim m_iId As Integer
        Dim m_sDateAdded As String
        Dim m_sDateDone As String
        Dim m_bItemDone As Boolean
        Dim m_sItemText As String
        Dim m_sItemTitle As String
      #Region "Id "
        ''' <summary>
        ''' Gets or sets Identity integer primary key
        ''' </summary>
        Public Property Id AS Integer 
          Get
            Return m_iId
          End Get
          Set(ByVal Value As Integer)
            m_iId = value
          End Set
        End Property
      #End Region 
      #Region "DateAdded "
        ''' <summary>
        ''' Gets or sets Date/time row added
        ''' </summary>
        Public Property DateAdded AS String 
          Get
            Return m_sDateAdded
          End Get
          Set(ByVal Value As String)
            m_sDateAdded = value
          End Set
        End Property
      #End Region 
      #Region "DateDone "
        ''' <summary>
        ''' Gets or sets Date item marked as done
        ''' </summary>
        Public Property DateDone AS String 
          Get
            Return m_sDateDone
          End Get
          Set(ByVal Value As String)
            m_sDateDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemDone "
        ''' <summary>
        ''' Gets or sets 0 = To do, 1 = Done
        ''' </summary>
        Public Property ItemDone AS bool 
          Get
            Return m_bItemDone
          End Get
          Set(ByVal Value As bool)
            m_bItemDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemText "
        ''' <summary>
        ''' Gets or sets Text to to do item
        ''' </summary>
        Public Property ItemText AS String 
          Get
            Return m_sItemText
          End Get
          Set(ByVal Value As String)
            m_sItemText = value
          End Set
        End Property
      #End Region 
      #Region "ItemTitle "
        ''' <summary>
        ''' Gets or sets Title of to do item
        ''' </summary>
        Public Property ItemTitle AS String 
          Get
            Return m_sItemTitle
          End Get
          Set(ByVal Value As String)
            m_sItemTitle = value
          End Set
        End Property
      #End Region 
      End Class

      ''' <summary>
      '''    Helper class for ToDoSelect
      '''    20Feb2010Autogenerated
      ''' </summary>
      Public Class ToDoSelect
        Dim m_iId As Integer
        Dim m_sDateAdded As String
        Dim m_sDateDone As String
        Dim m_bItemDone As Boolean
        Dim m_sItemText As String
        Dim m_sItemTitle As String
      #Region "Id "
        ''' <summary>
        ''' Gets or sets Identity integer primary key
        ''' </summary>
        Public Property Id AS Integer 
          Get
            Return m_iId
          End Get
          Set(ByVal Value As Integer)
            m_iId = value
          End Set
        End Property
      #End Region 
      #Region "DateAdded "
        ''' <summary>
        ''' Gets or sets Date/time row added
        ''' </summary>
        Public Property DateAdded AS String 
          Get
            Return m_sDateAdded
          End Get
          Set(ByVal Value As String)
            m_sDateAdded = value
          End Set
        End Property
      #End Region 
      #Region "DateDone "
        ''' <summary>
        ''' Gets or sets Date item marked as done
        ''' </summary>
        Public Property DateDone AS String 
          Get
            Return m_sDateDone
          End Get
          Set(ByVal Value As String)
            m_sDateDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemDone "
        ''' <summary>
        ''' Gets or sets 0 = To do, 1 = Done
        ''' </summary>
        Public Property ItemDone AS bool 
          Get
            Return m_bItemDone
          End Get
          Set(ByVal Value As bool)
            m_bItemDone = value
          End Set
        End Property
      #End Region 
      #Region "ItemText "
        ''' <summary>
        ''' Gets or sets Text to to do item
        ''' </summary>
        Public Property ItemText AS String 
          Get
            Return m_sItemText
          End Get
          Set(ByVal Value As String)
            m_sItemText = value
          End Set
        End Property
      #End Region 
      #Region "ItemTitle "
        ''' <summary>
        ''' Gets or sets Title of to do item
        ''' </summary>
        Public Property ItemTitle AS String 
          Get
            Return m_sItemTitle
          End Get
          Set(ByVal Value As String)
            m_sItemTitle = value
          End Set
        End Property
      #End Region 
      End Class



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 20, 2010 11:55 AM