This SP will be create C# classes using all table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Satyanarayana... » This SP will be create C# classes using all...

This SP will be create C# classes using all table

Rate Topic

Display Mode

Topic Options

Author

Message

Satyanarayana B

Posted Thursday, September 27, 2007 2:27 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, February 02, 2013 2:41 AM
Points: 48, Visits: 25

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

Post #403726

William Soranno

Posted Tuesday, November 13, 2007 1:04 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:42 AM
Points: 65, Visits: 344

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 148

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

Post #421753

Madhu-453926

Posted Tuesday, November 13, 2007 3:49 PM

Forum 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

William Soranno

Posted Tuesday, November 13, 2007 4:02 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:42 AM
Points: 65, Visits: 344

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 148

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

Post #421828

Madhu-453926

Posted Tuesday, November 13, 2007 4:10 PM

Forum 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

William Soranno

Posted Tuesday, November 13, 2007 4:47 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:42 AM
Points: 65, Visits: 344

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 148

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

Post #421841

Madhu-453926

Posted Tuesday, November 13, 2007 6:32 PM

Forum 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

William Soranno

Posted Wednesday, November 14, 2007 7:13 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:42 AM
Points: 65, Visits: 344

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 148

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

Post #422085

SQL_ME_RICH

Posted Thursday, March 03, 2011 11:11 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 436, Visits: 1,141

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 »

Permissions