当前位置:主页 > 资料 >

Absolute beginner’s tutorial on understanding and using Dap
栏目分类:资料   发布日期:2018-08-03   浏览次数:

导读:本文为去找网小编(www.7zhao.net)为您推荐的Absolute beginner’s tutorial on understanding and using Dapper ORM,希望对您有所帮助,谢谢! This article introduces Dapper to absolute beginners. The article is meant for

本文为去找网小编(www.7zhao.net)为您推荐的Absolute beginner’s tutorial on understanding and using Dapper ORM,希望对您有所帮助,谢谢! copyright www.7zhao.net



www.7zhao.net

This article introduces Dapper to absolute beginners. The article is meant for developers who are primarily using ADO.NET to write their data access layers. Many experienced developers will find this article very basic but since the article is written from the perspective of beginners, I’ve tried to keep things simple.

copyright www.7zhao.net

Background

ADO.NET is a very strong framework for data access. ADO.NET has been around since many years and there are a lot of systems running over ADO.NET. Developers who are totally oblivious to the concept of ORMs will probably be asking “What is dapper? What are the benefits of using it and is it an alternative to ADO.NET?”

欢迎访问www.7zhao.net

Let’s first start the discussion by understanding what an ORM is: an ORM or Object-relational mapper in piece of software that help us in converting data between incompatible type systems i.e. relational databases and object-oriented programming languages. There are many ORMs being used like Entity Framework(EF) , NHibernate , Dapper etc. Dapper is often called as Micro ORM as it is light weight and does not provide so many features that are provided by other ORMs like NHibernate or Entity Framework. The main focus of Dapper is on performance and simplicity of use. www.7zhao.net

Other ORMs like Entity Framework generates the queries automatically based on how the Objects are being used/fetched (CRUD) in the application. This process has some performance overhead and the generated queries are often black box for the developers. Dapper, on the other hand, let developers write their own SQL to perform database operations and map the results to C# objects i.e. POCOs (Plain Old CLR Objects). Since we as developers are writing queries and dapper and only doing the mapping to POCOs, it is being called as micro ORM or mini ORM. But because of the same reason, the performance of dapper is almost same as using plain ADO.NET.

欢迎访问www.7zhao.net

In the remaining article, we will try to focus on how to use Dapper for basic database operations. We will be creating a simple API that will let the users perform CRUD operations using the API itself and internally we will use dapper to perform the operations on the database. 内容来自www.7zhao.net

