source article:http://www.cnblogs.com/perfectdesign/archive/2008/01/15/gernerateADOCode.html
下面的存储过程可以在查询管理器里面生成ADO.NET 存储过程调用代码,省去很多的Coding功夫。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ALTER PROCEDURE [dbo].[sqltoolforexcuteandadapter]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@objName nvarchar(100),--存储过程名称
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@isexcute int --是否为execute 或者是sqladapter 0是execute,1是sqladapter
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @parameterCount int
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @errMsg varchar(100)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @parameterAt varchar(1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @connName varchar(100)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @outputValues varchar(100)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Change the following variable to the name of your connection instance
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @connName='conn.Connection'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @parameterAt=''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @outputValues=''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.sysobjects.name AS ObjName,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.sysobjects.xtype AS ObjType,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.name AS ColName,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.colorder AS ColOrder,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.length AS ColLen,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.colstat AS ColKey,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.isoutparam AS ColIsOut,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.systypes.xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
INTO #t_obj
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns INNER JOIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHERE
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(dbo.sysobjects.name = @objName)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AND
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(dbo.systypes.status <> 1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ORDER BY
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.sysobjects.name,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
dbo.syscolumns.colorder
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @parameterCount=(SELECT count(*) FROM #t_obj)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF(@errMsg is null)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print 'SqlConnection conn = new SqlConnection("");
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SqlCommand com = new SqlCommand("'+@objName+'", conn);'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print 'com.CommandType = CommandType.StoredProcedure;'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ' SqlParameter[] Parameters = new SqlParameter[' +
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
cast(@parameterCount as varchar) + '];'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE @source_name nvarchar,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@source_type varchar,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_name nvarchar(100),
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_order int,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_type varchar(20),
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_len int,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_key int,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_xtype int,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_redef varchar(20),
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_isout tinyint
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE cur CURSOR FOR
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT * FROM #t_obj
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
OPEN cur
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Perform the first fetch.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FETCH NEXT FROM cur INTO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if(@source_type=N'U') SET @parameterAt='@'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHILE @@FETCH_STATUS = 0
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @col_redef=(SELECT CASE @col_xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 34 THEN 'Image'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 35 THEN 'Text'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 36 THEN 'UniqueIdentifier'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 48 THEN 'TinyInt'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 52 THEN 'SmallInt'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 56 THEN 'Int'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 58 THEN 'SmallDateTime'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 59 THEN 'Real'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 60 THEN 'Money'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 61 THEN 'DateTime'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 62 THEN 'Float'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 99 THEN 'NText'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 104 THEN 'Bit'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 106 THEN 'Decimal'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 122 THEN 'SmallMoney'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 127 THEN 'BigInt'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 165 THEN 'VarBinary'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 167 THEN 'VarChar'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 173 THEN 'Binary'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 175 THEN 'Char'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 231 THEN 'NVarChar'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
WHEN 239 THEN 'NChar'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ELSE '!MISSING'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END AS C)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Write out the parameter
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ' Parameters[' + cast(@col_order-1 as varchar)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+ '] = new SqlParameter("' + @parameterAt + @col_name
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+ '", SqlDbType.' + @col_redef
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+ ');'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Write out the parameter direction it is output
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF(@col_isout=1)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ' Parameters['+ cast(@col_order-1 as varchar)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+'].Direction=ParameterDirection.Output;'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET @outputValues=@outputValues+' ?=Parameters['+
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
cast(@col_order-1 as varchar) +'].Value;'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ELSE
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Write out the parameter value line
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ' Parameters['+ cast(@col_order-1 as varchar) + '].Value = ?;'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--If the type is a string then output the size declaration
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
BEGIN
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ' Parameters[' + cast(@col_order-1 as varchar) +
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
'].Size=' + cast(@col_len as varchar) + ';'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- This is executed as long as the previous fetch succeeds.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FETCH NEXT FROM cur INTO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@source_name,@source_type,@col_name,@col_order,
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@col_len,@col_key,@col_isout,@col_xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PRINT ''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print ' com.Parameters.AddRange(Parameters);'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @isexcute = 0 --使用的execute方法执行sql语句
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print 'try
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
conn.Open();
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
com.ExecuteNonQuery();
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
catch (Exception ee)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
throw ee;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
conn.Close();
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
else if @isexcute = 1--需要返回数据集的话使用这个
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print 'try
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
da.Fill(ds);
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
catch (Exception ee)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
throw ee;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
//do what you want to do or dispose resoures.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CLOSE cur
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DEALLOCATE cur
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
END
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if(LEN(@errMsg)>0) PRINT @errMsg
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DROP TABLE #t_obj
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
测试代码:
sqltoolforexcuteandadapter 'YourProcName',1--or 0
显示出啦的结果是:
SqlConnection conn = new SqlConnection("");
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
da.Fill(ds);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
catch (Exception ee)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
throw ee;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
finally
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//do what you want to do or dispose resoures.
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
这样非常方便,而且不用以后手写非常多的参数了,参数指定了精确的长度和类型,速度更快。
参考自codeproject