Insert

Dapper Insert Operations

1. Dapper provides the Execute() extension method for running INSERT, UPDATE and DELETE SQL statements, returning the number of affected rows (int).
2. Insert workflow: write hand‑crafted INSERT SQL with named parameter placeholders @ParameterName; pass in an entity object for automatic parameter mapping to prevent SQL injection.
3. Syntax format: INSERT INTO TableName(Column1,Column2...) VALUES(@Param1,@Param2...)

Example

using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using System.Configuration;
using System.Collections.Generic;

namespace DapperDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // 1.Read connection string
            var connStr = ConfigurationManager.ConnectionStrings["StudentConnection"].ConnectionString;

            // 2.using statement automatically disposes database connection
            using (IDbConnection db = new MySqlConnection(connStr))
            {
                // 3.Write insert SQL to operate student table
                string sql = @"INSERT INTO student (FirstName, LastName, Email) 
                               VALUES (@FirstName, @LastName, @Email)";

                // 4.Construct entity object
                var stu = new Student()
                {
                    FirstName = "foxdevelop",
                    LastName = "com",
                    Email = "admin@foxdevelop.com"
                };

                // 5.Execute insert and get affected row count
                int rows = db.Execute(sql, stu);

                // 6.Query to verify inserted data
                List<Student> stuList = db.Query<Student>("SELECT * FROM student").ToList();
            }

            Console.ReadKey();
        }
    }

    // Student entity class (property names must match SQL @parameter names)
    public class Student
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
    }
}Code language: HTML, XML (xml)

Notes

1. Parameter Mapping Rules
   Dapper matches entity properties against @xxx SQL parameters automatically. Matching is case‑insensitive, though keeping names identical is recommended.
2. Execute Return Value
   int rowsAffected = db.Execute(sql, stu);

• Return value ≥1: Insert succeeded;
• Return value 0: No rows were inserted.

1. Connection string configuration (App.config)

<configuration>
  <connectionStrings>
    <add name="StudentConnection" 
         connectionString="server=localhost;database=testdb;uid=root;pwd=123456" 
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
</configuration>Code language: HTML, XML (xml)

Retrieve Auto‑Increment Primary Key ID for New Records (MySQL)

If the student table has an auto‑increment primary key Id, fetch the ID of the newly‑added row:

string sql = @"INSERT INTO student (FirstName, LastName, Email) 
               VALUES (@FirstName, @LastName, @Email);
               SELECT LAST_INSERT_ID();";

long newStudentId = db.ExecuteScalar<long>(sql, stu);Code language: HTML, XML (xml)

Insert