private void BuildScriptWithInsertSQL(Table table, string tableName, IDataWrapper data, IDataFilter filter,
out object script)
{
StringBuilder sb = new StringBuilder();
string[] fields = ExcludeFields(table.DestFields, table.SkipFields);
data.MapFields(fields);
sb.Append($"INSERT INTO {ProcessTableName(tableName, table.DestSchema)} ({ProcessFieldNames(fields)})")
.AppendLine().Append("VALUES").AppendLine()
.Append("(").Append(GetFmtValue(filter.GetValue(data, 0, fields[0])));
for (int i = 1; i < fields.Length; i++)
{
sb.Append(", ").Append(GetFmtValue(filter.GetValue(data, i, fields[i])));
}
sb.Append(")");
int r = 1;
while (r < table.PageSize && data.Read())
{
r++;
sb.Append(",").AppendLine().Append("(").Append(GetFmtValue(filter.GetValue(data, 0, fields[0])));
for (int i = 1; i < fields.Length; i++)
{
sb.Append(", ").Append(GetFmtValue(filter.GetValue(data, i, fields[i])));
}
sb.Append(")");
}
script = sb.ToString();
}
/// <summary>
/// 返回首列值清单
/// </summary>
/// <param name="data">数据</param>
/// <returns>首列值清单</returns>
protected string[] GetValues(IDataWrapper data)
{
try
{
List <string> lst = new List <string>();
while (data.Read())
{
object obj = data.GetValue(0);
if (obj == DBNull.Value || obj == null)
{
lst.Add(null);
}
else
{
lst.Add(obj.ToString());
}
}
return(lst.ToArray());
}
finally
{
data.Close();
}
}
public bool BuildScript(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
// 有数据
if (data.Read())
{
if (table is MaskingTable)
{
BuildScriptWithMaskSQL(table, data, filter, out script);
}
else if (table.WriteMode == WriteModes.Append)
{
BuildScriptWithDataTable(table, data, filter, out script);
}
else
{
BuildScriptWithMergeSQL(table, data, filter, out script);
}
return(true);
}
else
{
script = null;
return(false);
}
}
public bool BuildScript(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
if (data.Read())
{
BuildScriptWithKeyValuePair(table, data, filter, out script);
return(true);
}
else
{
script = null;
return(false);
}
}
private void BuildScriptWithDataTable(Table table, IDataWrapper data, IDataFilter filter,
out object script)
{
// 创建数据表，字段清单与目标表须一致
DataTable dt = new DataTable();
for (int i = 0; i < table.DestFields.Length; i++)
{
dt.Columns.Add(table.DestFields[i], data.GetFieldType(i));
}
// 给跳过字段名称增加一个前缀，这样字段映射后取值就为 NULL
string[] fields = ModifyFields(table.DestFields, table.SkipFields, "!!!");
data.MapFields(fields);
// 添加第一笔记录，仍然使用正确的字段名
DataRow row = dt.NewRow();
for (int i = 0; i < table.DestFields.Length; i++)
{
row[i] = filter.GetValue(data, i, table.DestFields[i]);
}
dt.Rows.Add(row);
// 添加后续记录，仍然使用正确的字段名
int r = 1;
while (r < table.PageSize && data.Read())
{
row = dt.NewRow();
for (int i = 0; i < table.DestFields.Length; i++)
{
row[i] = filter.GetValue(data, i, table.DestFields[i]);
}
dt.Rows.Add(row);
r++;
}
script = new AppendScript()
{
Data = dt, KeepIdentity = table.KeepIdentity
};
}
private void BuildScriptWithCSV(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
string file = Path.GetTempFileName();
FileStreamWriter fc = new FileStreamWriter(file, new UTF8Encoding(false));
string[] fields = ExcludeFields(table.DestFields, table.SkipFields);
uint r = 1;
data.MapFields(fields);
try
{
// 每行最后增加一个逗号分隔符，以免最后一个字段是字符串时解析有误
fc.Append(GetCSVValue(filter.GetValue(data, 0, fields[0]))).Append(',');
for (int i = 1; i < fields.Length; i++)
{
fc.Append(GetCSVValue(filter.GetValue(data, i, fields[i]))).Append(',');
}
while (r < table.PageSize && data.Read())
{
fc.AppendLine().Append(GetCSVValue(filter.GetValue(data, 0, fields[0]))).Append(',');
for (int i = 1; i < fields.Length; i++)
{
fc.Append(GetCSVValue(filter.GetValue(data, i, fields[i]))).Append(',');
}
r++;
}
}
finally
{
fc.Close();
}
for (int i = 0; i < fields.Length; i++)
{
fields[i] = ProcessFieldName(fields[i]);
}
script = new CSVScript()
{
CSVFile = file, Fields = fields, Count = r
};
}
public bool BuildScript(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
if (data.Read())
{
if (table is MaskingTable || table.WriteMode == WriteModes.Update)
{
BuildScriptWithUpdate(data, filter, out script);
}
else
{
BuildScriptWithInsert(table, data, filter, out script);
}
return(true);
}
else
{
script = null;
return(false);
}
}
public bool BuildScript(Table table, IDataWrapper data, IDataFilter filter, out object script)
{
if (data.Read())
{
if (table is MaskingTable)
{
BuildScriptWithMaskSQL(table, data, filter, out script);
}
else if (table.WriteMode == WriteModes.Append)
{
if (isSupportCSV)
{
BuildScriptWithCSV(table, data, filter, out script);
}
else
{
BuildScriptWithInsertSQL(table, table.DestName, data, filter, out script);
}
}
else if (!isSupportCSV)
{
BuildScriptWithReplaceSQL(table, table.DestName, data, filter, out script);
}
else
{
BuildScriptWithMergeSQL(table, data, filter, out script);
}
return(true);
}
else
{
script = null;
return(false);
}
}