private void BuildCreateTableScriptForVariable(MyQuery Query, String TableName, Object Value)
{
if (MyTypeHelper.IsPrimitive(Value))
{
}
else if (Value is IDictionary <string, object> dict)
{
Query.Append("declare @").Append(TableName).Append(" table (");
Int32 index = 0;
foreach (var keyAndValue in dict)
{
if (index > 0)
{
Query.Append(", ");
}
Query.Append(keyAndValue.Key).Append(" ").Append(TSqlHelper.GetSqlType(keyAndValue.Value?.GetType()));
index++;
}
Query.Append(");");
}
else if (Value is IList)
{
}
else if (Value.GetType().IsClass)
{
BuildCreateTableScriptForVariable(Query, TableName, ReflectionHelper.ToDictionary(Value));
}
}
private void BuildScriptForVariable(MyQuery Query, String Name, Object Value, String ParentName = null)
{
string name = string.IsNullOrEmpty(ParentName) ? Name : (ParentName + "_" + Name);
if (MyTypeHelper.IsPrimitive(Value))
{
Query.Append($"declare @{name} {TSqlHelper.GetSqlType(Value?.GetType())};\n");
Query.Append($"set @{name} = {{0}};\n", Value);
}
else if (Value is IDictionary <string, object> dict)
{
if (string.IsNullOrEmpty(ParentName))
{
foreach (var keyAndValue in dict)
{
BuildScriptForVariable(Query, keyAndValue.Key, keyAndValue.Value, Name);
}
}
else
{
BuildCreateTableScriptForVariable(Query, name, dict);
BuildInsertIntoTableScriptForVariable(Query, name, dict);
}
//BuildCreateTableScriptForVariable(Query, name, dict);
// BuildInsertIntoTableScriptForVariable(Query, name, dict);
}
else if (Value is IList list)
{
var firstValue = ReflectionHelper.ToDictionary(list.Count > 0 ? list[0] : null);
BuildCreateTableScriptForVariable(Query, name, firstValue);
foreach (var item in list)
{
BuildInsertIntoTableScriptForVariable(Query, name, item);
}
}
else if (Value.GetType().IsClass)
{
var classAsDict = ReflectionHelper.ToDictionary(Value);
if (string.IsNullOrEmpty(ParentName))
{
foreach (var keyAndValue in classAsDict)
{
BuildScriptForVariable(Query, keyAndValue.Key, keyAndValue.Value, Name);
}
}
else
{
BuildCreateTableScriptForVariable(Query, name, classAsDict);
BuildInsertIntoTableScriptForVariable(Query, name, classAsDict);
}
}
}
public async Task <Object> Evaluate(S4JExecutor Executor, S4JToken token, IDictionary <String, object> variables)
{
S4JTokenFunction functionToken = token as S4JTokenFunction;
S4JStateFunction functionState = token.State as S4JStateFunction;
MyQuery query = new MyQuery();
foreach (KeyValuePair <string, object> keyAndVal in variables)
{
BuildScriptForVariable(query, keyAndVal.Key, keyAndVal.Value);
}
query.Append(functionToken.ToJsonWithoutGate());
String connectionString = Executor.Sources.Get(functionState.FunctionName);
using (SqlConnection con = new SqlConnection(connectionString))
{
var result = con.SelectItems(query.ToString());
return(result);
}
}
private void BuildInsertIntoTableScriptForVariable(MyQuery Query, String TableName, Object Value)
{
if (MyTypeHelper.IsPrimitive(Value))
{
}
else if (Value is IDictionary <string, object> dict)
{
Query.Append("insert into @").Append(TableName).Append(" (");
Int32 index = 0;
foreach (var keyAndValue in dict)
{
if (index > 0)
{
Query.Append(", ");
}
Query.Append(keyAndValue.Key);
index++;
}
Query.Append(") values (");
index = 0;
foreach (var keyAndValue in dict)
{
if (index > 0)
{
Query.Append(", ");
}
Query.AppendVal(keyAndValue.Value);
index++;
}
Query.Append(");");
}
else if (Value is IList)
{
}
else if (Value.GetType().IsClass)
{
BuildInsertIntoTableScriptForVariable(Query, TableName, ReflectionHelper.ToDictionary(Value));
}
}