.Net如何优雅的使用Dapper访问oracle数据库
程序员文章站
2022-03-08 22:23:58
1、引用Dapper2、创建DapperFactory类public class DapperFactory { public static readonly string connectionString = ConfigurationManager.AppSettings["connection_string"]; public static OracleConnection CrateOracleConnection() {...
1、引用Dapper
2、创建DapperFactory类
public class DapperFactory
{
public static readonly string connectionString = ConfigurationManager.AppSettings["connection_string"];
public static OracleConnection CrateOracleConnection()
{
var connection = new OracleConnection(connectionString);
connection.Open();
return connection;
}
}
3、创建测试类以及映射关系
public class SysUser
{
public string EmpID { get; set; }
public string ID { get; set; }
public string UserName { get; set; }
public string RealName { get; set; }
}
[Serializable]
public class SysUserMapping : ClassMapper<SysUser>
{
public SysUserMapping()
{
base.Table("SYS_USER");
Map(p => p.ID).Column("ID").Key(KeyType.Assigned);
Map(p => p.EmpID).Column("EMP_ID");
Map(p => p.UserName).Column("USERNAME");
Map(p => p.RealName).Column("REALNAME");
// 启用自动映射,一定要调用此方法
AutoMap();
}
}
4、使用Dapper访问数据库
using (var conn = DapperFactory.CrateOracleConnection())
{
string query = "SELECT * FROM sys_user t where username=:id ";
var par = new { id = "admin" };
var lst = conn.Query<SysUser>(query, par).ToList();
conn.Insert(new SysUser());
conn.Update(new SysUser());//根据主键更新
conn.Delete(new SysUser()); //根据主键删除
}
5、一些其他的写法
public int Insert(StudentModel model)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string executeSql = @" INSERT INTO student VALUES(:PkId, :ClsCode, :StudentName)";
return conn.Execute(executeSql, model);
}
}
public List<StudentModel> GetAll()
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string query = "SELECT pk_id as PkId,Cls_Code as ClsCode,Student_Name as StudentName FROM student ";
return conn.Query<StudentModel>(query).ToList();
}
}
public List<StudentModel> Get(string strName)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string query = "SELECT pk_id as PkId,Cls_Code as ClsCode,Student_Name as StudentName FROM student WHERE Student_Name = :param";
return conn.Query<StudentModel>(query, new { param = strName }).ToList();
}
}
public List<StudentModel> GetIn(string[] strNames)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string query = "SELECT pk_id as PkId,Cls_Code as ClsCode,Student_Name as StudentName FROM student WHERE Student_Name in :param";
return conn.Query<StudentModel>(query, new { param = strNames }).ToList();
}
}
/// <summary>
/// 使用事务批量新增
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public int InsertByTrans(List<StudentModel> list)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
IDbTransaction transaction = conn.BeginTransaction();
int row = 0;
foreach (var item in list)
{
string executeSql = @" INSERT INTO student VALUES(:PkId, :ClsCode, :StudentName)";
row += conn.Execute(executeSql, item, transaction, null, null);
}
transaction.Commit();
return row;
}
}
public bool Delete(int PkId)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string executeSql = @" DELETE FROM student WHERE PkId = :PkId ";
var conditon = new { PkId = PkId };
//或者下面的添加方式
//var param = new DynamicParameters();
//param.Add("PkId", PkId);
return conn.Execute(executeSql, conditon) > 0 ? true : false;
}
}
public int MultDelete(List<StudentModel> ulist)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string query = "DELETE student PkId uID=@PkId";
return conn.Execute(query, ulist);
}
}
public bool EditWUFEI_TESTTB(StudentModel student)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
string executeSql = @" UPDATE student 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, student) > 0 ? true : false;
}
}
public bool UpdateWUFEI_TESTTB(string sqlStr)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
return conn.Execute(sqlStr) > 0 ? true : false;
}
}
public StudentModel GetWUFEI_TESTTB(Decimal USER_ID)
{
using (var conn = DapperFactory.CrateOracleConnection())
{
String executeSql = @" SELECT USER_ID, USER_NAME, USER_ADDRESS, USER_SEX, USER_BIRTHDAY, USER_REMARK FROM student
WHERE USER_ID = :USER_ID ";
var conditon = new { USER_ID = USER_ID };
return conn.Query<StudentModel>(executeSql, conditon).SingleOrDefault();
}
}
本文地址:https://blog.csdn.net/u014312720/article/details/107296076
上一篇: 缓存雪崩,缓存穿透
下一篇: 荐 Java实现发送手机验证码
推荐阅读
-
C#使用ADO.Net部件来访问Access数据库的方法
-
在代码生成工具Database2Sharp中使用ODP.NET(Oracle.ManagedDataAccess.dll)访问Oracle数据库,实现免安装Oracle客户端,兼容32位64位Oracle驱动
-
Oracle数据库查找第n行(n>=2)时使用rownum查不到的问题如何解决?
-
使用ASP.NET Web Api构建基于REST风格的服务实战系列教程——使用Repository模式构建数据库访问层
-
C#使用ADO.Net部件来访问Access数据库的方法
-
Oracle数据库因长期开启,出现不能使用pl/sql登录,远程链接提示没有监听程序的问题如何解决?
-
在代码生成工具Database2Sharp中使用ODP.NET(Oracle.ManagedDataAccess.dll)访问Oracle数据库,实现免安装Oracle客户端,兼容32位64位Oracle驱动
-
.Net如何优雅的使用Dapper访问oracle数据库
-
PHP使用PDO访问oracle数据库的步骤详解
-
PHP 使用 POD 访问mysql数据库,如何返回上一个插入记录的自增编号ID