C# OracleDBhelper

標籤：from   連接字串   row   參數   串連數   splay   pager   procedure   ted   

引用

using System.Configuration;
using System.Data.OracleClient;

 

連接字串

<connectionStrings>    <add name="OracleConnString" connectionString="User ID=xxx;Password=xxx;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=IP地址)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))" /></connectionStrings>

View Code

 

    public static class OracleDBhelper    {        private static OracleConnection cnn = null;        /// <summary>        /// 串連資料庫        /// </summary>        public static OracleConnection Cnn        {            get            {                if (cnn == null)                {                    string cnnstr = ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;                    cnn = new OracleConnection(cnnstr);                    cnn.Open();                }                else if (cnn.State == ConnectionState.Closed)                {                    cnn.Open();                }                else if (cnn.State == ConnectionState.Broken)                {                    cnn.Close();                    cnn.Open();                }                return cnn;            }        }        /// <summary>        /// int 增刪改功能的Oracle語句。實現增刪改功能，返回受影響行數。        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static int ExecuteCommand(string Oracle)        {            int intResult = 0;            try            {                string cnnstr = ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;                Console.Write(cnnstr);                cnn = new OracleConnection(cnnstr);                cnn.Open();                OracleCommand cmm = new OracleCommand(Oracle, cnn);                intResult = cmm.ExecuteNonQuery();                cmm.Dispose();            }            catch (Exception ex)            {                Console.Write(ex.Message);                throw ex;            }            finally            {                cnn.Close();                cnn.Dispose();            }            return intResult;        }        /// <summary>        /// int 增刪改功能的Oracle語句。實現增刪改功能，返回受影響行數。        /// [where條件時綁定資料，防止注入式攻擊]        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static int ExecuteCommand(string Oracle, OracleParameter[] sp)        {            int intResult = 0;            try            {                OracleCommand cmm = new OracleCommand(Oracle, Cnn);                cmm.Parameters.AddRange(sp);                intResult = cmm.ExecuteNonQuery();            }            catch (Exception ex)            {                throw ex;            }            finally            {                cnn.Close();                cnn.Dispose();            }            return intResult;        }        /// <summary>        /// string 查詢返回單行單列的Oracle語句。（ 例：select count(*) from 表名或 select name from 表名 ）。        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static string ExecuteScalar(string Oracle)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            return cmm.ExecuteScalar().ToString();        }        /// <summary>        /// string 查詢返回單行單列的Oracle語句。（ 例：select count(*) from 表名或 select name from 表名 ）。        ///  [where條件時綁定資料，防止注入式攻擊]        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static string ExecuteScalar(string Oracle, OracleParameter[] sp)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            cmm.Parameters.AddRange(sp);            return cmm.ExecuteScalar().ToString();        }        /// <summary>        /// OracleDataReader 查詢返回合格記錄 每次返回一行        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static OracleDataReader ExecuteDataReader(string Oracle)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            return cmm.ExecuteReader();        }        /// <summary>        /// OracleDataReader 查詢返回合格記錄 每次返回一行.        ///  [where條件時綁定資料，防止注入式攻擊]        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static OracleDataReader ExecuteDataReader(string Oracle, OracleParameter[] sp)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            cmm.Parameters.AddRange(sp);            return cmm.ExecuteReader();        }        /// <summary>        /// DataTable 查詢返回合格記錄表        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static DataTable ExcuteDataTable(string Oracle)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            OracleDataAdapter da = new OracleDataAdapter(cmm);            DataSet ds = new DataSet();            da.Fill(ds);            return ds.Tables[0];        }        /// <summary>        /// DataTable 查詢返回合格記錄表        ///  [where條件時綁定資料，防止注入式攻擊]        /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static DataTable ExcuteDataTable(string Oracle, OracleParameter[] sp)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            cmm.Parameters.AddRange(sp);            OracleDataAdapter da = new OracleDataAdapter(cmm);            DataSet ds = new DataSet();            da.Fill(ds);            return ds.Tables[0];        }        /// <summary>        /// DataTable 查詢返回DataTable 帶有分頁參數，startRecord 開始記錄數，maxRecords 最大記錄數 適用於分頁控制項        /// startRecord ：(AspNetPager1.CurrnetPageIndex - 1 )*AspNetPager1.Pagesize        ///  maxRecords：AspNetPager1.pagesize        /// </summary>        /// <param name="Oracle"></param>        /// <param name="startRecord"></param>        /// <param name="maxRecords"></param>        /// <returns></returns>        public static DataTable DataTablePage(string Oracle, int startRecord, int maxRecords)        {            OracleCommand cmm = new OracleCommand(Oracle, Cnn);            OracleDataAdapter sdp = new OracleDataAdapter(cmm);            DataTable dt = new DataTable();            sdp.Fill(startRecord, maxRecords, dt);            return dt;        }        /// <summary>        /// DataTable 查詢返回【預存程序的Oracle語句】        ///         /// </summary>        /// <param name="Oracle"></param>        /// <returns></returns>        public static DataTable ExcuteDataTableByProcedure(string procedure, OracleParameter[] sp)        {            OracleCommand cmm = new OracleCommand(procedure, Cnn);            cmm.CommandType = CommandType.StoredProcedure;            cmm.Parameters.AddRange(sp);            OracleDataAdapter da = new OracleDataAdapter(cmm);            DataSet ds = new DataSet();            da.Fill(ds);            return ds.Tables[0];        }    }

View Code

 

C# OracleDBhelper