mizuneko
4/23/2015 - 2:16 PM

SqlBulkCopy.cs

public int ImportCsvData(string fullPath, string targetTableName, SqlConnection conn)
{
    int affected = 0;
    System.IO.FileInfo fi = new System.IO.FileInfo(fullPath);
    
    using (System.Data.Odbc.OdbcConnection odbcconn = new System.Data.Odbc.OdbcConnection(@"DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=" + fi.DirectoryName))
    {
        odbcconn.Open();
        System.Data.Odbc.OdbcCommand cmd  = odbcconn.CreateCommand();

        cmd.CommandText = "select * from " + fi.Name;
        System.Data.Odbc.OdbcDataReader reader = cmd.ExecuteReader();
        using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(conn)){
            bcp.DestinationTableName = targetTableName;
            bcp.NotifyAfter = 1000;
            bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
            bcp.WriteToServer(reader);
            affected =bcp.BatchSize;
        }
    }
    
    return affected;
}

private void bcp_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    Console.WriteLine("インポート実行中:" + (int)e.RowsCopied + "件");
}