SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [ dbo ] . [ sqltoolforexcuteandadapter ]
(
@objName nvarchar ( 100 ), -- 存储过程名称
@isexcute int -- 是否为execute 或者是sqladapter 0是execute,1是sqladapter
)
AS
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 = ' conn.Connection '
SET @parameterAt = ''
SET @outputValues = ''
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.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
print ' SqlConnection conn = new SqlConnection("");
SqlCommand com = new SqlCommand(" ' + @objName + ' ", conn); '
print ' com.CommandType = CommandType.StoredProcedure; '
PRINT ' SqlParameter[] Parameters = 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 ),
@col_isout tinyint
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 ' Image '
WHEN 35 THEN ' Text '
WHEN 36 THEN ' UniqueIdentifier '
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 ' Parameters[ ' + cast ( @col_order - 1 as varchar )
+ ' ] = new SqlParameter(" ' + @parameterAt + @col_name
+ ' ", SqlDbType. ' + @col_redef
+ ' ); '
-- Write out the parameter direction it is output
IF ( @col_isout = 1 )
BEGIN
PRINT ' Parameters[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Direction=ParameterDirection.Output; '
SET @outputValues = @outputValues + ' ?=Parameters[ ' +
cast ( @col_order - 1 as varchar ) + ' ].Value; '
END
ELSE
BEGIN
-- Write out the parameter value line
PRINT ' Parameters[ ' + cast ( @col_order - 1 as varchar ) + ' ].Value = ?; '
END
-- 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 ' Parameters[ ' + cast ( @col_order - 1 as varchar ) +
' ].Size= ' + cast ( @col_len as varchar ) + ' ; '
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 ''
print ' com.Parameters.AddRange(Parameters); '
if @isexcute = 0 -- 使用的execute方法执行sql语句
begin
print ' try
{
conn.Open();
com.ExecuteNonQuery();
}
catch (Exception ee)
{
throw ee;
}
finally
{
conn.Close();
} '
end
else if @isexcute = 1 -- 需要返回数据集的话使用这个
begin
print ' try
{
da.Fill(ds);
}
catch (Exception ee)
{
throw ee;
}
finally
{
//do what you want to do or dispose resoures.
} '
end
CLOSE cur
DEALLOCATE cur
END
if ( LEN ( @errMsg ) > 0 ) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
测试代码:
显示出啦的结果是:
这样非常方便,而且不用以后手写非常多的参数了,参数指定了精确的长度和类型,速度更快。
参考自codeproject
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [ dbo ] . [ sqltoolforexcuteandadapter ]
(
@objName nvarchar ( 100 ), -- 存储过程名称
@isexcute int -- 是否为execute 或者是sqladapter 0是execute,1是sqladapter
)
AS
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 = ' conn.Connection '
SET @parameterAt = ''
SET @outputValues = ''
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.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
print ' SqlConnection conn = new SqlConnection("");
SqlCommand com = new SqlCommand(" ' + @objName + ' ", conn); '
print ' com.CommandType = CommandType.StoredProcedure; '
PRINT ' SqlParameter[] Parameters = 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 ),
@col_isout tinyint
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 ' Image '
WHEN 35 THEN ' Text '
WHEN 36 THEN ' UniqueIdentifier '
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 ' Parameters[ ' + cast ( @col_order - 1 as varchar )
+ ' ] = new SqlParameter(" ' + @parameterAt + @col_name
+ ' ", SqlDbType. ' + @col_redef
+ ' ); '
-- Write out the parameter direction it is output
IF ( @col_isout = 1 )
BEGIN
PRINT ' Parameters[ ' + cast ( @col_order - 1 as varchar )
+ ' ].Direction=ParameterDirection.Output; '
SET @outputValues = @outputValues + ' ?=Parameters[ ' +
cast ( @col_order - 1 as varchar ) + ' ].Value; '
END
ELSE
BEGIN
-- Write out the parameter value line
PRINT ' Parameters[ ' + cast ( @col_order - 1 as varchar ) + ' ].Value = ?; '
END
-- 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 ' Parameters[ ' + cast ( @col_order - 1 as varchar ) +
' ].Size= ' + cast ( @col_len as varchar ) + ' ; '
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 ''
print ' com.Parameters.AddRange(Parameters); '
if @isexcute = 0 -- 使用的execute方法执行sql语句
begin
print ' try
{
conn.Open();
com.ExecuteNonQuery();
}
catch (Exception ee)
{
throw ee;
}
finally
{
conn.Close();
} '
end
else if @isexcute = 1 -- 需要返回数据集的话使用这个
begin
print ' try
{
da.Fill(ds);
}
catch (Exception ee)
{
throw ee;
}
finally
{
//do what you want to do or dispose resoures.
} '
end
CLOSE cur
DEALLOCATE cur
END
if ( LEN ( @errMsg ) > 0 ) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
测试代码:
sqltoolforexcuteandadapter
'
YourProcName
'
,
1
--
or 0
显示出啦的结果是:
SqlConnection conn
=
new
SqlConnection(
""
);
SqlCommand com = new SqlCommand( " YourProcName " , conn);
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] Parameters = new SqlParameter[ 1 ];
Parameters[ 0 ] = new SqlParameter( " @yourparam " , SqlDbType.VarChar);
Parameters[ 0 ].Value = ? ;
Parameters[ 0 ].Size = 6 ;
com.Parameters.AddRange(Parameters);
try
{
da.Fill(ds);
}
catch (Exception ee)
{
throw ee;
}
finally
{
//do what you want to do or dispose resoures.
}
SqlCommand com = new SqlCommand( " YourProcName " , conn);
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] Parameters = new SqlParameter[ 1 ];
Parameters[ 0 ] = new SqlParameter( " @yourparam " , SqlDbType.VarChar);
Parameters[ 0 ].Value = ? ;
Parameters[ 0 ].Size = 6 ;
com.Parameters.AddRange(Parameters);
try
{
da.Fill(ds);
}
catch (Exception ee)
{
throw ee;
}
finally
{
//do what you want to do or dispose resoures.
}
这样非常方便,而且不用以后手写非常多的参数了,参数指定了精确的长度和类型,速度更快。
参考自codeproject