.NET中使用ORACLE函数和过程并输出参数（1）

.net,ADO.NET,oracle,DBHELPERORA 我们知道.NET中调用ORACLE 的过程没有调用MS-SQLSERVER中的存储过程那么方便，尤其用到参数输出的时候，但是ORACLE的功能强大不会这个不能处理，这个你懂得，呵呵。 2个解决方案，一个是ORACLE中的函数可以带参数输出的，这

.net,ADO.NET,oracle,DBHELPERORA

我们知道.NET中调用ORACLE 的过程没有调用MS-SQLSERVER中的存储过程那么方便，尤其用到参数输出的时候，但是ORACLE的功能强大不会这个不能处理，这个你懂得，呵呵。

2个解决方案，一个是ORACLE中的函数可以带参数输出的，这个比较吻合SQLSERVER中的存储过程（个人比较意见用 ORACLE中的函数应对SQLSERVER中的存储过

程。

二就是用PROCEDURE 的OUT参数带出结果来解决这个问题。

下面来做个程序测试

<span>qiantian</span>" runat="<span>server</span>">    <p>            Button1" runat="<span>server</span>" OnClick="<span>Button1_Click</span>" Text="<span>测试FUNCTION输出</span>" />        button2" runat="<span>server</span>" OnClick="<span>button2_Click</span>" Text="<span>测试PROCEDUCE输出</span>" />        <br />        Button3" runat="<span>server</span>" Text="<span>测试FUNCTION三层</span>" OnClick="<span>Button3_Click</span>" />        Button4" runat="<span>server</span>" Text="<span>测试PROCEDURE三层</span>" OnClick="<span>Button4_Click</span>" />             </p>    

后台代码 针对测试FUNCTION输出（直接写在代码里面）

       <span>protected</span> <span>void</span> Button1_Click(<span>object</span> sender, EventArgs e)        {            OracleConnection conn = <span>new</span> OracleConnection("<span>Data Source=yu;Persist Security Info=True;User ID=$$$$$$;Password=$$$$$$$$;Unicode=True;</span>");            OracleCommand cmd = <span>new</span> OracleCommand();            cmd.Connection = conn;            cmd.CommandText = "<span>F_ACC_CREATEPERMISSION</span>";            cmd.CommandType = CommandType.StoredProcedure;            <span>// add the parameters, including the return parameter to retrieve            </span>            cmd.Parameters.Add("<span>CategoryID</span>", OracleType.Number).Value = 555;            cmd.Parameters.Add("<span>Description</span>", OracleType.VarChar, 50).Value = "<span>zzz1</span>";<span>// the return value </span>            cmd.Parameters.Add("<span>Result</span>", OracleType.Number).Direction = ParameterDirection.ReturnValue;            <span>// execute the function</span>            conn.Open();            cmd.ExecuteNonQuery();            conn.Close();            <span>// output the result</span>            Response.Write("<span>Resultis: </span>" + cmd.Parameters["<span>Result</span>"].Value);        }

后台代码 针对测试FUNCATION （通过参数 数据库类的 三层模式输出）

<span>       <span>protected</span> <span>void</span> Button3_Click(<span>object</span> sender, EventArgs e)        {         <span>//   Accounts.Bus.Permissions myperm = new Accounts.Bus.Permissions();</span>            Accounts.Data.Permission myperm = <span>new</span> Accounts.Data.Permission();            <span>int</span> i = myperm.Create(555, "<span>zzz3</span>");            Response.Write(i.ToString());                              }</span><p><br /> </p>

PERMISSION 类中代码

        <span>/// </span>        <span>/// 创建一个权限</span>        <span>/// </span>        <span>public</span> <span>int</span> Create(<span>int</span> categoryID, <span>string</span> description)        {            <span>int</span> rowsAffected;            OracleParameter[] parameters = 				{					<span>new</span> OracleParameter("<span>CategoryID</span>", OracleType.Number),					<span>new</span> OracleParameter("<span>Description</span>", OracleType.VarChar,50) 				};            parameters[0].Value = categoryID;            parameters[1].Value = description;            <span>return</span> DbHelperOra.RunProcedure("<span>F_ACC_CREATEPERMISSION</span>", parameters, <span>out</span> rowsAffected);                  }

DbHelperOra中的代码

   <span>/// </span>        <span>/// 执行存储过程，返回影响的行数	对应ORACLE 的FUNCTION 的RETURN用的	</span>        <span>/// </span>        <span>/// 存储过程名</span>        <span>/// 存储过程参数</span>        <span>/// 影响的行数</span>        <span>/// </span>        <span>public</span> <span>static</span> <span>int</span> RunProcedure(<span>string</span> storedProcName, IDataParameter[] parameters, <span>out</span> <span>int</span> rowsAffected)        {            <span>using</span> (OracleConnection connection = <span>new</span> OracleConnection(connectionString))            {                <span>int</span> result;                connection.Open();                OracleCommand command = BuildIntCommand(connection, storedProcName, parameters);                rowsAffected = command.ExecuteNonQuery();                result = <span>int</span>.Parse(command.Parameters["<span>ReturnValue</span>"].Value.ToString());                <span>//Connection.Close();</span>                <span>return</span> result;            }        }

BuildIntCommand调用的代码段

        <span>/// </span>        <span>/// 创建OracleCommand 对象实例(用来返回一个整数值)	</span>        <span>/// </span>        <span>/// 存储过程名</span>        <span>/// 存储过程参数</span>        <span>/// OracleCommand 对象实例</span>        <span>private</span> <span>static</span> OracleCommand BuildIntCommand(OracleConnection connection, <span>string</span> storedProcName, IDataParameter[] parameters)        {            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);            <span>//command.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int32, 4, ParameterDirection.ReturnValue,false, 0, 0, string.Empty, DataRowVersion.Default, null));</span>            command.Parameters.Add("<span>ReturnValue</span>", OracleType.Number).Direction = ParameterDirection.ReturnValue;            <span>return</span> command;        }

BuildQueryCommand调用的代码段

        <span>/// </span>        <span>/// 构建OracleCommand 对象(用来返回一个结果集，而不是一个整数值)</span>        <span>/// </span>        <span>/// 数据库连接</span>        <span>/// 存储过程名</span>        <span>/// 存储过程参数</span>        <span>/// OracleCommand</span>        <span>private</span> <span>static</span> OracleCommand BuildQueryCom<strong>本文来源gaodai#ma#com搞@@代~&码网</strong>mand(OracleConnection connection, <span>string</span> storedProcName, IDataParameter[] parameters)        {            OracleCommand command = <span>new</span> OracleCommand(storedProcName, connection);            command.CommandType = CommandType.StoredProcedure;            <span>foreach</span> (OracleParameter parameter <span>in</span> parameters)            {                command.Parameters.Add(parameter);            }            <span>return</span> command;        }

喜欢 (0)赏

[搞代码]

分享 (0)