C # in the use of dapper tutorial

Time:2021-10-27

1、 What is dapper

Dapper is a lightweight ORM tool (GitHub). If you use Entity Framework and NHibernate to handle big data access and relationship mapping in a small project, it will be a bit of a killing. You also think ORM saves time and effort. At this time, dapper will be your best choice.

2、 Advantages of dapper

  1. Lightweight. There is only one file sqlmapper.cs, which compiles a small DLL of 40K
  2. Fast. The speed of dapper is close to that of IDataReader, and the data in the fetching list exceeds the DataTable.
  3. Support multiple databases. Dapper can work under all ado.net providers, including SQLite, sqlce, Firebird, Oracle, mysql, PostgreSQL and SQL server
  4. You can map one-to-one, one to many, many to many and other relationships.
  5. High performance. The sequence queue of IDataReader is reflected by emit to quickly obtain and generate objects, with good performance.
  6. Support Framework2.0, 3.0, 3.5, 4.0, 4.5
  7. Dapper syntax is very simple. And there is no need to accommodate the design of the database.

3、 Method of use

1. Add nuget package dapper

  

2. Add a string connecting to the database from the configuration file


<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8"/>
  </startup>
  <connectionStrings>
    <add name="MyStrConn" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DHR_DB;Integrated Security=True" />
  </connectionStrings>
</configuration>

3. Add dappertools.cs (add, batch add, delete, batch delete, modify, batch modify, query, query in operation, multi statement operation)

// <copyright file="DapperTools.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace CSharpStudy
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Dapper;

    internal class DapperTools
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["MyStrConn"].ConnectionString;

        /// <summary>
        ///Add
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > incoming entity type. < / param >
        /// <returns>int.</returns>
        public static int Add<T>(string sql, T t)
            where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Batch add
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > pass in generic class. < / param >
        /// <returns>int.</returns>
        public static int Add<T>(string sql, List<T> t)
            where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Delete
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > incoming entity type. < / param >
        /// <returns>int.</returns>
        public static int Delete<T>(string sql, T t)
              where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Batch delete
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > pass in generic class. < / param >
        /// <returns>int.</returns>
        public static int Delete<T>(string sql, List<T> t)
              where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Modification
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > incoming entity type. < / param >
        /// <returns>int.</returns>
        public static int Update<T>(string sql, T t)
              where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Batch modification
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > pass in generic class. < / param >
        /// <returns>int.</returns>
        public static int Update<T>(string sql, List<T> t)
              where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        ///Inquiry
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< returns > generic class. < / returns >
        public static List<T> Query<T>(string sql)
             where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql).ToList();
            }
        }

        /// <summary>
        ///Query the specified data
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< param name = "t" > pass in generic class. < / param >
        ///< returns > class. < / returns >
        public static T Query<T>(string sql, T t)
             where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql, t).SingleOrDefault();
            }
        }

        /// <summary>
        ///The in operation of the query
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        ///< returns > generic class. < / returns >
        public static List<T> Query<T>(string sql, int[] ids)
            where T : class
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                return connection.Query<T>(sql, new { ids }).ToList();
            }
        }

        /// <summary>
        ///Multi statement operation
        /// </summary>
        ///< typeparam name = "t" > entity type. < / typeparam >
        ///< param name = "SQL" > pass in the SQL execution statement. < / param >
        public static void QueryMultiple(string sql)
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            {
                var multiReader = connection.QueryMultiple(sql);
                var userInfo = multiReader.Read<UserInfo>();
                var student = multiReader.Read<Student>();

                multiReader.Dispose();
            }
        }
    }
}

4. Add test classes student.cs and userinfo. Add table data


 public class Student
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }

        public string Memo { get; set; }

        public DateTime CreateTime { get; set; }

        public DateTime UpdateTime { get; set; }
    }

public class UserInfo
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }

        public string Memo { get; set; }

        public DateTime CreateTime { get; set; }

        public DateTime UpdateTime { get; set; }
    }

5. Call method

//-----------------------------------------------------------------------
// <copyright file="Program.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>
// <author>John Doe</author>
//-----------------------------------------------------------------------

