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);
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);
}
}
{
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());
});
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);
}
}
/// 数据传输对象
/// </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;
}
}
{
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());
});
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;
}
}
}
{
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());
});
DataAccessor<PersonCourseGradeDTO> PersonCourseGradeDTOAccessor2 = db.CreateSqlStringAccessor<PersonCourseGradeDTO>(sql3, rowMapper2);
List<PersonCourseGradeDTO> PersonCourseGradeDTOs2 = PersonCourseGradeDTOAccessor2.Execute().ToList<PersonCourseGradeDTO>();
PersonCourseGradeDTOs2.ForEach(p =>
{
Console.WriteLine(p.ToString());
});
入门文章,给初次接触企业库的朋友!