This SP will be create C# classes using all table

  • Satyanarayana B

    SSC Enthusiast

    Points: 152

    Comments posted to this topic are about the item This SP will be create C# classes using all table

  • William Soranno

    SSCommitted

    Points: 1578

    The procedure works great, once you make some minor changes.

    Please see the corrected code below:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    Purpose : This SP will be create C# classes using all table in the specified Database

    Author : Satyanarayana Bommidi

    Created Date : 16th July 2004

    Example : EXEC uspSQLObjectsToClass 'Northwind'

    Output folder : c:\SQLClasses

    Please see additional comments below. William Soranno 11/13/2007

    */

    ALTER PROCEDURE [dbo].[uspSQLObjectsToClass] (@DBName VARCHAR(100))

    AS

    BEGIN

    SET NOCOUNT ON

    --variable declaration

    DECLARE @Counter INT, --loop counter

    @Count INT, --loop counter

    @ObjectName VARCHAR(100), --to hold Object Name like (Tables, Views, SPs and Triggers)

    @ErrorNo INT, --to hold the error number

    @ErrorStr VARCHAR(255), --to hold the error description

    @TEXTOUTPUT VARCHAR(1000), --to hold the html tags

    @FileName VARCHAR(255), --to hold the output file name

    @FS INT, --to hold the output parameter of file system

    @OLEResult INT, --to hold the result parameter of file system

    @FileID INT --to hold the file system object id

    --initialization

    SELECT @Counter = 0, @Count = 0, @ObjectName = '', @ErrorNo = 0, @ErrorStr = '',

    @TEXTOUTPUT = '', @FileName = '', @FS = 0, @OLEResult = 0 , @FileID = 0

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#ObjectInfo%')

    DROP TABLE #ObjectInfo

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#ObjectColumns%')

    DROP TABLE #ObjectColumns

    CREATE TABLE #ObjectInfo(TableID INT, TableName VARCHAR(100), isProcessed BIT)

    CREATE TABLE #ObjectColumns(TableID INT, ColumnID INT, ColumnName VARCHAR(100), DataTypeName VARCHAR(50))

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to create the temporary tables

    please check the user permission, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --getting the all table

    EXEC ('INSERT INTO #ObjectInfo(TableID, TableName, isProcessed)

    SELECT id, name, 0 as isProcessed

    FROM ['+@DBName+'].dbo.SYSOBJECTS

    WHERE TYPE = ''U''

    and name <> ''dtproperties''

    ORDER BY name')

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to insert records in ObjectInfo table, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --getting the all cloumns

    -- Had to put this into an EXEC because SYSCOLUMNS needs

    -- to be database qualified.

    EXEC ('INSERT INTO #ObjectColumns(TableID, ColumnID, ColumnName, DataTypeName)

    SELECT id, colid, col.name, typ.name

    FROM ['+@DBName+'].dbo.SYSCOLUMNS col

    INNER JOIN SYSTYPES typ

    on col.xtype = typ.xtype

    INNER JOIN #ObjectInfo obj

    on col.id = obj.TableID

    where typ.name <> ''sysname''

    order by 1, 2')

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to insert records in #ObjectColumns table, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --Class files

    WHILE EXISTS(SELECT * FROM #ObjectInfo WHERE isProcessed = 0)

    BEGIN

    SELECT TOP 1 @FileName = TableName, @Counter = TableID, @TEXTOUTPUT = 'using System;' FROM #ObjectInfo WHERE isProcessed = 0

    -- Make sure the folder exists.

    SET @FileName = 'c:\SQLClasses\'+LTRIM(RTRIM(@FileName))+'.cs'

    --Deleting the file

    EXECUTE @OLEResult = master..xp_fileexist @FileName, @FS OUT

    IF @FS > 0 EXECUTE ('EXEC master..xp_CMDShell "Del '+@FileName+'"')

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to delete file, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --creating file object

    EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to Create File System Object, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --Open a file

    EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

    IF @OLEResult <> 0 PRINT 'OpenTextFile ' + @FileName

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to Open File, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    --Write Text1

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SET @ErrorNo = @@Error

    IF ISNULL(@ErrorNo, 0) > 0

    BEGIN

    SELECT @ErrorStr = @ErrorStr + 'Unable to Write to File, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0

    GOTO Error_Handler

    END

    SELECT @TEXTOUTPUT = 'using System.Data;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Configuration;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web.Security;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web.UI;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web.UI.WebControls;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web.UI.WebControls.WebParts;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'using System.Web.UI.HtmlControls;'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'public class '+TableName FROM #ObjectInfo WHERE TableID = @Counter

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    --Column wise

    SET @Count = 1

    WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)

    BEGIN

    SELECT @TEXTOUTPUT = 'private '+LTRIM(RTRIM(DataTypeName))+' _'+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(DataTypeName))+' '+LTRIM(RTRIM(ColumnName)) FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'get'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'return _'+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'set'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '_'+LTRIM(RTRIM(ColumnName))+' = Value;' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SET @Count = @Count + 1

    END

    SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(TableName))+'()' FROM #ObjectInfo WHERE TableID = @Counter

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(TableName))+'(' FROM #ObjectInfo WHERE TableID = @Counter

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'Write', NULL, @TEXTOUTPUT

    SET @Count = 1

    WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)

    BEGIN

    SELECT @TEXTOUTPUT = LTRIM(RTRIM(DataTypeName))+' '+LTRIM(RTRIM(ColumnName))+', ' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'Write', NULL, @TEXTOUTPUT

    SET @Count = @Count + 1

    END

    SELECT @TEXTOUTPUT = ')'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '{'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SET @Count = 1

    WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)

    BEGIN

    SELECT @TEXTOUTPUT = ' _'+LTRIM(RTRIM(ColumnName))+' = '+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SET @Count = @Count + 1

    END

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    SELECT @TEXTOUTPUT = '}'

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

    EXECUTE @OLEResult = sp_OADestroy @FileID

    EXECUTE @OLEResult = sp_OADestroy @FS

    UPDATE #ObjectInfo SET isProcessed = 1 WHERE TableID = @Counter

    IF NOT EXISTS(SELECT * FROM #ObjectInfo WHERE isProcessed = 0)

    BEGIN

    BREAK

    END

    END

    DROP TABLE #ObjectInfo

    DROP TABLE #ObjectColumns

    Error_Handler:

    IF ISNULL(@ErrorStr, '') <> ''

    BEGIN

    PRINT @ErrorStr

    RETURN

    END

    END

    --End of the SP

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • Madhu-453926

    Valued Member

    Points: 66

    I'm getting output as

    (x row(s ) affected) - x is a number

    OpenTextFile (a whole bunch of them)

    And the C:\SQLClasses directory is empty.

    Do I need to set any Permissions to sp_OACreate or other stored procedure?

    I don't see it as problem as there no error message regarding permissions.

    Madhu

  • William Soranno

    SSCommitted

    Points: 1578

    If you used the original script to create the sp, it has errors.

    Copy the script from my post that has the corrected version.

    The script is an ALTER statement. You should be able to run it from a new query that is connected to the db you created the sp in.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • Madhu-453926

    Valued Member

    Points: 66

    William,

    I'm still getting the ourput as

    OpenTextFile C:\SQLClasses\table1.cs

    OpenTextFile C:\SQLClasses\table2.cs

    .

    .

    .

    .

    OpenTextFile C:\SQLClasses\tableN.cs

    EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

    IF @OLEResult <> 0 PRINT 'OpenTextFile ' + @FileName

    I believe it is because of the above statement. And I do not know how to fix it.

  • William Soranno

    SSCommitted

    Points: 1578

    Madhu,

    I had the same thing happen to me. It stopped once I created the path on my system that the sp is looking for.

    Make sure that c:\SQLClasses folder exists.

    Change the IF so it has the following:

    IF @OLEResult <> 0 PRINT 'OpenTextFile ' + @FileName + ' error number:' + CAST(@OLEResult AS VARCHAR(20))

    if you get the following:

    OpenTextFile c:\sqlclasses\tablen.cs error number:-2146828212

    This error number means the path is not found.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • Madhu-453926

    Valued Member

    Points: 66

    Thanks Williams,

    I figured out whats wrong. I have created the folder in my PC rather than the Server's C Drive. Once I created and ran the Stored Proc, it created the files clean.

    Satyanaraya garu,

    Very thanks for developing such a nice functionality. I'm beel looking for such functionality. I had used MyGeneration Doodads before for the same functionality. But this one pretty straight forward and optimal compared to that..

    Madhu

  • William Soranno

    SSCommitted

    Points: 1578

    Madhu,

    I did not create the original stored procedure, I just made some corrections.

    It was created by Satyanarayana Bommidi, he(?) deserves the credit.

    Bill

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    Pretty awesome stuff! I like it when I can find things to keep my developers happy, and this is just one of a bounty of goodies I have been collecting for them.

    Thank you for the revision, and to the original author as well!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply