锐客网

  1. 首页 > 睿知 > it技术 > >

dapper Oracle

IT知识Oracle

落花踏尽游何处，笑入胡姬酒肆中。这篇文章主要讲述dapper Oracle相关的知识，希望能为你提供帮助。
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;
}
}
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 ( SELECTUSER_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;
}
}
}
}
 
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
【dapper Oracle】}
}










































































































































































    推荐阅读


    上一篇：Java中session与application的异同

    下一篇：Dapper SQL