September 27, 2007 at 2:27 pm
Comments posted to this topic are about the item This SP will be create C# classes using all table
November 13, 2007 at 1:04 pm
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 @CounterINT,--loop counter
@CountINT,--loop counter
@ObjectNameVARCHAR(100),--to hold Object Name like (Tables, Views, SPs and Triggers)
@ErrorNoINT,--to hold the error number
@ErrorStrVARCHAR(255),--to hold the error description
@TEXTOUTPUTVARCHAR(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
November 13, 2007 at 3:49 pm
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
November 13, 2007 at 4:02 pm
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
November 13, 2007 at 4:10 pm
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.
November 13, 2007 at 4:47 pm
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
November 13, 2007 at 6:32 pm
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
November 14, 2007 at 7:13 am
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
March 3, 2011 at 11:11 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply