CREATE
PROCEDURE
dbo.tools_CS_SPROC_Builder
(
@objName nvarchar ( 100 )
)
AS
/*
___________________________________________________________________
Name: CS SPROC Builder
Version: 1
Date: 20/06/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".
Sample:
EXEC tools_CS_SPROC_Builder 'InsertSQL'
*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar ( 100 )
DECLARE @parameterAt varchar ( 1 )
DECLARE @connName varchar ( 100 )
SET @connName = ' conn.Connection '
SET @parameterAt = ''
SELECT
dbo.sysobjects.name AS ObjName,
dbo.sysobjects.xtype AS ObjType,
dbo.syscolumns.name AS ColName,
dbo.syscolumns.colorder AS ColOrder,
dbo.syscolumns.length AS ColLen,
dbo.syscolumns.colstat AS ColKey,
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
PRINT ' try '
PRINT ' { '
PRINT ' SqlParameter[] paramsToStore = new SqlParameter[ ' + cast ( @parameterCount as varchar ) + ' ]; '
PRINT ''
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 )
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_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 ' Image '
WHEN 35 THEN ' Text '
WHEN 48 THEN ' TinyInt '
WHEN 52 THEN ' SmallInt '
WHEN 56 THEN ' Int '
WHEN 58 THEN ' SmallDateTime '
WHEN 59 THEN ' Real '
WHEN 60 THEN ' Money '
WHEN 61 THEN ' DateTime '
WHEN 62 THEN ' Float '
WHEN 99 THEN ' NText '
WHEN 104 THEN ' Bit '
WHEN 106 THEN ' Decimal '
WHEN 122 THEN ' SmallMoney '
WHEN 127 THEN ' BigInt '
WHEN 165 THEN ' VarBinary '
WHEN 167 THEN ' VarChar '
WHEN 173 THEN ' Binary '
WHEN 175 THEN ' Char '
WHEN 231 THEN ' NVarChar '
WHEN 239 THEN ' NChar '
ELSE ' !MISSING '
END AS C)
-- Write out the parameter
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ] = new SqlParameter(" ' + @parameterAt + @col_name
+ ' ", SqlDbType. ' + @col_redef
+ ' ); '
-- If the type is a string then output the size declaration
IF ( @col_xtype = 231 ) OR ( @col_xtype = 167 ) OR ( @col_xtype = 175 ) OR ( @col_xtype = 99 ) OR ( @col_xtype = 35 )
BEGIN
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Size= ' + cast ( @col_len as varchar ) + ' ; '
END
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Value = ; '
-- 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_xtype
END
PRINT ''
PRINT ' SqlHelper.ExecuteNonQuery( ' + @connName + ' , CommandType.StoredProcedure," ' + @objName + ' ", paramsToStore); '
PRINT ' } '
PRINT ' catch(Exception excp) '
PRINT ' { '
PRINT ' } '
PRINT ' finally '
PRINT ' { '
PRINT ' ' + @connName + ' .Dispose(); '
PRINT ' ' + @connName + ' .Close(); '
PRINT ' } '
CLOSE cur
DEALLOCATE cur
END
if ( LEN ( @errMsg ) > 0 ) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
GO
(
@objName nvarchar ( 100 )
)
AS
/*
___________________________________________________________________
Name: CS SPROC Builder
Version: 1
Date: 20/06/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".
Sample:
EXEC tools_CS_SPROC_Builder 'InsertSQL'
*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar ( 100 )
DECLARE @parameterAt varchar ( 1 )
DECLARE @connName varchar ( 100 )
SET @connName = ' conn.Connection '
SET @parameterAt = ''
SELECT
dbo.sysobjects.name AS ObjName,
dbo.sysobjects.xtype AS ObjType,
dbo.syscolumns.name AS ColName,
dbo.syscolumns.colorder AS ColOrder,
dbo.syscolumns.length AS ColLen,
dbo.syscolumns.colstat AS ColKey,
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
PRINT ' try '
PRINT ' { '
PRINT ' SqlParameter[] paramsToStore = new SqlParameter[ ' + cast ( @parameterCount as varchar ) + ' ]; '
PRINT ''
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 )
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_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 ' Image '
WHEN 35 THEN ' Text '
WHEN 48 THEN ' TinyInt '
WHEN 52 THEN ' SmallInt '
WHEN 56 THEN ' Int '
WHEN 58 THEN ' SmallDateTime '
WHEN 59 THEN ' Real '
WHEN 60 THEN ' Money '
WHEN 61 THEN ' DateTime '
WHEN 62 THEN ' Float '
WHEN 99 THEN ' NText '
WHEN 104 THEN ' Bit '
WHEN 106 THEN ' Decimal '
WHEN 122 THEN ' SmallMoney '
WHEN 127 THEN ' BigInt '
WHEN 165 THEN ' VarBinary '
WHEN 167 THEN ' VarChar '
WHEN 173 THEN ' Binary '
WHEN 175 THEN ' Char '
WHEN 231 THEN ' NVarChar '
WHEN 239 THEN ' NChar '
ELSE ' !MISSING '
END AS C)
-- Write out the parameter
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ] = new SqlParameter(" ' + @parameterAt + @col_name
+ ' ", SqlDbType. ' + @col_redef
+ ' ); '
-- If the type is a string then output the size declaration
IF ( @col_xtype = 231 ) OR ( @col_xtype = 167 ) OR ( @col_xtype = 175 ) OR ( @col_xtype = 99 ) OR ( @col_xtype = 35 )
BEGIN
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Size= ' + cast ( @col_len as varchar ) + ' ; '
END
PRINT ' paramsToStore[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Value = ; '
-- 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_xtype
END
PRINT ''
PRINT ' SqlHelper.ExecuteNonQuery( ' + @connName + ' , CommandType.StoredProcedure," ' + @objName + ' ", paramsToStore); '
PRINT ' } '
PRINT ' catch(Exception excp) '
PRINT ' { '
PRINT ' } '
PRINT ' finally '
PRINT ' { '
PRINT ' ' + @connName + ' .Dispose(); '
PRINT ' ' + @connName + ' .Close(); '
PRINT ' } '
CLOSE cur
DEALLOCATE cur
END
if ( LEN ( @errMsg ) > 0 ) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
GO
示例:存储过程名'1_Proc_admin_publish'
exec
dbo.tools_CS_SPROC_Builder
'
1_Proc_admin_publish
'
显示结果如下:
try
{
SqlParameter[] paramsToStore = new SqlParameter[ 4 ];
paramsToStore[ 0 ] = new SqlParameter( " @memberName " , SqlDbType.VarChar);
paramsToStore[ 0 ].Size = 60 ;
paramsToStore[ 0 ].Value = ;
paramsToStore[ 1 ] = new SqlParameter( " @type " , SqlDbType.Int);
paramsToStore[ 1 ].Value = ;
paramsToStore[ 2 ] = new SqlParameter( " @static " , SqlDbType.Int);
paramsToStore[ 2 ].Value = ;
paramsToStore[ 3 ] = new SqlParameter( " @returnType " , SqlDbType.Int);
paramsToStore[ 3 ].Value = ;
SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure, " 1_Proc_admin_publish " , paramsToStore);
}
catch (Exception excp)
{
}
finally
{
conn.Connection.Dispose();
conn.Connection.Close();
}
{
SqlParameter[] paramsToStore = new SqlParameter[ 4 ];
paramsToStore[ 0 ] = new SqlParameter( " @memberName " , SqlDbType.VarChar);
paramsToStore[ 0 ].Size = 60 ;
paramsToStore[ 0 ].Value = ;
paramsToStore[ 1 ] = new SqlParameter( " @type " , SqlDbType.Int);
paramsToStore[ 1 ].Value = ;
paramsToStore[ 2 ] = new SqlParameter( " @static " , SqlDbType.Int);
paramsToStore[ 2 ].Value = ;
paramsToStore[ 3 ] = new SqlParameter( " @returnType " , SqlDbType.Int);
paramsToStore[ 3 ].Value = ;
SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure, " 1_Proc_admin_publish " , paramsToStore);
}
catch (Exception excp)
{
}
finally
{
conn.Connection.Dispose();
conn.Connection.Close();
}