oracle数据库操作类

 

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;

namespace Com.AX.MKTJBB.DAL
{
    public class DB
    {
        #region 数据库公共变量
        public OracleConnection MySqlConnection;
        private OracleCommand cmd;
        private OracleDataAdapter MyDataAdapter;
        private DataSet MyDataSet;
        private static String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConn"].ToString();
        #endregion
      

        #region ConnOpen打开数据库连接

        public void ConnOpen()
        {

            if ((MySqlConnection != null) && (MySqlConnection.State == ConnectionState.Open))
            { }
            else
            {
                if (MySqlConnection == null)
                    MySqlConnection = new OracleConnection();
                if (MySqlConnection.State == ConnectionState.Closed)
                {
                    MySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["MyConn"].ToString();
                    MySqlConnection.Open();
                }
            }
        }

        #endregion

        #region ConnClose关闭数据库连接
        public void ConnClose()
        {
            if (MySqlConnection != null)
            {
                MySqlConnection.Dispose();
                MySqlConnection.Close();
            }
        }
        #endregion

        #region 返回Command对象的方法
        /// <summary>
        /// 返回Command对象的方法
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="parames">参数列表</param>
        /// <returns>SqlCommand对象</returns>
        private static OracleCommand GetCommand(string sql, params OracleParameter[] parames)
        {
            OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConn"].ToString());
            OracleCommand cmd = new OracleCommand(sql, connection);
            //加入参数列表
            if (parames != null)
                cmd.Parameters.AddRange(parames);
            //返回对象
            return cmd;
        }
        #endregion

        #region ExecuteScalar
        public static object ExecuteScalar(string sql, params OracleParameter[] cmdParms)
        {
           using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand(sql, connection);
                if (cmdParms != null)
                    cmd.Parameters.AddRange(cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                connection.Close();
                return val;
            }
        }

