C#连接Oracle全攻略：ManagedDataAccess与Dapper实战详解

简介面向C#开发者聚焦Visual Studio 2010下通过ODP.NET操作Oracle数据库这份压缩包提供完整增删改查源码示例覆盖连接数据库、创建表、插入数据、查询结果、更新字段与删除记录等核心操作基本对应企业级数据访问中最常用的需求。资源共28个文件以9个C#源文件为主体包含登录窗体、主窗体及DataLayer数据访问层另有8个图片文件、3个资源文件、3个图标文件以及工程配置文件压缩包仅34KB轻量易用。整套代码采用WinForms界面展示配合OracleConnection、OracleCommand、OracleDataReader等常用类直观呈现C#与Oracle交互的完整流程尤其是连接字符串配置、命令对象执行和结果集读取部分适合初学者快速搭建开发环境也可作为日常开发的参考片段。目前已有334人学习适合需要入门Oracle数据库编程、了解ADO.NET数据访问或补充项目实操经验的开发者。 最近在搞一个数据采集项目上位机用C#数据库用的Oracle核心需求就是那老三样——增删改查。本来以为轻车熟路结果真上手才发现C#连Oracle这事文档少、坑多、网上答案还老掉牙。折腾了几天踩了N个坑之后把这套东西彻底捋顺了。今天就把我的实操过程完整记录下来从环境搭建到增删改查的完整代码再到分页、批量操作这些进阶玩法最后是踩坑实录全给出来。这篇文章适合谁看如果你正准备在C#项目里接入Oracle或者在用Oracle.ManagedDataAccess的时候碰到各种奇怪问题又或者想了解参数化查询、事务处理、批量DML、调用存储过程的正确姿势那这篇内容能帮你省下不少时间。1. 整体设计与技术选型为什么这么搭1.1 连接方案的几代更迭C#连Oracle历史上大概有三条路。最老的是System.Data.OracleClient微软自己出的但老早就标记为过时了在.NET Framework 4.0之后基本就是废弃状态性能和安全都有隐患现在完全不该用了。其次是Oracle.DataAccessODP.NET这是Oracle官方提供的功能很强性能也不错但有个致命伤——它分.NET Framework版本和.NET Core/.NET 5版本而且必须安装Oracle Client客户端部署的时候光配置环境就能让人崩溃。我这次用的是第三条路Oracle.ManagedDataAccess也就是ODP.NET Core版本Managed驱动。它是Oracle官方出品最核心的优势是纯托管代码不需要单独安装Oracle Client一个DLL搞定所有事。部署的时候把Oracle.ManagedDataAccess.dll直接扔到运行目录就行适配Windows、Linux都没问题。对于.NET Core/.NET 5项目来说这几乎是唯一的选择也是Oracle官方主推的方向。1.2 为什么推荐Dapper搭配使用原生ADO.NET写增删改查代码量大容易出错尤其是参数管理这块写多了真的烦。我的做法是数据访问层用原生ADO.NET封装底层但业务层直接用Dapper做对象映射。Dapper是一个轻量级ORM核心是扩展IDbConnection接口性能接近原生ADO.NET但写出来的代码干净得多。这套组合的逻辑是这样的Dapper负责对象映射和SQL执行底层还是Oracle.ManagedDataAccess来跟Oracle通信。既保留了原生驱动的性能和灵活性又免去了手写DataReader逐行读取、手工赋值给实体类的枯燥工作。项目中引用Dapper只需要NuGet装一个包非常简单。这套方案解决的核心问题是部署方便无需客户端、性能好原生驱动Dapper的轻量映射、代码维护性高SQL清晰、参数化彻底、跨平台能力强.NET Core全支持。2. 环境准备与基础连接搭建2.1 必要的包引用用NuGet装两个包就行Install-Package Oracle.ManagedDataAccess.Core Install-Package Dapper注意版本匹配问题。我用的是.NET 6Oracle.ManagedDataAccess.Core要选3.21.x以上版本这个版本开始支持.NET 6。如果你还在用.NET Core 3.1就选2.19.x。装错版本会出现运行时异常这种问题排查起来很消耗时间。Dapper方面2.x版本都很稳定直接装最新版就行。Dapper对Oracle的支持还算不错但注意一个问题——Oracle的SQL语法和SQL Server有差异Dapper默认的某些行为比如参数前缀需要配置一下。2.2 连接字符串的坑与解决方案Oracle的连接字符串格式跟SQL Server差异很大第一次接触时很容易搞晕。最简单可靠的写法string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;;这种写法有几个关键点User Id / Password当然是你自己的Oracle账号密码。Data Source的三段式格式IP地址:端口/服务名1521是Oracle默认端口ORCL是服务名Service Name不是SID。如果客户环境用的是SID而不是Service Name格式是Data Source192.168.1.100:1521/ORCL;—— 注意这里依然用/连接这种写法同时兼容SID和Service Name。还有更完整的连接串我建议把连接池参数也加上string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;Poolingtrue;Min Pool Size1;Max Pool Size100;Connection Lifetime300;;连接池很有必要聊一下。Oracle连接建立的代价非常高TCP握手认证会话初始化一套流程下来可能几十到几百毫秒。如果每次都新建连接、用完就销毁性能损耗极大。开了连接池之后物理连接复用应用程序拿到的只是连接池里借出来的引用速度提升非常明显。实际测试下来不开连接池的情况下一次查询可能250ms其中连接建立占了200ms开连接池后后续查询稳定在10~30ms这个差距在高频访问的场景下是决定性的。2.3 最简单的连通性测试配置完连接串先写个小方法验证一下网络和权限using Oracle.ManagedDataAccess.Client; public static bool TestConnection(string connStr) { try { using (var conn new OracleConnection(connStr)) { conn.Open(); Console.WriteLine(conn.ServerVersion); return true; } } catch (Exception ex) { Console.WriteLine($连接失败: {ex.Message}); return false; } }这里用using保证连接用完后会释放。如果打印出了版本号说明连接有效如果抛异常优先检查网络通不通、防火墙有没有放行1521端口、账号密码对不对、服务名是否正确。3. 增删改查的完整实现从零到可用3.1 SQL参数化的正确姿势这一节是整个文章的核心请着重看。很多新手喜欢拼接SQL字符串比如// 反面教材千万别模仿 string sql $SELECT * FROM USERS WHERE NAME {inputName};这种方式极其危险。SQL注入是一方面更重要的是Oracle处理这种SQL的方式——每次SQL文本不同硬解析每次都要重新生成执行计划性能消耗很大。而且字符串拼接还得处理单引号转义各种边界情况极易出错。正确的做法是参数化查询。Oracle的参数化语法是冒号:前缀这一点跟SQL Server的不一样很多人在这里踩坑。看例子using Dapper; public class UserInfo { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public string Email { get; set; } } public static ListUserInfo GetAllUsers() { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql SELECT ID, NAME, AGE, EMAIL FROM USERS ORDER BY ID DESC; using (var conn new OracleConnection(connStr)) { return conn.QueryUserInfo(sql).ToList(); } }用Dapper时查询结果会按照列名自动映射到UserInfo对象的同名属性。ID列对应Id属性NAME对应Name属性注意Oracle列名默认是大写Dapper的映射默认不区分大小写所以这里没问题。但如果你的属性名和列名完全对不上比如列叫USER_NAME属性叫Name就需要用SQL别名处理SELECT USER_NAME AS Name FROM USERS。3.2 单条记录的增删改查实例新增一条记录的正确写法public static int AddUser(UserInfo user) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql INSERT INTO USERS(NAME, AGE, EMAIL) VALUES(:NAME, :AGE, :EMAIL); using (var conn new OracleConnection(connStr)) { return conn.Execute(sql, new { NAME user.Name, AGE user.Age, EMAIL user.Email }); } }注意这里匿名对象的属性名是NAME、AGE、EMAIL必须和SQL里的参数名:NAME保持一致不区分大小写但建议全大写跟Oracle习惯对齐。Execute返回受影响的行数一般约定大于0就是插入成功。更新记录其实类似public static int UpdateUser(UserInfo user) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql UPDATE USERS SET NAME :NAME, AGE :AGE, EMAIL :EMAIL WHERE ID :ID; using (var conn new OracleConnection(connStr)) { return conn.Execute(sql, new { NAME user.Name, AGE user.Age, EMAIL user.Email, ID user.Id }); } }删除记录public static int DeleteUser(int userId) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql DELETE FROM USERS WHERE ID :ID; using (var conn new OracleConnection(connStr)) { return conn.Execute(sql, new { ID userId }); } }查询单条记录public static UserInfo GetUserById(int userId) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql SELECT ID, NAME, AGE, EMAIL FROM USERS WHERE ID :ID; using (var conn new OracleConnection(connStr)) { return conn.QueryFirstOrDefaultUserInfo(sql, new { ID userId }); } }如果查不到记录QueryFirstOrDefault会返回null记得在业务层做判空处理。3.3 事务处理一次性操作多张表业务里经常遇到一个操作要写多张表的场景比如创建订单要同时更新订单表和库存表。如果中间某一步失败前一步已经写入了数据就不一致了。这种情况必须用事务。public static bool CreateOrder(OrderInfo order, ListOrderDetail details) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; using (var conn new OracleConnection(connStr)) { conn.Open(); using (var tx conn.BeginTransaction()) { try { string sqlOrder INSERT INTO ORDERS(ORDER_NO, CUSTOMER_NAME, TOTAL_AMOUNT, CREATE_TIME) VALUES(:ORDER_NO, :CUSTOMER_NAME, :TOTAL_AMOUNT, SYSDATE); conn.Execute(sqlOrder, new { ORDER_NO order.OrderNo, CUSTOMER_NAME order.CustomerName, TOTAL_AMOUNT order.TotalAmount }, tx); string sqlDetail INSERT INTO ORDER_DETAILS(ORDER_NO, PRODUCT_ID, QUANTITY, PRICE) VALUES(:ORDER_NO, :PRODUCT_ID, :QUANTITY, :PRICE); foreach (var item in details) { conn.Execute(sqlDetail, new { ORDER_NO order.OrderNo, PRODUCT_ID item.ProductId, QUANTITY item.Quantity, PRICE item.Price }, tx); } // 所有SQL都要传入事务对象tx tx.Commit(); return true; } catch (Exception ex) { tx.Rollback(); Console.WriteLine($事务失败已回滚: {ex.Message}); return false; } } } }这里有几个细节值得注意。事务要求连接必须先Open()之后才能BeginTransaction()。Dapper的Execute方法要传入事务对象tx作为参数很多人忘了传这个参数导致SQL在隐式独立事务中执行外层事务回滚不了这部分数据。回滚后连接状态需要重新处理一般建议直接释放连接让连接池重新创建。补充代码public class OrderInfo { public string OrderNo { get; set; } public string CustomerName { get; set; } public decimal TotalAmount { get; set; } } public class OrderDetail { public string OrderNo { get; set; } public string ProductId { get; set; } public int Quantity { get; set; } public decimal Price { get; set; } }4. 进阶实战分页、批量操作与存储过程调用4.1 Oracle分页查询的三种写法分页是增删改查里最常遇到的需求之一Oracle和SQL Server的写法差距很大。SQL Server用OFFSET...FETCH或ROW_NUMBER()MySQL用LIMIT...OFFSETOracle原生不支持这些写法。我常用的有三种方案方案一ROWNUM分页Oracle经典写法兼容性最好public static ListUserInfo GetUsersByPage(int pageIndex, int pageSize) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql SELECT * FROM ( SELECT T.*, ROWNUM AS RN FROM ( SELECT ID, NAME, AGE, EMAIL FROM USERS ORDER BY ID DESC ) T WHERE ROWNUM :MAX_ROW ) WHERE RN :MIN_ROW; int maxRow pageIndex * pageSize; int minRow (pageIndex - 1) * pageSize 1; using (var conn new OracleConnection(connStr)) { return conn.QueryUserInfo(sql, new { MAX_ROW maxRow, MIN_ROW minRow }).ToList(); } }ROWNUM是Oracle在结果返回前分配的序号它的计算时机是在WHERE子句执行之前所以不能直接在WHERE里用ROWNUM 100这种条件必须嵌套子查询。这也是为什么写了两层嵌套——内层先排序并限制最大行号外层再过滤最小行号。方案二OFFSET...FETCHOracle 12c及以上可用string sql SELECT ID, NAME, AGE, EMAIL FROM USERS ORDER BY ID DESC OFFSET :OFFSET_ROW ROWS FETCH NEXT :PAGE_SIZE ROWS ONLY;如果你的客户用的Oracle 12c以上版本直接用它语法更清晰。当然罗如果客户还在Oracle 11g那只能用ROWNUM方案所以做ToB项目的时候一定要先确认数据库版本。方案三ROW_NUMBER()窗口函数string sql SELECT ID, NAME, AGE, EMAIL FROM ( SELECT T.*, ROW_NUMBER() OVER (ORDER BY ID DESC) AS RN FROM USERS T ) WHERE RN BETWEEN :MIN_ROW AND :MAX_ROW;三种方案都能用但性能上方案一和方案二在数据量大时差异不大关键是排序字段一定要有索引否则数据量大了全表扫描会让你怀疑人生。4.2 批量插入别用循环单条插入循环单条插入在数据量小的时候无所谓但如果要插入几千条性能完全不可接受。Oracle批量插入主要有两个方向方向一Oracle.ManagedDataAccess的OracleBulkCopy类似SQL Server的SqlBulkCopypublic static void BulkInsertUsers(DataTable dt) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; using (var conn new OracleConnection(connStr)) { conn.Open(); using (var bulk new OracleBulkCopy(conn)) { bulk.DestinationTableName USERS; bulk.ColumnMappings.Add(NAME, NAME); bulk.ColumnMappings.Add(AGE, AGE); bulk.ColumnMappings.Add(EMAIL, EMAIL); bulk.BatchSize 1000; bulk.WriteToServer(dt); } } }DataTable的列名和类型要跟目标表匹配OracleBulkCopy的性能非常好上万行的数据秒级完成。注意如果需要自动生成ID先要从序列获取或生成好再放进DataTable。方向二Oracle的数组绑定Array Binding适合用官方驱动直接写public static void ArrayBindInsert(ListUserInfo users) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; string sql INSERT INTO USERS(NAME, AGE, EMAIL) VALUES(:NAME, :AGE, :EMAIL); using (var conn new OracleConnection(connStr)) { conn.Open(); using (var cmd new OracleCommand(sql, conn)) { cmd.ArrayBindCount users.Count; cmd.Parameters.Add(:NAME, OracleDbType.Varchar2).Value users.Select(u u.Name).ToArray(); cmd.Parameters.Add(:AGE, OracleDbType.Int32).Value users.Select(u u.Age).ToArray(); cmd.Parameters.Add(:EMAIL, OracleDbType.Varchar2).Value users.Select(u u.Email).ToArray(); cmd.ExecuteNonQuery(); } } }数组绑定减少了客户端和数据库之间的往返次数一次性把数组发给服务器处理。4.3 调用Oracle存储过程存储过程在传统架构里用得很多尤其是一些复杂的报表、批处理任务。C#调用Oracle存储过程要注意命令类型和参数方向的设置。假设数据库里有一个存储过程CREATE OR REPLACE PROCEDURE SP_GET_USER_COUNT( P_MIN_AGE IN NUMBER, P_COUNT OUT NUMBER ) AS BEGIN SELECT COUNT(*) INTO P_COUNT FROM USERS WHERE AGE P_MIN_AGE; END;C#调用代码public static int CallProcGetUserCount(int minAge) { string connStr User Idscott;Passwordtiger;Data Source192.168.1.100:1521/ORCL;; using (var conn new OracleConnection(connStr)) { conn.Open(); using (var cmd new OracleCommand(SP_GET_USER_COUNT, conn)) { cmd.CommandType CommandType.StoredProcedure; cmd.Parameters.Add(P_MIN_AGE, OracleDbType.Int32).Value minAge; cmd.Parameters.Add(P_COUNT, OracleDbType.Int32, ParameterDirection.Output); cmd.ExecuteNonQuery(); return Convert.ToInt32(cmd.Parameters[P_COUNT].Value); } } }这里有几个关键点。CommandType必须设置为CommandType.StoredProcedure否则驱动会把它当SQL语句执行。输出参数P_COUNT必须指定ParameterDirection.Output而且参数名要和存储过程定义保持一致。执行完ExecuteNonQuery之后要把输出参数从Parameters集合里取出来类型转换要小心Oracle返回的可能是decimal。如果存储过程返回游标OPEN cursorC#这边用OracleDataReader接收cmd.Parameters.Add(P_CURSOR, OracleDbType.RefCursor, ParameterDirection.Output); using (var reader cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader[NAME].ToString()); } }RefCursor是Oracle特有的游标类型只能作为输出参数接收不能作为输入参数传进去。5. 常见问题与排查技巧实录5.1 ORA-00933/00904SQL语法错误ORA-00933是SQL命令未正确结束ORA-00904是无效标识符。这两个错误基本都是SQL文本写错了。最常见的原因是SQL Server习惯的写法直接拿到Oracle用——比如GETDATE()在Oracle里不存在要换成SYSDATETOP n在Oracle里用ROWNUM字符串拼接用||而不是。还有表名或列名用了Oracle的保留字比如有张表叫ORDER、GROUP查询时必须加双引号SELECT * FROM ORDER——但强烈不建议这么设计表名。5.2 ORA-01017用户名/密码无效这个错很直白检查账号密码。但有个隐蔽的坑Oracle的用户名密码大小写敏感而且默认情况下如果你用CREATE USER scott IDENTIFIED BY tiger这种不带引号的SQL密码会被转成大写如果带了引号IDENTIFIED BY tiger密码就区分大小写了。连接串里的密码必须严格匹配一个字符都不能差。5.3 ORA-12514/12541监听器问题ORA-12514通常是服务名不对ORA-12541通常是监听器没启动。排查思路是先用telnet测端口通不通telnet 192.168.1.100 1521。端口不通查防火墙、Oracle服务是否启动Windows上是OracleService和OracleOraDb11g_home1TNSListener服务端口通了查服务名对不对。在Oracle服务器上执行lsnrctl status看监听器状态用sqlplus / as sysdba进去执行show parameter service_names;查看服务名。注意连接串里的服务名不是实例名SID两回事搞混了就会报ORA-12514。5.4 中文乱码问题C#写入Oracle后读出来中文乱码十有八九是字符集不匹配。Oracle服务端的字符集是AL32UTF8或ZHS16GBK而C#这边传过去的是UTF-16驱动会自动转。但如果数据库字符集本身跟应用程序预期不一致就会出现乱码。解决办法是统一建议Oracle用AL32UTF8字符集。连接串可以加UnicodeTrue参数让驱动按UTF-16处理参数绑定。5.5 性能问题连接池耗尽与慢查询连接池耗尽容易出现在高频访问场景。现象是程序跑一段时间后操作数据库就卡住或者报超时。排查方向检查代码中每次conn有没有正确释放using关键字是最简单的保障如果连接串里Max Pool Size太小加大到200或更高用SELECT COUNT(*) FROM V$SESSION WHERE USERNAMESCOTT查数据库当前会话数看会话是否堆积查慢SQLSELECT * FROM V$SQL WHERE ELAPSED_TIME 10000000 ORDER BY ELAPSED_TIME DESC。慢查询九成是缺索引。5.6 本地时间写入Oracle的DATE类型处理Oracle的DATE类型包含日期和时间C#的DateTime直接绑定可以但要注意时区问题。如果服务器跨时区建议连接串里加TimeZoneTrue、TimeZoneOffset08:00之类的参数或者统一用字符串传到Oracle让它自己转换。最简单粗暴的方式绑定参数传DateTime.Now没问题但读取的时候注意reader.GetDateTime()拿到的值是数据库当前会话时区的值如果服务器时间和本地时间不一致别惊讶。写在最后的一点体会C#操作Oracle这套组合表面上就是个数据库访问的事真正深挖进去才发现连接串、参数绑定、事务、分页、批处理、存储过程、字符集、连接池每个环节都有坑。我这次做完项目最大的感受是参数化查询和连接管理是最值得花时间做对的两件事——它们决定了系统的安全性和稳定性其他都是细节。如果你也是刚开始接触Oracle C#建议先不用追求最高级的写法把原生ADO.NET的连接、命令、DataReader完整走一遍流程理解数据是怎么从Oracle流转到C#的再上Dapper做简化这样遇到问题的时候才能快速定位到底是驱动的问题、SQL的问题还是架构的问题。最后分享一个小技巧开发阶段一定要把异常信息完整地打出来包括InnerException。Oracle驱动返回的错误信息里通常藏着关键线索——比如ORA-12899表示字段值超长ORA-01438表示数值精度超限中文描述基本一眼就能看懂问题出在哪。日志别只记一句“操作失败”把SQL文本、参数值、堆栈统统写进去排查效率完全不一样。本文还有配套的精品资源点击获取