private async void ExecuteSelection(object sender, SelectionArgs e)
{
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
/*
* using (session)
* {
* for (int r = 1; r <= inputrange.Rows.Count; r++)
* {
*
* string cypher = "";
*
* foreach (Range col in inputrange.Rows[r].Columns)
* {
* try
* {
* cypher += col.Cells[1, 1].Value2.ToString();
* }
* catch
* {
*
* }
* }
* var result = session.Run(cypher);
*
* if (r == inputrange.Rows.Count)
* {
* CurrentControl.SetMessage(result.Summary.Statement.Text);
* }
*
* }
* }*/
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
finally
{
await session.CloseAsync();
}
}
private async Task <List <IRecord> > ExecuteCypherQuery(string queryString)
{
var session = _driver.AsyncSession();
List <IRecord> records = new List <IRecord>();
try
{
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var worksheet = ((Worksheet)Application.ActiveSheet);
try
{
IResultCursor cursor = await session.RunAsync(queryString);
records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
string summaryText = summary.ToString();
CurrentControl.SetMessage("Execution Summary :" + "\n\n" + summaryText);
return(records);
}
finally
{
await session.CloseAsync();
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
finally
{
await session.CloseAsync();
}
return(records);
}
private void ExecuteSelection(object sender, SelectionArgs e)
{
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
using (var session = _driver.Session())
{
for (int r = 1; r <= inputrange.Rows.Count; r++)
{
string cypher = "";
foreach (Range col in inputrange.Rows[r].Columns)
{
try
{
cypher += col.Cells[1, 1].Value2.ToString();
}
catch
{
}
}
var result = session.Run(cypher);
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(result.Summary.Statement.Text);
}
}
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private async void CreateNodes(object sender, SelectionArgs e)
{
var control = _customTaskPane.Control as ExecuteQuery;
if (_connected == false)
{
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
control.progress.Report(0);
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 2; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 2] = Convert.ToString(inputrange.Cells[1, i].Value2);
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
control.progress.Report(r / inputrange.Rows.Count * 100);
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
int i = 2;
{
string propval = Convert.ToString(row.Cells[1, i].Value2);
if (properties[i - 2].Length > 0 && propval.Length > 0)
{
cypher += "`" + properties[i - 2] + "`" + ": \"" + propval + "\",";
}
}
cypher = cypher.TrimEnd(',');
cypher += "})";
if (row.columns.count > 2)
{
cypher += " SET a += { ";
for (i = 3; i <= row.Columns.Count; i++)
{
string propval = Convert.ToString(row.Cells[1, i].Value2);
if (properties[i - 2] != null && propval != null)
{
if (properties[i - 2].Length > 0 && propval.Length > 0)
{
cypher += "`" + properties[i - 2] + "`" + ": \"" + propval + "\",";
}
}
}
cypher = cypher.TrimEnd(',');
cypher += "}";
}
try
{
IResultCursor cursor = await session.RunAsync(cypher);
var records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
string message = summary.ToString();
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(message);
}
}
catch (Neo4jException ee)
{
CurrentControl.SetMessage(ee.Message);
}
}
await session.CloseAsync();
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private void LoadAllNodes(object sender, SelectionArgs e)
{
//ExecuteLoadAllNodes(sender, e);
ExecuteLoadActiveWorksheet(sender, e);
CurrentControl.SetMessage("PULL");
}
private void UpdateAllNodes(object sender, SelectionArgs e)
{
CurrentControl.SetMessage("PUSH");
UpdateActiveWorksheet(sender, e);
}
private void SyncAllNodes(object sender, EventArgs e)
{
ExecuteLoadAllNodes();
CurrentControl.SetMessage("SYNC");
}
private async void UpdateActiveWorksheet(object sender, SelectionArgs e)
{
var control = _customTaskPane.Control as ExecuteQuery;
if (_connected == false)
{
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
inputrange = worksheet.UsedRange;
int indexOfIdentifier = 0;
control.progress.Report(0);
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 0; i < inputrange.Columns.Count; i++)
{
int excelIndex = i + 1;
try
{
string colName = Convert.ToString(inputrange.Cells[1, excelIndex].Value2);
properties[i] = colName;
string s = colName.ToLowerInvariant();
if (s == "uuid")
{
indexOfIdentifier = i;
}
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
control.progress.Report(r / inputrange.Rows.Count * 100);
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
label = worksheet.Name;
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
{
cypher += GetIdentifierPropertyValue(row, properties, indexOfIdentifier);
}
cypher = cypher.TrimEnd(',');
cypher += "})";
// check if worksheet have 1 property column + 1 data column
if (row.columns.count > 1)
{
cypher += " SET a += { ";
for (int i = 0; i < row.Columns.Count; i++)
{
int excelIndex = i + 1;
// Skip first record as it is used in Merge identifier.
if (i == indexOfIdentifier)
{
continue;
}
string secondRecordValue = Convert.ToString(row.Cells[1, excelIndex].Value2);
if (properties[i] != null && secondRecordValue != null)
{
if (properties[i].Length > 0 && secondRecordValue.Length > 0)
{
cypher += "`" + properties[i] + "`" + ": \"" + secondRecordValue + "\",";
}
}
}
cypher = cypher.TrimEnd(',');
cypher += "}";
}
try
{
IResultCursor cursor = await session.RunAsync(cypher);
var records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
string message = summary.ToString();
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(message);
}
}
catch (Neo4jException ee)
{
CurrentControl.SetMessage(ee.Message);
}
}
await session.CloseAsync();
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private void CreateNodes(object sender, SelectionArgs e)
{
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
using (var session = _driver.Session())
{
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 2; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 2] = inputrange.Cells[1, i].Value2.ToString();
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
for (int i = 2; i <= row.Columns.Count; i++)
{
cypher += properties[i - 2].ToString() + ": \"" + row.Cells[1, i].Value2.ToString() + "\",";
}
cypher = cypher.TrimEnd(',');
cypher += "})";
var result = session.Run(cypher);
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(result.Summary.Statement.Text);
}
}
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}