使用系统表根据存储过程名字生成ADO.NET数据库访问代码
来源:互联网 发布:淘宝上卖奶粉怎么开店 编辑:程序博客网 时间:2024/05/16 01:33
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
- 使用系统表根据存储过程名字生成ADO.NET数据库访问代码
- 根据存储过程名字生成ADO.NET数据库访问代码
- 根据存储过程名字生成ADO.NET数据库访问代码
- ADO.NET使用存储过程访问数据库
- Sybase ASE数据库使用ADO.net访问存储过程方法详解
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用ADO.NET访问Oracle 9i存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用ADO.NET访问Oracle 9i存储过程(下)
- 使用ADO.NET访问Oracle 9i存储过程(上)
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 使用 ADO.NET 访问 Oracle 9i 存储过程
- 如何使用ADO访问Oracle数据库存储过程
- Windows XP系统中组件服务没有属性选项的解决办法
- CSS边框
- 使用EasyRecovery恢复变成RAW的移动硬盘分区
- ASP.NET AJAX入门系列(5):使用UpdatePanel控件(二)
- NTFS体系结构
- 使用系统表根据存储过程名字生成ADO.NET数据库访问代码
- 利用js生成文本框的setfocus的问题
- 赤壁
- ASP.NET AJAX入门系列(6):UpdateProgress控件简单介绍
- [转]使用Firefox+Tor+FoxyProxy+Noscript+IETab进行安全自由的网上冲浪
- ASP.NET AJAX入门系列(7):使用客户端脚本对UpdateProgress编程
- 最新有效刷ip方法
- 《数码摄影手册》Scott Kelby
- 为什么浏览器总报