public override int UploadImpl(DataTable dt)
{
var matchedColumns = GetMapping(dt.Columns.Cast <DataColumn>());
int affected = 0;
using (var cmd = new MySqlCommand("", (MySqlConnection)Connection.Connection,
(MySqlTransaction)Connection.Transaction))
{
if (BulkInsertBatchTimeoutInSeconds != 0)
{
cmd.CommandTimeout = BulkInsertBatchTimeoutInSeconds;
}
string commandPrefix = string.Format("INSERT INTO {0}({1}) VALUES ", TargetTable.GetFullyQualifiedName(), string.Join(",", matchedColumns.Values.Select(c => "`" + c.GetRuntimeName() + "`")));
StringBuilder sb = new StringBuilder();
int row = 0;
foreach (DataRow dr in dt.Rows)
{
sb.Append('(');
DataRow dr1 = dr;
sb.Append(string.Join(",", matchedColumns.Keys.Select(k => ConstructIndividualValue(matchedColumns[k].DataType.SQLType, dr1[k]))));
sb.AppendLine("),");
row++;
//don't let command get too long
if (row % BulkInsertRowsPerNetworkPacket == 0)
{
cmd.CommandText = commandPrefix + sb.ToString().TrimEnd(',', '\r', '\n');
affected += cmd.ExecuteNonQuery();
sb.Clear();
}
}
//send final batch
if (sb.Length > 0)
{
cmd.CommandText = commandPrefix + sb.ToString().TrimEnd(',', '\r', '\n');
affected += cmd.ExecuteNonQuery();
sb.Clear();
}
}
return(affected);
}
public override int UploadImpl(DataTable dt)
{
var con = (NpgsqlConnection)Connection.Connection;
var matchedColumns = GetMapping(dt.Columns.Cast <DataColumn>());
//see https://www.npgsql.org/doc/copy.html
StringBuilder sb = new StringBuilder();
sb.Append("COPY ");
sb.Append(TargetTable.GetFullyQualifiedName());
sb.Append(" (");
sb.Append(string.Join(",", matchedColumns.Values.Select(v => v.GetWrappedName())));
sb.Append(")");
sb.Append(" FROM STDIN (FORMAT BINARY)");
var tt = new PostgreSqlTypeTranslater();
var dataColumns = matchedColumns.Keys.ToArray();
var types = matchedColumns.Keys.Select(v => tt.GetNpgsqlDbTypeForCSharpType(v.DataType)).ToArray();
using (var import = con.BeginBinaryImport(sb.ToString()))
{
foreach (DataRow r in dt.Rows)
{
import.StartRow();
for (var index = 0; index < dataColumns.Length; index++)
{
DataColumn dc = dataColumns[index];
if (r[dc] == DBNull.Value)
{
import.WriteNull();
}
else
{
import.Write(r[dc], types[index]);
}
}
}
import.Complete();
}
return(dt.Rows.Count);
}
public override int UploadImpl(DataTable dt)
{
//don't run an insert if there are 0 rows
if (dt.Rows.Count == 0)
{
return(0);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var tt = syntaxHelper.TypeTranslater;
//if the column name is a reserved keyword e.g. "Comment" we need to give it a new name
Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName);
int affectedRows = 0;
var mapping = GetMapping(dt.Columns.Cast <DataColumn>());
var dateColumns = new HashSet <DataColumn>();
var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})",
string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')),
string.Join(",", mapping.Keys.Select(c => parameterNames[c]))
);
using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection))
{
//send all the data at once
cmd.ArrayBindCount = dt.Rows.Count;
foreach (var kvp in mapping)
{
var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value);
p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType);
if (p.DbType == DbType.DateTime)
{
dateColumns.Add(kvp.Key);
}
}
var values = new Dictionary <DataColumn, List <object> >();
foreach (DataColumn c in mapping.Keys)
{
values.Add(c, new List <object>());
}
foreach (DataRow dataRow in dt.Rows)
{
//populate parameters for current row
foreach (var col in mapping.Keys)
{
var val = dataRow[col];
if (val is string && string.IsNullOrWhiteSpace((string)val))
{
val = null;
}
else
if (val == null || val == DBNull.Value)
{
val = null;
}
else if (dateColumns.Contains(col))
{
if (val is string s)
{
val = (DateTime)DateTimeDecider.Parse(s);
}
else
{
val = Convert.ToDateTime(dataRow[col]);
}
}
values[col].Add(val);
}
}
foreach (DataColumn col in mapping.Keys)
{
var param = cmd.Parameters[parameterNames[col]];
param.Value = values[col].ToArray();
}
//send query
affectedRows += cmd.ExecuteNonQuery();
}
return(affectedRows);
}
