C#調用存儲過程(帶返回值)和SQL Server DTS的方法
来源:互联网 发布:apache局域网访问配置 编辑:程序博客网 时间:2024/05/01 22:57
C#調用存儲過程(帶返回值)和SQL Server DTS的方法
在程序開發中,我們經常要做的事就是訪問資料庫.有時候會調用存儲過程和DTS,下面就此給出例子來說明如何實現第一部分 調用存儲過程(帶入參數和取得返回值)
1.調用SQL Server的存儲過程
假如我們有如下一個存儲過程,很簡單的實現,只是將兩個傳進來值做加法處理然后返回
CREATE PROCEDURE AddMethod
(
@returnvalue int OUTPUT, --返回結果
@Parameter_1 int, --參數一
@Parameter_2 int --參數二
)
AS
-----------------------------------------------
set @returnvalue = @Parameter_1 + @Parameter_2
RETURN @returnvalue
-------------------------------------------------
GO
以下是在c#中調用此存儲過程的代碼(
@returnvalue int OUTPUT, --返回結果
@Parameter_1 int, --參數一
@Parameter_2 int --參數二
)
AS
-----------------------------------------------
set @returnvalue = @Parameter_1 + @Parameter_2
RETURN @returnvalue
-------------------------------------------------
GO
private int TestProcedure()
{
System.Random rnd = new Random();
string constr = "Data Source=localhost;initial catalog=testdb;user id=sa;password=sa;connect timeout=3000";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "AddMethod";
cmd.CommandType = CommandType.StoredProcedure;
//參數一
SqlParameter parameter_1 = new SqlParameter("@Parameter_1",SqlDbType.Int);
parameter_1.Direction = ParameterDirection.Input;
parameter_1.Value = (int)(rnd.NextDouble() * 100);
//參數二
SqlParameter parameter_2 = new SqlParameter("@Parameter_2",SqlDbType.Int);
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = (int)(rnd.NextDouble() * 100);
//返回值
SqlParameter returnValue = new SqlParameter("@returnValue",SqlDbType.Int);
returnValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnValue);
cmd.Parameters.Add(parameter_1);
cmd.Parameters.Add(parameter_2);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
return (int)returnValue.Value;
}
{
System.Random rnd = new Random();
string constr = "Data Source=localhost;initial catalog=testdb;user id=sa;password=sa;connect timeout=3000";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "AddMethod";
cmd.CommandType = CommandType.StoredProcedure;
//參數一
SqlParameter parameter_1 = new SqlParameter("@Parameter_1",SqlDbType.Int);
parameter_1.Direction = ParameterDirection.Input;
parameter_1.Value = (int)(rnd.NextDouble() * 100);
//參數二
SqlParameter parameter_2 = new SqlParameter("@Parameter_2",SqlDbType.Int);
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = (int)(rnd.NextDouble() * 100);
//返回值
SqlParameter returnValue = new SqlParameter("@returnValue",SqlDbType.Int);
returnValue.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnValue);
cmd.Parameters.Add(parameter_1);
cmd.Parameters.Add(parameter_2);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
return (int)returnValue.Value;
}
2.調用Oralce的存儲過程
先創建一個package和package body,代碼如下
(1).Package
CREATE OR REPLACE package TestPackage is
type mytype is ref cursor;
procedure p_Test(mycs out mytype);
function f_get(str in varchar2) return varchar2;
end;
(2).Package Bodiestype mytype is ref cursor;
procedure p_Test(mycs out mytype);
function f_get(str in varchar2) return varchar2;
end;
create or replace package body TestPackage is
procedure p_Test(mycs out mytype) is
begin
open mycs for
select * from dual;
end p_Test;
function f_get(str varchar2) return varchar2 is
str_temp varchar2(100) := 'good luck !';
begin
str_temp := str_temp || str;
return str_temp;
end f_get;
end;
以上建好了存儲過程和方法,現在看下如何在c#中實現調用.procedure p_Test(mycs out mytype) is
begin
open mycs for
select * from dual;
end p_Test;
function f_get(str varchar2) return varchar2 is
str_temp varchar2(100) := 'good luck !';
begin
str_temp := str_temp || str;
return str_temp;
end f_get;
end;
先來個調用方法的
private string TestFunction()
{
string constr = "Data source=E4MT;user id=sa;password=sa";
OracleConnection conn = new OracleConnection(constr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "TestPackage.f_get";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameter_1 = new OracleParameter("str",OracleType.VarChar,100);
parameter_1.Direction = ParameterDirection.Input;
parameter_1.Value = "This is just a test";
OracleParameter parameter_2 = new OracleParameter("result",OracleType.VarChar,100);
parameter_2.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(parameter_1);
cmd.Parameters.Add(parameter_2);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
return parameter_2.Value.ToString();
}
再來個調用存儲過程的,返回的是個數據集{
string constr = "Data source=E4MT;user id=sa;password=sa";
OracleConnection conn = new OracleConnection(constr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "TestPackage.f_get";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameter_1 = new OracleParameter("str",OracleType.VarChar,100);
parameter_1.Direction = ParameterDirection.Input;
parameter_1.Value = "This is just a test";
OracleParameter parameter_2 = new OracleParameter("result",OracleType.VarChar,100);
parameter_2.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(parameter_1);
cmd.Parameters.Add(parameter_2);
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
return parameter_2.Value.ToString();
}
private DataSet TestProc()
{
string constr = "Data source=E4MT;user id=sa;password=sa";
OracleConnection conn = new OracleConnection(constr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "TestPackage.p_Test";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameter_1 = new OracleParameter("mycs",OracleType.Cursor);
parameter_1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameter_1);
DataSet ds = new DataSet();
try
{
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds,"test");
}
finally
{
conn.Close();
}
return ds;
}
{
string constr = "Data source=E4MT;user id=sa;password=sa";
OracleConnection conn = new OracleConnection(constr);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "TestPackage.p_Test";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameter_1 = new OracleParameter("mycs",OracleType.Cursor);
parameter_1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameter_1);
DataSet ds = new DataSet();
try
{
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds,"test");
}
finally
{
conn.Close();
}
return ds;
}
以上介紹了在c#中分別調用sql 和oracle的存儲的方法
第二部分 C#調用 sql server 的DTS
目前知道有兩種比較適用的方法:
(1).調用DTSRun命令來跑DTS.此種方法調用到了Master..xp_cmdshell擴展存儲過程,故需要將調用的用戶開通能訪問此擴展存儲過程的權限.
private void TestRunDTS()
{
string constr = "Data Source=localhost;initial catalog=master;user id=sa;password='';connect timeout=3000";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "exec master..xp_cmdshell 'DTSRun /~Z0x8E9EF55158ABA56C3C3346137F1F7B7B090F1F61D54D3981CFA1DB0E8B50C4E0D416AF2F746FA482B5E3C2AABA52D4838DAA496938F1E61155CBB055FF4082181E6BE53F08A47D7E6A82E6B77E3F83FCBAAD1B'";
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
注意看上面標紅色的部分,exec是來報告存儲過程的,xp_cmdshell 及后面的一長串代碼就是存儲過程及參數.dtsrun其實是dos下的命令.后面跟的是DTS Package的ID.這個可以將DTS排成排程然后在屬性中查看到.{
string constr = "Data Source=localhost;initial catalog=master;user id=sa;password='';connect timeout=3000";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "exec master..xp_cmdshell 'DTSRun /~Z0x8E9EF55158ABA56C3C3346137F1F7B7B090F1F61D54D3981CFA1DB0E8B50C4E0D416AF2F746FA482B5E3C2AABA52D4838DAA496938F1E61155CBB055FF4082181E6BE53F08A47D7E6A82E6B77E3F83FCBAAD1B'";
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
(2)第二種方法就要用到Microsoft提供的一個COM類.
直接提供下載吧:Microsoft.SQLServer.DTSPkg80.dll
用的是LoadFromSQLServer()方法
private void TestDTS()
{
Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("localhost", "sa", "",DTSSQLServerStorageFlags.DTSSQLStgFlag_Default
,null,null,null,"DTSTest", ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
package = null;
}
參數說明:LoadFromSQLServer參數分別是:ServerName,userID,password,DTSSQLServerStorageFlags,PackagePassword,PackageGuid,PackageVersionGuid,PackageName{
Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("localhost", "sa", "",DTSSQLServerStorageFlags.DTSSQLStgFlag_Default
,null,null,null,"DTSTest", ref pVarPersistStgOfHost);
package.Execute();
package.UnInitialize();
package = null;
}
第三部分:SQL Server 存儲過程中調用DTS
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
--生成對象
print 'start to create DTS.Pachage'
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'create DTS.Pachage successful'
--調用方法
print 'start to LoadFromSQLServer'
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer',NULL,
@ServerName='localhost', @PackageName='testdts', @Flags=256
IF @hr <> 0
BEGIN
print 'error LoadFromSQLServer'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'LoadFromSQLServer successful'
--設置全局屬性(如果DTS有的話)
print 'start to set property'
EXEC @hr = sp_OASetProperty @object, 'GlobalVariables("var_1").Value',123
IF @hr <> 0
BEGIN
print 'error set property'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'set property successful'
--運行DTS
print 'start to execute'
exec @hr = sp_OAMethod @object,'Execute'
if @hr <> 0
begin
print 'Execute Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
print 'Execute Successful'
--銷毀對象
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'Destroy Package failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'destroy successful'
GO
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
--生成對象
print 'start to create DTS.Pachage'
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'create DTS.Pachage successful'
--調用方法
print 'start to LoadFromSQLServer'
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer',NULL,
@ServerName='localhost', @PackageName='testdts', @Flags=256
IF @hr <> 0
BEGIN
print 'error LoadFromSQLServer'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'LoadFromSQLServer successful'
--設置全局屬性(如果DTS有的話)
print 'start to set property'
EXEC @hr = sp_OASetProperty @object, 'GlobalVariables("var_1").Value',123
IF @hr <> 0
BEGIN
print 'error set property'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'set property successful'
--運行DTS
print 'start to execute'
exec @hr = sp_OAMethod @object,'Execute'
if @hr <> 0
begin
print 'Execute Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
print 'Execute Successful'
--銷毀對象
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'Destroy Package failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'destroy successful'
GO
以上介紹了調用存儲過程及DTS的方法,可靈活應用於程式開發中.
- C#調用存儲過程(帶返回值)和SQL Server DTS的方法
- C#調用存儲過程(帶返回值)和SQL Server DTS的方法
- 转C#調用存儲過程(帶返回值)和SQL Server DTS的方法
- C# 插入数据后返回sql server主键值的方法
- SQL SERVER的返回值
- SQL Server中六种数据移动的方法(重点讲解DTS)
- C#获得sql存储过程return返回的值和返回参数返回的值
- SQL SERVER DTS(ACCESS,EXCEL)
- SQL Server DTS packages releated
- C#连接SQL Server的方法
- SQL Server 2008实现"编辑所有行"和"返回所有行"的方法
- SQL Server 2008实现"编辑所有行"和"返回所有行"的方法
- SQL Server 2008实现"编辑所有行"和"返回所有行"的方法
- VB调用sql server 的数据转换服务(DTS)
- SQL server 到oracle的DTS 作业调度
- c#之MessageBox的用法和获取返回值方法
- SQL Server存储过程的返回值
- C# 和SQL server 中生成GUID 的方法 以及他们的之间的区别
- 基于S3C2410X的Linux-2.6.14.1系统移植
- Linux下C语言编程基础知识
- 自定义控件(ascx)中如何使用事件
- 苹果 iPhome 对 Silverlight 与 Flash 无兴趣,Google 则通吃
- 天人合一与人生何干系?
- C#調用存儲過程(帶返回值)和SQL Server DTS的方法
- symbian C++搭建环境之carbide C++,vc6,vs2003
- Hibernate入门 - Transaction
- WebService中事务的多种控制方法
- jQuery选择器
- 学妹的哭诉
- 老公与老婆
- 微软20日将验证Windows与Office 盗版将黑屏
- jdk1.4和6.0的一点区别
评论
#1楼 2008-08-09 14:56 haitian
很实用的.谢谢分享!! 回复 引用 查看
#2楼 2008-08-18 09:31 coco333 [未注册用户]
第三部分:SQL Server 存儲過程中調用DTS, 我试验确实是成功的,但是为什么执行过后,DTS依然没有执行呢?直接执行DTS就可以 回复 引用
#3楼 2008-08-18 09:33 coco333 [未注册用户]
我就改了 @PackageName='DTSTest', 这个名字为自己的DTS包的名字,其余的未动 ,请指教,急 回复 引用
#4楼 [楼主] 2008-08-21 10:32 ZY.Zhou
@coco333
我检查了下我的代码,发现少了些内容,报歉,可能在我复制的时候出错了
请你在销毁对象前加上如下代码,就能运行DTS了
--運行DTS
print 'start to execute'
exec @hr = sp_OAMethod @object,'Execute'
if @hr <> 0
begin
print 'Execute Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return
end
print 'Execute Successful'
PS:给大家造成的困扰表示歉意
回复 引用 查看
#5楼 2008-09-10 15:39 wangjianguo [未注册用户]
指定的 DTS 包('名称 = 'testdts'; ID.VersionID = {[未指定]}.{[未指定]}')不存在。
请问这个存储过程怎么调用啊 回复 引用
#6楼 2008-09-16 19:11 zzyyll2 [未注册用户]
--引用--------------------------------------------------
wangjianguo: 指定的 DTS 包('名称 = 'testdts'; ID.VersionID = {[未指定]}.{[未指定]}')不存在。
请问这个存储过程怎么调用啊
--------------------------------------------------------
你要先在本机(localhost)上随便建立一个DTS,名称叫testdts,我这个例子是调用本机上名称叫testdts的一个DTS,在测试之前要先建立好