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) > ? 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 = ;
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) > ? 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) > ? true : false;
}
} public bool UpdateWUFEI_TESTTB(string sqlStr)
{
using (OracleConnection conn = DapperFactory.CrateOracleConnection())
{
return conn.Execute(sqlStr) > ? 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生成的，非常便于快速开发网站。
如果对你有用，请帮忙点推荐，谢谢。