Remove From My Forums

vb.net classes - create from SQL?

Question
0

Sign in to vote

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

0

Sign in to vote

User437720957 posted

You should check out:

http://www.codesmithtools.com/

http://www.mygenerationsoftware.com/portal/default.aspx

http://visualstudiomagazine.com/Articles/2009/05/01/Visual-Studios-T4-Code-Generation.aspx

Wednesday, February 17, 2010 8:58 PM
0

Sign in to vote

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
0

Sign in to vote

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

-- 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

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

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

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

Answered by:

vb.net classes - create from SQL?

Question

Answers

All replies