SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


This SP will be create C# classes using all table


This SP will be create C# classes using all table

Author
Message
Satyanarayana B
Satyanarayana B
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 26
Comments posted to this topic are about the item This SP will be create C# classes using all table
William Soranno
William Soranno
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 519
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
Madhu-453926
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
William Soranno
William Soranno
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 519
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
Madhu-453926
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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.
William Soranno
William Soranno
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 519
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
Madhu-453926
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
William Soranno
William Soranno
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 519
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
SQL_ME_RICH
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2264 Visits: 1596
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search