.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参数带出结果来解决这个问题。

下面来做个程序测试

<img  src="/static/imghw/default1.png" data-src="pic/20151007/fv4kap0u3kx.png" class="lazy" alt=".NET中使用ORACLE函数和过程并输出参数（1）" ><img  src="/static/imghw/default1.png" data-src="pic/20151007/v00wnsymgll.png" class="lazy" alt=".NET中使用ORACLE函数和过程并输出参数（1）" ><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 （通过参数 数据库类的 三层模式输出）

<img  src="/static/imghw/default1.png" data-src="pic/20151007/fv4kap0u3kx.png" class="lazy" alt=".NET中使用ORACLE函数和过程并输出参数（1）" ><img  src="/static/imghw/default1.png" data-src="pic/20151007/v00wnsymgll.png" class="lazy" alt=".NET中使用ORACLE函数和过程并输出参数（1）" ><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> </p><p><br> </p><p> </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>/// <param name="storedProcName">存储过程名</span>
        <span>/// <param name="parameters">存储过程参数</span>
        <span>/// <param name="rowsAffected">影响的行数</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>/// <param name="storedProcName">存储过程名</span>
        <span>/// <param name="parameters">存储过程参数</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>/// <param name="connection">数据库连接</span>
        <span>/// <param name="storedProcName">存储过程名</span>
        <span>/// <param name="parameters">存储过程参数</span>
        <span>/// OracleCommand</span>
        <span>private</span> <span>static</span> OracleCommand BuildQueryCommand(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;
        }