public async Task <ImportResultNew> ImportSystem(ulong userId, PKSystem?system, JObject importFile,
Func <string, Task> confirmFunc)
{
await using var conn = await _db.Obtain();
await using var tx = await conn.BeginTransactionAsync();
return(await BulkImporter.PerformImport(conn, tx, _repo, _logger, _dispatch, userId, system, importFile,
confirmFunc));
}
/// <summary>
/// Handles the Click event of the btnCheckForeignSystemKey control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnCheckForeignSystemKey_Click(object sender, EventArgs e)
{
nbCheckForeignSystemKey.Visible = false;
nbCheckForeignSystemKey.Text = string.Empty;
if (!string.IsNullOrEmpty(tbForeignSystemKey.Text))
{
var tableList = Rock.Slingshot.BulkImporter.TablesThatHaveForeignSystemKey(tbForeignSystemKey.Text);
if (!tableList.Any())
{
nbCheckForeignSystemKey.Text = "OK. Foreign System Key <strong>" + tbForeignSystemKey.Text + "</strong> has not be used to import data.";
nbCheckForeignSystemKey.NotificationBoxType = NotificationBoxType.Success;
}
else
{
nbCheckForeignSystemKey.Text = "Foreign System Key <strong>" + tbForeignSystemKey.Text + "</strong> has already been used. Import again to insert any new records that are detected, and update any person records that have changed.";
nbCheckForeignSystemKey.NotificationBoxType = NotificationBoxType.Info;
}
nbCheckForeignSystemKey.Details = tableList.AsDelimited("<br />");
nbCheckForeignSystemKey.Visible = true;
}
var foreignSystemKeyList = BulkImporter.UsedForeignSystemKeys();
if (nbCheckForeignSystemKey.Text == string.Empty)
{
nbCheckForeignSystemKey.NotificationBoxType = NotificationBoxType.Default;
}
else
{
nbCheckForeignSystemKey.Text += "<br /><br />";
}
if (foreignSystemKeyList.Any())
{
nbCheckForeignSystemKey.Text += "The following ForeignSystemKeys have been used from previous imports:<br /><br />" + foreignSystemKeyList.AsDelimited("<br />");
}
else
{
nbCheckForeignSystemKey.Text += "No ForeignSystemKeys have been used from previous imports";
}
nbCheckForeignSystemKey.Text += "<br />";
nbCheckForeignSystemKey.Visible = true;
}
public void Execute_Fills_Table()
{
var rowCount = 100000;
var schema = new
{
Id = typeof(long),
FundName = typeof(string),
Date = typeof(DateTime),
Amount = typeof(decimal),
SiteNumber = typeof(int)
};
const string tableName = "Execute_Fills_Table";
const int siteNumber = 654321;
var sql = string.Format(@"DELETE FROM {0} WHERE SiteNumber = {1}", tableName, siteNumber);
var sw = new Stopwatch();
sw.Start();
int rowsDeleted = Helpers.ExecuteSql(sql, Helpers.ConnectionString);
sw.Stop();
Console.WriteLine(@"{0} Records deleted in: {1}ms", rowsDeleted, sw.ElapsedMilliseconds);
var stream = Helpers.CreateCsv(rowCount, siteNumber);
var si = new BulkImporter
{
ConnectionString = Helpers.ConnectionString,
TableName = tableName,
Schema = schema,
DataStream = stream
};
sw.Reset();
sw.Start();
si.Execute();
sw.Stop();
Console.WriteLine(@"{0} Records imported in: {1}ms", rowCount, sw.ElapsedMilliseconds);
}
public void CheckForUpdates(string orgName, bool useBulk)
{
string downloadsFolder = Path.Combine(Path.GetDirectoryName(AppDomain.CurrentDomain.BaseDirectory), DOWNLOADS);
if (!Directory.Exists(downloadsFolder))
{
Directory.CreateDirectory(downloadsFolder);
}
int totalErrors;
int totalProcessed = totalErrors = 0;
log.InfoFormat("Check for updates at {0}", EXPORTER_CATALOG);
WebClient wc = new WebClient();
using (MemoryStream ms = new MemoryStream(wc.DownloadData(EXPORTER_CATALOG)))
{
ms.Seek(0, SeekOrigin.Begin);
HtmlDocument doc = new HtmlDocument();
doc.Load(ms);
List <string> links = (from link in doc.DocumentNode.SelectNodes("//a[@href]")
select link.Attributes["href"]
into href
where !string.IsNullOrWhiteSpace(href.Value) && href.Value.Contains("_PRJ_X_")
orderby href.Value
select "http://exporter.nih.gov/" + href.Value).ToList();
bool addToProcessed = false;
foreach (string link in links)
{
Uri uri = new Uri(link);
string fileName = Path.GetFileName(uri.LocalPath);
if (!FileProcessed(fileName))
{
log.InfoFormat("Downloading file {0}", fileName);
WebClient d = new WebClient();
byte[] compessed = d.DownloadData(uri);
// byte[] compessed = File.ReadAllBytes(@"F:\Downloads\RePORTER_PRJ_X_FY2012_016.zip");
log.InfoFormat("File '{0}' downloaded to {1} folder.", fileName, downloadsFolder);
string zipPath = Path.Combine(downloadsFolder, fileName);
File.WriteAllBytes(zipPath, compessed);
ZipFile file = new ZipFile(zipPath);
foreach (ZipEntry zipEntry in file)
{
string fName = zipEntry.FileName;
zipEntry.Extract(downloadsFolder, ExtractExistingFileAction.OverwriteSilently);
log.InfoFormat("Unpacked '{0}' to {1}.", fileName, fName);
try
{
if (useBulk)
{
BulkImporter importer = new BulkImporter();
importer.ImportData(Path.Combine(downloadsFolder, fName), orgName);
log.InfoFormat("{0} Records imported. {1} Errors", importer.TotalRecords, importer.ErrorsCount);
totalProcessed = totalProcessed + importer.TotalRecords;
totalErrors = totalErrors + importer.ErrorsCount;
addToProcessed = true;
}
else
{
GrantImporter gi = new GrantImporter();
gi.ImportData(Path.Combine(downloadsFolder, fName), orgName, null);
log.InfoFormat("{0} Records imported. {1} Errors", gi.TotalRecords, gi.ErrorsCount);
totalProcessed = totalProcessed + gi.TotalRecords;
totalErrors = totalErrors + gi.ErrorsCount;
addToProcessed = true;
}
}
catch (Exception ex)
{
log.Debug("Error running bulk insert.", ex);
}
finally
{
//add or not to add?
if (addToProcessed)
{
AddFileToProcessed(fileName);
}
}
}
}
}
log.InfoFormat("End of import.");
log.InfoFormat("Total: {0} Records imported with {1} Errors", totalProcessed, totalErrors);
}
}
private static void ExecTasks(Options options)
{
string fileName = null;
if (!options.CheckForUpdates && !options.Validate && !options.CheckForUpdatesNoBulk)
{
if (options.FileName == null || options.FileName.Count == 0)
{
Options.ShowUsage();
return;
}
fileName = options.FileName[0];
if (!File.Exists(fileName))
{
Options.ShowUsage();
log.InfoFormat("File {0} does not exists.", fileName);
Environment.ExitCode = 1;
return;
}
}
Stopwatch sw = new Stopwatch();
sw.Start();
if (options.UseBCP)
{
BCPImporter bi = new BCPImporter();
bi.Import(fileName);
}
else if (options.UseBULK)
{
BulkImporter bi = new BulkImporter();
bi.ImportData(fileName, options.OrgName, null);
log.InfoFormat("{0} Records imported. {1} Errors", bi.TotalProcessed, bi.ErrorsCount);
}
else if (options.CheckForUpdates)
{
WebDownloader d = new WebDownloader();
d.CheckForUpdates(options.OrgName, true);
}
else if (options.CheckForUpdatesNoBulk)
{
WebDownloader d = new WebDownloader();
d.CheckForUpdates(options.OrgName, false);
}
else if (!options.Validate)
{
GrantImporter gi = new GrantImporter();
gi.ImportData(fileName, options.OrgName, null);
log.InfoFormat("{0} Records imported. {1} Errors", gi.TotalRecords, gi.ErrorsCount);
}
if (options.Validate)
{
GrantOnlineValidator validator = new GrantOnlineValidator();
validator.ValidateGrants();
log.InfoFormat("Total {0} Record(s) validated. {1} invalid grant(s)", validator.TotalProcessed, validator.InvalidGrants);
if (validator.ErrorsCount > 0)
{
log.InfoFormat("Total {0} Errors(s)", validator.ErrorsCount);
}
}
sw.Stop();
log.InfoFormat("Total time: {0:0.#} seconds.", sw.Elapsed.TotalSeconds);
log.Info("Done.");
Environment.ExitCode = 0;
}
