C#調用存儲過程(帶返回值)和SQL Server DTS的方法
在程序開發中,我們經常要做的事就是訪問資料庫.有時候會調用存儲過程和DTS,下面就此給出例子來說明如何實現第一部分 調用存儲過程(帶入參數和取得返回值)
1.調用SQL Server的存儲過程
假如我們有如下一個存儲過程,很簡單的實現,只是將兩個傳進來值做加法處理然后返回
(
@returnvalue int OUTPUT, -- 返回結果
@Parameter_1 int , -- 參數一
@Parameter_2 int -- 參數二
)
AS
-- ---------------------------------------------
set @returnvalue = @Parameter_1 + @Parameter_2
RETURN @returnvalue
-- -----------------------------------------------
GO
{
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
type mytype is ref cursor ;
procedure p_Test(mycs out mytype);
function f_get( str in varchar2 ) return varchar2 ;
end ;
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 ;
先來個調用方法的
{
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.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擴展存儲過程,故需要將調用的用戶開通能訪問此擴展存儲過程的權限.
{
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()方法
{
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 @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的方法,可靈活應用於程式開發中.