您的当前位置: 源码爱好者 » 网页制作教程(编程教程) » C#技术教程

C# 连接Oracle数据库的方法附代码

  • 标签:数据库连接 Oracle  更新时间:2013-12-10
  • C#与oracle数据库连接的一个DBHelp类,除了实现连接Oracle数据库外,还可执行一些常用的SQL命令,并返回相应结果集,比如执行带参数sql语句或存储过程,返回结果集首行首列的值object或OracleDataReader对象,执行SQL后返回受影响的行数,返回DataSet对象,包括建立Oracle数据库连接对象,并返回一个数据库连接的OracleConnection对象,通过本类可实现一些C#与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;   
        public class OraDBHelper
        {
            public static OracleCommand cmd = null;
            public static OracleConnection conn = null;
            public static string connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            public OraDBHelper()
            { }
            #region 建立Oracle数据库连接对象 
            /// <returns>返回一个数据库连接的OracleConnection对象</returns>   
            public static OracleConnection init()
            {
                try
                {
                    conn = new OracleConnection(connstr);
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message.ToString());
                }
                return conn;
            }
            #endregion
            #region 设置OracleCommand对象 
            /// <param name="cmd">OracleCommand对象 </param>   
            /// <param name="cmdText">命令文本</param>   
            /// <param name="cmdType">命令类型</param>   
            /// <param name="cmdParms">参数集合</param>   
            private static void SetCommand(OracleCommand cmd, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
            {
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (cmdParms != null)
                {
                    cmd.Parameters.AddRange(cmdParms);
                }
            }
            #endregion
            #region 执行相应的Oracle sql语句,返回相应的DataSet对象  
            /// <param name="sqlstr">sql语句</param>   
            /// <returns>返回相应的DataSet对象</returns>   
            public static DataSet GetDataSet(string sqlstr)
            {
                DataSet set = new DataSet();
                try
                {
                    init();
                    OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
                    adp.Fill(set);
                    conn.Close();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message.ToString());
                }
                return set;
            }
            #endregion
            #region 执行sql语句,返回DataSet对象  
            /// <param name="sqlstr">sql语句</param>   
            /// <param name="tableName">表名</param>   
            /// <returns>返回DataSet对象</returns>   
            public static DataSet GetDataSet(string sqlstr, string tableName)
            {
                DataSet set = new DataSet();
                try
                {
                    init();
                    OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
                    adp.Fill(set, tableName);
                    conn.Close();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message.ToString());
                }
                return set;
            }
            #endregion
            #region 执行不带参数的sql语句,返回受影响的行数  
            /// <param name="cmdstr">增,删,改sql语句</param>   
            /// <returns>返回受影响的行数</returns>   
            public static int ExecuteNonQuery(string cmdText)
            {
                int count;
                try
                {
                    init();
                    cmd = new OracleCommand(cmdText, conn);
                    count = cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return count;
            }
            #endregion
            #region 执行带参数的Oracle sql语句或存储过程,返回行数 
            /// <param name="cmdText">带参数的sql语句和存储过程名</param>   
            /// <param name="cmdType">命令类型</param>   
            /// <param name="cmdParms">参数集合</param>   
            /// <returns>返回受影响的行数</returns>   
            public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
            {
                int count;
                try
                {
                    init();
                    cmd = new OracleCommand();
                    SetCommand(cmd, cmdText, cmdType, cmdParms);
                    count = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return count;
            }
            #endregion
            #region 执行不带参数sql语句,返回一个从数据源读取数据的OracleDataReader对象  
            /// <param name="cmdstr">相应的sql语句</param>   
            /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>   
            public static OracleDataReader ExecuteReader(string cmdText)
            {
                OracleDataReader reader;
                try
                {
                    init();
                    cmd = new OracleCommand(cmdText, conn);
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return reader;
            }
            #endregion
            #region 执行带参数的sql语句或存储过程,返回一个从数据源读取数据的OracleDataReader对象 
            /// <param name="cmdText">sql语句或存储过程名</param>   
            /// <param name="cmdType">命令类型</param>   
            /// <param name="cmdParms">参数集合</param>   
            /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>   
            public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
            {
                OracleDataReader reader;
                try
                {
                    init();
                    cmd = new OracleCommand();
                    SetCommand(cmd, cmdText, cmdType, cmdParms);
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return reader;
            }
            #endregion
            #region 执行不带参数sql语句,返回结果集首行首列的值object  
            /// <param name="cmdstr">相应的sql语句</param>   
            /// <returns>返回结果集首行首列的值object</returns>   
            public static object ExecuteScalar(string cmdText)
            {
                object obj;
                try
                {
                    init();
                    cmd = new OracleCommand(cmdText, conn);
                    obj = cmd.ExecuteScalar();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return obj;
            }
            #endregion
            #region 执行带参数sql语句或存储过程,返回结果集首行首列的值object  
            /// <param name="cmdText">sql语句或存储过程名</param>   
            /// <param name="cmdType">命令类型</param>   
            /// <param name="cmdParms">返回结果集首行首列的值object</param>   
            /// <returns></returns>   
            public static object ExecuteScalar(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
            {
                object obj;
                try
                {
                    init();
                    cmd = new OracleCommand();
                    SetCommand(cmd, cmdText, cmdType, cmdParms);
                    obj = cmd.ExecuteScalar();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                return obj;
            }
            #endregion
        }

    上述代码可保存为OraDBHelper.cs文件,在Web.config中,你需要做些设置:

    <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=jde;Persist Security Info=True;User ID=jde;Password=jde;Unicode=True" providerName="System.Data.OracleClient"/>
    </connectionStrings>

    在上边的oracle连接字符串中,name:连接字符串名;connectionString:连接字符串;Data Souuce:数据库名称;如果是远程数据库的话,如果你安装有Oracle的客户端则也可填写数据库名,否则要加一个域;User ID:用户名。

  • 已读: 次 收藏本文关闭本文打印本文复制链接