private void ExecuteExpressionCommand()
{
SQLExpression expression;
bool editExpression = InSQLExpression(out expression);
string expressionText = editExpression ? '=' + expression.ExpressionText : String.Empty;
IExpression modifiedValue;
if (designerContext.EditExpression(expressionText, out modifiedValue))
{
string wrappedExpression = string.Empty;
if (!modifiedValue.IsEmpty)
{
wrappedExpression = SqlStringHandler.CreateSqlExpression(modifiedValue.GetExpression());
}
if (editExpression)
{
Regex replaceRegex = new Regex(SqlStringHandler.expressionPattern);
SQL = replaceRegex.Replace(SQL, wrappedExpression, 1, expression.StartIndex);
}
else
{
SQL = SQL.Insert(SQLIndex, wrappedExpression);
}
}
}
private void RefreshExpressionParameters(string expressionName, string propertyName)
{
for (int i = 0; Properties.ContainsId(expressionName + i); i++)
{
Properties.Remove(expressionName + i);
}
if (!Properties.Contains(propertyName))
{
return;
}
var property = Properties[propertyName];
var expressionText = property.Value as string ?? string.Empty;
if (!string.IsNullOrEmpty(expressionText))
{
var index = 0;
SqlStringHandler.GetSqlStringHandler(expressionText).Expressions.ForEach(
expression =>
Properties.Add(new Property(expressionName + (index++), typeof(object), ValueUseOption.RuntimeRead, Context.CreateExpression(expression.ExpressionText))
{
IsVisible = false
}));
}
}
private void ExecuteExpressionCommand()
{
LinxExpression expression;
bool editExpression = InLinxExpression(out expression);
string expressionText = (editExpression) ? "=" + expression.ExpressionText : string.Empty;
IExpression modifiedValue;
if (designerContext.EditExpression(expressionText, out modifiedValue))
{
string wrappedExpression = string.Empty;
if (!modifiedValue.IsEmpty)
{
wrappedExpression = SqlStringHandler.CreateSqlExpression(modifiedValue.GetExpression());
}
if (editExpression)
{
SegmentedDocument.Replace(expression.StartIndex, expression.EndIndex - expression.StartIndex + 1,
wrappedExpression);
}
else
{
SegmentedDocument.Replace(CaretIndex, 0, wrappedExpression);
}
}
}
public void Drop(IDropInfo dropInfo)
{
TextEditor editor = dropInfo.VisualTarget as TextEditor;
string dragText = string.Empty;
if (dropInfo.Data is NodeViewModel)
{
var draggedModel = dropInfo.Data as NodeViewModel;
dragText = (string.IsNullOrEmpty(editor.Text.Trim())) ? draggedModel.GetExtendedDragText() : draggedModel.GetSimpleDragText();
}
else if (dropInfo.Data is string)
{
dragText = dropInfo.Data as string;
if (dragText == VariablesEditor.ExpressionEditorText)
{
ExecuteExpressionCommand();
dragText = string.Empty;
}
else
{
SQLExpression expression;
if (!InSQLExpression(out expression))
{
dragText = SqlStringHandler.CreateSqlExpression(dragText);
}
}
}
var caretIndex = editor.CaretOffset;
editor.Text = editor.Text.Insert(caretIndex, dragText);
editor.CaretOffset = caretIndex + dragText.Length;
}
public static void CompileExpression(IFunctionBuilder functionBuilder, IFunctionData data, string parameterName, string expressionName)
{
var propertyValue = data.Properties[parameterName].Value;
var expressionString = (propertyValue is string) ? propertyValue as string : string.Empty;
SqlStringHandler stringHandler = SqlStringHandler.GetSqlStringHandler(expressionString);
var parameterValues = new string[stringHandler.Expressions.Count];
for (int i = 0; i < parameterValues.Length; i++)
{
parameterValues[i] = functionBuilder.GetParamName(expressionName + i) + ".ToString()";
}
if (parameterValues.Length > 0)
{
functionBuilder.AddCode(string.Format(@"{0} = Twenty57.Linx.Components.Database.Mongo.MongoStringHandler.GetExecutableQuery(
{0},
{1},
new [] {{ {2} }});" ,
functionBuilder.GetParamName(parameterName),
CSharpUtilities.ArrayAsString(stringHandler.Expressions.Select(x => x.MatchText)),
parameterValues.Aggregate((a, b) => a + ",\n" + b)
));
}
}
public override IFunctionData GetFunctionData()
{
FunctionData functionData = new FunctionData {
Version = FunctionUpdater.Instance.CurrentVersion
};
foreach (var nextProperty in Properties)
{
if (!nextProperty.Name.StartsWith(ExecuteSQLShared.SqlValuePropertyPrefix))
{
functionData.Properties.Add(nextProperty);
}
}
if (!string.IsNullOrEmpty(ResolvedSqlStatementValue))
{
int i = 1;
SqlStringHandler.GetSqlStringHandler(ResolvedSqlStatementValue).Expressions.ForEach(
ex => functionData.Properties.Add(new Property(ExecuteSQLShared.SqlValuePropertyPrefix + (i++), typeof(object), ValueUseOption.RuntimeRead,
Context.CreateExpression(ex.ExpressionText))
{
IsVisible = false
}));
}
functionData.ExecutionPaths.AddRange(ExecutionPaths);
functionData.Output = Output;
return(functionData);
}
private void BuildResultType()
{
if (!string.IsNullOrEmpty(ResolvedConnectionString) && !string.IsNullOrEmpty(ResolvedSqlStatementValue))
{
string designTimeSql = SqlStringHandler.GetSqlStringHandler(ResolvedSqlStatementValue).GetExecutableDesignTimeSql();
DataTable schema = null;
try
{
schema = DatabaseHelpers.RetrieveSchema(ConnectionType, ResolvedConnectionString, designTimeSql);
}
catch { }
if (schema != null)
{
var resultFields = ResultTypeValue.Fields;
ResultTypeFields newResults = BuildResultType(schema, resultFields);
resultFields.Clear();
foreach (ResultTypeField nextField in newResults)
{
resultFields.Add(nextField);
}
}
}
BuildDataOut();
BuildExecutionPaths();
}
private ExecuteSQLX(int timeout, string[] outputNames, string[] columnNames, string sqlToExecute, string[] sqlIdentifiers, object[] sqlValues)
{
if (outputNames.Length != columnNames.Length)
{
throw new Exception("The output name count must match the column name count.");
}
this.timeout = timeout;
this.outputNames = outputNames;
this.columnNames = columnNames;
this.sql = SqlStringHandler.GetParameterizedSql(sqlToExecute, sqlIdentifiers);
this.sqlValues = sqlValues;
}
private void CreateFromSQL()
{
try
{
var connectionType = executeSQLDesigner.ResolvedConnectionType;
string connectionString = executeSQLDesigner.ResolvedConnectionString;
DataTable schemaTable = DatabaseHelpers.RetrieveSchema(connectionType, connectionString,
SqlStringHandler.GetSqlStringHandler(this.executeSQLDesigner.ResolvedSqlStatementValue ?? string.Empty).GetExecutableDesignTimeSql());
var newResults = new List <ResultFieldModel>();
foreach (DataColumn dataColumn in schemaTable.Columns)
{
var oldModel = ResultFields.FirstOrDefault(x => x.ColumnName == dataColumn.ColumnName);
if (oldModel != null)
{
newResults.Add(oldModel);
continue;
}
ResultFieldModel newModel = new ResultFieldModel(this);
newModel.IsDefault = false;
newModel.ColumnName = dataColumn.ColumnName;
newModel.Name = DatabaseHelpers.GetValidName(dataColumn.ColumnName);
newModel.SetTypeReferenceValues(dataColumn.DataType);
newModel.NameChanged += ModelNameChanged;
newModel.ColumnNameChanged += ModelColumnNameChanged;
newResults.Add(newModel);
}
ResultFields.Where(x => !newResults.Contains(x)).ToList().ForEach(x =>
{
x.NameChanged -= ModelNameChanged;
x.ColumnNameChanged -= ModelColumnNameChanged;
});
ResultFields.Clear();
newResults.ForEach(x => ResultFields.Add(x));
AddDefaultChild();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private DataView ExecuteSQL()
{
string[] expressionNames = this.sqlStringHandler.ExpressionTexts.Select(et => SqlStringHandler.CreateSqlExpression(et)).ToArray();
string[] expressionValues = this.sqlStringHandler.ExpressionTexts.Select(et => SQLParameters.First(sp => sp.Name == et).Value).ToArray();
string parameterizedSql = SqlStringHandler.GetParameterizedSql(this.sqlStringHandler.SqlString, expressionNames);
using (var adapter = GetAdapter(this.connectionType, this.connectionString, parameterizedSql, expressionValues))
{
DataTable dataTable = new DataTable();
adapter.Fill(0, MaxTestResultCount + 1, dataTable);
if ((MoreResults = dataTable.Rows.Count == MaxTestResultCount + 1))
{
dataTable.Rows.RemoveAt(dataTable.Rows.Count - 1);
}
return(dataTable.DefaultView);
}
}
public SQLViewModel(ExecuteSQLDesigner executeSQLDesigner)
{
this.connectionType = executeSQLDesigner.ResolvedConnectionType;
this.connectionString = executeSQLDesigner.ResolvedConnectionString;
this.sqlStringHandler = new SqlStringHandler();
this.sqlStringHandler.SqlString = executeSQLDesigner.ResolvedSqlStatementValue ?? string.Empty;
this.sqlIndex = 0;
this.designerContext = executeSQLDesigner.Context;
Designer = executeSQLDesigner;
this.queryResults = null;
this.queryError = String.Empty;
DatabaseObjects = new ObservableCollection <NodeViewModel>();
this.refreshIsRunning = false;
this.executeSQLIsRunning = false;
}
public void Drop(IDropInfo dropInfo)
{
var draggedModel = dropInfo.Data as TemplateTreeItemViewModel;
bool isStringDraggedModel = dropInfo.Data is string;
if (draggedModel == null && !isStringDraggedModel)
{
return;
}
if (!isStringDraggedModel && draggedModel.Template == null)
{
return;
}
if (dropInfo.Data is string)
{
string dragText = dropInfo.Data as string;
if (dragText == VariablesEditor.ExpressionEditorText)
{
ExecuteExpressionCommand();
dragText = string.Empty;
}
else
{
if (!this.CaretInExpression)
{
dragText = SqlStringHandler.CreateSqlExpression(dragText);
}
var editor = dropInfo.VisualTarget as TextEditor;
var caretIndex = editor.CaretOffset;
editor.Text = editor.Text.Insert(caretIndex, dragText);
editor.CaretOffset = caretIndex + dragText.Length;
}
}
else
{
InsertTemplate(draggedModel.Template);
}
}
public override void GenerateCode(IFunctionBuilder functionBuilder)
{
ExecuteSQL_Gen generator = new ExecuteSQL_Gen();
generator.Session = new Dictionary <string, object>();
generator.Session.Add("FunctionContextProperty", functionBuilder.ContextParamName);
generator.Session.Add("GetParamName", new Func <string, string>(functionBuilder.GetParamName));
// Function properties
generator.Session.Add("Timeout", FunctionData.Properties[ExecuteSQLShared.TimeoutPropertyName].GetValue <int>());
ConnectionTypeSelection connectionType = FunctionData.Properties[DbShared.ConnectionTypePropertyName].GetValue <ConnectionTypeSelection>();
bool useTransaction = connectionType == ConnectionTypeSelection.UseTransaction;
generator.Session.Add("UseTransaction", useTransaction);
if (useTransaction)
{
generator.Session.Add("TransactionProperty", functionBuilder.GetParamName(DbShared.TransactionPropertyName));
}
else
{
generator.Session.Add("ConnectionType", connectionType.ToConnectionType());
generator.Session.Add("ConnectionStringProperty", functionBuilder.GetParamName(DbShared.ConnectionStringPropertyName));
}
generator.Session.Add("Sql", functionBuilder.GetParamName(ExecuteSQLShared.SqlStatementPropertyName));
ExecuteSQLShared.ReturnModeType returnMode = FunctionData.Properties[ExecuteSQLShared.ReturnOptionsPropertyName].GetValue <ExecuteSQLShared.ReturnModeType>();
generator.Session.Add("ReturnMode", returnMode);
// SQL placeholders
SqlStringHandler sqlStringHandler = SqlStringHandler.GetSqlStringHandler(StaticSqlStatementValue);
generator.Session.Add("SqlIdentifiers", CSharpUtilities.ArrayAsString(sqlStringHandler.Expressions.Select(ex => SqlStringHandler.CreateSqlExpression(ex.ExpressionText))));
int parameterIndex = 0;
generator.Session.Add("SqlValues", "new object[] { " + string.Join(", ", sqlStringHandler.Expressions.Select(ex => functionBuilder.GetParamName(ExecuteSQLShared.SqlValuePropertyPrefix + (++parameterIndex)))) + " }");
// Output columns
var resultType = FunctionData.Properties[ExecuteSQLShared.ResultTypePropertyName].GetValue <ResultType>();
generator.Session.Add("ResultTypeFields", resultType.Fields);
ITypeReference rowTypeReference = null;
if (resultType.Fields.Count != 0)
{
switch (returnMode)
{
case ExecuteSQLShared.ReturnModeType.RowByRow:
rowTypeReference = FunctionData.ExecutionPaths[ExecuteSQLShared.ExecutionPathName].Output;
break;
case ExecuteSQLShared.ReturnModeType.ListOfRows:
rowTypeReference = FunctionData.Output.GetEnumerableContentType();
break;
case ExecuteSQLShared.ReturnModeType.FirstRow:
case ExecuteSQLShared.ReturnModeType.FirstRowElseEmptyRow:
rowTypeReference = FunctionData.Output;
break;
}
}
generator.Session.Add("RowTypeName", rowTypeReference == null ? null : functionBuilder.GetTypeName(rowTypeReference));
generator.Session.Add("RowType", rowTypeReference);
generator.Session.Add("ExecutionPathName", ExecuteSQLShared.ExecutionPathName);
generator.Session.Add("ExecutionPathOutputName", functionBuilder.ExecutionPathOutParamName);
generator.Initialize();
functionBuilder.AddCode(generator.TransformText());
functionBuilder.AddAssemblyReference(typeof(ExecuteSQL));
functionBuilder.AddAssemblyReference(typeof(System.Data.IDataReader));
}
