private void bExportDocuments_Click(object sender, EventArgs ea)
{
if (treeView1.SelectedNode == null || treeView1.SelectedNode.Nodes.Count > 0)
{
MessageBox.Show("Select a database.");
return;
}
int total = 0;
long startTicks = 0;
long lastTicks = 0;
string timeLeft = "";
string timeElapsed = "0:00:00";
string databasePath = treeView1.SelectedNode.Name;
ProgressDialog pDialog = new ProgressDialog();
pDialog.Title = "Exporting Documents";
#region Export Documents
pDialog.DoWork += delegate(object dialog, DoWorkEventArgs e)
{
ExportTarget exportDialog = new ExportTarget();
try
{
//export documents
NotesSession nSession = initSession(notesPassword);
Dictionary <string, Table> tables = new Dictionary <string, Table>();
NotesDatabase db;
if (onLocalComputer)
{
db = nSession.GetDatabase("", databasePath, false);
}
else
{
db = nSession.GetDatabase(notesServer + "//" + notesDomain, databasePath, false);
}
//get all documents
total = db.AllDocuments.Count;
NotesDocumentCollection allDocuments = db.AllDocuments;
NotesDocument doc = allDocuments.GetFirstDocument();
startTicks = DateTime.Now.Ticks;
for (int i = 0; i < total; i++)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
if (doc.HasItem("Form") && (string)doc.GetItemValue("Form")[0] != "")
{
//get form
string form = (string)doc.GetItemValue("Form")[0];
if (!tables.ContainsKey(form))
{
tables.Add(form, new Table(form));
}
int row = tables[form].AddRow();
//get fields
//set multiple values
foreach (NotesItem item in doc.Items)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
string field = item.Name;
//exclude fields that start with $ and the Form field and Readers field
if (field == null || excludeField.IsMatch(field))
{
continue;
}
string type = "";
switch (item.type)
{//TODO: get more types
case IT_TYPE.NUMBERS:
type = "decimal(20,10)";
break;
case IT_TYPE.DATETIMES:
type = "datetime";
break;
default:
type = "text";
break;
}
object values = item.Values;
bool multiple = item.Values.Length > 1;
if (!tables[form].Columns.ContainsKey(field))
{
tables[form].Columns.Add(field, new Column(field, type));
}
if (multiple && !tables[form].Columns[field].MultipleValues)
{
tables[form].Columns[field].MultipleValues = multiple;
}
if (!tables[form].Columns[field].Values.ContainsKey(row))
{
tables[form].Columns[field].Values.Add(row, values);
}
else
{
int j = 1;
while (tables[form].Columns.ContainsKey(field + j) && tables[form].Columns[field + j].Values.ContainsKey(row))
{
j++;
}
field += j;
if (!tables[form].Columns.ContainsKey(field))
{
tables[form].Columns.Add(field, new Column(field, type));
}
if (multiple && !tables[form].Columns[field].MultipleValues)
{
tables[form].Columns[field].MultipleValues = multiple;
}
tables[form].Columns[field].Values.Add(row, values);
}
}
}
//update progress
pDialog.ReportProgress(i, "Parsing Documents");
doc = allDocuments.GetNextDocument(doc);
}
//add tables for columns with multiple values
Dictionary <string, Table> newTables = new Dictionary <string, Table>(tables.Count);
lastTicks = 0;
startTicks = DateTime.Now.Ticks;
total = tables.Count;
int count = 0;
foreach (Table table in tables.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Formatting Tables");
Dictionary <string, Column> columns = new Dictionary <string, Column>(table.Columns);
foreach (Column column in columns.Values)
{
if (column.MultipleValues)
{
string tableName = table.Name + "_" + column.Name;
Table newTable = new Table(tableName, table.Name);
Column values = new Column(column.Name, column.Type);
Column ids = new Column(table.Name + "id", "int");
foreach (KeyValuePair <int, object> cell in column.Values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int id = cell.Key;
object[] valueArray;
if (cell.Value.GetType().IsArray)
{
valueArray = (object[])cell.Value;
}
else
{
valueArray = new object[] { cell.Value };
}
foreach (object value in valueArray)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int row = newTable.AddRow();
ids.Values.Add(row, id);
values.Values.Add(row, value);
}
}
newTable.Columns.Add(table.Name + "id", ids);
newTable.Columns.Add(column.Name, values);
newTables.Add(tableName, newTable);
table.Columns.Remove(column.Name);
}
else
{
Dictionary <int, object> values = new Dictionary <int, object>(column.Values);
foreach (KeyValuePair <int, object> cell in values)
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
int id = cell.Key;
object value;
if (cell.Value.GetType().IsArray)
{
if (((object[])cell.Value).Length > 0)
{
value = ((object[])cell.Value)[0];
}
else
{
value = null;
}
}
else
{
value = cell.Value;
}
column.Values[id] = value;
}
}
}
newTables.Add(table.Name, table);
}
//format to sql
total = newTables.Count;
bool complete = false;
lastTicks = 0;
count = 0;
DialogResult result = DialogResult.Cancel;
Invoke((MethodInvoker) delegate() { result = exportDialog.ShowDialog(this); });
if (result == DialogResult.Cancel)
{
e.Cancel = true;
return;
}
if (exportDialog.SqlServerExport)
{
sqlGenerator = new TsqlGenerator();
}
else if (exportDialog.MySqlExport)
{
sqlGenerator = new MySqlGenerator();
}
do
{
if (exportDialog.ServerExport)
{
InputBox input = null;
Invoke((MethodInvoker) delegate() { input = InputBox.Show(pDialog.Window, "SQL server info?", new InputBoxItem[] { new InputBoxItem("Server", mysqlServer), new InputBoxItem("Database", mysqlDatabase), new InputBoxItem("Username", mysqlUsername), new InputBoxItem("Password", mysqlPassword, true), new InputBoxItem("Number of rows per INSERT", mysqlNumRowsPerInsert.ToString()) }, InputBoxButtons.OKCancel); });
if (input.Result == InputBoxResult.OK)
{
mysqlServer = input.Items["Server"];
mysqlDatabase = input.Items["Database"];
mysqlUsername = input.Items["Username"];
mysqlPassword = input.Items["Password"];
int.TryParse(input.Items["Number of rows per INSERT"], out mysqlNumRowsPerInsert);
DbConnection conn = null;
if (exportDialog.MySqlExport)
{
conn = new MySqlConnection("SERVER=" + mysqlServer + ";USERNAME="******";PASSWORD="******";");
}
else if (exportDialog.SqlServerExport)
{
conn = new SqlConnection("Server=" + mysqlServer + ";User Id=" + mysqlUsername + ";Password="******";");
}
try
{
startTicks = DateTime.Now.Ticks;
conn.Open();
string[] tokens = null;
if (exportDialog.SqlServerExport)
{
tokens = sqlGenerator.CreateDatabase(mysqlDatabase).Split(new string[] { "GO\r\n" }, StringSplitOptions.None);
}
else
{
tokens = new string[] { sqlGenerator.CreateDatabase(mysqlDatabase) + sqlGenerator.SetVariables() };
}
DbCommand command = conn.CreateCommand();
foreach (string sqlString in tokens)
{
command.CommandText = sqlString;
command.ExecuteNonQuery();
}
foreach (Table table in newTables.Values.Where(t => string.IsNullOrEmpty(t.LinkedTable)))
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Inserting SQL");
if (table.Columns.Count > 0)
{
command.CommandText = sqlGenerator.CreateTable(table);
command.ExecuteNonQuery();
List <string> rows = sqlGenerator.InsertTableRowsToList(table);
for (int i = 0; i < rows.Count; i += mysqlNumRowsPerInsert)
{
command.CommandText = sqlGenerator.BeginInsertTable(table);
command.CommandText += String.Join(",", rows.GetRange(i, Math.Min(rows.Count - i, mysqlNumRowsPerInsert))) + ";\n";
command.CommandText += sqlGenerator.EndInsertTable(table);
command.ExecuteNonQuery();
pDialog.ReportProgress(count, "Inserting SQL");
}
}
}
foreach (Table table in newTables.Values.Where(t => !string.IsNullOrEmpty(t.LinkedTable)))
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Inserting SQL");
if (table.Columns.Count > 0)
{
command.CommandText = sqlGenerator.CreateTable(table);
command.ExecuteNonQuery();
List <string> rows = sqlGenerator.InsertTableRowsToList(table);
for (int i = 0; i < rows.Count; i += mysqlNumRowsPerInsert)
{
command.CommandText = sqlGenerator.BeginInsertTable(table);
command.CommandText += String.Join(",", rows.GetRange(i, Math.Min(rows.Count - i, mysqlNumRowsPerInsert))) + ";\n";
command.CommandText += sqlGenerator.EndInsertTable(table);
command.ExecuteNonQuery();
pDialog.ReportProgress(count, "Inserting SQL");
}
}
}
command.CommandText = sqlGenerator.RestoreVariables();
if (!string.IsNullOrEmpty(command.CommandText))
{
command.ExecuteNonQuery();
}
complete = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
conn.Dispose();
}
}
else if (exportDialog.FileExport)
{
saveFileDialog1.FileName = "export.sql";
result = DialogResult.Cancel;
Invoke((MethodInvoker) delegate() { result = saveFileDialog1.ShowDialog(pDialog.Window); });
if (result == DialogResult.OK)
{
InputBox input = null;
Invoke((MethodInvoker) delegate() { input = InputBox.Show(pDialog.Window, "Database name?", "Database Name", mysqlDatabase, InputBoxButtons.OKCancel); });
if (input.Result == InputBoxResult.OK)
{
mysqlDatabase = input.Items["Database Name"];
StreamWriter file = new StreamWriter(saveFileDialog1.FileName, false);
try
{
startTicks = DateTime.Now.Ticks;
file.WriteLine(sqlGenerator.CreateDatabase(mysqlDatabase));
file.WriteLine(sqlGenerator.SetVariables());
foreach (Table table in newTables.Values.Where(t => string.IsNullOrEmpty(t.LinkedTable)))
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Formatting SQL");
if (table.Columns.Count > 0)
{
file.WriteLine(sqlGenerator.CreateTable(table));
file.WriteLine(sqlGenerator.BeginInsertTable(table));
file.WriteLine(sqlGenerator.InsertTableRowsToString(table));
file.WriteLine(sqlGenerator.EndInsertTable(table));
}
}
foreach (Table table in newTables.Values.Where(t => !string.IsNullOrEmpty(t.LinkedTable)))
{
//check if cancelled
if (pDialog.IsCancelled)
{
e.Cancel = true;
return;
}
pDialog.ReportProgress(++count, "Formatting SQL");
if (table.Columns.Count > 0)
{
file.WriteLine(sqlGenerator.CreateTable(table));
file.WriteLine(sqlGenerator.BeginInsertTable(table));
file.WriteLine(sqlGenerator.InsertTableRowsToString(table));
file.WriteLine(sqlGenerator.EndInsertTable(table));
}
}
file.WriteLine(sqlGenerator.RestoreVariables());
complete = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
file.Close();
}
}
}
}
} while (!complete);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
e.Cancel = true;
}
exportDialog.Dispose();
};
#endregion
pDialog.ProgressChanged += delegate(object dialog, ProgressChangedEventArgs e)
{
if (lastTicks == 0)
{
lastTicks = DateTime.Now.Ticks;
timeLeft = "Calculating...";
}
else if (e.ProgressPercentage > 0 && DateTime.Now.Ticks > lastTicks + 10000000)
{
lastTicks = DateTime.Now.Ticks;
long ticksPassed = lastTicks - startTicks;
long thingsCompleted = e.ProgressPercentage;
long thingsLeft = total - thingsCompleted;
long ticks = thingsLeft * ticksPassed / thingsCompleted;
timeLeft = ticksToString(ticks);
timeElapsed = ticksToString(ticksPassed);
}
pDialog.Message = e.UserState.ToString() + ": " + e.ProgressPercentage + "/" + total + " Time Remaining: " + timeLeft + " Time Elapsed: " + timeElapsed;
if (total == 0)
{
pDialog.Progress = 0;
}
else
{
pDialog.Progress = (100 * e.ProgressPercentage / total) % 101;
}
};
pDialog.Completed += delegate(object dialog, RunWorkerCompletedEventArgs e)
{
if (!e.Cancelled)
{
MessageBox.Show("Completed Successfully");
}
};
pDialog.Run();
}