Note: Since the focus of this article is on understanding Dapper, some shortcuts have been taken in API design to keep things simple. 去找(www.7zhao.net欢迎您

Dapper Essentials

Lets start by understanding the basic constructs of dapper so that we can use it effectively. 本文来自去找www.7zhao.net

The best way to understand dapper is to think of as an extension(enhanced features) to existing IDbConnection object. Dapper is nothing but a set of extension methods for IDbConnection . This is what makes is so powerful in terms of performance but leaves the scope for defining queries to the user much like vanilla ADO.NET. All these extension methods reside in “Dapper” namespace and to use dapper methods, we need to include this namespace in our code.

欢迎访问www.7zhao.net

Now lets look at the basic commands that are available in dapper that are required to perform the CRUD operations.

欢迎访问www.7zhao.net

  • Execute : This extension method is used to execute INSERT, UPDATE and DELETE queries. This is also used when we want to execute a stored procedure using dapper.
  • Query : This extension method is used to execute SELECT queries.

Apart from these 2 basic methods, there are other methods like QueryFirst , QueryFirstOrDefault , QuerySingle and QuerySingleOrDefault . For someone who was used LINQ these are self explanatory and we will not be discussing them to avoid digression for beginners. But there is one important command QueryMultiple that can be used to execute multiple queries in one command itself. We will be not be covering this command in this article to keep it free from digression for beginners but for it is highly advisable to read about this. 去找(www.7zhao.net欢迎您

Using the Code

Let us try to understand the concepts further by creating a small toy application. The application that we will be creating is a small books information application which will 内容来自www.7zhao.net

  • Retrieve a list of books
  • Retrieve the details of selected book.
  • Add new book
  • Delete a Book

Lets start by looking at the database schema for our Books database. 去找(www.7zhao.net欢迎您

欢迎访问www.7zhao.net

Here is the script to create this Books table:

www.7zhao.net

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Books](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BookName] [nvarchar](200) NULL,
	[ISBN] [nvarchar](50) NULL,
 CONSTRAINT [PK_Books] 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
 
copyright www.7zhao.net

Now That we have our database ready, we will install Dapper in our application. Dapper can be installed as a Nuget package in our applications. 本文来自去找www.7zhao.net

www.7zhao.net

Once Dapper is installed in our project, first thing we need to do is to create the POCOs for our database tables. In our case we need to create the class for our Book object.

本文来自去找www.7zhao.net

public class Book
{
	public int ID { get; set; }
	public string BookName { get; set;}
	public string ISBN { get; set; }
}
 本文来自去找www.7zhao.net 

Now since we are dealing with database operations, lets create an abstraction on top of our database calls i.e. a repository class. We will use this repository class to perform all the Book related database operations. Lets start by defining a contract for our BooksRepository class and define the basic CRUD operations in this contract.

copyright www.7zhao.net

interface IRepository<T> where T : class
{
	List<T> GetAll();
	bool Add(T employee);
	T GetById(int id);
	bool Update(T employee);
	bool Delete(int id);
}
 

本文来自去找www.7zhao.net

With this abstraction in place, lets create a concrete BooksRepository class that will contain all the database CRUD operation logic for our Book entity.

去找(www.7zhao.net欢迎您

using Dapper;
public class BooksRepository : IRepository<Book>
{
	private IDbConnection dbConnection = null;
 
	public BooksRepository()
	{
		dbConnection = new SqlConnection(ConfigReader.ConnectionString);
	}
 
	public bool Add(Book book)
	{
		throw new NotImplementedException();
	}
 
	public bool Delete(int id)
	{
		throw new NotImplementedException();
	}
 
	public List<Book> GetAll()
	{
		throw new NotImplementedException();
	}
 
	public Book GetById(int id)
	{
		throw new NotImplementedException();
	}
 
	public bool Update(Book employee)
	{
		throw new NotImplementedException();
	}
}
 
copyright www.7zhao.net

Now we have all the boilerplate code ready for our test application. Let’s now start looking at the basic CRUD operations using Dapper.

本文来自去找www.7zhao.net

Create

Since we know that dapper expects developers to take care of SQL queries, let’s first define our SQL query that we will use to insert the data into our Books table. 去找(www.7zhao.net欢迎您

INSERT INTO [dbo].[Books] ([BookName], [ISBN]) VALUES (@bookName ,@isbn)
 www.7zhao.net 

We are going to define this query(and all other queries) in our application config file along with other settings. Now that we have our query defined. Let’s look at the code that is inserting the data in our Book Table:

内容来自www.7zhao.net

public bool Add(Book book)
{
	var result = false;
	try
	{
		string sql = ConfigReader.InsertCommand;
 
		var count = dbConnection.Execute(sql, book);
		result =  count > 0; 
	}
	catch { }
	
	return result;
}
 www.7zhao.net 

Read

Now that we have seen the code to insert the data in the table, lets look at the code to read the data from the database. First lets look at how we can retrieve all the items from the table. Lets start by looking at the SQL query for this operation.

本文来自去找www.7zhao.net

SELECT * from Books
 
copyright www.7zhao.net

Now lets look at the implementation of GetAll method in our repository.

内容来自www.7zhao.net

public List<Book> GetAll()
{
	string sql = ConfigReader.ReadAllCommand;
	var queryResult = dbConnection.Query<Book>(sql);
 
	return queryResult.ToList();
}
 欢迎访问www.7zhao.net 

The above function will return list of all books. Now lets look at the code where we will retrieve one single book record with a given ID . Lets start by looking at the SQL for this.

欢迎访问www.7zhao.net

SELECT * from Books WHERE Id=@Id
 

欢迎访问www.7zhao.net

Now lets look at the implementation of GetById method in our repository.

www.7zhao.net

public Book GetById(int id)
{
	Book book = null;
	string sql = ConfigReader.ReadOneCommand;
	var queryResult = dbConnection.Query<Book>(sql, new { Id = id});
 
	if(queryResult != null)
	{
		book = queryResult.FirstOrDefault();
	}
	return book;
}
 copyright www.7zhao.net 

Update

Now that we are done with the read operations, lets start with the update operation. lets look at the SQL query for the update operation.

本文来自去找www.7zhao.net

UPDATE Books SET BookName = @BookName, ISBN = @ISBN WHERE ID = @ID
 内容来自www.7zhao.net 

Now lets look at the implementation of our Update method in our repository.

copyright www.7zhao.net

public bool Update(Book book)
{
	string sql = ConfigReader.UpdateCommand;
	var count = dbConnection.Execute(sql, book);
	return count > 0;
}
 
本文来自去找www.7zhao.net

Delete

Finally Lets look at how we can implement the Delete operation. 去找(www.7zhao.net欢迎您

DELETE FROM Books WHERE ID = @Id
 
欢迎访问www.7zhao.net

Now lets look at the implementation of our Delete method in our repository.

本文来自去找www.7zhao.net

public bool Delete(int id)
{
	string sql = ConfigReader.DeleteCommand;
	var count = dbConnection.Execute(sql, new { Id = id });
	return count > 0;
}
 

www.7zhao.net

With this we have the basic CRUD operations implemented for our Book entity. The important thing to note here is that using Dapper is almost same as using ADO.NET. Dapper simply does the translation of results obtained from relational to objects and vice versa. This is where the real power of dapper lies. If someone is familiar with ADO.NET, using dapper is simply a piece of cake for them. In the next section, lets look at how to execute stored procedures and use transactions with dapper. 欢迎访问www.7zhao.net

Executing Stored Procedures

Using stored procedure is as simple as specifying the commandType in the Execute method.

copyright www.7zhao.net

string sql = "MyStoredProc;
var result = dbConnection.Execute(sql, commandType: CommandType.StoredProcedure);
 
去找(www.7zhao.net欢迎您

Using Transactions

Transactions will be same as they are in ADO.NET. We can either using Transaction or TransactionScope in our code. Following sections shows an example snippet. www.7zhao.net

// Using transactions
using (var tx = connection.BeginTransaction())
{
	// All Our Dapper code goes here
	
	tx.Commit();
}
 
// Using TransactionScope
using (var ts = new TransactionScope())
{
	// All Our Dapper code goes here
 
	ts.Complete();
}
 copyright www.7zhao.net 

With this we have a fair idea on how we can perform basic CRUD operations using Dapper in our applications. Before closing the article, lets look at the configuration file containing all our app settings and our API controller code that can be testing using any rest client like Postman. The reference code can be found in the attached demo project.

欢迎访问www.7zhao.net

Note: The demo project is an ASP.NET Core API application.

内容来自www.7zhao.net

www.7zhao.net

[Route("api/[controller]")]
[ApiController]
public class BookController : ControllerBase
{
	IRepository<book> booksRepository = null;
 
	public BookController()
	{
		booksRepository = new BooksRepository();
	}
 
	[HttpGet]
	public IActionResult Get()
	{
		IList<book> books = booksRepository.GetAll();
 
		return Ok(books);
	}
 
	[HttpGet("{id}", Name = "Get")]
	public IActionResult Get(int id)
	{
		Book book = booksRepository.GetById(id);
 
		if (book != null)
		{
			return Ok(book);
		}
		return NotFound();
	}
 
	[HttpPost]
	public IActionResult Post(Book book)
	{
		if (!ModelState.IsValid)
		{
			return BadRequest(ModelState);
		}
 
		if (true == booksRepository.Add(book))
		{
			return Ok(book);
		}
 
		return BadRequest();
	}
 
	[HttpPut("{id}")]
	public IActionResult Put(int id, Book book)
	{
		if (!ModelState.IsValid)
		{
			return BadRequest(ModelState);
		}
 
		book.ID = id;
		var result = booksRepository.Update(book);
 
		if (result == true)
		{
			return Ok(book);
		}
 
		return NotFound();
	}
 
	[HttpDelete("{id}")]
	public IActionResult Delete(int id)
	{
		if (id <= 0)
		{
			return BadRequest("invalid id");
		}
 
		var result = booksRepository.Delete(id);
 
		if (result == true)
		{
			return Ok();
		}
 
		return NotFound();
	}
}
</book></book>
 欢迎访问www.7zhao.net 

Point of interest

In this article we looked at how we can use Dapper to perform basic CRUD operations in our .NET applications. This article has been written from a beginner’s perspective. I hope this has been somewhat informative.

本文来自去找www.7zhao.net

Download sample code for this article here:DapperTest

www.7zhao.net

内容来自www.7zhao.net


本文原文地址:http://rahulrajatsingh.com/2018/08/absolute-beginners-tutorial-on-understanding-and-using-dapper-orm/

以上为Absolute beginner’s tutorial on understanding and using Dapper ORM文章的全部内容,若您也有好的文章,欢迎与我们分享! 本文来自去找www.7zhao.net

Copyright ©2008-2017去找网版权所有   皖ICP备12002049号-2 皖公网安备 34088102000435号   关于我们|联系我们| 免责声明|友情链接|网站地图|手机版