Answered by:
vb.net classes - create from SQL?

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 ToDoSelectDim m_iId As IntegerDim m_sDateAdded As StringDim m_sDateDone As StringDim m_bItemDone As BooleanDim m_sItemText As StringDim m_sItemTitle As String#Region "Id "''' <summary>''' Gets or sets Identity integer primary key''' </summary>Public Property Id AS IntegerGetReturn m_iIdEnd GetSet(ByVal Value As Integer)m_iId = valueEnd SetEnd Property#End Region#Region "DateAdded "''' <summary>''' Gets or sets Date/time row added''' </summary>Public Property DateAdded AS StringGetReturn m_sDateAddedEnd GetSet(ByVal Value As String)m_sDateAdded = valueEnd SetEnd Property#End Region#Region "DateDone "''' <summary>''' Gets or sets Date item marked as done''' </summary>Public Property DateDone AS StringGetReturn m_sDateDoneEnd GetSet(ByVal Value As String)m_sDateDone = valueEnd SetEnd Property#End Region#Region "ItemDone "''' <summary>''' Gets or sets 0 = To do, 1 = Done''' </summary>Public Property ItemDone AS boolGetReturn m_bItemDoneEnd GetSet(ByVal Value As bool)m_bItemDone = valueEnd SetEnd Property#End Region#Region "ItemText "''' <summary>''' Gets or sets Text to to do item''' </summary>Public Property ItemText AS StringGetReturn m_sItemTextEnd GetSet(ByVal Value As String)m_sItemText = valueEnd SetEnd Property#End Region#Region "ItemTitle "''' <summary>''' Gets or sets Title of to do item''' </summary>Public Property ItemTitle AS StringGetReturn m_sItemTitleEnd GetSet(ByVal Value As String)m_sItemTitle = valueEnd SetEnd Property#End RegionEnd 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
-
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 -
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 codingSET NOCOUNT ONDECLARE @colid INTDECLARE @ColName VARCHAR(80)DECLARE @ColLength INTDECLARE @COUNT_ROW INTDECLARE @HasIdent INTDECLARE @RowCount INTDECLARE @RowIndex INTDECLARE @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 INTDECLARE @xscale INTDECLARE @DOM_NAME VARCHAR(50)DECLARE @SpPrefix VARCHAR(10)DECLARE @SET_DB VARCHAR(50)DECLARE @ObPrefix VARCHAR(10)DECLARE @VarPrefix VARCHAR(10)DECLARE @ALLOWED BITDECLARE @DefLanguage CHAR(2)DECLARE @Separator2 VARCHAR(1)DECLARE @ArgName VARCHAR(80)DECLARE @COL_NAME VARCHAR(80)DECLARE @HAS_LAST BITDECLARE @Type VARCHAR(32)DECLARE @Length INTDECLARE @SqlDbType VARCHAR(20)DECLARE @COMMENT VARCHAR(132)DECLARE @PREFIX VARCHAR(1)DECLARE @ModPrefix VARCHAR(10)DECLARE @USEVAR BITDECLARE @USETNAME BITSELECT @LANG = RTRIM(UPPER(LTRIM(@LANG)))SELECT @ALLOWED = 0IF @LANG = 'CS' SELECT @LANG = 'C#'IF @LANG = 'VB' SELECT @ALLOWED = 1IF @LANG = 'C#' SELECT @ALLOWED = 1-- Configuration startSELECT @SpPrefix = 'usp_' -- Set to desired prefix for stored proceduresSELECT @DOM_NAME = 'DALETH' + '\' -- Set to your domain name-- Set @SET_DB to name of SP to set permissions or to blankSELECT @SET_DB = 'asp_dbPermissions'SELECT @ObPrefix = 'x' -- Prefix for object variablesSELECT @DefLanguage = 'C#' -- Default language C# or VBSELECT @Separator2 = '' -- Separator between Tablename and actionSELECT @ModPrefix = 'm_' -- Module level prefixSELECT @USEVAR = 1 -- set to 1 for VS2008 C#, otherwise 0SELECT @USETNAME = 0 -- 1=Use Table name in wrapper name-- Configuration endIF @ALLOWED = 0 SET @LANG = @DefLanguageIF @TABLENAME ='xyzzy'OR NOT EXISTS (SELECT * FROM sysobjectsWHERE UPPER(name)=UPPER(@TABLENAME) and type='U')BEGINPRINT '--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!'RETURNENDSELECT @TN = name FROM sysobjectsWHERE 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.xscaleFROM syscolumns CJOIN sys.tables O ON C.id = O.object_idJOIN systypes U ON C.xusertype = U.xusertypeLEFT OUTER JOIN SYS.extended_properties P ON P.minor_id = C.COLID AND C.id = P.major_idWHERE O.name = @TN AND U.name <> 'timestamp'ORDER BY C.colidSELECT @RowCount=0DECLARE xArgs CURSOR FORSELECT Colname FROM @DocTableORDER BY ColIdOPEN xArgsFETCH xArgs INTO @ColNameWHILE @@FETCH_STATUS=0BEGINSELECT @RowCount = @RowCount+1FETCH xArgs INTO @ColNameENDCLOSE xArgsDEALLOCATE xArgsSELECT @SpName = @SpPrefix + RTRIM(@TN) + @Separator2 + 'Select'SELECT @HelperName = RTRIM(@TN) + 'Select'SELECT @ColLength= MAX(DATALENGTH(CONVERT(VARCHAR(64),Colname)))FROM @DocTableWHERE Cstatus<>128SELECT @SpUpper = UPPER(@SpName)IF EXISTS(select * from sysobjectswhere (UPPER(name) = @SpUpper) AND (sysstat & 0xf = 4))SELECT @PrintLine='ALTER PROCEDURE dbo.' + @SpNameELSESELECT @PrintLine='CREATE PROCEDURE dbo.' + @SpNamePRINT @PrintLinePRINT '('SELECT @HasIdent=COUNT(*) FROM @DocTableWHERE Cstatus=128SELECT @HAS_LAST = 0SELECT @RowIndex = 0IF @ColLength < 16 SELECT @ColLength = 16DECLARE xArgs CURSOR FORSELECT '@' + Colname, Coltype, length, xprec, xscaleFROM @DocTableORDER BY ColIdOPEN xArgsFETCH xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscaleWHILE @@FETCH_STATUS = 0BEGIN ----SELECT @RowIndex=@RowIndex + 1SELECT @ArgName = SUBSTRING(@COL_NAME+ ' ',1,@ColLength + 2)SELECT @Type = UPPER(@Type)SELECT @PrintLine = ' ' + @ArgName + @TypeIF @Type='char'SELECT @PrintLine=@PrintLine+ ' (' + RTRIM(convert(varchar,@Length)) + ')'IF @Type = 'VARCHAR' BEGINIF @Length = -1SELECT @PrintLine = @PrintLine + ' (MAX)'ELSESELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length)) + ')'ENDIF @Type = 'NCHAR'SELECT @PrintLine=@PrintLine + ' ('+ RTRIM(convert(varchar,@Length/2)) + ')'IF @Type = 'NVARCHAR' BEGINIF @Length = -1SELECT @PrintLine = @PrintLine + ' (MAX)'ELSESELECT @PrintLine = @PrintLine + ' (' + RTRIM(convert(varchar, @Length / 2)) + ')'ENDIF (@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 < @RowCountSELECT @PrintLine = @PrintLine + ','PRINT @PrintLineFETCH xArgs INTO @COL_NAME, @Type, @Length, @xprec, @xscaleENDCLOSE xArgsDEALLOCATE xArgsPRINT ') AS'PRINT '-- Purpose:'SELECT @PrintLine='-- Select record on ' + @TN + ' table'PRINT @PrintLinePRINT '-- Parameters:'DECLARE xArgs CURSOR FORSELECT Colname FROM @DocTableWHERE Cstatus = 128ORDER BY ColIdOPEN xArgsFETCH xArgs INTO @ArgNameWHILE @@FETCH_status=0BEGINSELECT @ArgName=SUBSTRING(@ArgName+ ' ',1,@ColLength + 2)SELECT @PrintLine='-- ' + @ArgName + '-'PRINT @PrintLineFETCH xArgs INTO @ArgNameENDCLOSE xArgsDEALLOCATE xArgsPRINT '-- History: 'SELECT @USER = system_userIF SUBSTRING(@USER,1,DATALENGTH(@DOM_NAME)) = @DOM_NAME BEGINSELECT @USER = RTRIM(SUBSTRING(@USER,DATALENGTH(@DOM_NAME) + 1,20))ENDSELECT @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 @PrintLinePRINT 'SET NOCOUNT ON'SELECT @PrintLine='SELECT 'PRINT @PrintLineSELECT @RowCount=COUNT(*)FROM @DocTableWHERE Colname NOT IN ('Id')DECLARE xArgs CURSOR FORSELECT Colname FROM @DocTableWHERE Colname NOT IN ('Id')ORDER BY ColIdSELECT @RowIndex=0OPEN xArgsFETCH xArgs INTO @ArgNameWHILE @@FETCH_STATUS=0BEGINSELECT @RowIndex=@RowIndex + 1SELECT @ArgName=SUBSTRING(@ArgName + ' ',1,@ColLength+2)SELECT @PrintLine = ' @' + @ArgName + ' = ' + @TN+ '.' + RTRIM(@ArgName)IF @RowIndex <> @RowCountSELECT @PrintLine=@PrintLine + ','PRINT @PrintLineFETCH xArgs INTO @ArgNameENDCLOSE xArgsDEALLOCATE xArgsSELECT @RowCount = COUNT(*) FROM @DocTable-- Where clause missingIF @RowCount = 0 BEGINPRINT '-- You MUST amend the following line to correctly identify the row to update'---------SELECT @PrintLine=' FROM ' + @TN + ' WHERE SOMECOLUMN = @SOMEVALUE'PRINT @PrintLineENDELSE BEGINSELECT @PrintLine=' FROM ' + @TN + ' WHERE 'PRINT @PrintLineSELECT @RowCount = COUNT(*) FROM @DocTableWHERE Cstatus = 128IF @RowCount = 0 BEGINPRINT '-- THERE IS NO Identity ROW - assuming first row'DECLARE xArgs CURSOR FORSELECT Colname + '=@' + ColnameFROM @DocTableORDER BY ColIdOPEN xArgsFETCH xArgs INTO @SetValuePRINT ' ' + @TN + '.' + @SetValueCLOSE xArgsDEALLOCATE xArgsENDELSE BEGINDECLARE xArgs CURSOR FORSELECT Colname + '=@' + ColnameFROM @DocTableWHERE Cstatus = 128ORDER BY ColIdSELECT @RowIndex=0OPEN xArgsFETCH xArgs INTO @SetValueWHILE @@FETCH_STATUS=0BEGINSELECT @RowIndex=@RowIndex + 1SELECT @PrintLine=' ' + @TN + '.' + @SetValueIF @RowIndex <> @RowCountSELECT @PrintLine=@PrintLine + ' AND'PRINT @PrintLineFETCH xArgs INTO @SetValueENDCLOSE xArgsDEALLOCATE xArgsENDENDPRINT 'RETURN'PRINT '-------------- this is the end ----------------'PRINT '/* Data Access code follows:'IF @LANG = 'VB' BEGINPRINT '#Region " Using "'PRINT 'Imports System'PRINT 'Imports System.Data'PRINT 'Imports System.Data.SqlClient'PRINT '#End Region'ENDELSE BEGINPRINT '#region " Using "'PRINT 'using System;'PRINT 'using System.Data;'PRINT 'using System.Data.SqlClient;'PRINT '#endregion'ENDIF @LANG = 'VB' BEGINPRINT ' #Region " SelectRecord "'PRINT ' '''''' <summary>'PRINT ' '''''' Select record on ' + @TNPRINT ' '''''' </summary>'PRINT ' '''''' <param name="id">Id of record to fetch</param>'PRINT ' '''''' <returns>' + @HelperName+ ' containing data for selected row</returns>'ENDELSE BEGINPRINT ' #region " Select "'PRINT ' /// <summary>'PRINT ' /// Select record on ' + @TNPRINT ' /// </summary>'PRINT ' /// <param name="id">Id of record to fetch</param>'PRINT ' /// <returns>' + @HelperName+ ' containing data for selected row</returns>'ENDSELECT @RowIndex = 0SELECT @RowCount=COUNT(*) FROM @DocTableIF @LANG = 'VB' BEGINIF @USETNAME = 1PRINT ' Public Function SelectRecord' + @TN + '(ByRef id AS Integer) AS ' + @HelperNameELSEPRINT ' Public Function SelectRecord(ByRef id AS Integer) AS ' + @HelperNamePRINT ' 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'ENDELSE BEGINIF @USETNAME = 1PRINT ' public static ' + @HelperName + ' Select' + @TN + '(int id) 'ELSEPRINT ' public static ' + @HelperName + ' Select(int id) 'PRINT ' {'IF @USEVAR = 0 BEGINPRINT ' ' + @HelperName + ' ' + @ObPrefix + '' + @HelperName + ' = new ' + @HelperName + '(); 'PRINT ' string connect = CommonData.ConnectionString;'PRINT ' SqlConnection sqlConnection = new SqlConnection(connect);'PRINT ' SqlCommand sqlCommand = new SqlCommand("' + @SpName + '", sqlConnection);'ENDELSE BEGINPRINT ' 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);'ENDPRINT ' try'PRINT ' {'PRINT ' sqlCommand.CommandType = CommandType.StoredProcedure;'ENDSELECT @RowIndex = 0DECLARE xArgs CURSOR FORSELECT DISTINCT '@' + Colname, Coltype, length, ColId, xprec, xscaleFROM @DocTableORDER BY ColIdOPEN xArgsFETCH xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscaleWHILE @@FETCH_STATUS=0BEGINSELECT @SqlDbType = @Type -- Default assignmentIF @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 + @SqlDbTypeIF @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 + ')'ELSESELECT @PrintLine=@PrintLine + ');'PRINT @PrintLineIF (@Type='decimal') OR (@Type='numeric') BEGINIF @LANG = 'VB' BEGINIF @LANG = 'VB'SELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec)ELSESELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'PRINT @PrintLineIF @LANG = 'VB'SELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)ELSESELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'ENDELSE BEGINIF @LANG = 'VB'SELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Precision = ' + CONVERT(VARCHAR(3), @xprec)ELSESELECT @PrintLine=' sqlCommand.Parameters["' + @COL_NAME + '"].Precision = ' + CONVERT(VARCHAR(3), @xprec) + ';'PRINT @PrintLineIF @LANG = 'VB'SELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Scale = ' + CONVERT(VARCHAR(3), @xscale)ELSESELECT @PrintLine=' sqlCommand.Parameters["' + @COL_NAME + '"].Scale = ' + CONVERT(VARCHAR(3), @xscale) + ';'ENDPRINT @PrintLineENDSELECT @RowIndex = @RowIndex + 1IF @LANG = 'VB' BEGINIF @RowIndex = 1SELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Value = id'ELSESELECT @PrintLine=' sqlCommand.Parameters("' + @COL_NAME + '").Direction = ParameterDirection.Output'ENDELSE BEGINIF @RowIndex = 1SELECT @PrintLine=' sqlCommand.Parameters["' + @COL_NAME + '"].Value = id;'ELSESELECT @PrintLine=' sqlCommand.Parameters["' + @COL_NAME + '"].Direction = ParameterDirection.Output;'ENDPRINT @PrintLineFETCH xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscaleENDCLOSE xArgsDEALLOCATE xArgsIF @LANG = 'VB' BEGINPRINT ' sqlCommand.Connection.Open()'PRINT ' sqlCommand.ExecuteNonQuery()'ENDELSE BEGINPRINT ' sqlCommand.Connection.Open();'PRINT ' sqlCommand.ExecuteNonQuery();'ENDDECLARE xArgs CURSOR FORSELECT DISTINCT Colname, Coltype, length, ColId, xprec, xscaleFROM @DocTable ORDER BY ColIdOPEN xArgsFETCH xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscaleWHILE @@FETCH_STATUS = 0BEGINSELECT @RowIndex = @RowIndex + 1IF @RowIndex > 0 BEGINSELECT @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' BEGINSELECT @PrintLine = ' ' + @ObPrefix + '' + @HelperName + '.' + @COL_NAME + ' = CommonData.'SELECT @PrintLine = @PrintLine + @SqlDbType + '(' + @ObPrefix + 'SqlCommand.Parameters("@'SELECT @PrintLine = @PrintLine + @COL_NAME + '").Value)'ENDELSE BEGINSELECT @PrintLine = ' ' + dbo.ufn_CamelCase(@HelperName) + '.' + @COL_NAME + ' = CommonData.'SELECT @PrintLine = @PrintLine + @SqlDbType + '(sqlCommand.Parameters["@'SELECT @PrintLine = @PrintLine + @COL_NAME + '"].Value);'ENDPRINT @PrintLineENDFETCH xArgs INTO @COL_NAME, @Type, @Length, @colid, @xprec, @xscaleENDCLOSE xArgsDEALLOCATE xArgsIF @LANG = 'VB' BEGINPRINT ' 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 + '' + @HelperNamePRINT ' End Function'PRINT ' #End Region'ENDELSE BEGINPRINT ' }'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'ENDPRINT '*/'PRINT '/* Helper class code follows:'IF @LANG = 'VB' BEGIN--PRINT ' #region " Helper class:' + @HelperName + '"'PRINT ' '''''' <summary>'PRINT ' '''''' Helper class for ' + @HelperNamePRINT ' '''''' ' + @DateWork + 'Autogenerated'PRINT ' '''''' </summary>'PRINT ' Public Class ' + @HelperNameENDELSE BEGIN--PRINT ' #region " Helper class:"' + @HelperNamePRINT ' /// <summary>'PRINT ' /// Helper class for ' + @HelperNamePRINT ' /// ' + @DateWork + 'Autogenerated'PRINT ' /// </summary>'PRINT ' public class ' + @HelperNamePRINT ' {'ENDSELECT @COMMENT = 'Not Documented Yet'SELECT @RowIndex = 0DECLARE xArgs CURSOR FORSELECT Comment, Colname, Coltype, length, xprec, xscaleFROM @DocTableORDER BY ColIdOPEN xArgsFETCH xArgs INTO @COMMENT, @COL_NAME, @Type, @Length,@xprec, @xscaleWHILE @@FETCH_STATUS=0BEGINSELECT @RowIndex = @RowIndex + 1IF @RowIndex > 0 BEGIN -- Generate id data memberSELECT @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 assignmentIF @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' BEGINIF @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 ' + @SqlDbTypeENDELSE BEGINIF @USEVAR = 0 BEGINPRINT ' ' + @SqlDbType + ' ' + @ModPrefix + @VarPrefix + @COL_NAME + ';'ENDENDENDFETCH xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscaleENDCLOSE xArgsSELECT @RowIndex = 0OPEN xArgsFETCH xArgs INTO @COMMENT, @COL_NAME, @Type, @Length,@xprec, @xscaleWHILE @@FETCH_STATUS = 0BEGINSELECT @RowIndex = @RowIndex + 1SELECT @PREFIX = 'x' -- Default assignmentIF @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 assignmentIF @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' BEGINIF @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 ' + @COMMENTPRINT ' '''''' </summary>'PRINT ' Public Property ' + @COL_NAME + ' AS ' + @SqlDbType + ' '-----------PRINT ' Get'PRINT ' Return ' + @ModPrefix + @PREFIX + @COL_NAMEPRINT ' End Get'PRINT ' Set(ByVal Value As ' + @SqlDbType + ')'PRINT ' ' + @ModPrefix + + @PREFIX + @COL_NAME + ' = value'PRINT ' End Set'PRINT ' End Property'PRINT ' #End Region 'ENDELSE BEGINPRINT ' /// <summary>'IF @Type='bit'PRINT ' /// Gets or sets a value indicating whether ' + @COMMENTELSEPRINT ' /// Gets or sets ' + @COMMENTPRINT ' /// </summary>'IF @USEVAR = 0 BEGINPRINT ' public ' + @SqlDbType + ' ' + @COL_NAMEPRINT ' {'PRINT ' get { return ' + @ModPrefix + @PREFIX + @COL_NAME + ';}'PRINT ' set { ' + @ModPrefix + @PREFIX + @COL_NAME + ' = value; }'PRINT ' }'PRINT ''ENDELSE BEGINPRINT ' public ' + @SqlDbType + ' ' + @COL_NAME + ' { get; set; }'IF @RowIndex < @RowCountPRINT ''ENDENDFETCH xArgs INTO @COMMENT, @COL_NAME, @Type, @Length, @xprec, @xscaleENDCLOSE xArgsDEALLOCATE xArgsIF @LANG = 'VB' BEGINPRINT ' End Class'ENDELSE BEGINPRINT ' }'ENDPRINT '*/'PRINT 'GO'PRINT @SET_DBPRINT '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 ToDoSelectDim m_iId As IntegerDim m_sDateAdded As StringDim m_sDateDone As StringDim m_bItemDone As BooleanDim m_sItemText As StringDim m_sItemTitle As String#Region "Id "''' <summary>''' Gets or sets Identity integer primary key''' </summary>Public Property Id AS IntegerGetReturn m_iIdEnd GetSet(ByVal Value As Integer)m_iId = valueEnd SetEnd Property#End Region#Region "DateAdded "''' <summary>''' Gets or sets Date/time row added''' </summary>Public Property DateAdded AS StringGetReturn m_sDateAddedEnd GetSet(ByVal Value As String)m_sDateAdded = valueEnd SetEnd Property#End Region#Region "DateDone "''' <summary>''' Gets or sets Date item marked as done''' </summary>Public Property DateDone AS StringGetReturn m_sDateDoneEnd GetSet(ByVal Value As String)m_sDateDone = valueEnd SetEnd Property#End Region#Region "ItemDone "''' <summary>''' Gets or sets 0 = To do, 1 = Done''' </summary>Public Property ItemDone AS boolGetReturn m_bItemDoneEnd GetSet(ByVal Value As bool)m_bItemDone = valueEnd SetEnd Property#End Region#Region "ItemText "''' <summary>''' Gets or sets Text to to do item''' </summary>Public Property ItemText AS StringGetReturn m_sItemTextEnd GetSet(ByVal Value As String)m_sItemText = valueEnd SetEnd Property#End Region#Region "ItemTitle "''' <summary>''' Gets or sets Title of to do item''' </summary>Public Property ItemTitle AS StringGetReturn m_sItemTitleEnd GetSet(ByVal Value As String)m_sItemTitle = valueEnd SetEnd Property#End RegionEnd 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