C# 连接Oracle Demo

最新推荐文章于 2024-08-13 08:47:02 发布

zq爱生活爱代码

最新推荐文章于 2024-08-13 08:47:02 发布

阅读量399

点赞数

分类专栏： C#

本文链接：https://blog.csdn.net/m0_37879526/article/details/105120689

版权

C# 专栏收录该内容

61 篇文章 3 订阅

订阅专栏

安装包 Oracle.ManagedDataAccess

测试时遇到的问题， SERVICE_NAME=ORCL 这个不改，不然会报错TNS:监听程序当前无法识别连接描述符中请求的服务解决方案

然后数据库测试 conn可以open 说明连同了

但是查询需要的SQL还是不行，提示表或视图不存在，但是表是存在的

检查后发现使用的账号没有查询这个表的权限，这边需要DBA 协助了。。。。

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConnectToOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = "User Id=XXX;Password=XXX;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=XXX)))(CONNECT_DATA=(SERVICE_NAME=XXX)))";
            OracleConnection conn = new OracleConnection(connString);
            try
            {
                conn.Open();
                Console.WriteLine(conn.State.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            finally
            {
                conn.Close();
            }
            Console.ReadKey();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using CommonUtilsLibrary.Models;

namespace CommonUtilsLibrary.Utils
{
    public class OracleUtil:IDBUtil
    {
        public OracleUtil(OracleConPara para)
        {
            connStr = string.Format("User Id={0};Password={1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})))", para.UserID,para.Password,para.HostName,para.Port,para.ServiceName);
        }

        private string connStr { get; set; }

        #region 执行SQL语句,返回受影响行数
        public int ExecuteNonQuery(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, new OracleParameter());
        }


        #region 执行SQL语句,返回DataTable;只用来执行查询结果比较少的情况
        public  DataTable ExecuteDataTable(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    return datatable;
                }
            }
        }
        #endregion

        public  DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, new OracleParameter());
        }

        public int UpdateBatchCommand(List<string> commandStringList)
        {
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                OracleTransaction m_OraTrans = conn.BeginTransaction();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    string tmpStr = "";
                    int influenceRowCount = 0;
                    try
                    {
                        foreach (string commandString in commandStringList)
                        {
                            tmpStr = commandString;
                            cmd.CommandText = tmpStr;
                            influenceRowCount += cmd.ExecuteNonQuery();
                        }
                        m_OraTrans.Commit();
                        return influenceRowCount;
                    }
                    catch (OracleException ex)
                    {
                        m_OraTrans.Rollback();
                        throw ex;
                    }
                }
                   
            }
        }

    }
}