C#調用存儲過程(帶返回值)和SQL Server DTS的方法

542505989

在程序開發中,我們經常要做的事就是訪問資料庫.有時候會調用存儲過程和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的方法,可靈活應用於程式開發中.