|
|
|
bravo! this has been good help - thank you
|
|
|
|
|
I took the liberty of adding a couple of things to the script to fit some of my projects. Here's what I added:
a) Updated to include 'Scale' for 'Numeric' and 'Decimal'.
b) Updated to include 'Precision' for 'Numeric' and 'Decimal'.
c) Added the column type (@col_xtype) for types that are "missing" from the definition for further debugging.
Also changed the name of the proc - sorry Paul
Enjoy!!!
<br />
CREATE PROCEDURE [dbo].[tools_STOREDPROC_Builder] <br />
(<br />
@objName nvarchar(100)<br />
)<br />
AS<br />
<br />
SET NOCOUNT ON<br />
DECLARE @parameterCount int<br />
DECLARE @errMsg varchar(100)<br />
DECLARE @parameterAt varchar(1)<br />
DECLARE @connName varchar(100)<br />
DECLARE @outputValues varchar(100)<br />
--Change the following variable to the name of your connection instance<br />
SET @connName='conn.Connection'<br />
SET @parameterAt=''<br />
SET @outputValues=''<br />
SELECT <br />
dbo.sysobjects.name AS ObjName, <br />
dbo.sysobjects.xtype AS ObjType,<br />
dbo.syscolumns.name AS ColName, <br />
dbo.syscolumns.colorder AS ColOrder, <br />
dbo.syscolumns.length AS ColLen, <br />
dbo.syscolumns.colstat AS ColKey, <br />
dbo.syscolumns.prec AS ColPrecision,<br />
dbo.syscolumns.scale AS ColScale,<br />
dbo.syscolumns.isoutparam AS ColIsOut,<br />
dbo.systypes.xtype<br />
INTO #t_obj<br />
FROM <br />
dbo.syscolumns INNER JOIN<br />
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN<br />
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype<br />
WHERE <br />
(dbo.sysobjects.name = @objName) <br />
AND <br />
(dbo.systypes.status <> 1) <br />
ORDER BY <br />
dbo.sysobjects.name, <br />
dbo.syscolumns.colorder<br />
<br />
SET @parameterCount=(SELECT count(*) FROM #t_obj)<br />
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName<br />
IF(@errMsg is null)<br />
BEGIN<br />
PRINT 'try'<br />
PRINT ' {'<br />
PRINT ' SqlParameter[] spParams = new SqlParameter[' + cast(@parameterCount as varchar) + '];'<br />
PRINT ''<br />
<br />
DECLARE @source_name nvarchar,@source_type varchar,<br />
@col_name nvarchar(100),@col_order int,@col_type varchar(20),<br />
@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20), @col_precision int, @col_scale int, @col_isout tinyint<br />
<br />
DECLARE cur CURSOR FOR<br />
SELECT * FROM #t_obj<br />
OPEN cur<br />
-- Perform the first fetch.<br />
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_precision,@col_scale,@col_isout,@col_xtype<br />
<br />
if(@source_type=N'U') SET @parameterAt='@'<br />
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SET @col_redef=(SELECT CASE @col_xtype<br />
WHEN 34 THEN 'Image'<br />
WHEN 35 THEN 'Text'<br />
WHEN 36 THEN 'UniqueIdentifier'<br />
WHEN 48 THEN 'TinyInt'<br />
WHEN 52 THEN 'SmallInt'<br />
WHEN 56 THEN 'Int'<br />
WHEN 58 THEN 'SmallDateTime'<br />
WHEN 59 THEN 'Real'<br />
WHEN 60 THEN 'Money'<br />
WHEN 61 THEN 'DateTime'<br />
WHEN 62 THEN 'Float'<br />
WHEN 99 THEN 'NText'<br />
WHEN 104 THEN 'Bit'<br />
WHEN 106 THEN 'Decimal'<br />
WHEN 108 THEN 'Numeric'<br />
WHEN 122 THEN 'SmallMoney'<br />
WHEN 127 THEN 'BigInt'<br />
WHEN 165 THEN 'VarBinary'<br />
WHEN 167 THEN 'VarChar'<br />
WHEN 173 THEN 'Binary'<br />
WHEN 175 THEN 'Char'<br />
WHEN 231 THEN 'NVarChar'<br />
WHEN 239 THEN 'NChar'<br />
ELSE '!MISSING ' + CAST(@col_xtype AS varchar(20))<br />
END AS C) <br />
<br />
--Write out the parameter<br />
PRINT ' spParams[' + cast(@col_order-1 as varchar) <br />
+ '] = new SqlParameter("' + @parameterAt + @col_name<br />
+ '", SqlDbType.' + @col_redef<br />
+ ');'<br />
<br />
--Write out the parameter direction it is output<br />
IF(@col_isout=1)<br />
BEGIN<br />
PRINT ' spParams['+ cast(@col_order-1 as varchar) +'].Direction=ParameterDirection.Output;'<br />
SET @outputValues=@outputValues+' ?=spParams['+ cast(@col_order-1 as varchar) +'].Value;'<br />
END<br />
ELSE<br />
BEGIN<br />
--Write out the parameter value line<br />
PRINT ' spParams['+ cast(@col_order-1 as varchar) + '].Value = ?;'<br />
END<br />
--If the type is a string then output the size declaration<br />
IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)<br />
BEGIN<br />
PRINT ' spParams[' + cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';'<br />
END<br />
--If the type is a funky number then output the scale and precision declarations<br />
IF(@col_xtype=108)OR(@col_xtype=106)<br />
BEGIN<br />
PRINT ' spParams[' + cast(@col_order-1 as varchar) + '].Precision=' + cast(@col_precision as varchar) + ';'<br />
PRINT ' spParams[' + cast(@col_order-1 as varchar) + '].Scale=' + cast(@col_scale as varchar) + ';'<br />
END<br />
<br />
-- This is executed as long as the previous fetch succeeds.<br />
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_precision,@col_scale,@col_isout,@col_xtype <br />
END<br />
PRINT ''<br />
PRINT ' SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' + @objName + '", spParams);'<br />
PRINT @outputValues<br />
PRINT ' }'<br />
PRINT 'catch(Exception excp)'<br />
PRINT ' {'<br />
PRINT ' }'<br />
PRINT 'finally'<br />
PRINT ' {'<br />
PRINT ' ' + @connName + '.Dispose();'<br />
PRINT ' ' + @connName + '.Close();'<br />
PRINT ' }' <br />
CLOSE cur<br />
DEALLOCATE cur<br />
END<br />
if(LEN(@errMsg)>0) PRINT @errMsg<br />
DROP TABLE #t_obj<br />
SET NOCOUNT ON<br />
|
|
|
|
|
Thank you for sharing. This is a nice piece of code.
/sheijin
~living on the edge.~
[http://www.randz.tk]
|
|
|
|
|
Just wonder why timestamp data type being excluded.
mike
|
|
|
|
|
Because of programmers like you, others are inspired to become better at coding. Who needs something like CodeSmith. This is a brilliant piece of coding. Bravo! Well done!
|
|
|
|
|
Very handy tool thanks a lot.
If you get a chance have a look at GENNIT @ http://gennit.com it is a code generator tool which uses industrial strength foundations (NHibernate / ActiveRecord) to build a DAL from scratch (as well as other generators such as XHTML and Facebook API), all using online tools - you download a zip file containing the VS project with everything you designed, ready to go.
|
|
|
|
|
Ok, I'll admit it - the idea to put code generator into SQL stored procedure is brilliant.
5
|
|
|
|
|
Thank you, this is a very useful tool.
|
|
|
|
|
i am working on how to calucate Interest in Saving account using Visual studio and SQl Server Express Edtion.
i have a AmountIn, AmountOut and Balance Field... in SavingAccountTable
net interest will be automatically added every month......Help coz i don't know how to go on about this
thanks
Please anybody help
|
|
|
|
|
This is a good way. However, consider a professional web project, you may want to use SqlHelperParameterCache from Microsoft instead.
SqlHelperParameterCache has overhead if your project only calls most stored procedures once in a life time. Of course that's not the real case because most web projects are not to be used for only 1 visit then scrap the website.
|
|
|
|
|
Hello,
I've written a custom generator which generates Stored Procs as well as DAL code. pretty funky. Feel free to check it out =) please let me know what you think.
http://www.codeauthor.org[^]
Xin Zhao
http://www.codeauthor.org
|
|
|
|
|
|
The product has a free version. You should try it.
|
|
|
|
|
How about a SQL stored procedure generator and c# DAL generator for almost nothing! Then check out my www.dashSQL.com. dashSQL generaters all the code for stored procedures and as well as a base data access layer and then another c# DAL class that gets created per your table. No need for parameter types, int, varchar, etc. All parameters in the dashSQL code generator are strings!! SQL automatically converts everything for you! Give it a try!
www.dashsql.com
|
|
|
|
|
My issue with SubSonic, nHibernate and CodeAuthor is tha fact that they use adhoc or parameterized queries.
Using Stored Procedures is more secure than using adhoc queries or even parameterized queries via the command builder.
The security problem that this creates is that you are forced to assign SELECT permissions for tables, to the account that is running the queries.
What you really want is to assign EXEC permissions against your stored procedures. That way, users only get access to the data you package for them, not access to the entire table.
Here's a c# generator that also uses stored procedures exclusively:
OxyGen Code
http://www.techinceptions.com/codegenerator.html
You get pure commented c# code, paging (data source paging), caching etc.
And no dlls (but you can choose an option to compile the output).
The data is presented as generic lists or class instances, and there's special handling for static tables as well.
|
|
|
|
|
It looks like a great product.
Thanks for the link.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
This is really sweet but has anyone used this with an output variable set by ScopeIdentity? If so could someone please post an example of how to do this.
Thanks.!
Chaci Mulch
|
|
|
|
|
Here's what would be ideal for me:
A Visual Studio add-in that would allow you to generate C# calling code for a sproc simply by right-clicking the sproc in the server explorer and selecting "generate C# method". It would then analyze the sproc and generate a corresponding C# static class with methods to execute the sproc, get the SqlParameter[] for the sproc, overloads to execute the sproc with a specified connection, transaction, etc...
For example, say we have
ALTER PROC AddNewSignature (@Description varchar(255), @UserID uniqueidentifier, @Signature text, @EmailAccountID uniqueidentifier = null)
as
DECLARE @GUID uniqueidentifier
SELECT @GUID = newid()
INSERT SignatureTable (ID,Description,UserID,Signature)
SELECT @GUID,@Description, @UserID, @Signature
exec AssociateSignatureToMailAccount @SignatureID = @GUID, @MailAccID = @EmailAccountID
END
I want it to generate a C# static class containing methods like
public static void AddNewSignature(string description, Guid userID, string signature, Guid emailAccountID)
{
}
The method would have overloads for supplying connection, transactions, etc. Additionally, there would be more methods in the class that would just generate the SqlCommand with parameters and connection, allowing the consumer code to actually perform the execution. This would be important as many data tiers have custom execution with custom error handling.
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Connor's Christmas Spectacular!
Judah Himango
|
|
|
|
|
|
CREATE PROCEDURE tools_CS_SPROC_Builder_2005
(
@objName nvarchar(100),
@Executetype nvarchar(100) -- ExecuteNonQuery -or- ExecuteReader -or- ExecuteScalar
)
AS
/*
___________________________________________________________________
Name: CS SPROC Builder
Version: 1
Date: 10/09/2004
Author: Paul McKenzie
Description: Call this stored procedue passing the name of your
database object that you wish to insert/update
from .NET (C#) and the code returns code to copy
and paste into your application. This version is
for use with "Microsoft Data Application Block".
Version: 1.1
Date: 17/02/2006
Author: Paul McKenzie
Description: a) Updated to include 'UniqueIdentifier' Data Type
b) Support for 'ParameterDirection.Output'
*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
--Change the following variable to the name of your connection instance
SET @connName='ConfigurationSettings.AppSettings("connectionString")'
SET @parameterAt=''
SET @outputValues=''
SELECT
dbo.sysobjects.name AS ObjName,
dbo.sysobjects.xtype AS ObjType,
REPLACE(dbo.syscolumns.name,'@','') AS ColName,
dbo.syscolumns.colorder AS ColOrder,
dbo.syscolumns.length AS ColLen,
dbo.syscolumns.colstat AS ColKey,
dbo.syscolumns.isoutparam AS ColIsOut,
dbo.systypes.xtype
INTO #t_obj
FROM
dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE
(dbo.sysobjects.name = @objName)
AND
(dbo.systypes.status <> 1)
ORDER BY
dbo.sysobjects.name,
dbo.syscolumns.colorder
SET @parameterCount=(SELECT count(*) FROM #t_obj)
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
BEGIN
DECLARE @source_name nvarchar,@source_type varchar,
@col_name nvarchar(100),@col_order int,@col_type varchar(20),
@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20), @col_isout tinyint
IF @ExecuteType = 'ExecuteNonQuery'
BEGIN
PRINT 'SUB '+@objName+'( _'
END
IF @ExecuteType = 'ExecuteReader'
BEGIN
PRINT 'FUNCTION '+@objName+'( _'
END
IF @ExecuteType = 'ExecuteScalar'
BEGIN
PRINT 'FUNCTION '+@objName+'( _'
END
declare @parameterOut nvarchar(1000)
DECLARE cur CURSOR FOR
SELECT * FROM #t_obj
OPEN cur
-- Perform the first fetch.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
--if(@source_type=N'U') SET @parameterAt=''
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN 'Binary'
WHEN 35 THEN 'String'
WHEN 36 THEN 'Guid'
WHEN 48 THEN 'Byte'
WHEN 52 THEN 'Int16'
WHEN 56 THEN 'Int32'
WHEN 58 THEN 'DateTime'
WHEN 59 THEN 'Single'
WHEN 60 THEN 'Currency'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Double'
WHEN 99 THEN 'String'
WHEN 104 THEN 'Boolean'
WHEN 106 THEN 'Decimal'
WHEN 122 THEN 'Currency'
WHEN 127 THEN 'Int64'
WHEN 165 THEN 'Binary'
WHEN 167 THEN 'String'
WHEN 173 THEN 'Binary'
WHEN 175 THEN 'String'
WHEN 231 THEN 'String'
WHEN 239 THEN 'String'
ELSE '!MISSING'
END AS C)
--capture parameter
select @parameterOut = ' ByVal ' + @col_name+ ' AS '+@col_redef
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
IF @@FETCH_STATUS<>0
BEGIN
PRINT @parameterOut + ' _'
END
IF @@FETCH_STATUS=0
BEGIN
PRINT @parameterOut+', _'
END
END
CLOSE cur
DEALLOCATE cur
IF @ExecuteType = 'ExecuteNonQuery'
BEGIN
PRINT ')'
END
IF @ExecuteType = 'ExecuteReader'
BEGIN
PRINT ') AS SQLDATAREADER'
END
IF @ExecuteType = 'ExecuteScalar'
BEGIN
PRINT ') AS ?'
END
PRINT ''
PRINT ' Dim db As Database = DatabaseFactory.CreateDatabase() '
PRINT ' Dim sqlCommand As String = "'+@objName+'" '
PRINT ' Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand) '
PRINT ''
DECLARE cur CURSOR FOR
SELECT * FROM #t_obj
OPEN cur
-- Perform the first fetch.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
--if(@source_type=N'U') SET @parameterAt=''
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN 'Binary'
WHEN 35 THEN 'String'
WHEN 36 THEN 'Guid'
WHEN 48 THEN 'Byte'
WHEN 52 THEN 'Int16'
WHEN 56 THEN 'Int32'
WHEN 58 THEN 'DateTime'
WHEN 59 THEN 'Single'
WHEN 60 THEN 'Currency'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Double'
WHEN 99 THEN 'String'
WHEN 104 THEN 'Boolean'
WHEN 106 THEN 'Decimal'
WHEN 122 THEN 'Currency'
WHEN 127 THEN 'Int64'
WHEN 165 THEN 'Binary'
WHEN 167 THEN 'String'
WHEN 173 THEN 'Binary'
WHEN 175 THEN 'String'
WHEN 231 THEN 'String'
WHEN 239 THEN 'String'
ELSE '!MISSING'
END AS C)
--Write out the parameter
IF(@col_isout<>1)
BEGIN
PRINT ' db.AddInParameter(dbCommand, "' + @col_name+ '", DbType.'+@col_redef+', ' + @col_name+ ')'
END
--Write out the parameter direction it is output
IF(@col_isout=1)
BEGIN
PRINT ' db.AddOutParameter(dbCommand, "' + @col_name+ '", DbType.'+@col_redef+', ' + @col_name+ ')'
END
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
END
PRINT ''
IF @ExecuteType = 'ExecuteNonQuery'
BEGIN
PRINT ' db.ExecuteNonQuery(dbCommand)'
PRINT 'END SUB'
END
IF @ExecuteType = 'ExecuteReader'
BEGIN
PRINT ' Return db.ExecuteReader(dbCommand) '
PRINT 'END FUNCTION'
END
IF @ExecuteType = 'ExecuteScalar'
BEGIN
PRINT ' Return db.ExecuteScalar(dbCommand) '
PRINT 'END FUNCTION'
END
CLOSE cur
DEALLOCATE cur
END
if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
GO
|
|
|
|
|
Anybody got a vesion of the code generator for Jan 2006 MS DAAB.
Great time saver.
Cheers.
Zac
|
|
|
|
|
Hello mister,
Where I get reference about SQL Types and codes associated ?
WHEN 52 THEN 'SmallInt'
WHEN 56 THEN 'Int'
For example, the column col_xtype has many values: 34, 35, 52, etc, but I don't know that 56 code is the INT SQL SERVER type, I need some reference, do you know ?
Thanks and greetings.
|
|
|
|
|
Do you have a version for Oracle?
Joseph
|
|
|
|
|