namespace CSharpStudy
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Dapper;

    internal class Program
    {
        private static void Main(string[] args)
        {
            //---------------- add--------------
            // UserInfo user = new UserInfo();
            //User.name = "99";
            // user.Age = 10;
            //User.memo = "this is a note";
            // user.CreateTime = DateTime.Now;

            // string sql = "Insert into UserInfo(Name,Age,Memo,CreateTime) values (@name, @Age, @Memo,@CreateTime)";
            // int result = DapperTools.Add<UserInfo>(sql, user);
            // if (result > 0)
            // {
            //Console.writeline ("added successfully");
            //     Console.ReadKey();
            // }

            //---------------- batch add--------------
            // UserInfo user = new UserInfo();
            //User.name = "Li Kui";
            // user.Age = 50;
            //User.memo = "this is a note";
            // user.CreateTime = DateTime.Now;

            // UserInfo user2 = new UserInfo();
            //User2.name = "Liang Shanbo";
            // user2.Age = 54;
            //User2. Memo = "this is a note";
            // user2.CreateTime = DateTime.Now;

            // List<UserInfo> list = new List<UserInfo>();
            // list.Add(user);
            // list.Add(user2);

            // string sql = "Insert into UserInfo(Name,Age,Memo,CreateTime) values (@name, @Age, @Memo,@CreateTime)";
            // int result = DapperTools.Add<UserInfo>(sql, list);
            // if (result > 0)
            // {
            //Console.writeline ("added successfully");
            //     Console.ReadKey();
            // }

            //---------------- delete--------------
            // UserInfo user = new UserInfo();
            // user.Id = 18;

            // string sql = "delete from UserInfo where [email protected]";
            // int result = DapperTools.Delete<UserInfo>(sql, user);
            // if (result > 0)
            // {
            //Console.writeline ("deletion succeeded");
            //     Console.ReadKey();
            // }

            //---------------- batch deletion--------------
            // UserInfo user = new UserInfo();
            // user.Id = 15;

            // UserInfo user2 = new UserInfo();
            // user2.Id = 16;

            // List<UserInfo> list = new List<UserInfo>();
            // list.Add(user);
            // list.Add(user2);

            // string sql = "delete from UserInfo where [email protected]";
            // int result = DapperTools.Delete<UserInfo>(sql, list);
            // if (result > 0)
            // {
            //Console.writeline ("added successfully");
            //     Console.ReadKey();
            // }

            //---------------- modification--------------
            // UserInfo user = new UserInfo();
            // user.Id = 14;
            //User.name = "99";

            // string sql = "update UserInfo set [email protected],UpdateTime=GETDATE() where [email protected]";
            // int result = DapperTools.Update<UserInfo>(sql, user);
            // if (result > 0)
            // {
            //Console.writeline ("modified successfully");
            //     Console.ReadKey();
            // }

            //---------------- batch modification--------------
            // UserInfo user = new UserInfo();
            // user.Id = 13;
            //User.name = "Wang Wen";

            // UserInfo user2 = new UserInfo();
            // user2.Id = 14;
            //User2.name = "Bingbing";

            // List<UserInfo> list = new List<UserInfo>();
            // list.Add(user);
            // list.Add(user2);

            // string sql = "update UserInfo set [email protected],UpdateTime=GETDATE() where [email protected]";
            // int result = DapperTools.Update<UserInfo>(sql, list);
            // if (result > 0)
            // {
            //Console.writeline ("modified successfully");
            //     Console.ReadKey();
            // }

            //---------------- query--------------
            // string sql = "select * from UserInfo";
            // List<UserInfo> list = DapperTools.Query<UserInfo>(sql);
            // foreach (var item in list)
            // {
            //     Console.WriteLine(item.Id + "-" + item.Name + "-" + item.Age + "-" + item.Memo);
            // }

            // Console.ReadKey();

            //---------------- query specified data--------------
            // UserInfo user = new UserInfo();
            // user.Id = 14;

            // string sql = "select * from UserInfo where [email protected]";
            // UserInfo userInfo = DapperTools.Query<UserInfo>(sql, user);

            // Console.WriteLine(userInfo.Id + "-" + userInfo.Name + "-" + userInfo.Age + "-" + userInfo.Memo);
            // Console.ReadKey();

            //---------------- query in operation--------------
            // string sql = "select * from UserInfo where Id in @ids";
            // int[] ids = { 1, 2 };

            // List<UserInfo> list = DapperTools.Query<UserInfo>(sql, ids);
            // foreach (var item in list)
            // {
            //     Console.WriteLine(item.Id + "-" + item.Name + "-" + item.Age + "-" + item.Memo);
            // }

            //---------------- multi statement operation--------------
            // string sql = "select * from userinfo;select * from student";

            // DapperTools.QueryMultiple(sql);
        }
    }
}

Source code:

Link: https://pan.baidu.com/s/1IM3diCqJCoSpvr11H6zddQ

Extraction code: 6ag9

Other information will be updated in the future. Please pay attention if you like!

My blog: https://www.cnblogs.com/duhaoran

This is the end of this article about the use of c# dapper. For more information about the use of c# dapper, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!