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#中調用此存儲過程的代碼

         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).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 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的方法,可靈活應用於程式開發中.