Entlib5.0之数据查询

        Entlib5.0早就出来了，在这个版本中提供了一些很好用的特性，今天小试一下，用用SprocAccessor和SqlStringAccessor这两个类，这两个类能够将返回的DataSet, DataTable样式的数据集转换为实体数据的形式。

1.原始的查询方式。

            Database db = DatabaseFactory.CreateDatabase();

            DbConnection connection = db.CreateConnection();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from person";
            DataSet ds = db.ExecuteDataSet(cmd);

说明：关于数据连接的配置，请参考帮助文档，项目地址：http://entlib.codeplex.com/

 

2.采用默认的映射。这种映射数据库列和实体数据的属性是一一对应的。

数据库中的列

 

实体类型：Person

    public class Person
    {
        public int PersonID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime? HireDate { get; set; }
        public DateTime? EnrollmentDate { get; set; }
        public override string ToString()
        {
            return string.Format(@"PersonID:{0},Name:{1}-{2}", PersonID, FirstName, LastName);
        }
    }

接下来看看我们如何查询

            string sql1 = "select * from person";//查询全部数据
            DataAccessor<Person> accessor = db.CreateSqlStringAccessor<Person>(sql1);
            List<Person> persons = accessor.Execute().ToList<Person>();
            persons.ForEach(p =>
            {
                Console.WriteLine(p.ToString());
            });

 

3.自定义映射。

   单行数据的映射，考虑到实际项目的情况，我们前段展示的数据形式（ViewModel）可能和原始的Model有些不一样，这里我们重新定义个实体类型，可能和数据库列不存在一一对应的关系

/// <summary>
    /// 数据传输对象
    /// </summary>
    public class PersonCourseGradeDTO
    {
        public string PersonName { get; set; }
        public string CourseName { get; set; }
        public double Grade { get; set; }
        public override string ToString()
        {
            return string.Format(@"PersonName:{0},CourseName:{1},Grade:{2}", PersonName, CourseName, Grade);
        }
    }

这个时候我们就需要自定义一个RowMapper了

    public class PersonRowMapper : IRowMapper<PersonCourseGradeDTO>
    {
        public PersonCourseGradeDTO MapRow(IDataRecord row)
        {
            PersonCourseGradeDTO dto = new PersonCourseGradeDTO();
            int col = row.GetOrdinal("FirstName");
            string FirstName = row.GetValue(col) == DBNull.Value ? "" : row.GetValue(col).ToString();
            col = row.GetOrdinal("LastName");
            string LastName = row.GetValue(col) == DBNull.Value ? "" : row.GetValue(col).ToString();
            col = row.GetOrdinal("Title");
            string Title = row.GetValue(col) == DBNull.Value ? "" : row.GetValue(col).ToString();
            col = row.GetOrdinal("Grade");
            double Grade = row.GetValue(col) == DBNull.Value ? 0 : Convert.ToDouble(row.GetValue(col));

            dto.PersonName = FirstName + "-" + LastName;
            dto.CourseName = Title;
            dto.Grade = Grade;
            return dto;
        }
    }

 

如何调用？

            IRowMapper<PersonCourseGradeDTO> rowMapper = new PersonRowMapper();
            DataAccessor<PersonCourseGradeDTO> PersonCourseGradeDTOAccessor = db.CreateSqlStringAccessor<PersonCourseGradeDTO>(sql3, rowMapper);
            List<PersonCourseGradeDTO> PersonCourseGradeDTOs = PersonCourseGradeDTOAccessor.Execute().ToList<PersonCourseGradeDTO>();
            PersonCourseGradeDTOs.ForEach(p =>
            {
                Console.WriteLine(p.ToString());
            });

多行数据的映射

    public class PersonRowsetMapper : IResultSetMapper<PersonCourseGradeDTO>
    {
        public IEnumerable<PersonCourseGradeDTO> MapSet(IDataReader reader)
        {
            while (reader.Read())
            {
                PersonCourseGradeDTO dto = new PersonCourseGradeDTO();
                int col = reader.GetOrdinal("FirstName");
                string FirstName = reader.GetValue(col) == DBNull.Value ? "" : reader.GetValue(col).ToString();
                col = reader.GetOrdinal("LastName");
                string LastName = reader.GetValue(col) == DBNull.Value ? "" : reader.GetValue(col).ToString();
                col = reader.GetOrdinal("Title");
                string Title = reader.GetValue(col) == DBNull.Value ? "" : reader.GetValue(col).ToString();
                col = reader.GetOrdinal("Grade");
                double Grade = reader.GetValue(col) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetValue(col));

                dto.PersonName = FirstName + "-" + LastName;
                dto.CourseName = Title;
                dto.Grade = Grade;

                yield return dto;
            }
        }
    }

如何查询？

           IResultSetMapper<PersonCourseGradeDTO> rowMapper2 = new PersonRowsetMapper();
            DataAccessor<PersonCourseGradeDTO> PersonCourseGradeDTOAccessor2 = db.CreateSqlStringAccessor<PersonCourseGradeDTO>(sql3, rowMapper2);
            List<PersonCourseGradeDTO> PersonCourseGradeDTOs2 = PersonCourseGradeDTOAccessor2.Execute().ToList<PersonCourseGradeDTO>();
            PersonCourseGradeDTOs2.ForEach(p =>
            {
                Console.WriteLine(p.ToString());
            });

 

入门文章，给初次接触企业库的朋友！