Dapper
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
/// <summary>
/// Dapper1.60.0.0;.net framework4.5.2
/// </summary>
public partial class _Default : Page
{
private static string conn = System.Configuration.ConfigurationManager.ConnectionStrings["SAPConnection"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Unnamed1_Click(object sender, EventArgs e)
{
using (IDbConnection connection = new SqlConnection(conn))
{
////insert one
//var insert = connection.Execute("Insert into AUsers values (@UserName, @Email, @Address)",
// new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
////insert batch 1
//var usersList = Enumerable.Range(0, 10).Select(i => new Users()//新建一个User 类;返回user数组
//{
// Email = i + "qq.com",
// Address = "N安徽",
// UserName = i + "jack"
//});
//var insertbul = connection.Execute("Insert into aUsers values (@UserName, @Email, @Address)", usersList);
////insert batch 2
//var nusersList = Enumerable.Range(0, 10).Select(i => new //新建一个匿名类；返回匿名数组
//{
// Email = i + "qq.com",
// Address = "Nss",
// UserName = i + "jack"
//});
//var ninsertbul = connection.Execute("Insert into aUsers values (@UserName, @Email, @Address)", nusersList);
////query单个
//var query = connection.Query<Users>("select * from aUsers where UserName=@UserName", new { UserName = "jack" });//connection.Query("select * from aUsers where UserName=@UserName", new { UserName = "jack" }).toList();
////update 1
//var update = connection.Execute("update ausers set username='qwe' where username=@username", new { username = "jack" });
////update batch
//var updateb = connection.Execute("update ausers set username=@username where userid=@userid",new[]{ new {userid=47, username = "jack47" }, new {userid=48, username="jack48" } });
////delete single
//var delete = connection.Execute("delete from ausers where username=@username", new { username = "jack9" });
////delete batch
//var deletebul = connection.Execute("delete from ausers where username=@username", new[] { new{ username = "jack9" }, new { username = "jack8" } });
////stored procedure
//var sp = "A_users";
//string gg = "4jack";
//List<Users> lsp= connection.Query<Users>(sp, new { username = gg,userid=47 },commandType:CommandType.StoredProcedure).AsList();
////select in
//List<Users> sin = connection.Query<Users>("select * from ausers where userid in @id and username like @name",
// new { id=new int[] {36,37,38 },
// name="%jack"}).AsList();
////mutiple select
var msql = "select * from ausers where userid=38;select * from ausers where userid=37";
var re = connection.QueryMultiple(msql);
List<Users> mre = re.Read<Users>().ToList();
mre.AddRange(re.Read<Users>().ToList());
////dynamic params to stored procedure
//var sp1 = "A_users";
//DynamicParameters parameter = new DynamicParameters();
//parameter.Add("@userid", 47, DbType.Int32, ParameterDirection.Input);
//parameter.Add("@username", "ggg", DbType.String, ParameterDirection.Input);
//parameter.Add("@RowCount",dbType: DbType.Int32, direction: ParameterDirection.Output);
//connection.Execute(sp1,
// parameter,
// commandType: CommandType.StoredProcedure);
//int rowCount = parameter.Get<int>("@RowCount");
//结果映射，子集型
var sql = "select * from Ausers as a inner join auserbooks as b on a.userid=b.userid";
var UbookDict = new Dictionary<int, Users>();
var userbooks = connection.Query<Users, UserBook, Users>(
sql, (users, userbook) => {
Users userEntry;
if (!UbookDict.TryGetValue(users.Userid, out userEntry)) {//可添加逻辑
userEntry = users;
userEntry.books = new List<UserBook>();//也可在类实例化
UbookDict.Add(userEntry.Userid, userEntry);
}
userEntry.books.Add(userbook);
return userEntry;
},
splitOn: "userid")//按照userid分组
.Distinct().ToList();
//原生sql型
var sql1 = "select * from Ausers as a right join auserbooks as b on a.userid=b.userid";
var userbooks1 = connection.Query<Users, UserBook, Users>(sql1, (u, b) => { u.books = new List<UserBook>(); u.books.Add(b); return u; }, splitOn: "userid").Distinct().ToList();
//mutiple join,子集型
var sql2 = "select * from Ausers as a inner join auserbooks as b on a.userid=b.userid inner join abooks as c on b.bookid=c.bookid";
var UbookDict2 = new Dictionary<int, Users>();
var userbooks2 = connection.Query<Users, UserBook, BookDetail, Users>(
sql2, (users, userbook, bookdetail) => {
Users userEntry; UserBook bookEntry;
if (!UbookDict2.TryGetValue(users.Userid, out userEntry))
{//可添加逻辑
userEntry = users;
userEntry.books = new List<UserBook>();//也可在类实例化
UbookDict2.Add(users.Userid, users);
}
bookEntry = userbook;
bookEntry.bookDetail = bookdetail;
userEntry.books.Add(bookEntry);
return userEntry;
},
splitOn: "bookid")//按照最后一组关系的映射分组
.Distinct().ToList();
//多join原生sql型
var userbooks3 = connection.Query<Users, UserBook, BookDetail, Users>(
sql2, (users, userbook, bookdetail) => {userbook.bookDetail = bookdetail; users.books = new List<UserBook>(); users.books.Add(userbook);return users; },splitOn:"bookId").Distinct().ToList();
GridView1.DataSource = userbooks2;
GridView1.DataBind();
}
}
}
//object
#region
internal class BookDetail
{
public BookDetail()
{
}
public int bookid { get; set; }
public string bookname { get; set; }
}
internal class UserBook
{
public UserBook()
{
}
public int Userid { get; set; }//与表字段对应
public int bookid { get; set; }
public BookDetail bookDetail { get; set; }
}
internal class Users
{
public List<UserBook> books;
public Users()
{
//books = new List<UserBook>();
}
public int Userid { get; set; }
public string Address { get; set; }
public string Email { get; set; }
public string UserName { get; set; }
}
#endregion
////table definition relationship
#region
////AUsers
//CREATE TABLE[dbo].[AUsers] (
//[UserID]
//INT IDENTITY(1, 1) NOT NULL,
//[UserName] VARCHAR(50) NULL,
//[Email] VARCHAR(100) NULL,
//[Address] VARCHAR(100) NULL,
//CONSTRAINT[PK_Users] PRIMARY KEY CLUSTERED([UserID] ASC)
//);
////Abooks
//CREATE TABLE[dbo].[ABooks] (
// [BookId]
// INT NOT NULL,
// [bookname] VARCHAR(20) NOT NULL,
// PRIMARY KEY CLUSTERED([BookId] ASC)
//);
////auserbooks
//CREATE TABLE[dbo].[AUserBooks] (
//[UserId]
//INT NOT NULL,
//[BookId]
//INT NOT NULL,
//PRIMARY KEY CLUSTERED([UserId] ASC, [BookId] ASC),
//CONSTRAINT[fk_ubk] FOREIGN KEY([UserId]) REFERENCES[dbo].[AUsers] ([UserID]) ON DELETE CASCADE,
//FOREIGN KEY([BookId]) REFERENCES[dbo].[ABooks] ([BookId])
//);
#endregion
}
