将List数据导入Snowflake表时遇AnsiString类型不匹配错误求助

需要将List<Proposal>中的数据批量导入Snowflake数据库的TCT_WEBAPP.DBO.PROPOSALS_KEK表中，相关代码及执行错误如下：

1. Proposal实体定义 #

public class Proposal
{
    public string ServiceCode { get; set; }
    public string ServiceDescription { get; set; }
    public string ProviderGrossTariff { get; set; }
    public string ProviderProposal { get; set; }
    public string CleanedProposal { get; set; }
    public string Notes { get; set; }
}

2. Snowflake表结构 #

create or replace TABLE TCT_WEBAPP.DBO.PROPOSALS_KEK (
    SERVICECODE VARCHAR(255),
    SERVICEDESCRIPTION VARCHAR(255),
    PROVIDERGROSSTARIFF VARCHAR(255),
    PROVIDERPROPOSAL VARCHAR(255),
    CLEANEDPROPOSAL VARCHAR(255),
    NOTES VARCHAR(255)
);

3. List转DataTable代码 #

private DataTable ConvertToDataTable(List<Proposal> proposals)
{
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("ServiceCode");
    dataTable.Columns.Add("ServiceDescription");
    dataTable.Columns.Add("ProviderGrossTariff");
    dataTable.Columns.Add("ProviderProposal");
    dataTable.Columns.Add("CleanedProposal");
    dataTable.Columns.Add("Notes");

    foreach (var proposal in proposals)
    {
        dataTable.Rows.Add(
            proposal.ServiceCode,
            proposal.ServiceDescription,
            proposal.ProviderGrossTariff,
            proposal.ProviderProposal,
            proposal.CleanedProposal,
            proposal.Notes
        );
    }

    return dataTable;
}

4. 导入代码及错误信息 #

DataTable dataTable = ConvertToDataTable(proposals);

using (IDbCommand command = conn.CreateCommand())
{
    // Set up a SQL command to execute stored procedure for bulk insert
    command.CommandText = "$\"COPY INTO DBO.SOME_TABLE FROM (SELECT * FROM @dataTable)\"";
    command.Parameters.Add(new SnowflakeDbParameter
    {
        ParameterName = "dataTable",
        Value = dataTable
    });
    command.ExecuteNonQuery();
}

执行时触发错误：

Error: No corresponding Snowflake type for type AnsiString. SqlState: , VendorCode: 270053, QueryId:

错误原因 #

1. Snowflake的ADO.NET驱动不支持直接将DataTable作为参数绑定，无法自动映射DataTable默认的AnsiString列类型到Snowflake的VARCHAR类型，导致类型匹配失败。
2. 代码中的COPY INTO语句存在错误：目标表写为DBO.SOME_TABLE，与实际目标表TCT_WEBAPP.DBO.PROPOSALS_KEK不符；且COPY INTO本身是用于从外部存储（如本地文件、云存储）导入数据的语法，不适合直接处理内存中的DataTable。

可行的批量导入方法 #

方法1：使用Snowflake官方.NET SDK的BulkCopy（推荐） #

官方驱动提供的批量复制API可直接处理内存数据集合，无需转DataTable：

using Snowflake.Data.Client;
using Snowflake.Data.Core;

// 假设已存在有效的Snowflake连接对象conn
using (var bulkCopy = new SnowflakeBulkCopy(conn, new SnowflakeBulkCopyOptions
{
    DestinationDatabase = "TCT_WEBAPP",
    DestinationSchema = "DBO",
    DestinationTableName = "PROPOSALS_KEK",
    BatchSize = 1000 // 根据数据量调整批次大小
}))
{
    var dataRows = proposals.Select(p => new object[]
    {
        p.ServiceCode,
        p.ServiceDescription,
        p.ProviderGrossTariff,
        p.ProviderProposal,
        p.CleanedProposal,
        p.Notes
    }).ToList();

    bulkCopy.WriteToServer(dataRows);
}

方法2：使用SqlBulkCopy（驱动兼容时适用） #

若Snowflake驱动兼容SqlBulkCopy，可直接用其批量插入：

using (var bulkCopy = new SqlBulkCopy(conn as SnowflakeDbConnection))
{
    bulkCopy.DestinationTableName = "TCT_WEBAPP.DBO.PROPOSALS_KEK";
    
    // 列名映射（实体与表列大小写一致可省略）
    bulkCopy.ColumnMappings.Add("ServiceCode", "SERVICECODE");
    bulkCopy.ColumnMappings.Add("ServiceDescription", "SERVICEDESCRIPTION");
    bulkCopy.ColumnMappings.Add("ProviderGrossTariff", "PROVIDERGROSSTARIFF");
    bulkCopy.ColumnMappings.Add("ProviderProposal", "PROVIDERPROPOSAL");
    bulkCopy.ColumnMappings.Add("CleanedProposal", "CLEANEDPROPOSAL");
    bulkCopy.ColumnMappings.Add("Notes", "NOTES");
    
    DataTable dataTable = ConvertToDataTable(proposals);
    bulkCopy.WriteToServer(dataTable);
}

方法3：生成参数化批量INSERT（小数据量适用） #

数据量较小时，可拼接参数化INSERT语句避免SQL注入：

using (IDbCommand command = conn.CreateCommand())
{
    StringBuilder sb = new StringBuilder("INSERT INTO TCT_WEBAPP.DBO.PROPOSALS_KEK (SERVICECODE, SERVICEDESCRIPTION, PROVIDERGROSSTARIFF, PROVIDERPROPOSAL, CLEANEDPROPOSAL, NOTES) VALUES ");
    
    List<string> valueClauses = new List<string>();
    int paramIndex = 0;
    
    foreach (var proposal in proposals)
    {
        string clause = $"(@p{paramIndex}, @p{paramIndex+1}, @p{paramIndex+2}, @p{paramIndex+3}, @p{paramIndex+4}, @p{paramIndex+5})";
        valueClauses.Add(clause);
        
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex}", Value = proposal.ServiceCode });
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex+1}", Value = proposal.ServiceDescription });
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex+2}", Value = proposal.ProviderGrossTariff });
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex+3}", Value = proposal.ProviderProposal });
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex+4}", Value = proposal.CleanedProposal });
        command.Parameters.Add(new SnowflakeDbParameter { ParameterName = $"@p{paramIndex+5}", Value = proposal.Notes });
        
        paramIndex += 6;
    }
    
    sb.Append(string.Join(", ", valueClauses));
    command.CommandText = sb.ToString();
    command.ExecuteNonQuery();
}

内容的提问来源于stack exchange，提问作者Beaver