private void btnCancel_Click(object sender, RoutedEventArgs e)
{
this.tblStatus.Text = "Cancelling....";
_SelectedLoadfile = null;
this.DialogResult = false;
this.Close();
}
public AqcUi(LoadfileBase loadfile, string tablename)
{
InitializeComponent();
this.Loadfile = loadfile;
this.TableName = tablename;
}
private void BuildNewLoadfileView(LoadfileBase selectedloadfile)
{
try
{
Frame NewLoadfileFrame = new Frame();
Views.LoadfilePage NewLoadfilePage = new Views.LoadfilePage(selectedloadfile);
// NewLoadfilePage.StatusUpdate += OnStatusUpdate;
NewLoadfilePage.StatusUpdate += OnRefreshMainWindow;
NewLoadfilePage.LoadLoadfile();
NewLoadfileFrame.Content = NewLoadfilePage;
// we have to add a textblock to the tabitem header because the table names contain underscores
// default tabitem header will treat FIRST underscore as indication of following character being a shortcut like ampersand used to function in winforms
TextBlock NewTabTitle = new TextBlock();
NewTabTitle.Text = selectedloadfile.FileInformation.Name;
Button NewTabCloseButton = new Button();
NewTabCloseButton.Content = (char)215;
NewTabCloseButton.FontSize = 10;
NewTabCloseButton.Height = 15;
NewTabCloseButton.Width = 15;
NewTabCloseButton.Click += tabHeaderCloseButton_OnClick;
NewTabCloseButton.Margin = new Thickness(5, 0, 0, 0);
NewTabCloseButton.Padding = new Thickness(0);
NewTabCloseButton.BorderThickness = new Thickness(0);
NewTabCloseButton.VerticalContentAlignment = System.Windows.VerticalAlignment.Center;
NewTabCloseButton.ToolTip = "Close";
StackPanel NewTabHeader = new StackPanel();
NewTabHeader.Orientation = Orientation.Horizontal;
NewTabHeader.Children.Add(NewTabTitle);
NewTabHeader.Children.Add(NewTabCloseButton);
TabItem NewTab = new TabItem();
NewTab.Header = NewTabHeader;
NewTab.Content = NewLoadfileFrame;
this.tabcontrolMain.Items.Add(NewTab);
this.tabcontrolMain.SelectedItem = NewTab;
}
catch (Exception Ex)
{
this.tblStatus.Text = "Failed to open new loadfile.";
Log.ErrorLog.AddMessage("Failed to open new loadfile.");
}
if (this.tabcontrolMain.Items.Count > 0)
{
this.btnSqlConsole.IsEnabled = true;
}
else
{
this.btnSqlConsole.IsEnabled = false;
}
}
public ExportWindow(LoadfileBase loadfile)
{
InitializeComponent();
this._SelectedLoadfile = loadfile;
// populate comboboxes, etc
Init();
this.tbFilepath.Text = this._SelectedLoadfile.FileInformation.FullName;
}
public TablePage(GridViewLoadfile gridview, LoadfileBase loadfile)
{
InitializeComponent();
Dgv = gridview;
// Collect the fieldnames added to the grid
this.cmboFunctionField.ItemsSource = Dgv.FieldNamesAsDisplayed;
this.cmboFunctionField.DataContext = Dgv;
this.tbPageRowCount.Text = Dgv.PageRowCount.ToString();
this.tbLastPage.Text = Dgv.LastPage.ToString();
this.tblTotalRowCount.Text = Dgv.TotalRowCount.ToString("#,##0") + " rows";
// wire up the paging events now that we are ready for the user
this.tbLastPage.TextChanged += tbLastPage_TextChanged;
this.tbCurrentPage.TextChanged += tbCurrentPage_TextChanged;
Dgv.Sort(this.dgData, 0);
// the loadfile object
this.Loadfile = loadfile;
}
public void LoadToDatabase(LoadfileBase Loadfile)
{
Log.ErrorLog.AddMessage("Loading file: " + Loadfile.FileInformation.FullName);
// builds the table and returns its name
TableName = Connect.NewTable(
Connect.GetTableName(Loadfile.FileInformation.FullName),
Loadfile.FieldNamesSelected
);
ErrorTableName = Db.Connect.NewTable(
Connect.GetTableName(Loadfile.FileInformation.FullName) + "_error",
new List <string>()
{
"Position", "Error", "Line"
}
);
// ?VALUES? is only a placeholder for the list of input values we read from file
string InsertStatement =
"INSERT INTO [" +
TableName +
"] ([" +
string.Join("],[", Loadfile.FieldNamesSelected) + // .Replace(" ", "_")
"]) VALUES ('{0}');";
// count of fields in a good row should match count of true values in Loadfile.FieldsSelected bool array
// by default this should be same as count of FieldNames
int RowLength = Loadfile.FieldsSelected.Count <bool>(x => x == true);
// count of current number of uncommitted commands in the transaction
int CurrentBatch = -1;
SQLiteCommand MyCommand = new SQLiteCommand();
SQLiteTransaction MyTransaction = Connect.Connection.BeginTransaction();
string MyCommandString;
// accumulate as many records as we declare with BatchSize and do not read past end of file
while (CurrentBatch < BatchSize && !Loadfile.EndOfStream)
{
CurrentBatch = -1;
try
{
foreach (List <string> Record in Loadfile.NextRecord())
{
CountTotalRecords++;
CurrentBatch++;
for (int i = 0; i < Record.Count; i++)
{
Record[i] = Record[i].Replace("'", "''");
}
// build insert statement for one line
MyCommandString =
string.Format(
InsertStatement,
string.Join("','", Record)
);
MyCommand = new SQLiteCommand(MyCommandString, Connect.Connection, MyTransaction);
MyCommand.ExecuteNonQuery();
CountLoadedRecords++;
}
}
catch (RowException REx)
{
CountBadRecords++;
AddError(
ErrorTableName,
REx.Position,
REx.ErrorMessage,
REx.Text
);
}
catch (Exception Ex)
{
Log.ErrorLog.AddMessage(
"Error during load of "
+ Loadfile.FileInformation.Name
+ " at "
+ CountTotalRecords.ToString("#,##0")
+ Environment.NewLine
+ Ex.Message
+ Environment.NewLine
+ Ex.StackTrace
);
}
MyTransaction.Commit();
MyTransaction = Connect.Connection.BeginTransaction();
}
MyTransaction.Commit();
FlushErrorBuffer();
MyCommand = null;
// count of records in errors table
using (SQLiteCommand CommandCountErrors = new SQLiteCommand("SELECT COUNT(*) FROM [" + ErrorTableName + "];", Db.Connect.Connection))
{
CountTotalRecordsErrors = Convert.ToInt32(CommandCountErrors.ExecuteScalar());
}
Log.ErrorLog.AddMessage(
"Loaded to "
+ TableName
+ " "
+ CountLoadedRecords.ToString("#,##0")
+ " / "
+ CountTotalRecords.ToString("#,##0")
+ " total records, "
+ (CountBadRecords + CountErrors).ToString("#,##0")
+ " error lines"
);
}
private void tbFilepath_TextChanged(object sender, TextChangedEventArgs e)
{
if (File.Exists(this.tbFilepath.Text))
{
_SelectedLoadfile = LoadfileFactory.Build(this.tbFilepath.Text);
this.tblLoadfileType.Text = _SelectedLoadfile.LoadfileType.ToString();
this.tblStatus.Text = "Examining " + _SelectedLoadfile.FileInformation.Name;
this.Title = "Open Loadfile - " + _SelectedLoadfile.FileInformation.Name;
this.stackFieldPreview.Visibility = System.Windows.Visibility.Visible;
if (_SelectedLoadfile is IPlainText)
{
this.stackEncoding.Visibility = Visibility.Visible;
this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
}
if (_SelectedLoadfile is ConcordanceDat || _SelectedLoadfile is Csv)
{
this.cmboDelimField.SelectedValue = ((IDelimited)_SelectedLoadfile).FieldDelimiter[0];
this.cmboDelimText.SelectedValue = ((IDelimited)_SelectedLoadfile).TextDelimiter[0];
}
switch (_SelectedLoadfile.LoadfileType)
{
case LoadfileTypes.ConcordanceDat:
this.wrapDelimiters.Visibility = Visibility.Visible;
this.cmboDelimLineBreakSub.IsEnabled = true;
this.cmboDelimLineBreakSub.SelectedValue = ((ConcordanceDat)_SelectedLoadfile).LineBreakSubstitute[0];
this.cmboDelimField.SelectedValue = ((ConcordanceDat)_SelectedLoadfile).FieldDelimiter[0];
this.cmboDelimText.SelectedValue = ((ConcordanceDat)_SelectedLoadfile).TextDelimiter[0];
this.stackHasHeaders.Visibility = Visibility.Visible;
this.chkHasHeaders.IsChecked = ((ConcordanceDat)_SelectedLoadfile).HasHeader;
//this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
break;
case LoadfileTypes.Opticon:
this.cmboDelimLineBreakSub.IsEnabled = false;
this.wrapDelimiters.Visibility = Visibility.Collapsed;
this.stackHasHeaders.Visibility = Visibility.Collapsed;
this.chkHasHeaders.IsChecked = false;
//this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
break;
case LoadfileTypes.Csv:
this.cmboDelimLineBreakSub.IsEnabled = false;
this.wrapDelimiters.Visibility = Visibility.Visible;
this.stackHasHeaders.Visibility = Visibility.Visible;
this.chkHasHeaders.IsChecked = ((Csv)_SelectedLoadfile).HasHeader;
//this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
break;
case LoadfileTypes.IproLfp:
this.cmboDelimLineBreakSub.IsEnabled = false;
this.wrapDelimiters.Visibility = Visibility.Collapsed;
this.stackHasHeaders.Visibility = Visibility.Collapsed;
this.chkHasHeaders.IsChecked = false;
//this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
break;
case LoadfileTypes.SummationDii:
this.wrapDelimiters.Visibility = Visibility.Collapsed;
this.stackHasHeaders.Visibility = Visibility.Collapsed;
this.chkHasHeaders.IsChecked = false;
//this.cmboEncodings.SelectedValue = ((IPlainText)_SelectedLoadfile).FileEncoding.CodePage;
break;
}
// load list of fields AFTER we've set the default encoding and delimiters
OpenPreview();
}
else
{
this.tblStatus.Text = "File \"" + this.tbFilepath.Text + "\" does not exist!";
}
}
/// <summary>
/// Write the loadfile data in the database to a file.
/// Cannot sort descending.
/// </summary>
/// <param name="loadfile">Loadfile object</param>
/// <param name="outputfilepath">Output path to new file, may overwrite existing file</param>
/// <param name="tablename">The name of the table in our SQLite database</param>
/// <param name="fieldnames">List of fields from the View, in their View order</param>
/// <param name="sortfield">Optional, Name of the field on which to sort the output.
/// Default is _rowid_, ie, order in which rows were loaded</param>
public static void Save(LoadfileBase loadfile, string outputfilepath, string tablename, List <string> fieldnames, string Command, string sortfield = "[rowid]")
{
string HeaderCommaSeparated = null;
fieldnames.Remove("rowid");
if (fieldnames.Count != 0)
{
HeaderCommaSeparated = "[" + string.Join("],[", fieldnames.ToArray()) + "]";
}
// initiate the loadfile object's streamwriter
if (loadfile is ConcordanceDat || loadfile is Csv)
{
loadfile.Save(outputfilepath, ((IDelimited)loadfile).HasHeader, fieldnames);
}
else
{
loadfile.Save(outputfilepath);
}
// Select statement order by the sort field and the rows that we want to show it in the grid
// in the future, we may want to provide output for only selected fields
// *** DO NOT use brackets when building the following SQL statement.... brackets must be added when the sortState is set on the main form.
// We want to sort on >1 field in either asc or desc order, eg, "ORDER BY [Custodian-Name] DESC, [ControlNumber] ASC" ***
string commandString;
//If Command is null, it means save is pressed from a normal tab
//Else Save is pressed from an Ad-Hoc Select tab:
commandString =
Command == null
?
"SELECT "
+ HeaderCommaSeparated
+ " FROM ["
+ tablename
+ "] ORDER BY "
+ sortfield
:
Command;
Log.ErrorLog.AddMessage("Exporting to " + outputfilepath + " using command: " + commandString);
try
{
// build the command
using (SQLiteCommand MySelectCommand = new SQLiteCommand(commandString, Db.Connect.Connection))
{
using (SQLiteDataReader MyReader = MySelectCommand.ExecuteReader())
{
while (MyReader.Read())
{
List <string> nextRecord = new List <string>();
for (int i = 0; i < MyReader.FieldCount; i++)
{
nextRecord.Add(MyReader.GetString(i));
}
loadfile.WriteRecord(nextRecord);
}
}
}
}
catch (Exception Ex)
{
// log it and
Log.ErrorLog.AddMessage("Error during Export operation to " + outputfilepath + Environment.NewLine + Ex.Message + Environment.NewLine + Ex.StackTrace);
// tell user
global::System.Windows.MessageBox.Show(Ex.Message + Environment.NewLine + Ex.StackTrace);
}
finally
{
// this call to the loadfile object's close method ensures that the streamwriter flushes and closes
loadfile.Close();
}
}
public LoadfilePage(LoadfileBase selectedloadfile)
{
InitializeComponent();
this.Loadfile = selectedloadfile;
this.tbFilepath.IsReadOnly = true;
}
// Save, SaveAs, or Export
private void SaveAsButton_Click(object sender, RoutedEventArgs e)
{
// we should be checking the Loadfile object
if (Loadfile == null)
{
// Get the table name from the command in SQL Console
#region Select Ad-Hoc
string pattern = @".*\s*FROM\s*(?<tblname>\w*)\s*";
Regex rgx = new Regex(pattern, RegexOptions.IgnoreCase);
Match match = rgx.Match(SelectCommandString);
CurrentView.TableName = match.Groups["tblname"].Value.ToString();
#endregion
string NewLoadfileFilepath =
System.IO.Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
CurrentView.TableName + "_" + Log.ErrorLog.TimeStamp() + ".dat"
);
this.tbFilepath.Text = NewLoadfileFilepath;
ConcordanceDat Dat = new ConcordanceDat(NewLoadfileFilepath);
Dat.SetFieldNames(this.CurrentView.FieldNamesAsDisplayed);
Dat.Save(NewLoadfileFilepath);
Dat.Close();
this.Loadfile = Dat;
}
ExportWindow Ew = null;
try
{
Ew = new ExportWindow(this.Loadfile);
Ew.ShowDialog();
}
catch (Exception Ex)
{
Status("Error opening Save dialog with loadfile: "
+ this.Loadfile.FileInformation.Name
+ Environment.NewLine
+ Ex.Message
);
}
if (Ew.DialogResult == true)
{
Log.Timer.Start();
// try to make a backup
try
{
// make a backup of the loadfile
if (this.Loadfile.FileInformation.FullName.ToUpper() == Ew.OutputFilepath.ToUpper())
{
Status("Creating backup of the original loadfile....");
string backupfilename = System.IO.Path.Combine(
System.IO.Path.GetDirectoryName(this.Loadfile.FileInformation.FullName),
System.IO.Path.GetFileNameWithoutExtension(this.Loadfile.FileInformation.FullName)
+ "_LFUBACKUP_"
+ Log.ErrorLog.TimeStamp()
+ System.IO.Path.GetExtension(this.Loadfile.FileInformation.FullName)
);
Log.ErrorLog.AddMessage("Creating backup of " + this.Loadfile.FileInformation.FullName + " as " + backupfilename);
// make the copy!
File.Copy(this.Loadfile.FileInformation.FullName, backupfilename);
Status("Created backup " + backupfilename);
}
}
catch (Exception Ex)
{
Log.ErrorLog.AddMessage("Error during backup of file: "
+ this.Loadfile.FileInformation.FullName
+ Environment.NewLine
+ Ex.Message
+ Environment.NewLine
+ Ex.StackTrace);
Status("Error during save of file: " + this.Loadfile.FileInformation.FullName);
}
// try to save the loadfile
try
{
// we need to save against a select statement and NOT a tablename -ds 09-23-2015
Export.Save(Loadfile, Ew.OutputFilepath, CurrentView.TableName, CurrentView.FieldNamesAsDisplayed, SelectCommandString);
Status("Save is complete: "
+ Ew.OutputFilepath
+ " ("
+ Log.Timer.ElapsedTime().ToString()
+ ")"
);
// let's not rename table
// -ds 2015-07-17
/*if (Db.Connect.RenameTable(TableName, Db.Connect.GetTableName(Ew.OutputFilepath)) == 1)
* {
* // change visible path only if we successfully renamed that backing table
* this.tbFilepath.Text = Ew.OutputFilepath;
* TableName = Db.Connect.GetTableName(this.tbFilepath.Text);
* }*/
}
catch (Exception Ex)
{
Log.ErrorLog.AddMessage("Error during save of file: "
+ this.Loadfile.FileInformation.FullName
+ Environment.NewLine
+ Ex.Message
+ Environment.NewLine
+ Ex.StackTrace);
Status("Error during save of file: " + this.Loadfile.FileInformation.FullName);
}
}
else
{
Status("Export or Save was cancelled.");
}
}
