-
c#调用存储过程带返回值和SQL Server 的DTS方法
在程序开发中,我们经常要做的事就是访问资料库.有时候会调用存储过程和DTS,下面就此给出例子来说明如何实现
第一部分 调用存储过程(带入参数和取得返回值)
1.调用SQL Server的存储过程
假如我们有如下一个存储过程,很简单的实现,只是将两个传进来值做加法处理然后返回
CREATE PROCEDURE AddMethod以下是在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;
}
2.调用Oralce的存储过程
先创建一个package和package body,代码如下
(1).PackageCREATE 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 Bodies
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#中实现调用.
先来个调用方法的
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();
}
再来个调用存储过程的,返回的是个数据集
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;
}
以上介绍了在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排成排程然后在属性中查看到.
(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
第三部分: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
以上介绍了调用存储过程及DTS的方法,可灵活应用於程式开发中.
2.调用Oralce的存储过程
先创建一个package和package body,代码如下
(1).PackageCREATE 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 Bodies
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#中实现调用.
先来个调用方法的
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();
}
