（C#.NET）Oracle資料庫操作類

（C#.NET）Oracle資料庫操作類

【轉】http://hi.baidu.com/di_wan/blog/item/e51effd61814bb2a06088b62.html

 

用OracleConnection串連Oracle
在Web.config中配置
<add key="Oracle" value="XXX"/>
<add key="USER" value="XXX"/>
<add key="PASS" value="XXX"/>

------------------------------------------------------------------------------------------

    public System.Data.OracleClient.OracleConnection cnn;

    /// <summary>
    /// 開啟資料庫連接
    /// </summary>
    public void Open()
    {
        cnn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["Oracle"]);
        cnn.Open();
    }

    /// <summary>
    /// 開啟資料庫連接，返回cnn
    /// </summary>
    public OracleConnection OpenCnn()
    {
        cnn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["Oracle"]);
        cnn.Open();
        return (cnn);
    }

    /// <summary>
    /// 關閉資料庫連接
    /// </summary>
    public void Close()
    {
        cnn.Close();
    }

    /// <summary>
    /// 返回DataSet
    /// </summary>
    /// <param name="CmdString"></param>
    /// <param name="TableName"></param>
    /// <returns></returns>
    public DataSet GetDataSet(string CmdString, string TableName)
    {
        Open();
        OracleDataAdapter myDa = new OracleDataAdapter();
        myDa.SelectCommand = new OracleCommand(CmdString, cnn);
        DataSet myDs = new DataSet();
        myDa.Fill(myDs, TableName);
        Close();
        return myDs;
    }

    /// <summary>
    /// 返回OleDbDataReader
    /// </summary>
    /// <param name="CmdString"></param>
    /// <returns></returns>
    public OracleDataReader GetDataReader(string CmdString)
    {
        Open();
        OracleCommand myCmd = new OracleCommand(CmdString, cnn);
        OracleDataReader myDr = myCmd.ExecuteReader();
        return myDr;
    }

    /// <summary>
    /// 返回影響資料庫的行數
    /// </summary>
    /// <param name="CmdString"></param>
    /// <returns></returns>
    public int ExecuteSQL(string CmdString)
    {
        Open();
        OracleCommand myCmd = new OracleCommand(CmdString, cnn);
        int Cmd = myCmd.ExecuteNonQuery();
        Close();
        return Cmd;
    }

    /// <summary>
    /// 邦定DroDownList
    /// </summary>
    /// <param name="DroDList"></param>控制項名
    /// <param name="sql"></param>連接字串
    /// <param name="TableName"></param>表名
    /// <param name="DataTextFd"></param>提供常值內容的資料來源欄位
    /// <param name="DataValueFd"></param>為清單項目提供值的資料來源欄位
    public void DroList(DropDownList DroDList, string sql, string TableName, string DataTextFd, string DataValueFd)
    {
        Open();
        OracleDataAdapter myDa = new OracleDataAdapter(sql, cnn);
        myDa.SelectCommand.CommandType = CommandType.Text;
        DataSet myDs = new DataSet();
        try
        {
            myDa.Fill(myDs, TableName);
            DroDList.DataSource = myDs.Tables[TableName];
            DroDList.DataTextField = DataTextFd;
            DroDList.DataValueField = DataValueFd;
            DroDList.DataBind();
        }
        catch (Exception e)
        {
            Response.Write(e.Message);
        }
        finally
        {
            Close();
        }
    }