public override void BulkInsert(ITableData data, string tableName)
{
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var sourceColumnValues = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => "?").ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
using (var transaction = this.DbConnection.BeginTransaction())
using (var command = this.DbConnection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText =
$@"INSERT INTO {tableName}
({String.Join(",", sourceColumnNames)})
VALUES ({String.Join(",", sourceColumnValues)})
";
command.Prepare();
while (data.Read())
{
foreach (var mapping in destColumnNames)
{
SQLiteParameter par = new SQLiteParameter();
par.Value = data.GetValue(data.GetOrdinal(mapping));
command.Parameters.Add(par);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
transaction.Commit();
}
}
internal string CreateBulkInsertStatementWithParameter(ITableData data, string tableName, List <OdbcParameter> parameters)
{
QueryText.Clear();
TableName = tableName;
GetSourceAndDestColumnNames(data);
AppendBeginSql(tableName);
while (data.Read())
{
QueryText.Append("(");
string[] placeholder = new string[DestColumnNames.Count];
QueryText.Append(string.Join(",", placeholder.Select(s => s + "?")));
QueryText.AppendLine(")");
foreach (string destColumnName in DestColumnNames)
{
int colIndex = data.GetOrdinal(destColumnName);
string dataTypeName = data.GetDataTypeName(colIndex);
if (data.IsDBNull(colIndex))
{
parameters.Add(new OdbcParameter(destColumnName, DBNull.Value));
}
else
{
parameters.Add(new OdbcParameter(destColumnName, data.GetValue(colIndex)));
}
}
if (data.NextResult())
{
QueryText.Append(",");
}
}
AppendEndSql();
return(QueryText.ToString());
}
internal string CreateBulkInsertStatement(ITableData data, string tableName)
{
_sb.Clear();
GetSourceAndDestColumNames(data);
AppendBeginSql(tableName);
while (data.Read())
{
List <string> values = new List <string>();
foreach (string destColumnName in DestColumnNames)
{
int colIndex = data.GetOrdinal(destColumnName);
string dataTypeName = data.GetDataTypeName(colIndex);
if (data.IsDBNull(colIndex))
{
values.Add(NullValueSql(destColumnName));
}
else
{
values.Add(ValueSql(destColumnName, data.GetString(colIndex)));
}
}
AppendValueListSql(values, data.NextResult());
}
AppendEndSql();
return(_sb.ToString());
}
public override void BulkInsert(ITableData data, string tableName)
{
var TN = new ObjectNameDescriptor(tableName, QB, QE);
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
var quotedDestColumns = destColumnNames.Select(col => TN.QB + col + TN.QE);
using (var writer = DbConnection.BeginBinaryImport($@"
COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)})
FROM STDIN (FORMAT BINARY)"))
{
while (data.Read())
{
writer.StartRow();
foreach (var destCol in destColumnNames)
{
TableColumn colDef = DestinationColumns[destCol];
int ordinal = data.GetOrdinal(destCol);
object val = data.GetValue(ordinal);
if (val != null)
{
object convertedVal = System.Convert.ChangeType(data.GetValue(ordinal), colDef.NETDataType);
writer.Write(convertedVal, colDef.InternalDataType.ToLower());
}
else
{
writer.WriteNull();
}
}
}
writer.Complete();
}
}
protected override void DoBulkInsert(ITableData data)
{
if (ModifyDBSettings)
{
try
{
Synchronous = this.ExecuteScalar("PRAGMA synchronous").ToString();
JournalMode = this.ExecuteScalar("PRAGMA journal_mode").ToString();
this.ExecuteNonQuery("PRAGMA synchronous = OFF");
this.ExecuteNonQuery("PRAGMA journal_mode = MEMORY");
}
catch
{
ModifyDBSettings = false;
}
}
try
{
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var sourceColumnValues = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => "?").ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
using (var transaction = this.DbConnection.BeginTransaction())
using (var command = this.DbConnection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText =
$@"INSERT INTO {data.Definition.Name}
({String.Join(",", sourceColumnNames)})
VALUES ({String.Join(",", sourceColumnValues)})
";
command.Prepare();
while (data.Read())
{
foreach (var mapping in destColumnNames)
{
SQLiteParameter par = new SQLiteParameter();
par.Value = data.GetValue(data.GetOrdinal(mapping));
command.Parameters.Add(par);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
transaction.Commit();
}
}
finally
{
if (ModifyDBSettings)
{
try
{
this.ExecuteNonQuery($"PRAGMA synchronous = {Synchronous}");
this.ExecuteNonQuery($"PRAGMA journal_mode = {JournalMode}");
}
catch { }
}
}
}
private void ReadDataAndCreateQuery(ITableData data)
{
while (data.Read())
{
List<string> values = new List<string>();
foreach (string destColumnName in DestColumnNames)
{
int colIndex = data.GetOrdinal(destColumnName);
if (data.IsDBNull(colIndex))
AddNullValue(values, destColumnName);
else
AddNonNullValue(data, values, destColumnName, colIndex);
}
AppendValueListSql(values, data.NextResult());
}
}
public static IEnumerable <IEnumerable <(TableColumn column, object value)> > EnumerateRows(this ITableData data, params string[] columnNames)
{
if (data is null)
{
throw new ArgumentNullException(nameof(data));
}
var columns = (columnNames?.Length > 0 ?
data.Definition.ColumnsSynchronized.WithNames(true, columnNames) :
data.Definition.ColumnsSynchronized).
ToArray();
data.Reset();
while (data.Read())
{
yield return(columns.Select(column =>
{
int ordinal = data.GetOrdinal(column.DataSetColumn);
object value = data.GetValue(ordinal);
return (column, value);
}));
}
}