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;
}
阅读(1517) | 评论(0) | 转发(0) |