/// <summary>
/// </summary>
/// <param name="monetCredentialsModel"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <param name="rows"></param>
/// <param name="batchSize"></param>
public static bool BulkCopy(MonetDbConnectionInfo monetCredentialsModel, string schemaName, string tableName, TableColumnInfo[] columns, string[] rows, int batchSize)
{
var serviceClient = GetFactory(monetCredentialsModel.Host);
var model = new MonetBulkCopyModel(monetCredentialsModel)
{
SchemaName = schemaName,
TableName = tableName,
Columns = columns,
BatchSize = batchSize
};
var rowsBytes = rows.ToBytes();
model.Rows = rowsBytes.Lz4Compress();
return serviceClient.ServiceProxy.BulkCopy(model);
}
/// <summary>
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <param name="values"></param>
/// <param name="batchSize"></param>
public bool BulkCopy(string schemaName, string tableName, TableColumnInfo[] columns, object[][] values, int batchSize)
{
var statements = values.MapToFile();
return MonetDbtcpProxy.BulkCopy(ConnectionInfo, schemaName, tableName, columns, statements, batchSize);
}
/// <summary>
/// https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="columns"></param>
public void CreateTableIfNotExists(string schemaName, string tableName, TableColumnInfo[] columns)
{
//validate schema
if (!schemaName.IsNull())
CreateSchemaIfNotExists(schemaName);
//validate table
var query = schemaName.IsNull()
? $@"SELECT COUNT(*) FROM TABLES WHERE name ilike '{tableName}';"
: $@"SELECT COUNT(t.*) FROM TABLES AS t WHERE t.schema_id in (SELECT s.id FROM SCHEMAS AS s WHERE s.name ilike '{
schemaName}') AND t.name ilike '{tableName}';";
var result = MonetDbHelper.ExecuteScalar(ConnectionInfo.ToString(), query).To<int>();
if (result == 0)
{
var sb = new StringBuilder();
if (schemaName.IsNull())
{
sb.AppendFormat("CREATE TABLE \"{0}\"(", tableName);
}
else
{
sb.AppendFormat("CREATE TABLE \"{0}\".\"{1}\"(", schemaName, tableName);
}
for (var index = 0; index < columns.Count(); index++)
{
var column = columns.ElementAt(index);
var dbType = (column.RuntimeType == null || column.DataType.GetSystemType() == column.RuntimeType)
? column.DataType
: column.RuntimeType.GetDbTypeName();
if (index > 0)
sb.Append(",");
sb.AppendFormat("\"{0}\" {1} {2}", column.Column, dbType, column.Nullable.ToBool() ? "NULL" : "NOT NULL");
}
sb.Append(");");
MonetDbHelper.ExecuteNonQuery(ConnectionInfo.ToString(), sb.ToString());
}
}
/// <summary>
///
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static TableColumnInfo[] GetColumns(this IDataReader reader)
{
if (reader == null)
return new TableColumnInfo[] { };
var dt = reader.GetSchemaTable();
if (dt == null)
return new TableColumnInfo[] { };
var columns = new TableColumnInfo[dt.Rows.Count];
for (var i = 0; i < columns.Length; i++)
{
var row = dt.Rows[i];
var columnName = row["ColumnName"].To<string>();
columns[i] = new TableColumnInfo(columnName)
{
DataType = reader.GetDataTypeName(i),
Nullable = row["AllowDBNull"].ToBool(),
RuntimeType = Type.GetType(row["DataType"].To<string>()),
Ordinal = row["ColumnOrdinal"].To<int>()
};
}
return columns;
}
private void BulkCopy(string schemaName, string tableName, TableColumnInfo[] columns,
IDataReader reader)
{
try
{
long sizeCounter = 0, rowIndex = 0;
var secondValues = reader.GetMatrixInstance(BatchSize);
while (reader.Read())
{
reader.GetValues(secondValues[sizeCounter]);
sizeCounter++;
if (NotifyAfter > 0 && (rowIndex % NotifyAfter == 0 && rowIndex > 0))
{
RowsCopied?.Invoke(this, new RowsCopiedEventArgs(rowIndex));
}
if (sizeCounter == BatchSize)
{
_db.BulkCopy(schemaName, tableName, columns, secondValues, BatchSize);
sizeCounter = 0;
secondValues = reader.GetMatrixInstance(BatchSize);
}
rowIndex++;
}
var lastValues = new object[sizeCounter][];
for (var lastIndex = 0; lastIndex < sizeCounter; lastIndex++)
lastValues[lastIndex] = secondValues[lastIndex];
_db.BulkCopy(schemaName, tableName, columns, lastValues, BatchSize);
RowsCopied?.Invoke(this, new RowsCopiedEventArgs(rowIndex));
}
catch (Exception exception)
{
LoggerHelper.Write(LoggerOption.Error, exception.Message);
RowsCopied?.Invoke(this, new RowsCopiedEventArgs
{
Abort = true
});
}
}