private void LoadTable(string table, int?schemaId, long rowCount)
{
try
{
var takeRowCount = 1000;
if (int.TryParse(ConfigurationManager.AppSettings["SelectTopNRowsCount"], out var configRowCount))
{
takeRowCount = configRowCount;
}
var scanner = new DataScanner(Database);
var rows = scanner.ScanTable(table, schemaId, false).Take(takeRowCount);
var schemaRow = scanner.GetEmptyDataRow(table, schemaId);
ShowRows(rows, schemaRow, table, rowCount);
}
catch (Exception ex)
{
LogException(ex);
}
}
private async void ExportDataMenuItem_Click(object sender, EventArgs e)
{
var tableNameParts = treeview.SelectedNode.Text.Split('.');
var tableName = tableNameParts.Last();
var newTableName = tableName + DateTime.Now.ToString("_yyyyMMddHHmmss");
var dlg = new SqlConnectionDialog
{
SaveHelper = { SaveMethod = connectionString => ConnectionString = connectionString },
Title = "Specify connection and target table name",
ConnectionString = ConnectionString,
TableName = newTableName
};
var dialogResult = dlg.Show();
if (dialogResult != DialogResult.OK)
{
return;
}
var bulkCopyBatchSize = 1000_000;
if (int.TryParse(ConfigurationManager.AppSettings["BulKCopyBatchSize"], out var configBulkCopyBatchSize))
{
bulkCopyBatchSize = configBulkCopyBatchSize;
}
var bulkCopyNotifyAfter = 1000;
if (int.TryParse(ConfigurationManager.AppSettings["BulKCopyNotifyAfter"], out var configBulkCopyNotifyAfter))
{
bulkCopyNotifyAfter = configBulkCopyNotifyAfter;
}
var schema = Database.Dmvs.Schemas.FirstOrDefault(x => x.name == tableNameParts[0]);
var scanner = new DataScanner(Database);
var schemaRow = scanner.GetEmptyDataRow(tableName, schema?.schema_id);
var rows = scanner.ScanTable(tableName, schema?.schema_id, false);
var createTableSql = new CreateTableScriptWriter(Database).GetCreateExportTableScript(dlg.TableName, schemaRow);
using (var connection = new SqlConnection(dlg.ConnectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = createTableSql;
await command.ExecuteNonQueryAsync();
var dataReader = new TableReader(rows, schemaRow);
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.BatchSize = bulkCopyBatchSize;
bulkCopy.DestinationTableName = dlg.TableName;
bulkCopy.BulkCopyTimeout = (int)TimeSpan.FromHours(1).TotalSeconds;
bulkCopy.SqlRowsCopied += BulkCopyOnSqlRowsCopied;
bulkCopy.NotifyAfter = bulkCopyNotifyAfter;
// Just to exclude Computed columns from the mappings
foreach (var columnIdx in schemaRow.Columns
.Select((c, i) => new
{
c.Type,
Index = i
}).Where(x => x.Type != ColumnType.Computed)
.Select(x => x.Index))
{
bulkCopy.ColumnMappings.Add(columnIdx, columnIdx);
}
await bulkCopy.WriteToServerAsync(dataReader);
bulkCopy.SqlRowsCopied -= BulkCopyOnSqlRowsCopied;
}
}
}
gridStatusRows.Text = $"Export to table {dlg.TableName} finished.";
}