private void ExecuteSql(int update, ref bool started)
{
var queryText = AddSqlForVariables(SqlQuery);
var statements = TSQLStatementReader.ParseStatements(queryText);
if (queryText.Contains("UNION") && statements.Count == 2)
{
var tables = statements[0].GetAllTables();
foreach (var table in tables)
{
LoadRecordset(table.TableName);
}
var sqlQueryToUpdate = queryText;
foreach (var item in AdvancedRecordset.HashedRecSets)
{
sqlQueryToUpdate = sqlQueryToUpdate.Replace(item.recSet, item.hashCode);
}
var results = AdvancedRecordset.ExecuteQuery(sqlQueryToUpdate);
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(dt.TableName, Outputs, dt.Rows.Cast <DataRow>().ToList(), false, update, ref started);
}
}
else
{
ExecuteAllSqlStatements(update, statements, ref started);
}
}
string AddSqlForVariables(string queryText) => Regex.Replace(queryText, @"\@\w+\b", match =>
{
if (DeclareVariables.FirstOrDefault(nv => "@" + nv.Name == match.Value) != null)
{
return(AdvancedRecordset.GetVariableValue(match.Value));
}
return(match.Value);
});
void ProcessSelectStatement(TSQLSelectStatement selectStatement, int update, ref bool started)
{
var results = AdvancedRecordset.ExecuteQuery(AdvancedRecordset.ReturnSql(selectStatement.Tokens));
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(dt.TableName, Outputs, dt.Rows.Cast <DataRow>().ToList(), false, update, ref started);
}
}
void ProcessCreateTableStatement(TSQLUnknownStatement complexStatement)
{
var recordset = new DataTable();
recordset.Columns.Add("records_affected", typeof(int));
recordset.Rows.Add(AdvancedRecordset.ExecuteNonQuery(AdvancedRecordset.ReturnSql(complexStatement.Tokens)));
var outputName = Outputs.FirstOrDefault(e => e.MappedFrom == "records_affected").MappedTo;
AdvancedRecordset.ApplyScalarResultToEnvironment(outputName, int.Parse(recordset.Rows[0].ItemArray[0].ToString()));
}
void ProcessSelectStatement(TSQLSelectStatement selectStatement, int update, ref bool started)
{
var sqlQuery = AdvancedRecordset.UpdateSqlWithHashCodes(selectStatement);
var results = AdvancedRecordset.ExecuteQuery(sqlQuery);
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(dt.TableName, Outputs, dt.Rows.Cast <DataRow>().ToList(), false, update, ref started);
}
}
void InsertIntoVariableTable(string varName, string value)
{
try
{
AdvancedRecordset.InsertIntoVariableTable(varName, value);
}
catch (Exception e)
{
Dev2Logger.Error("AdvancedRecorset", e, GlobalConstants.WarewolfError);
}
}
void ProcessUpdateStatement(TSQLUnknownStatement complexStatement, int update, ref bool started)
{
var tokens = complexStatement.Tokens;
var outputRecordsetName = "";
for (int i = 0; i < complexStatement.Tokens.Count; i++)
{
if (i == 1 && tokens[i].Type.ToString() == "Identifier" && (tokens[i - 1].Text.ToUpper() == "UPDATE"))
{
outputRecordsetName = tokens[i].Text;
}
if (tokens[i].Type.ToString() == "Keyword" && (tokens[i].Text.ToUpper() == "INSERT"))
{
outputRecordsetName = tokens[i + 2].Text;
}
if (tokens[i].Type.ToString() == "Identifier" && (tokens[i].Text.ToUpper() == "REPLACE"))
{
outputRecordsetName = tokens[i + 2].Text;
}
if (tokens[i].Type.ToString() == "Keyword" && (tokens[i].Text.ToUpper() == "DELETE"))
{
outputRecordsetName = tokens[i + 2].Text;
}
}
var sqlQuery = AdvancedRecordset.UpdateSqlWithHashCodes(complexStatement);
var recordset = new DataTable();
recordset.Columns.Add("records_affected", typeof(int));
recordset.Rows.Add(AdvancedRecordset.ExecuteNonQuery(sqlQuery));
object sumObject;
sumObject = recordset.Compute("Sum(records_affected)", "");
_recordsAffected += Convert.ToInt16(sumObject.ToString());
var mapping = Outputs.FirstOrDefault(e => e.MappedFrom == "records_affected");
if (mapping != null)
{
AdvancedRecordset.ApplyScalarResultToEnvironment(mapping.MappedTo, _recordsAffected);
}
var results = AdvancedRecordset.ExecuteQuery("SELECT * FROM " + AdvancedRecordset.HashedRecSets.FirstOrDefault(x => x.recSet == outputRecordsetName).hashCode);
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(outputRecordsetName, Outputs, dt.Rows.Cast <DataRow>().ToList(), true, update, ref started);
}
}
void ExecuteRecordset(IDSFDataObject dataObject, int update)
{
var env = dataObject.Environment;
AdvancedRecordset = new AdvancedRecordset(env);
var iter = new WarewolfListIterator();
var started = false;
var itemsToIterateOver = new Dictionary <string, IWarewolfIterator>();
var allTables = new List <string>();
if (DeclareVariables == null || DeclareVariables.All(d => String.IsNullOrEmpty(d.Value)))
{
ExecuteSql(update, allTables, ref started);
}
else
{
_recordsAffected = 0;
foreach (var declare in DeclareVariables)
{
if (string.IsNullOrEmpty(declare.Value))
{
continue;
}
var res = new WarewolfIterator(env.Eval(declare.Value, update));
iter.AddVariableToIterateOn(res);
itemsToIterateOver.Add(declare.Name, res);
}
while (iter.HasMoreData())
{
foreach (var item in itemsToIterateOver)
{
AddDeclarations(item.Key, iter.FetchNextValue(item.Value));
}
ExecuteSql(update, allTables, ref started);
started = true;
}
}
foreach (var table in allTables)
{
AdvancedRecordset.DeleteTableInSqlite(table);
}
}
private void ExecuteSql(int update, List <string> allTables, ref bool started)
{
var queryText = AddSqlForVariables(SqlQuery);
var statements = TSQLStatementReader.ParseStatements(queryText);
if (queryText.Contains("UNION") && statements.Count == 2)
{
var tables = statements[0].GetAllTables();
foreach (var table in tables)
{
LoadRecordset(table.TableName);
allTables.Add(table.TableName);
}
var results = AdvancedRecordset.ExecuteQuery(queryText);
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(dt.TableName, Outputs, dt.Rows.Cast <DataRow>().ToList(), false, update, ref started);
}
}
else
{
ExecuteAllSqlStatements(update, allTables, statements, ref started);
}
}
string AddSqlForVariables(string queryText) => Regex.Replace(queryText, @"\@\w+\b", match => AdvancedRecordset.GetVariableValue(match.Value));
void LoadRecordset(string tableName)
{
AdvancedRecordset.LoadRecordsetAsTable(tableName);
}
