写在前面
读取大容量文本数据到数据库,可以用流的方式读取到内存然后再拼接成T-SQL语句插入,显然,这个是效率极低的做法。
下面给大家展示一个超快速的大容量读取文本插入SqlServer数据库,用的是Sql * Loader (sqlldr)
SqlServer实现方法点击我
Mysql实现方法点击我
分三步,第一步创建表空间、用户和数据表,第二步构造出一个文本数据,第三步读取文本数据到数据库,完整代码在文章结尾处。
第一步:第一步创建表空间、用户和数据表,其中的表空间和用户在这里不作为展示,自己想要的表空间下新建一张Test表代码如下
--创建数据表
CREATE TABLE Test(
test1 int NULL,
test2 int NULL
)
第二步:构造测试数据到文本,结果如下图
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());
}
}
第三步:读取文本数据到数据库,这一步分两个部分,一个部分是构建一个控制文件CTL和另一个部分启动CMD进程执行这个sqlldr指令
第一部分:构建控制CTL文件,如图3.1
CreateOracleControlFile("Test", $"test.txt", $"Oracle.ctl");
/// <summary>
/// 构建控制CTL文件
/// </summary>
/// <param name="dbTable">数据表名称</param>
/// <param name="dataFile">源数据文件</param>
/// <param name="oracleCtl">控制CTL文件</param>
public static void CreateOracleControlFile(string dbTable, string dataFile, string oracleCtl)
{
string strOracleFile = Path.Combine(Environment.CurrentDirectory, oracleCtl);
string bcpFilePath = Path.Combine(Environment.CurrentDirectory, dataFile);
bcpFilePath = bcpFilePath.Replace(@"\", @"/");
try
{
if (File.Exists(strOracleFile))
{
File.Delete(strOracleFile);
}
if (!File.Exists(strOracleFile))
{
using (StreamWriter sw = File.CreateText(strOracleFile))
{
sw.WriteLine($"load data infile '{bcpFilePath}'");
sw.WriteLine($"append into table {dbTable}");
sw.WriteLine($"fields terminated by ','");
sw.WriteLine($"(Test_ID,Channel_ID,Date_Time,Data_Point,Test_Time,Step_Time,Cycle_ID,Step_ID,Currentt,Voltage,Charge_Capacity,Discharge_Capacity,Charge_Energy,Discharge_Energy,Data_Flags,Limit_ID,Data_Flags2)");
}
}
}
catch (Exception)
{
throw;
}
}
第二部分:启动CMD进程执行sqlldr命令,结果如图3.2,数据库查询结果3.3
ExcuteOracle($"Oracle.ctl");
/// <summary>
/// 启动CMD进程执行sqlldr
/// </summary>
/// <param name="oracleCtl">控制文件</param>
public static void ExcuteOracle(string oracleCtl)
{
string strOracleFile = Path.Combine(Environment.CurrentDirectory, oracleCtl);
//我的用户名是c##sa,密码是arbin
string command = $@"sqlldr userid=c##sa/arbin control={strOracleFile}";
Process.Start("CMD.exe", "/K " + command);
Thread.Sleep(15000);
KillProcess("CMD");
}
图3.1
图3.2
图3.3
完整代码
class Program
{
static void Main(string[] args)
{
SetData();
BCP_Oracle();
}
/// <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_Oracle()
{
CreateOracleControlFile("Test", $"test.txt", $"Oracle.ctl");
ExcuteOracle($"Oracle.ctl");
}
/// <summary>
/// 构建控制CTL文件
/// </summary>
/// <param name="dbTable">数据表名称</param>
/// <param name="dataFile">源数据文件</param>
/// <param name="oracleCtl">控制CTL文件</param>
public static void CreateOracleControlFile(string dbTable, string dataFile, string oracleCtl)
{
string strOracleFile = Path.Combine(Environment.CurrentDirectory, oracleCtl);
string bcpFilePath = Path.Combine(Environment.CurrentDirectory, dataFile);
bcpFilePath = bcpFilePath.Replace(@"\", @"/");
try
{
if (File.Exists(strOracleFile))
{
File.Delete(strOracleFile);
}
if (!File.Exists(strOracleFile))
{
using (StreamWriter sw = File.CreateText(strOracleFile))
{
sw.WriteLine($"load data infile '{bcpFilePath}'");
sw.WriteLine($"append into table {dbTable}");
sw.WriteLine($"fields terminated by ','");
sw.WriteLine($"(test1,test2)");
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 启动CMD进程执行sqlldr
/// </summary>
/// <param name="oracleCtl">控制文件</param>
public static void ExcuteOracle(string oracleCtl)
{
string strOracleFile = Path.Combine(Environment.CurrentDirectory, oracleCtl);
//我的用户名是c##sa,密码是arbin
string command = $@"sqlldr userid=c##sa/arbin control={strOracleFile}";
Process.Start("CMD.exe", "/K " + command);
Thread.Sleep(15000);
KillProcess("CMD");
}
public static void KillProcess(string name)
{
foreach (Process process in Process.GetProcessesByName(name))
{
try
{
process.Kill();
process.WaitForExit();
}
catch (InvalidOperationException)
{
}
}
}
}