        public static object ExecuteScalar(string sql)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand(sql, connection);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                connection.Close();
                return val;
            }
        }
        #endregion

 

        #region ExecuteSQL执行SQL语句
        public static int ExecuteSQL(string strSql)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                int result = 0;
                try
                {
                    connection.Open();
                    OracleCommand oracmd = new OracleCommand(strSql, connection);
                    result= oracmd.ExecuteNonQuery();
                    oracmd.Dispose();
                }
                catch (Exception e)
                {
                    System.Console.Write(e.Message);
                }
                finally
                {
                   
                    connection.Close();
                }
                return result;
            }
        }
        /// <summary>
        /// 增,删,改的方法
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="parames">参数类表</param>
        /// <returns>int数字</returns>
        public static int ExecuteSQLPara(string sql, params OracleParameter[] parames)
        {
            OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConn"].ToString());
            OracleCommand cmd = new OracleCommand(sql, connection);
            //加入参数列表
            if (parames != null)
                cmd.Parameters.AddRange(parames);

            int result = 0;
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return result;
        }
        #endregion

        #region GetDataReaderBySQL通过SQL语句返回DataReader
        public OracleDataReader GetDataReaderBySQL(string sSQL)
        {
            ConnOpen();
            cmd = new OracleCommand(sSQL, MySqlConnection);
            return cmd.ExecuteReader();
        }
        public OracleDataReader GetDataReaderBySQLPara(string sql, params OracleParameter[] parames)
        {
            ConnOpen();
            OracleCommand mycmd = new OracleCommand(sql, MySqlConnection);
            if (parames != null)
                mycmd.Parameters.AddRange(parames);
            OracleDataReader mydr = mycmd.ExecuteReader();
            return mydr;
        }
        #endregion

        #region GetDataSetBySql通过SQL语句返回DataSet
        public DataSet GetDataSetBySql(string strSQL)
        {
            ConnOpen();
            MyDataAdapter = new OracleDataAdapter(strSQL, MySqlConnection);
            DataSet MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet);
            return MyDataSet;
        }

        public DataSet GetDataSetBySqlPara(string sql, string tableName, params OracleParameter[] parames)
        {
            DataSet ds = new DataSet();
            OracleDataAdapter dapter = new OracleDataAdapter();
            dapter.SelectCommand = GetCommand(sql, parames);
            try
            {
                dapter.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// 运行SQL语句,返回DataSet对象,用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后RunProc(SQL,ds,1,10,"test"),之后这个ds就可以直接用了
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <param name="Ds">DataSet对象</param>
        /// <param name="StartIndex">开始的页面，第一页是1</param>
        /// <param name="PageSize">每页显示的大小</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public static DataSet RunProc(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    OracleDataAdapter Da = new OracleDataAdapter(SQL, connection);
                    Da.Fill(Ds, StartIndex, PageSize, tablename);
                    connection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }           
                return Ds;
            }
        }

        /// <summary>
        /// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后RunProc(SQL,ds,"test"),之后这个ds就可以直接用了
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <param name="Ds">DataSet对象</param>
        /// <param name="tablename">表名</param>
        public static DataSet RunProc(string SQL, DataSet Ds, string tablename)
        {

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    OracleDataAdapter Da = new OracleDataAdapter(SQL, connection);
                    Da.Fill(Ds, tablename);
                    connection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return Ds;
            }           
        }
        /// <summary>
        /// 运行SQL语句,返回DataSet对象 用法：先实例化一个DataSet，如 DataSet ds=new DataSet;然后MSQL.RunProc(SQL,ds,"test"),之后这个ds就可以直接用了
        /// </summary>
        /// <param name="procName">SQL语句</param>
        /// <param name="DataSet">DataSet对象</param>
        public static DataSet RunProc(string SQL, DataSet Ds)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    OracleDataAdapter Da = new OracleDataAdapter(SQL, connection);
                    Da.Fill(Ds);
                    connection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return Ds;
            }
        }

        public static DataSet RunProc(string SQL,OracleParameter[] param, DataSet Ds)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                     OracleDataAdapter Da = new OracleDataAdapter(SQL, connection);
                     if (param != null)
                         Da.SelectCommand.Parameters.AddRange(param);
                    Da.Fill(Ds);
                    connection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return Ds;
            }
        }
        #endregion

        #region Fill重写
        public void Fill(string sSQL)
        {
            ConnOpen();
            MyDataAdapter = new OracleDataAdapter(sSQL, MySqlConnection);
            MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet);
        }
        public void Fill(string TableName, string sql)
        {
            ConnOpen();
            MyDataAdapter = new OracleDataAdapter(sql, MySqlConnection);
            MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet, TableName);
        }

        public void Fill(string TableName, string sql, int start, int end)
        {
            ConnOpen();
            MyDataAdapter = new OracleDataAdapter(sql, MySqlConnection);
            MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet, start, end, TableName);
        }
        #endregion

 

        #region 六种类型控件数据绑定
        public void BindGridView(string sSQL, GridView MyGridView)
        {
            Fill(sSQL);
            MyGridView.DataSource = MyDataSet.Tables[0].DefaultView;
            MyGridView.DataBind();
        }
        public void BindGridView(string sSQL, int StartRecord, int RecordNum, GridView MyGridView)
        {
            MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet, StartRecord, RecordNum, "tempTable");
            MyGridView.DataSource = MyDataSet.Tables[0].DefaultView;
            MyGridView.DataBind();
        }
        public void BindDataList(string sSQL, DataList MyDataList)
        {
            Fill(sSQL);
            MyDataList.DataSource = MyDataSet.Tables[0].DefaultView;
            MyDataList.DataBind();
        }
        public void BindDataList(string sSQL, int StartRecord, int RecordNum, DataList MyDataList)
        {
            MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet, StartRecord, RecordNum, "tempTable");
            MyDataList.DataSource = MyDataSet.Tables["tempTable"].DefaultView;
            MyDataList.DataBind();
        }
        public void BindRepeater(string sSQL, Repeater MyRepeater)
        {
            Fill(sSQL);
            MyRepeater.DataSource = MyDataSet.Tables[0].DefaultView;
            MyRepeater.DataBind();
        }
        public void BindDropDownList(string sSQL, string ShowField, string ValuesField, DropDownList MyDDl)
        {
            Fill(sSQL);
            MyDDl.DataSource = MyDataSet.Tables[0].DefaultView;
            MyDDl.DataTextField = ShowField;
            MyDDl.DataValueField = ValuesField;
            MyDDl.DataBind();
            ConnClose();
        }
        #endregion

    }

   
}