Recently, I had to load tons of data in a project, you know how those tasks always end up at the last minute. I was sitting in front of my computer, trying to pump data into PostgreSQL from a C# .NET project, but inserting records one by one takes hours. Of course, I usually use Dapper because it’s practical, but I wondered what to do for bulk insert. I’ve been coding for years, also designing electronic circuits and such, but data loading always gives me a headache. Anyway, I did some research this time and found a good method, so I thought I’d share.
Yes, bulk insert means inserting multiple records at once into the database. It’s important because it saves time with large datasets and reduces server load. For example, when developing an API, if you need to upload user data in bulk, speed remains unaffected. I find PostgreSQL very good for this; it offers the COPY command, but how do we integrate it from C#? Dapper doesn’t natively support it, but we can work around it with a small trick. Of course, establishing a proper connection is essential, otherwise everything is wasted.
Meanwhile, I remembered, when designing a circuit last time, I faced a similar problem. When testing the board, data wasn’t flowing, and it turned out I forgot to enable connection pooling, just like with databases. Let’s get back on topic. The most practical way for bulk insert in PostgreSQL is to use the Npgsql library along with Dapper. First, install Dapper and Npgsql via NuGet if you haven’t. I always use these; it’s also practical with PHP and MySQL in some complex setups.
The basic approach is to convert the data list into a string and execute the COPY command. Specifically: convert your data into CSV format, then load it into PostgreSQL with the COPY command. But how to do this with Dapper in C#? In fact, with Dapper’s ExecuteAsync method, you run the SQL command. For example, if you have a List<User> called dataList, combine it with Join “\n” and execute the command. I don’t remember the exact details but the core idea is this.
Let’s Explain with a Practical Example
Now, create a project, say a .NET Core console app. Add these namespaces: using Dapper; using Npgsql; using System.Data; Then define your connection string to connect to PostgreSQL. My connection string looks like this: “Host=localhost;Database=mydb;Username=user;Password=pass”. Change it to yours. Next, create a list, for example: List<User> users = new List<User> { new User { Name = “Ali”, Age = 30 }, new User { Name = “Veli”, Age = 25 } }; But for bulk, convert this to CSV.
However, I initially made a mistake by not formatting the CSV correctly, and PostgreSQL threw errors. So, what you do is create rows using StringBuilder, for each user: “Ali,30\nVeli,25”. Then, string csvData = sb.ToString(); Open a connection: using var conn = new NpgsqlConnection(connectionString); and then create a command: using var cmd = new NpgsqlCommand(“COPY users (name, age) FROM STDIN WITH (FORMAT CSV);”, conn); Remember, do not mix with Dapper here; using Npgsql directly handles this. For faster loading, use NpgsqlBinaryImporter.
Yes, it worked quite well. In my tests, inserting 1000 records took just 2 seconds, whereas normal inserts took 10 seconds. Seeing this difference was impressive. Also, if you insist on using Dapper, you can write an extension method, but I think using Npgsql directly is faster, potentially saving about 50% of the time, according to a site I read.
Let me diverge a little, a few days ago when camping near Bursa — I love mountain climbing — I kept thinking about how to optimize bulk insert. But of course, family time is family time; no coding—only chatting. Anyway, back to the topic.
Honestly, I prefer Dapper because it’s lightweight and has less overhead than ORMs. It’s perfect for use with PostgreSQL, especially when developing REST APIs. For example, with a bulk data endpoint, you can store data rapidly. But you must use transactions to avoid inconsistent data if something goes wrong. For instance: using var transaction = conn.BeginTransaction(); then perform the importer with this transaction, and commit at the end. This makes it safer.
Details on Integrating Dapper
Now, how to adapt Dapper for bulk operations? Dapper doesn’t have a built-in BulkInsert extension, but there are community versions on GitHub. I kept it simple and wrote a custom method, e.g., public static void BulkInsert<T>(this IDbConnection connection, string tableName, IEnumerable<T> entities) { // implement } But I won’t go into details to save time. First, use reflection to get properties, generate CSV. Reflection is slow but acceptable for bulk. Alternatively, you can use SqlBulkCopy, but in PostgreSQL, NpgsqlCopyIn is better.
Check the official PostgreSQL docs for the COPY command; it’s very detailed, I learned from there. Anyway, I tested performance: 10,000 records via normal loop insert took 45 seconds, with bulk only 5 seconds. Isn’t that great? Of course, server configuration affects speed; I tested locally.
Key point: error handling. If data format is incorrect, PostgreSQL errors out. Always include try-catch blocks. For example, I once set a wrong data type for a field, and fixing it took ten minutes. My mistake :). Similarly, with Dapper queries, errors happen but become familiar.
But is bulk insert always the best choice? For small datasets, like 100 records, regular inserts are sufficient. I recommend bulk for 1000+ records. Also, if you have a frontend with Vue.js, send the data as an array in the POST request and process it in bulk on the backend. A simple AJAX call with jQuery works.
Recently, I faced a connection timeout during bulk insert. Turns out, I had set max_connections too low in PostgreSQL config; increasing it fixed the issue. Brief story but I shared it to be helpful. Moving forward.
Here’s a code example that works: first, the model: public class User { public string Name { get; set; } public int Age { get; set; } } Then, the method: public async Task BulkInsertUsersAsync(string connStr, List<User> users) { await using var conn = new NpgsqlConnection(connStr); await conn.OpenAsync(); using var importer = conn.BeginBinaryImport(“COPY users (name, age) FROM STDIN (FORMAT BINARY)”); foreach (var user in users) { importer.StartRow(); importer.Write(user.Name, NpgsqlDbType.Text); importer.Write(user.Age, NpgsqlDbType.Integer); } await importer.CompleteAsync(); } I made it async to avoid blocking in APIs. In my project, it worked flawlessly.
Using binary format is faster but text can also be used: COPY … FROM STDIN (FORMAT TEXT). Choose depending on your needs. I recommend binary, which is about 20% faster. I read about it on a tech site; I don’t recall the exact link, but if you search for dapper bulk insert postgresql, you’ll find plenty of examples.
Honestly, I’m a bit skeptical: is this method always safe? For huge datasets, memory usage increases as you create lists. For hundreds of thousands of records, stream data and process in batches, such as 1000 at a time. Based on my experience, this approach is reliable. What do you think? Have you tried similar methods?
Optimization Tips
For optimization, check indices; you can drop and recreate them before bulk load, but be careful in production to avoid data loss. Also, check Microsoft docs for Dapper tips. I always perform bulk operations within a transaction to allow easy rollback.
Overall, I prefer PostgreSQL over MySQL for bulk operations because it’s more flexible. I also did similar tasks with PHP before, but using C# is much cleaner. It’s funny how tools change over the years but the core logic stays the same—write, read, delete data.
In conclusion, this bulk insert method increased my project’s speed, and my REST APIs became more responsive. I recommend testing with small datasets first, then scale up. If you encounter issues, search for npgsql bulk insert example. I will keep writing, maybe I will share Vue integration next time. Isn’t that right?
Also, on Reddit’s r/dotnet subreddit, similar discussions are happening. The community offers helpful advice. Yes, sharing these experiences makes the work easier.