專心寫好程式

這部份的寫法改為逐行讀取(StreamReader) + SqlBulkCopy

主程式

using Data;
using System;
using System.IO;

namespace SqlBulkCopyToDb
{
    class Program
    {
        static void Main(string[] args)
        {
            #region 宣告

            var watch = new System.Diagnostics.Stopwatch();
            AccountSqlBulkCopy sbc = new AccountSqlBulkCopy();
            string line = "";

            #endregion

            try
            {
                watch.Restart();

                #region 逐行處理
                using (var sr = new StreamReader(Common.file))
                {
                    while ((line = sr.ReadLine()) != null)
                    {
                        sbc.lineToAccount(line, false);
                    }
                    sbc.lineToAccount(line, true);
                }
                #endregion
                Common.readerClose();
            
                //讀取結束，把未寫入的處理
                sbc.lineToAccount(line, true);

                Console.WriteLine("處理完畢~!!");

                watch.Stop();
                var elapsedMs = watch.ElapsedMilliseconds;
                Console.WriteLine("Time Cost:{0}", elapsedMs);

            }
            catch (Exception e)
            {
                Console.WriteLine("檔案處理失敗");
                Common.errorToFile(e.Message);
                Common.errorToFile(e.StackTrace);
            }

            Console.ReadLine();
        }
    }
}

Common Class

using System;
using System.IO;

namespace Data
{
    public class Common
    {
        public static string connectionString = @"Data Source=.\mssql2014;Initial Catalog=dropbox;Integrated Security=True";
        public static int maxCountToDb = 1000000;
        public static string file = @"../../../txt/bf_1.txt";

        private static FileStream _fileStream;
        private static BinaryReader _reader;
        
        public static BinaryReader reader
        {
            get
            {
                if (_reader != null) return _reader;

                _fileStream = File.Open(file, FileMode.Open, FileAccess.ReadWrite);
                _reader = new BinaryReader(_fileStream);

                return _reader;
            }
        }

        public static bool readerClose()
        {
            try
            {
                _fileStream.Close();
                _reader.Close();
                return true;
            }
            catch (Exception e)
            {
                Common.errorToFile(e.Message);
                Common.errorToFile(e.StackTrace);
                Console.ReadLine();
                return false;
            }
        }


        public static void errorToFile(string str)
        {
            TextWriter writer = new StreamWriter("error.txt", true);
            writer.Write(str + "\r\n");
            writer.Flush();
            writer.Close();
        }
    }
}

AccountSqlBulkCopy Class

using DapperToDb;
using Data;
using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkCopyToDb
{
    public class AccountSqlBulkCopy
    {
        #region field 宣告

        DataTable _dt = new DataTable();
        Account _acc;
        
        #endregion

        #region 建構子
        public AccountSqlBulkCopy()
        {
            #region 初始化 _dt
            _dt.Columns.Add("id", typeof(string));
            _dt.Columns.Add("pw", typeof(string));
            #endregion
        }
        #endregion

        // 整行轉為 account
        public void lineToAccount(string line, bool end)
        {
            try
            {
                _acc = new Account(line);
                DataRow row = _dt.NewRow();
                row["id"] = _acc.id;
                row["pw"] = _acc.pw;
                _dt.Rows.Add(row);
                Console.Write('.');

                if (_dt.Rows.Count >= Common.maxCountToDb || end)
                {
                    bulkWriteToDb(_dt);
                    _dt.Rows.Clear();
                }
            }
            catch (Exception e)
            {
                Common.errorToFile(line);
                Common.errorToFile(e.Message);
                Common.errorToFile(e.StackTrace);
                Console.ReadLine();
            }
        }

        // 透過 SqlBulkCopy 寫入資料庫
        public void bulkWriteToDb(DataTable dt)
        {
            using (SqlConnection connection = new SqlConnection(Common.connectionString))
            {
                SqlBulkCopy SBC = new SqlBulkCopy(connection);

                //複製到目的地的哪個資料表
                SBC.DestinationTableName = "dbo.account";
                SBC.BatchSize = 3000;
                SBC.BulkCopyTimeout = 300;

                //SBC.ColumnMappings.Add("id", "id");
                //SBC.ColumnMappings.Add("pw", "pw");

                connection.Open();

                //開始寫入
                SBC.WriteToServer(dt);
                SBC.Close();
            }
        }
    }
}

Account Class

using System;

namespace DapperToDb
{
    public class Account
    {
        public string id { get; set; }
        public string pw { get; set; }

        public Account(string line)
        {
            string[] lineArray = line.Split(':');
            if (lineArray[0].Trim().Length > 300) throw new Exception("id 太長");
            if (lineArray[1].Trim().Length > 70) throw new Exception("pw 太長");
            id = lineArray[0].Trim();
            pw = lineArray[1].Trim();
        }
    }
}