Dapper连接Oracle
Dapper连接Oracle
去年写过了篇博客，名字叫：让dapper支持Oracle
网址：http://www.cnblogs.com/ushou/archive/2012/09/28/2706905.html
有很多网友查看并评论，也引来很多网站转载，在这里深表歉意，因为去年搞的思路是错的，我有罪呀。
其实Dapper原生是支持Oracle的，只是去年水平有限，或者说查阅的资料还不够多，所以“毁”人不倦。
今年7月份的时候搞出这个新的Demo,只是一直忙与工作，没来得及分享出来，现将主要代码写出来吧，希望对大家有帮助。
首先创建数据库连接，代码如下：
public class DapperFactory { public static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Wip_TestConnStr"].ToString(); public static OracleConnection CrateOracleConnection() { var connection = new OracleConnection(connectionString); connection.Open(); return connection; } }
其次，基于Oracle，实现简单的增、删、改、查、事务、分页等。
using System; using System.Collections.Generic; using System.Linq; using Dapper; using Tcl.ForLog.Model; using System.Data.OracleClient; using System.Data; namespace Strong.Plm.SqlRepository { public class WUFEI_TESTTBRepository { public bool AddWUFEI_TESTTB(WUFEI_TESTTBModel wufei_testtb) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { string executeSql = @" INSERT INTO WUFEI_TESTTB (USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK ) VALUES (:USER_ID, :USER_NAME, :USER_ADDRESS, :USER_SEX, :USER_BIRTHDAY, :USER_REMARK) "; return conn.Execute(executeSql, wufei_testtb) > 0 ? true : false; } } /// <summary> /// 使用事务批量新增 /// </summary> /// <param name="list"></param> /// <returns></returns> public int AddWUFEI_TestTbByTrans(List<WUFEI_TESTTBModel> list) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { IDbTransaction transaction = conn.BeginTransaction(); int row = 0; foreach (var item in list) { string executeSql = @" INSERT INTO WUFEI_TESTTB (USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK ) VALUES (:USER_ID, :USER_NAME, :USER_ADDRESS, :USER_SEX, :USER_BIRTHDAY, :USER_REMARK) "; row += conn.Execute(executeSql, item, transaction, null, null); } transaction.Commit(); return row; } } public bool DeleteWUFEI_TESTTB(int USER_ID) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { string executeSql = @" DELETE FROM WUFEI_TESTTB WHERE USER_ID = :USER_ID "; var conditon = new { USER_ID = USER_ID }; return conn.Execute(executeSql, conditon) > 0 ? true : false; } } public bool EditWUFEI_TESTTB(WUFEI_TESTTBModel wufei_testtb) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { string executeSql = @" UPDATE WUFEI_TESTTB SET USER_NAME = :USER_NAME,USER_ADDRESS = :USER_ADDRESS,USER_SEX = :USER_SEX,USER_BIRTHDAY = :USER_BIRTHDAY,USER_REMARK = :USER_REMARK WHERE USER_ID = :USER_ID "; return conn.Execute(executeSql, wufei_testtb) > 0 ? true : false; } } public bool UpdateWUFEI_TESTTB(string sqlStr) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { return conn.Execute(sqlStr) > 0 ? true : false; } } public WUFEI_TESTTBModel GetWUFEI_TESTTB(Decimal USER_ID) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { String executeSql = @" SELECT USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK FROM WUFEI_TESTTB WHERE USER_ID = :USER_ID "; var conditon = new { USER_ID = USER_ID }; return conn.Query<WUFEI_TESTTBModel>(executeSql, conditon).SingleOrDefault(); } } public KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>> WUFEI_TESTTBPaginationA(Pagination pagin, WUFEI_TESTTBModel condition) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { String condtionStr = ""; String executeQuery = String.Format(@"SELECT * FROM (SELECT table_source.*, ROWNUM AS rowno FROM ( SELECT USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK FROM WUFEI_TESTTB WHERE 1= 1 {2}) table_source WHERE ROWNUM <= ({0} * {1})) table_alias WHERE table_alias.rowno >= ((({0} - 1) * {1}) + 1)", pagin.CurrentPageIndex, pagin.PageSize,condtionStr); String executeCount = String.Format("SELECT COUNT(1) AS CountNum FROM WUFEI_TESTTB WHERE 1= 1 {0}", condtionStr); var mixCondition = new { CurrentPageIndex = pagin.CurrentPageIndex, PageSize = pagin.PageSize }; List<WUFEI_TESTTBModel> listScore = conn.Query<WUFEI_TESTTBModel>(executeQuery, null).ToList(); pagin.TotalItemCount = conn.Query<Decimal>(executeCount, null).SingleOrDefault(); KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>> result = new KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>>(pagin, listScore); return result; } } public KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>> WUFEI_TESTTBPagination(Pagination pagin, WUFEI_TESTTBModel condition) { using (OracleConnection conn = DapperFactory.CrateOracleConnection()) { String condtionStr = ""; String orderBy = "USER_ID DESC"; if (!String.IsNullOrEmpty(pagin.OrderBy)) { orderBy = pagin.OrderBy; } String executeQuery = String.Format(@"SELECT * FROM (SELECT table_source.*, ROWNUM AS rowno From( SELECT USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK FROM WUFEI_TESTTB WHERE 1= 1 {0} ) table_source WHERE ROWNUM <= (:CurrentPageIndex * :PageSize)) table_alias WHERE table_alias.rowno >= (((:CurrentPageIndex - 1) * :PageSize) + 1)", condtionStr, orderBy); String executeCount = String.Format("SELECT COUNT(*) AS CountNum FROM WUFEI_TESTTB WHERE 1= 1 {0} ", condtionStr); var mixCondition = new { CurrentPageIndex = pagin.CurrentPageIndex, PageSize = pagin.PageSize }; List<WUFEI_TESTTBModel> listScore = conn.Query<WUFEI_TESTTBModel>(executeQuery, mixCondition).ToList(); pagin.TotalItemCount = conn.Query<Decimal>(executeCount, mixCondition).SingleOrDefault(); KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>> result = new KeyValuePair<Pagination, IList<WUFEI_TESTTBModel>>(pagin, listScore); return result; } } } }
这里以表WUFEI_TESTTB为例，字段很少，就不提供Sql语句了，用户自己创建下吧。
其中WUFEI_TESTTBModel就是表WUFEI_TESTTB的实体类。
代码如下：
using System; using System.Data; using System.Collections; using System.Xml; using System.Runtime.Serialization; namespace Tcl.ForLog.Model { /// <summary> /// 表WUFEI_TESTTB的对象类 /// </summary> [DataContract(Name = "WUFEI_TESTTB")] public class WUFEI_TESTTBModel { #region 表名称常量 /// <summary> /// 表名称 /// </summary> public const string TABLE_NAME = "WUFEI_TESTTB"; #endregion #region 属性 /// <summary> /// 用户ID /// </summary> [DataMember(Name = "USER_ID")] public decimal USER_ID { get; set; } /// <summary> /// 用户名称 /// </summary> [DataMember(Name = "USER_NAME")] public string USER_NAME { get; set; } /// <summary> /// 用户地址 /// </summary> [DataMember(Name = "USER_ADDRESS")] public string USER_ADDRESS { get; set; } /// <summary> /// 用户性别 /// </summary> [DataMember(Name = "USER_SEX")] public string USER_SEX { get; set; } /// <summary> /// 用户生日 /// </summary> [DataMember(Name = "USER_BIRTHDAY")] public DateTime USER_BIRTHDAY { get; set; } /// <summary> /// 备注 /// </summary> [DataMember(Name = "USER_REMARK")] public string USER_REMARK { get; set; } /// <summary> /// WUFEI_TESTTBModel深拷贝 /// </summary> /// <returns></returns> public static WUFEI_TESTTBModel DeepCopy(WUFEI_TESTTBModel testTbA) { WUFEI_TESTTBModel testTbB = new WUFEI_TESTTBModel(); testTbB.USER_ID = testTbA.USER_ID; testTbB.USER_NAME = testTbA.USER_NAME; testTbB.USER_ADDRESS = testTbA.USER_ADDRESS; testTbB.USER_SEX = testTbA.USER_SEX; testTbB.USER_BIRTHDAY = testTbA.USER_BIRTHDAY; testTbB.USER_REMARK = testTbA.USER_REMARK; return testTbB; } #endregion } }
这里的代码看似很多，其实是通过CodeSmith生成的，非常便于快速开发网站。
如果对你有用，请帮忙点推荐，谢谢。
