写在前面
读取大容量文本数据到数据库,可以用流的方式读取到内存然后再拼接成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);
}
}
}