Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

This SP will be create C# classes using all table Expand / Collapse
Author
Message
Posted Thursday, September 27, 2007 2:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:07 AM
Points: 48, Visits: 26
Comments posted to this topic are about the item This SP will be create C# classes using all table
Post #403726
Posted Tuesday, November 13, 2007 1:04 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 6:46 AM
Points: 77, Visits: 436
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
Post #421753
Posted Tuesday, November 13, 2007 3:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 5:37 PM
Points: 6, Visits: 13
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
Post #421822
Posted Tuesday, November 13, 2007 4:02 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 6:46 AM
Points: 77, Visits: 436
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
Post #421828
Posted Tuesday, November 13, 2007 4:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 5:37 PM
Points: 6, Visits: 13
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.



Post #421830
Posted Tuesday, November 13, 2007 4:47 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 6:46 AM
Points: 77, Visits: 436
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
Post #421841
Posted Tuesday, November 13, 2007 6:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 5:37 PM
Points: 6, Visits: 13
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
Post #421860
Posted Wednesday, November 14, 2007 7:13 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 6:46 AM
Points: 77, Visits: 436
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
Post #422085
Posted Thursday, March 3, 2011 11:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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!
Post #1072741
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse