public class Book
{
public int ID;
public string Name;
public string Description;
public BookComment bookComment;
public BookPic bookPic;
}
public class BookPic
{
public int ID;
public int BookID;
public string Name;
public string PicDesc;
}
{
public int ID;
public int BookID;
public string Name;
public string PicDesc;
}
public class BookComment
{
public int ID;
public int BookId;
public string Name;
public string Comment;
}
{
public int ID;
public int BookId;
public string Name;
public string Comment;
}
查询用到splitOn,匹配是从结果列最后往前找,如下找到BookID到最后列映射到C表中,再往前找到Name,再映射到B表中,再前面的列则映射A表中,查找分隔匹配是不区分大小写的。
如果是跨库查询,配置的user需要有open其它库的权限。
如果是跨库查询,配置的user需要有open其它库的权限。
public List<Book> GetBookList()
{
List<Book> bList = null;
try
{
using (var t = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
bList= t.Query<Book, BookComment, BookPic, Book>(@"select
a.ID,a.Name,a.Description,
b.Name,b.ID,b.BookId,b.Comment,
c.BookID,c.ID,c.Name,c.PicDesc
from [dbo].[Book]
a inner join [dbo].[BookComment] b on a.id=b.bookid
inner join [dbo].[BookPic] c on a.id =c.bookid",
(book, bookcomment, bookpic) =>
{
book.bookComment = bookcomment;
book.bookPic = bookpic;
return book;
},
splitOn: "Name,BookID"
).ToList();
}
}
catch (Exception ex)
{
nlog.Error(ex.ToString());
}
return bList;
}
{
List<Book> bList = null;
try
{
using (var t = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
bList= t.Query<Book, BookComment, BookPic, Book>(@"select
a.ID,a.Name,a.Description,
b.Name,b.ID,b.BookId,b.Comment,
c.BookID,c.ID,c.Name,c.PicDesc
from [dbo].[Book]
a inner join [dbo].[BookComment] b on a.id=b.bookid
inner join [dbo].[BookPic] c on a.id =c.bookid",
(book, bookcomment, bookpic) =>
{
book.bookComment = bookcomment;
book.bookPic = bookpic;
return book;
},
splitOn: "Name,BookID"
).ToList();
}
}
catch (Exception ex)
{
nlog.Error(ex.ToString());
}
return bList;
}