c# 读取大容量文本插入SqlServer数据库（超详细）

写在前面
读取大容量文本数据到数据库，可以用流的方式读取到内存然后再拼接成T-SQL语句插入，显然，这个是效率极低的做法。
下面给大家展示一个超快速的大容量读取文本插入SqlServer数据库，用的是BULK INSERT
Oracle实现方法点击我
Mysql实现方法点击我
分三步，第一步创建数据库和表，第二步构造出一个文本数据，第三步读取文本数据到数据库，完整代码在文章结尾处。

第一步：创建数据库和创建数据表

--创建数据库
CREATE DATABASE MDBIArbinXMasterInfo;
--创建数据表
USE [MDBIArbinXMasterInfo]
GO
/****** Object:  Table [dbo].[Test]    Script Date: 2021/12/17 15:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
	[test1] [int] NULL,
	[test2] [int] NULL
) ON [PRIMARY]

GO

第二步：构造测试数据到文本，结果如下图

    public static void SetData()
        {
            //数据的存放路径
            string bcpFilePath = Path.Combine(Environment.CurrentDirectory, "test.txt");
            StringBuilder stringBuilder = new StringBuilder();
            using (StreamWriter sw = new StreamWriter(bcpFilePath))
            {
                for (int i = 0; i < 1000000; i++)
                {
                    stringBuilder.AppendLine($"{i}, {i}");
                }
                sw.Write(stringBuilder.ToString());
            }
        }

第三步：读取文本数据到数据库，结果如下图

  public static void BCP_SqlServer()
        {
            //数据的存放路径
            string bcpFilePath = Path.Combine(Environment.CurrentDirectory, "test.txt");
            //数据库连接字符串,MDBIArbinXMasterInfo是数据库
            string strConnectionString = $"server=.;uid=sa;pwd=arbin;database=MDBIArbinXMasterInfo;Min Pool Size = 5;Max Pool Size=512;MultipleActiveResultSets=true;Asynchronous Processing=true;Connection Lifetime=10";
            //BULK INSERT语句（Test是我的数据库表的名称，FIELDTERMINATOR代表文本中数据列是以逗号隔开的，ROWTERMINATOR代表文本中每一行对应数据库表的每一行
            string strSqlCmd = $"BULK INSERT Test FROM '{bcpFilePath}' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\r\n')";
            IDbConnection Connection = new SqlConnection();
            Connection.ConnectionString = strConnectionString;
            IDbCommand Command = new SqlCommand();
            Command.Connection = Connection;
            Command.CommandText = strSqlCmd;
            Command.CommandType = CommandType.Text;
            if (Connection.State != ConnectionState.Open)
            {
                try
                {
                    Connection.Open();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return;
                }
            }
            try
            {
                Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

完整代码

       class Program
    {
        static void Main(string[] args)
        {
            SetData();
            BCP_SqlServer();
        }
        /// <summary>
        /// 构造测试数据
        /// </summary>
        public static void SetData()
        {
            //数据的存放路径
            string bcpFilePath = Path.Combine(Environment.CurrentDirectory, "test.txt");
            StringBuilder stringBuilder = new StringBuilder();
            using (StreamWriter sw = new StreamWriter(bcpFilePath))
            {
                for (int i = 0; i < 10000; i++)
                {
                    stringBuilder.AppendLine($"{i}, {i}");
                }
                sw.Write(stringBuilder.ToString());
            }
        }
        public static void BCP_SqlServer()
        {
            //数据的存放路径
            string bcpFilePath = Path.Combine(Environment.CurrentDirectory, "test.txt");
            //数据库连接字符串,MDBIArbinXMasterInfo是数据库
            string strConnectionString = $"server=.;uid=sa;pwd=arbin;database=MDBIArbinXMasterInfo;Min Pool Size = 5;Max Pool Size=512;MultipleActiveResultSets=true;Asynchronous Processing=true;Connection Lifetime=10";
            //BULK INSERT语句（Test是我的数据库表的名称，FIELDTERMINATOR代表文本中数据列是以逗号隔开的，ROWTERMINATOR代表文本中每一行对应数据库表的每一行
            string strSqlCmd = $"BULK INSERT Test FROM '{bcpFilePath}' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\r\n')";
            IDbConnection Connection = new SqlConnection();
            Connection.ConnectionString = strConnectionString;
            IDbCommand Command = new SqlCommand();
            Command.Connection = Connection;
            Command.CommandText = strSqlCmd;
            Command.CommandType = CommandType.Text;
            if (Connection.State != ConnectionState.Open)
            {
                try
                {
                    Connection.Open();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return;
                }
            }
            try
            {
                Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }