Detailed explanation of how to use Dapper in C# (translation)

Preface:

Dapper is a lightweight ORM tool. If you use Entity Framework and NHibernate to handle big data access and relationship mapping in small projects, it is a bit of a killer. You also feel that ORM saves time and effort, and Dapper will be your best choice at this time.

Object Relational Mapping (ORM) has been used for a long time to solve the problem that object models and data models do not match in relational databases during programming.

Dapper is an open source, lightweight ORM developed by the Stack OverFlow team. Compared with other ORM frameworks, Dapper is very fast.

Dapper is designed with performance and ease of use in mind. It supports static and dynamic object binding using transactions, stored procedures, or batch insertion of data.

This article will introduce you in detail about the use of Dapper in C#. I won’t say much below. Let’s take a look at the detailed introduction together.

Download and install Dapper:

To get started with Dapper, follow these steps:

1. Open Visual Studio

2. Click File -> New -> Project
3. Select Web->Web Application from the New Project dialog box

4. Assign a name to the project

5. Select an empty project template

6. Click "OK"

The above will create an empty application project.

Note:If you have NuGet installed, you can use NuGet to install Dapper->Select the project in Solution Explorer, right-click the project and select "Manage Nuget Package...", find Dapper for installation, and after success, you can go to the next step.

Use Dapper for CRUD operations in .NET:

Now let's write some code, use Dapper to perform CRUD operations on the database, first create a database named "IDG", which contains a table named "Author", which contains the following fields: ID, FirstName, LastName.

Then we need to create an entity class (POCO class), and the following is the entity class Author corresponding to the Author table in the database IDG:

public class Author
 {
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
 }

The extension method Query() in Dapper allows you to retrieve data from the database and populate it into the object model.

The following method is to retrieve all records from the Author table, store them in memory, and finally return a collection:

public List<Author> ReadAll()
  {
   using (IDbConnection db = new SqlConnection(["AdventureWorks"].ConnectionString))
   {
    return <Author>
    ("Select * From Author").ToList();
   }
  }

Note that you need to include the Dapper namespace in the program to use Dapper:

using Dapper;

The following method demonstrates how to retrieve a specific record from an Auhor table:

public Author Find(int id)
  {
   using (IDbConnection db = new SqlConnection(["AdventureWorks"].ConnectionString))
   {
    return <Author>("Select * From Author WHERE Id = @Id", new { id }).SingleOrDefault();
   }
  }

The Execute() method in Dapper can be used to insert, update, and delete data into the database. This method returns an integer indicating the number of rows affected when the query is executed.

The following method demonstrates how to update a record with Dapper:

public int Update(Author author)
  {
   using (IDbConnection db = new SqlConnection(["AdventureWorks"].ConnectionString))
   {
    string sqlQuery = "UPDATE Author SET FirstName = @FirstName, " +
    " LastName = @LastName “ + “WHERE Id = @Id";
    int rowsAffected = (sqlQuery, author);
    return rowsAffected;
   }
  }

As seen in the above code, the Update() method returns the number of affected rows, that is, the number of updated records. In this case, only one record will be updated, so when the method succeeds, it will return 1.

Use of stored procedures in Dapper:

To use Dapper to process stored procedures, you need to display the command type when calling the Query() or Exectue() method. The following example demonstrates how to use Dapper to handle stored procedures:

public List<Author> Read()
  {
   using (IDbConnection db = new SqlConnection(["AdventureWorks"].ConnectionString))
   {
    string readSp = "GetAllAuthors";
    return <Author>(readSp, commandType: ).ToList();
   }
  }

Dapper also supports transactions, for example, if you need it, we can use transaction operations, for this, you can use the BeginTransaction() and EndTransaction() methods, just like you usually handle transactions in, and then you need to write transaction statements in the BeginTransaction() and EndTransaction() method calls.

Dapper is very light and very simple to use, it won't generate SQL for you, but it can easily map query results to POCOs (normal old CLR objects). On top of that, you can get faster execution than EntityFrameWork, in fact, almost the same.

This is an article I translated from abroad, aiming to improve my English level and deepen my accumulation of knowledge points. If it can help some friends, it would be great.

Original link:

/article/3025784/application-development/

Summarize

The above is the entire content of this article. I hope that the content of this article has certain reference value for everyone's study or work. If you have any questions, you can leave a message to communicate. Thank you for your support.