Keywords: C# | Dapper.NET | SQL Server | List Insertion | Database Optimization
Abstract: This article explains how to use Dapper.NET to insert a C# List into a SQL Server database efficiently, avoiding manual loops and leveraging Dapper's object mapping capabilities. Based on Dapper's principles, it provides code examples and best practices to streamline database operations.
Introduction
In C# development, when inserting multiple records from a list into a database, traditional approaches often involve explicit loops. Dapper.NET, as a lightweight ORM, offers a more efficient solution, simplifying code and enhancing performance.
Dapper's Object Mapping Principle
Dapper maps object properties to SQL parameters based on naming conventions. For instance, if you have a class ProcessLog with properties like ID, ST_TIME, etc., and a SQL query with parameters @ID, @ST_TIME, Dapper automatically matches them without additional configuration.
Inserting a Single Object
To insert a single object, use the Execute method. Example: connection.Execute("INSERT INTO PROCESS_LOGS VALUES (@ID, @ST_TIME, @ED_TIME, @TD_TIME)", new ProcessLog { ID = 1, ST_TIME = DateTime.Now }); Ensure that property names align with parameter names.
Batch Insertion with Lists
Dapper allows passing the entire list as a parameter to avoid manual loops. Example: connection.Execute("INSERT INTO PROCESS_LOGS VALUES (@ID, @ST_TIME, @ED_TIME, @TD_TIME)", processList); where processList is of type List<ProcessLog>. Dapper internally iterates over the list for insertion.
Code Example and Best Practices
Ensure that property names in the C# class match the parameter names in the SQL query. A complete example involves defining the class and using Dapper for insertion. This approach reduces database round-trips and improves efficiency. In code, represent generic lists as List<T> to prevent parsing errors.
Conclusion
Using Dapper.NET for list insertion simplifies code and boosts performance through efficient mapping. It is a recommended practice for batch operations in .NET applications.