欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.Net如何优雅的使用Dapper访问oracle数据库

程序员文章站 2022-06-24 23:13:02
1、引用Dapper2、创建DapperFactory类public class DapperFactory { public static readonly string connectionString = ConfigurationManager.AppSettings["connection_string"]; public static OracleConnection CrateOracleConnection() {...

1、引用Dapper

.Net如何优雅的使用Dapper访问oracle数据库

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