说明

各种原因要导入导出几张表的数据，表中有几个大字段，各种不方便……，就写了这个小工具

项目

.net framework 4.8+Oracle.ManagedDataAccess

代码

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text.Json;
using System.Threading;
using System.Windows.Forms;

namespace OracleLargeObjectHelper
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}

string connectionString = "";
OracleLargeObjectHandler oracleLargeObjectHandler = ;

///
/// 导出
///
/// <param name="sender">
/// <param name="e">
private void btnExportTable_Click(object sender, EventArgs e)
{
if (String.IsOrEmpty(txtExportTableName.Text))
{
MessageBox.Show("请输入导出表名！");
txtExportTableName.Focus();
return;
}

try
{
btnExportTable.Enabled = false;

if (oracleLargeObjectHandler == )
{
MessageBox.Show("请先测试连接！");
return;
}
int rowCount = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
oracleLargeObjectHandler.ExportTable(txtExportTableName.Text, txtExportTableName.Text + ".json", out rowCount);
sw.Stop();
MessageBox.Show($"导出成功，共计[{rowCount}]条记录，耗时[{sw.ElapsedMilliseconds / 1000.0}秒]");

}
catch (Exception ex)
{
MessageBox.Show("导出失败：" + ex.Message);
}
finally
{
btnExportTable.Enabled = true;
}
}

///
/// 导入
///
/// <param name="sender">
/// <param name="e">
private void btnImportTable_Click(object sender, EventArgs e)
{
//txtImportTableName
if (String.IsOrEmpty(txtImportTableName.Text))
{
MessageBox.Show("请输入导入表名！");
txtExportTableName.Focus();
return;
}

//txtinputFile
if (String.IsOrEmpty(txtinputFile.Text))
{
MessageBox.Show("请选择数据文件！");
txtinputFile.Focus();
return;
}

try
{
btnImportTable.Enabled = false;
if (oracleLargeObjectHandler == )
{
MessageBox.Show("请先测试连接！");
return;
}
int rowCount = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
oracleLargeObjectHandler.ImportTable(txtinputFile.Text, txtImportTableName.Text, out rowCount);
MessageBox.Show($"导入成功，共计[{rowCount}]条记录，耗时[{sw.ElapsedMilliseconds/1000.0}秒]");
}
catch (Exception ex)
{
MessageBox.Show("导入失败：" + ex.Message);
}
finally
{
btnImportTable.Enabled = true;
}
}

private void frmMain_Load(object sender, EventArgs e)
{

}

///
/// 测试链接
///
/// <param name="sender">
/// <param name="e">
private void btnTestConn_Click(object sender, EventArgs e)
{
if (String.IsOrEmpty(txtConnStr.Text))
{
MessageBox.Show("请输入连接字符串！");
txtConnStr.Focus();
return;
}
connectionString = txtConnStr.Text;

try
{
btnTestConn.Enabled = false;
var conn = new OracleConnection(connectionString);
conn.Open();
MessageBox.Show("连接成功！");
oracleLargeObjectHandler = new OracleLargeObjectHandler(connectionString);
}
catch (Exception ex)
{
MessageBox.Show("连接失败:" + ex.Message);
}
finally
{
btnTestConn.Enabled = true;
}

}

///
/// 选择导入文件
///
/// <param name="sender">
/// <param name="e">
private void btnSelectImportFile_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = $"选择导入文件";
ofd.Filter = "json文件| *.json;";
ofd.ValidateNames = true; //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
ofd.CheckFileExists = true; //验证路径有效性
ofd.CheckPathExists = true; //验证文件有效性
ofd.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory;
if (ofd.ShowDialog() == DialogResult.OK)
{
string file = ofd.FileName;//文件的完整路径
txtinputFile.Text = file;
//文件路径
var path = System.IO.Path.GetFullPath(file);
//文件名
string name = System.IO.Path.GetFileNameWithoutExtension(path);
txtImportTableName.Text = name;
}
}
}

