For projects with low traffic, I often use Entity Framework for database operations because of its rapid development speed, despite its slower performance. It saves a lot of SQL writing and makes it convenient to handle foreign keys, collections, and more. However, for websites that need to consider runtime speed, I have to use other ORM frameworks. I frequently use Dapper because of its high performance and flexible SQL writing. Below are some methods demonstrating the use of Dapper.

1. Connection String

var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlDiagnosticsDb"].ConnectionString);

When using Dapper, you don't need to worry about whether the connection is open; Dapper automatically checks and opens it if necessary during execution.

2. Insert

string query = "INSERT INTO Book(Name) VALUES(@name)";
conn.Execute(query, book);

If the book class has a Name property, you can write it conveniently. Alternatively, you can do:

string query = "INSERT INTO Book(Name) VALUES(@name)";
conn.Execute(query, new { @name = book.Name });

3. Update

string query = "UPDATE Book SET Name = @name WHERE Id = @id";
conn.Execute(query, book);

4. Delete

string query = "DELETE FROM Book WHERE Id = @id";
conn.Execute(query, book);
// or
conn.Execute(query, new { id = id });

5. Query

string query = "SELECT * FROM Book";
// No parameters, returns list
conn.Query<Book>(query).ToList();

// Single record
string query = "SELECT * FROM Book WHERE Id = @id";
book = conn.Query<Book>(query, new { id = id }).SingleOrDefault();

6. Using IN Clause

conn.Query<Users>("SELECT * FROM Users WHERE Id IN @ids", new { ids = new int[] { 1, 2, 3 } });

// Or using an array
conn.Query<Users>("SELECT * FROM Users WHERE Id IN @ids", new { ids = IDs.ToArray() });

Dapper requires parameterized queries for security; you cannot directly concatenate strings.

7. Batch Insert

conn.Execute(@"INSERT INTO MyTable (ColA, ColB) VALUES (@a, @b)",
    new[] { new { a = 1, b = 1 }, new { a = 2, b = 2 }, new { a = 3, b = 3 } });

Or you can pass a collection directly:

conn.Execute("INSERT INTO Users (Name) VALUES (@Name)", users);

Here, users is a collection of User objects; all data will be inserted in one go.

8. Multi-Table Query (One-to-Many)

string query = "SELECT * FROM Book b LEFT JOIN BookReview br ON br.BookId = b.Id WHERE b.Id = @id";
Book lookup = null;
var book = conn.Query<Book, BookReview, Book>(query,
    (book, bookReview) =>
    {
        if (lookup == null || lookup.Id != book.Id)
            lookup = book;
        if (bookReview != null)
            lookup.Reviews.Add(bookReview);
        return lookup;
    },
    new { id = id }).Distinct().SingleOrDefault();
return book;

Multi-table joins can be tricky. Here's another example:

var sql = @"SELECT * FROM Posts p JOIN Users u ON u.Id = p.OwnerId ORDER BY p.Id";
var data = conn.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; }, splitOn: "Id");

The splitOn parameter specifies the field where the mapping splits. Dapper maps all fields before the first Id (case-insensitive) to the first type, and from Id onward to the second type.

9. Three-Table Query (with Collecsion)

public partial class UserInfo
{
    public UserInfo()
    {
        this.Person = new HashSet<Person>();
        this.MyTYC = new HashSet<MyTYC>();
    }
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime? CreateTime { get; set; }
    public Movies Movies { get; set; }
    public virtual ICollection<MyTYC> MyTYC { get; set; }
}

public class Movies
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string ReleaseDate { get; set; }
    public string Genre { get; set; }
    public string Price { get; set; }
    public UserInfo UserInfo { get; set; }
}

public partial class MyTYC
{
    public int Id { get; set; }
    public string Name { get; set; }
}

string sql = @"SELECT * FROM UserInfo u
               INNER JOIN Movies m ON u.Id = m.ID
               INNER JOIN MyTYC t ON u.Id = t.Id";
var data = conn.Query<UserInfo, Movies, MyTYC, UserInfo>(sql,
    (u, m, t) => { u.Movies = m; u.MyTYC.Add(t); return u; });

Notice how the single object (Movies) and collection (MyTYC) are handled.

10. Multiple Result Sets

var sql = @"SELECT * FROM Customers WHERE CustomerId = @id;
             SELECT * FROM Orders WHERE CustomerId = @id;
             SELECT * FROM Returns WHERE CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new { id = selectedId }))
{
    var customer = multi.Read<Customer>().Single();
    var orders = multi.Read<Order>().ToList();
    var returns = multi.Read<Return>().ToList();
}

Another example:

class Program
{
    protected static SqlConnection GetConnection()
    {
        var connection = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=True");
        connection.Open();
        return connection;
    }

    static void Main(string[] args)
    {
        var sql = @"INSERT INTO [dbo].[Student] ([Name]) VALUES ('A1'); SELECT @@IDENTITY as A;
                     INSERT INTO [dbo].[Student] ([Name]) VALUES ('B1'); SELECT @@IDENTITY as A;
                     INSERT INTO [dbo].[Student] ([Name]) VALUES ('C1'); SELECT @@IDENTITY as A";

        using (SqlConnection connection = GetConnection())
        {
            List<int> ilist = new List<int>();
            var multi = connection.QueryMultiple(sql);

            while (!multi.IsConsumed)
            {
                var result = multi.Read().ToList()[0].A;
                if (result != null)
                {
                    ilist.Add(Convert.ToInt32(result));
                }
            }

            foreach (var item in ilist)
            {
                Console.WriteLine(item.ToString());
            }
        }
        Console.ReadLine();
    }
}

11. Managing Connection State

If you need to perform multiple operations, you can open the connection once and close it at the end:

conn.Open();
// Multiple Dapper operations...
conn.Close();