概述
Dapper是.NET下一个micro的ORM，它和Entity Framework或Nhibnate不同，属于轻量级的，并且是半自动的。也就是说实体类都要自己写。它没有复杂的配置文件，一个单文件就可以了。
现在已经取代了原来的SqlHelper优点：
- 使用Dapper可以自动进行对象映射！
- 轻量级，单文件。
- 支持多数据库。
- Dapper原理通过Emit反射IDataReader的序列队列，来快速的得到和产生对象
- 由于Dapper ORM的操作实际上是对IDbConnection类的扩展，所有的方法都是该类的扩展方法。所以在使用前先实例化一个IDBConnection对象。所以调用之前，一定先加上命名空间：using Dapper;
- Nuget查询Dapper，点击安装即可引入第三方DLL
使用举例
数据库表结构
----Book表，主表
CREATE TABLE [dbo].[Book](
[BookId] [int] IDENTITY(,) NOT NULL,
[Name] [nvarchar]() NOT NULL,
CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED
(
[BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] GO
----BookReview类似于book评价表
CREATE TABLE [dbo].[BookReview](
[Id] [int] IDENTITY(,) NOT NULL,
[BookId] [int] NULL,
[Content] [nvarchar]() NULL,
CONSTRAINT [PK_BookReview] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] GO ALTER TABLE [dbo].[BookReview] WITH CHECK ADD CONSTRAINT [FK_BookID_BookPreBookid] FOREIGN KEY([BookId])
REFERENCES [dbo].[Book] ([BookId])
GO ALTER TABLE [dbo].[BookReview] CHECK CONSTRAINT [FK_BookID_BookPreBookid]
GO
实体结构
public class Book
{
public Book()
{
Reviews = new List<BookReview>();
}
public int BookId { get; set; }
public string Name { get; set; }
public virtual List<BookReview> Reviews { get; set; }
public override string ToString()
{
return string.Format("[{0}]------《{1}》", BookId, Name);
}
} //书评
public class BookReview
{
public int Id { get; set; }
public int BookId { get; set; }
public virtual string Content { get; set; }
public virtual Book AssoicationWithBook { get; set; }
public override string ToString()
{
return string.Format("{0})--[{1}]\t\"{3}\"", Id, BookId, Content);
}
}
Execute执行数据库脚本
public static void OpeartData()
{
string sqlconnct = System.Configuration.ConfigurationManager.ConnectionStrings["myconnect"].ConnectionString;
using (IDbConnection comm = new SqlConnection(sqlconnct))
{
comm.Open();
//Book book = new Book() { Name="C#9论述",Id=1 };
string query = "insert into Book(Name) values(@name)";
//添加，可以执行SQL脚本 //comm.Execute(query, book);
//comm.Execute(query, new { name = "Java Learn" }); query = "UPDATE Book SET Name=@name WHERE BookId =@id";
//comm.Execute(query, book);
//comm.Execute(query, new {name="C985426",BookId=1 }); query = "DELETE FROM Book WHERE BookId = @id";
//var idsult=comm.Execute(query, new { name = "C985426", BookId = 1 }); query = "select * from Book where BookId=@id";
//var ruslt = comm.Query<Book>(query).ToList();
// var ruslt = comm.Query<Book>(query, new { BookId = 2 }).FirstOrDefault();
}
}
Query执行数据库脚本查询
- One To More 查询
public static Book OpeartMoData()
{
string sqlconnct = System.Configuration.ConfigurationManager.ConnectionStrings["myconnect"].ConnectionString;
using (IDbConnection conn = new SqlConnection(sqlconnct))
{
conn.Open();
string query = "SELECT * FROM Book b LEFT JOIN BookReview br ON br.BookId = b.Id WHERE b.id = @id";
Book lookup = null;
//Query<TFirst, TSecond, TReturn>
var b = conn.Query<Book, BookReview, Book>(query,
(book, bookReview) =>
{
//扫描第一条记录，判断非空和非重复
if (lookup == null || lookup.BookId != book.BookId)
lookup = book;
//书对应的书评非空，加入当前书的书评List中，最后把重复的书去掉。
if (bookReview != null)
lookup.Reviews.Add(bookReview);
return lookup;
}, new { id = }, splitOn: "id").Distinct().SingleOrDefault();
return b;
}
}
- One To One 查询
public static BookReview OpeartData1T1()
{
string sqlconnct = System.Configuration.ConfigurationManager.ConnectionStrings["myconnect"].ConnectionString;
using (IDbConnection conn = new SqlConnection(sqlconnct))
{
BookReview br;
string query = "SELECT br.Id,b.BookId,Content,Name FROM BookReview br inner JOIN Book b ON br.BookId = b.BookId WHERE br.id = @id";
using (conn)
{
br = conn.Query<BookReview, Book, BookReview>(query,
(bookReview, book) =>
{
bookReview.AssoicationWithBook = book;
return bookReview;
}, new { id = }, splitOn: "BookId",commandType:CommandType.StoredProcedure).FirstOrDefault();
return br;
}
}
}
- 执行事务
public static void BookTransaction()
{
string sqlconnct = System.Configuration.ConfigurationManager.ConnectionStrings["myconnect"].ConnectionString;
using (IDbConnection conn = new SqlConnection(sqlconnct))
{
conn.Open();
//开始事务
IDbTransaction transaction = conn.BeginTransaction();
try
{
string query = "DELETE FROM Book WHERE id = @id";
string query2 = "DELETE FROM BookReview WHERE BookId = @BookId";
conn.Execute(query2, new { BookId = }, transaction, null, null);
conn.Execute(query, new { id = }, transaction, null, null);
//提交事务
transaction.Commit();
}
catch (Exception ex)
{
//出现异常，事务Rollback
transaction.Rollback();
throw new Exception(ex.Message);
}
}
}
Dapper.NET 连接MySQL数据库
1、通过NuGet引用MySql数据库的客户端MySql.Data
2、配置引用客户端Config文件
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="MySqlConnect" value="server=serviceip;Port=3306;User Id=root;password=avc123;Database=testdb;CharSet=utf8;"/>
</appSettings>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
</configuration>
3、添加编写程序代码调用
/// <summary>
/// 执行返回第一行第一列值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlContent"></param>
/// <returns></returns>
public T ExecScalarSql<T>(string sqlContent)
{
try
{
using (var conn = new MySqlConnection(SqlConnect))
{
var resutl = conn.ExecuteScalar<T>(sqlContent);
conn.Close();
return resutl;
}
}
catch (Exception ex)
{
throw ex;
}
}