c#连接oracle查询

环境vs2008+oracle10g

//其它略

//using System.Data.OracleClient;
using Oracle.DataAccess.Client;  //如果没有,好像有装ODP,VS2008自带
public partial class pan_OracleData : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = binddata();
            Response.Write("合计:"+heji(dt));
            Response.Write("<br />");
            Response.Write("行数:"+dt.Rows.Count);
          
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
        }
    }

    public static decimal heji(DataTable dt)
    {
        decimal i = 0;
        foreach (DataRow row in dt.Rows)
        {
            if (row["AMOUNT"] == DBNull.Value)
                continue;
            i += Convert.ToDecimal(row["AMOUNT"]);
        }
        return i;
    }

    public DataTable binddata()
    {       
        string strCnn = "Data Source=服务;User ID=用户名;Password=pwd";
        OracleConnection cnn = new OracleConnection(strCnn);       
        OracleCommand cmm = new OracleCommand();  
        cmm.Connection = cnn;
        cmm.CommandType = CommandType.StoredProcedure;
        cmm.CommandText = "PKG_AM_Report.P_ProfitsAnalysis";

       OracleParameter param1 = new OracleParameter("v_StatisticsDate", OracleDbType.NVarchar2, ParameterDirection.Input);
       param1.Value = "2009-07-07";

       OracleParameter param2 = new OracleParameter("v_BranchFlag",OracleDbType.NVarchar2, ParameterDirection.Input);
       param2.Value = "fdj";

       OracleParameter param3 = new OracleParameter("cur_name", OracleDbType.RefCursor, ParameterDirection.Output);

       cmm.Parameters.Add(param1);
       cmm.Parameters.Add(param2);
       cmm.Parameters.Add(param3);

        try
        {
            cnn.Open();
            OracleDataAdapter ad = new OracleDataAdapter(cmm);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds.Tables[0];
        }
        catch (Exception e)
        {
            Console.Write(e.Message);
            Console.Read();
        }
        finally
        {
            cnn.Close();
        }
        return null;

    }
}

 

 数据库方面:

--包头

CREATE OR REPLACE PACKAGE PKG_AM_Report
AS
TYPE t_cursor IS REF CURSOR;   
PROCEDURE P_ProfitsAnalysis (V_StatisticsDate IN varchar2, V_BranchFlag IN VARCHAR2,cur_name OUT t_cursor);
END PKG_AM_Report;

 

--包体

 

CREATE OR REPLACE PACKAGE BODY PKG_AM_Report
AS

PROCEDURE P_ProfitsAnalysis ( V_StatisticsDate IN varchar2,V_BranchFlag IN VARCHAR2, cur_name OUT t_cursor )               
IS
    v_firstDate  VARCHAR2(10);
    v_month  VARCHAR2(7);
BEGIN

v_month:=substr(V_StatisticsDate,1,7);
v_firstDate:=v_month||'-01';
OPEN cur_name FOR

      select * from table;

END P_ProfitsAnalysis;
END PKG_AM_Report;