使用C#将数据从CSV文件批量拷贝到PostgreSQL数据库

using System;
using System.Data;
using System.IO;
using Npgsql;
using CsvHelper;

public class Program
{
    public static void Main()
    {
        string csvFilePath = "path/to/csv/file.csv";
        string connectionString = "Server=your_server;Port=your_port;Database=your_database;User Id=your_username;Password=your_password;";

        // 读取CSV文件
        using (var reader = new StreamReader(csvFilePath))
        using (var csv = new CsvReader(reader))
        {
            var records = csv.GetRecords<MyData>(); // MyData是一个自定义的类，用于存储CSV文件中的数据

            // 连接到PostgreSQL数据库
            using (var conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();

                // 创建数据表
                CreateDataTable(conn);

                // 插入数据
                InsertData(conn, records);

                // 关闭连接
                conn.Close();
            }
        }

        Console.WriteLine("数据已成功拷贝到PostgreSQL数据库。");
    }

    private static void CreateDataTable(NpgsqlConnection conn)
    {
        using (var cmd = new NpgsqlCommand())
        {
            cmd.Connection = conn;

            // 创建数据表的SQL语句，根据CSV文件的字段进行调整
            cmd.CommandText = @"
                CREATE TABLE IF NOT EXISTS my_table (
                    id SERIAL PRIMARY KEY,
                    column1 VARCHAR(255),
                    column2 INT,
                    column3 DECIMAL
                );";

            cmd.ExecuteNonQuery();
        }
    }

    private static void InsertData(NpgsqlConnection conn, IEnumerable<MyData> records)
    {
        using (var cmd = new NpgsqlCommand())
        {
            cmd.Connection = conn;

            // 插入数据的SQL语句，根据CSV文件的字段进行调整
            cmd.CommandText = @"
                INSERT INTO my_table (column1, column2, column3)
                VALUES (@Column1, @Column2, @Column3);";

            // 使用参数化查询来防止SQL注入
            cmd.Parameters.AddWithValue("@Column1", "");
            cmd.Parameters.AddWithValue("@Column2", 0);
            cmd.Parameters.AddWithValue("@Column3", 0.0);

            foreach (var record in records)
            {
                cmd.Parameters["@Column1"].Value = record.Column1;
                cmd.Parameters["@Column2"].Value = record.Column2;
                cmd.Parameters["@Column3"].Value = record.Column3;

                cmd.ExecuteNonQuery();
            }
        }
    }
}

public class MyData
{
    public string Column1 { get; set; }
    public int Column2 { get; set; }
    public decimal Column3 { get; set; }
}