Dapper连接Oracle

2年前 (2022) 程序员胖胖胖虎阿

301 0 0

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

    }
}