public class OracleLargeObjectHandler
{
private readonly string _connectionString;

public OracleLargeObjectHandler(string connectionString)
{
_connectionString = connectionString;
}

public void ExportTable(string tableName, string outputFilePath, out int rowCount)
{
var conn = new OracleConnection(_connectionString);
conn.Open();
rowCount = 0;
// 获取表结构信息
var columns = GetTableColumns(conn, tableName);

var cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM {tableName}";

var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); // 重要：顺序访问大字段
var fs = new FileStream(outputFilePath, FileMode.Create);
var writer = new Utf8JsonWriter(fs);

writer.WriteStartArray();

while (reader.Read())
{
rowCount++;
writer.WriteStartObject();

for (int i = 0; i < reader.FieldCount; i++)
{
var column = columns[i];
writer.WritePropertyName(column.Name);

if (reader.IsDB(i))
{
writer.WriteValue();
}
else
{
switch (column.DataType)
{
case"BLOB":
var buffer = reader.GetOracleBlob(i).Value;
writer.WriteBase64StringValue(buffer);
break;
case"CLOB":
writer.WriteStringValue(reader.GetOracleClob(i).Value);
break;
case"DATE":
writer.WriteStringValue(reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm:ss"));
break;
case"TIMESTAMP":
writer.WriteStringValue(reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm:ss.fff"));
break;
case"NUMBER":
var value = reader.GetOracleDecimal(i);
writer.WriteNumberValue(value.ToDouble());
break;
default:
writer.WriteStringValue(reader[i].ToString());
break;
}
}
}

writer.WriteEndObject();
}

writer.WriteEndArray();

// 显式刷新缓冲区
writer.Flush();

writer.Dispose();
fs.Dispose();
reader.Dispose();
cmd.Dispose();
conn.Dispose();
}

public void ImportTable(string inputFilePath, string tableName, out int rowCount, int batchSize = 1000)
{
if (!File.Exists(inputFilePath))
{
throw new FileNotFoundException($"导出文件 {inputFilePath} 不存在");
}

var fileInfo = new FileInfo(inputFilePath);
if (fileInfo.Length == 0)
{
throw new InvalidDataException("导出文件为空，请检查导出操作是否成功");
}

// 添加JSON格式预验证
try
{
var jsonDoc = JsonDocument.Parse(File.ReadAllBytes(inputFilePath));
jsonDoc.Dispose();
}
catch (JsonException ex)
{
throw new InvalidDataException("JSON文件格式错误", ex);
}

var conn = new OracleConnection(_connectionString);
conn.Open();

var columns = GetTableColumns(conn, tableName);
var insertColumns = string.Join(", ", columns.ConvertAll(c => c.Name));
var parameters = string.Join(", ", columns.ConvertAll(c => $":{c.Name}"));

var transaction = conn.BeginTransaction();
var cmd = conn.CreateCommand();
cmd.CommandText = $"INSERT INTO {tableName} ({insertColumns}) VALUES ({parameters})";
cmd.BindByName = true;

var records = JsonSerializer.Deserialize<List<Dictionary>>(File.ReadAllBytes(inputFilePath));
rowCount = 0;

foreach (var record in records)
{
cmd.Parameters.Clear();

foreach (var column in columns)
{
var jsonElement = record[column.Name];

if (jsonElement.ValueKind == JsonValueKind.)
{
cmd.Parameters.Add(new OracleParameter(column.Name, DB.Value));
continue;
}

switch (column.DataType)
{
case"BLOB":
var blobParam = new OracleParameter(column.Name, OracleDbType.Blob);
blobParam.Value = jsonElement.GetBytesFromBase64();
cmd.Parameters.Add(blobParam);
break;
case"CLOB":
var clobValue = jsonElement.GetString();
var clobParam = new OracleParameter(column.Name, OracleDbType.Clob)
{
Value = clobValue
};
cmd.Parameters.Add(clobParam);
break;
case"DATE":
var dateValue = DateTime.ParseExact(
jsonElement.GetString(),
"yyyy-MM-dd HH:mm:ss", // 与导出格式保持一致
CultureInfo.InvariantCulture
);
var dateParam = new OracleParameter(column.Name, OracleDbType.Date)
{
Value = dateValue
};
cmd.Parameters.Add(dateParam);
break;

case"TIMESTAMP":
var timestampValue = DateTime.ParseExact(
jsonElement.GetString(),
"yyyy-MM-dd HH:mm:ss.fff",// 与导出格式保持一致
CultureInfo.InvariantCulture
);
var timestampParam = new OracleParameter(column.Name, OracleDbType.TimeStamp)
{
Value = timestampValue
};
cmd.Parameters.Add(timestampParam);
break;

case"NUMBER":
cmd.Parameters.Add(new OracleParameter(column.Name,
OracleDbType.Decimal,
jsonElement.GetDecimal(),
ParameterDirection.Input));
break;
case"VARCHAR2":
case"CHAR":
var strValue = jsonElement.GetString();
if (strValue.Length > column.Length)
{
throw new InvalidOperationException(
$"字段 {column.Name} 值长度超过限制({column.Length})");
}
cmd.Parameters.Add(new OracleParameter(column.Name, strValue));
break;
default:
cmd.Parameters.Add(new OracleParameter(column.Name, jsonElement.GetString()));
break;
}
}

cmd.ExecuteNonQuery();

if (++rowCount % batchSize == 0)
{
transaction.Commit();
transaction.Dispose();
transaction = conn.BeginTransaction();
}
}

transaction.Commit();

transaction.Dispose();
cmd.Dispose();
conn.Close();
}

private List GetTableColumns(OracleConnection conn, string tableName)
{
var columns = new List();

var cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT
column_name,
data_type,
data_precision,
data_scale,
data_length
FROM all_tab_cols
WHERE table_name = :tableName
ORDER BY column_id";

cmd.Parameters.Add(new OracleParameter("tableName", tableName.ToUpper()));

var reader = cmd.ExecuteReader();
while (reader.Read())
{
columns.Add(new TableColumn(
reader.GetString(0),
reader.GetString(1),
reader.IsDB(2) ? (int?) : reader.GetInt32(2),
reader.IsDB(3) ? (int?) : reader.GetInt32(3),
reader.GetInt32(4)
));
}

reader.Dispose();
cmd.Dispose();

return columns;
}

private class TableColumn
{
public string Name { get; }
public string DataType { get; }
public int? Precision { get; }
public int? Scale { get; }
public int Length { get; }

public TableColumn(string name, string dataType, int? precision, int? scale, int length)
{
Name = name;
DataType = dataType.ToUpper();
Precision = precision;
Scale = scale;
Length = length;
}
}
}

}