public static Variable GetData(string query, string tableName = "")
{
Variable results = new Variable(Variable.VarType.ARRAY);
DataTable table = new DataTable("results");
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
SqlDataAdapter dap = new SqlDataAdapter(cmd);
con.Open();
dap.Fill(table);
con.Close();
}
}
Dictionary <string, SqlDbType> tableData = new Dictionary <string, SqlDbType>();
Variable headerRow = new Variable(Variable.VarType.ARRAY);
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
headerRow.AddVariable(new Variable(col.ColumnName));
if (!string.IsNullOrWhiteSpace(tableName))
{
tableData[col.ColumnName] = StringToSqlDbType(col.DataType.Name);
}
}
results.AddVariable(headerRow);
if (!string.IsNullOrWhiteSpace(tableName))
{
s_columns[tableName] = tableData;
}
foreach (var rowObj in table.Rows)
{
DataRow row = rowObj as DataRow;
Variable rowVar = new Variable(Variable.VarType.ARRAY);
int i = 0;
foreach (var item in row.ItemArray)
{
DataColumn col = table.Columns[i++];
rowVar.AddVariable(ConvertToVariable(item, col.DataType.Name));
}
results.AddVariable(rowVar);
}
return(results);
}
public static Variable FillWithResults(DataTable table, Variable results = null)
{
if (table.Rows == null || table.Rows.Count == 0)
{
return(results);
}
if (results == null)
{
results = new Variable(Variable.VarType.ARRAY);
}
foreach (var rowObj in table.Rows)
{
DataRow row = rowObj as DataRow;
Variable rowVar = new Variable(Variable.VarType.ARRAY);
int i = 0;
foreach (var item in row.ItemArray)
{
DataColumn col = table.Columns[i++];
rowVar.AddVariable(ConvertToVariable(item, col.DataType.Name));
}
results.AddVariable(rowVar);
}
return(results);
}
public static Variable GetColsData(string tableName)
{
var colData = SQLQueryFunction.GetColumnData(tableName);
if (colData == null || colData.Count == 0)
{
return(new Variable(""));
}
Variable results = new Variable(Variable.VarType.ARRAY);
foreach (KeyValuePair <string, SqlDbType> entry in colData)
{
results.AddVariable(new Variable(entry.Key));
results.AddVariable(new Variable(entry.Value.ToString()));
}
return(results);
}
static Variable GetVariableForJToken(JToken aToken)
{
JTokenType currentType = aToken.Type;
switch (currentType)
{
case JTokenType.Object:
{
Variable newValue = new Variable(Variable.VarType.ARRAY);
ParseJObjectIntoVariable(aToken as JObject, newValue);
return(newValue);
}
case JTokenType.Array:
{
Variable newValue = new Variable(Variable.VarType.ARRAY);
foreach (var aa in aToken)
{
Variable addVariable = GetVariableForJToken(aa);
newValue.AddVariable(addVariable);
}
return(newValue);
}
case JTokenType.Integer:
return(new Variable(aToken.ToObject <Int64>()));
case JTokenType.Float:
return(new Variable(aToken.ToObject <float>()));
case JTokenType.String:
return(new Variable(aToken.ToObject <String>()));
case JTokenType.Boolean:
return(new Variable(aToken.ToObject <Boolean>()));
case JTokenType.Null:
return(Variable.EmptyInstance);
case JTokenType.None:
case JTokenType.Constructor:
case JTokenType.Property:
case JTokenType.Comment:
case JTokenType.Undefined:
case JTokenType.Date:
case JTokenType.Raw:
case JTokenType.Bytes:
case JTokenType.Guid:
case JTokenType.Uri:
case JTokenType.TimeSpan:
return(new Variable(aToken.ToString()));
}
return(Variable.EmptyInstance);
}
static Variable ExtractArray(ParsingScript script)
{
Variable newValue = new Variable(Variable.VarType.ARRAY);
while (script.StillValid() && (newValue.Count == 0 || script.Current == ','))
{
script.Forward();
Variable addVariable = ExtractValue(script);
newValue.AddVariable(addVariable);
}
script.MoveForwardIf(']');
return(newValue);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
var tableName = Utils.GetSafeString(args, 0);
var colData = SQLQueryFunction.GetColumnData(tableName);
if (colData == null || colData.Count == 0)
{
return(new Variable(""));
}
Variable results = new Variable(Variable.VarType.ARRAY);
foreach (KeyValuePair <string, SqlDbType> entry in colData)
{
results.AddVariable(new Variable(entry.Key));
results.AddVariable(new Variable(entry.Value.ToString()));
}
return(results);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 2, m_name);
Variable currentValue = Utils.GetSafeVariable(args, 0);
Variable item = Utils.GetSafeVariable(args, 1);
currentValue.AddVariable(item);
if (!currentValue.ParsingToken.Contains(Constants.START_ARRAY.ToString()))
{
ParserFunction.AddGlobalOrLocalVariable(currentValue.ParsingToken,
new GetVarFunction(currentValue));
}
return(currentValue);
}
public static Variable GetData(string query, string tableName = null,
List <SqlParameter> sp = null, bool addHeader = true)
{
Variable results = new Variable(Variable.VarType.ARRAY);
DataTable table = new DataTable("results");
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
if (sp != null)
{
cmd.Parameters.AddRange(sp.ToArray());
}
SqlDataAdapter dap = new SqlDataAdapter(cmd);
con.Open();
dap.Fill(table);
con.Close();
}
}
if (addHeader)
{
Dictionary <string, SqlDbType> tableData = new Dictionary <string, SqlDbType>();
Variable headerRow = new Variable(Variable.VarType.ARRAY);
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
headerRow.AddVariable(new Variable(col.ColumnName));
if (!string.IsNullOrWhiteSpace(tableName))
{
tableData[col.ColumnName] = StringToSqlDbType(col.DataType.Name);
}
}
if (!string.IsNullOrWhiteSpace(tableName))
{
s_columns[tableName] = tableData;
}
results.AddVariable(headerRow);
}
return(FillWithResults(table, results));
}
static Variable GetNextRecord(int id)
{
SQLQueryObj obj = GetSQLObject(id);
if (obj == null || !obj.DataReader.HasRows || !obj.DataReader.Read())
{
return(Variable.EmptyInstance);
}
Variable rowVar = new Variable(Variable.VarType.ARRAY);
for (int i = 0; i < obj.DataReader.FieldCount; i++)
{
var cell = obj.DataReader.GetValue(i);
var cellType = obj.DataReader.GetDataTypeName(i);
var variable = SQLQueryFunction.ConvertToVariable(cell, cellType);
rowVar.AddVariable(variable);
}
obj.CurrentRow++;
return(rowVar);
}
protected override Variable Evaluate(ParsingScript script)
{
// 1. Get the name of the variable.
string varName = Utils.GetToken(script, Constants.NEXT_OR_END_ARRAY);
Utils.CheckNotEnd(script, Constants.CONTAINS);
// 2. Get the current value of the variable.
ParserFunction func = ParserFunction.GetFunction(varName);
Utils.CheckNotNull(varName, func);
Variable currentValue = func.GetValue(script);
// 3. Get the variable to add.
Variable item = Utils.GetItem(script);
// 4. Add it to the tuple.
currentValue.AddVariable(item);
ParserFunction.AddGlobalOrLocalVariable(varName,
new GetVarFunction(currentValue));
return(currentValue);
}
public static Variable GetData(string query, string tableName = "")
{
Variable results = new Variable(Variable.VarType.ARRAY);
DataTable table = new DataTable("results");
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
SqlDataAdapter dap = new SqlDataAdapter(cmd);
con.Open();
dap.Fill(table);
con.Close();
}
}
Dictionary <string, SqlDbType> tableData = new Dictionary <string, SqlDbType>();
Variable headerRow = new Variable(Variable.VarType.ARRAY);
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
headerRow.AddVariable(new Variable(col.ColumnName));
if (!string.IsNullOrWhiteSpace(tableName))
{
tableData[col.ColumnName] = StringToSqlDbType(col.DataType.Name);
}
}
results.AddVariable(headerRow);
if (!string.IsNullOrWhiteSpace(tableName))
{
s_columns[tableName] = tableData;
}
foreach (var rowObj in table.Rows)
{
DataRow row = rowObj as DataRow;
Variable rowVar = new Variable(Variable.VarType.ARRAY);
int i = 0;
foreach (var item in row.ItemArray)
{
DataColumn col = table.Columns[i++];
switch (col.DataType.Name)
{
case "Int16":
rowVar.AddVariable(new Variable((Int16)item));
break;
case "Int32":
rowVar.AddVariable(new Variable((int)item));
break;
case "Int64":
rowVar.AddVariable(new Variable((long)item));
break;
case "Boolean":
rowVar.AddVariable(new Variable((bool)item));
break;
case "Single":
rowVar.AddVariable(new Variable((float)item));
break;
case "Double":
rowVar.AddVariable(new Variable((double)item));
break;
case "String":
rowVar.AddVariable(new Variable((string)item));
break;
case "DateTime":
rowVar.AddVariable(new Variable((DateTime)item));
break;
case "Decimal":
rowVar.AddVariable(new Variable(Decimal.ToDouble((Decimal)item)));
break;
default:
throw new ArgumentException("Unknown type: " + col.DataType.Name);
}
}
results.AddVariable(rowVar);
}
return(results);
}
public static Variable GetData(string query, string tableName = "")
{
DataTable table = new DataTable("results");
Variable results = new Variable(Variable.VarType.ARRAY);
try
{
using (var connection = new SqliteConnection("Data Source=" + SqlLiteFunction.DBPath))
{
connection.Open();
using (var cmd = new SqliteCommand(query, connection))
{
SqliteDataAdapter dap = new SqliteDataAdapter(cmd);
dap.Fill(table);
}
}
}
catch (Exception exc)
{
throw new ArgumentException("Couldn't run [" + query + "] on DB " + SqlLiteFunction.DBPath +
" :" + exc.Message);
}
Dictionary <string, DbType> tableData = new Dictionary <string, DbType>();
Variable headerRow = new Variable(Variable.VarType.ARRAY);
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn col = table.Columns[i];
headerRow.AddVariable(new Variable(col.ColumnName));
if (!string.IsNullOrWhiteSpace(tableName))
{
tableData[col.ColumnName.ToLower()] = StringToSqlDbType(col.DataType.Name);
}
}
results.AddVariable(headerRow);
if (!string.IsNullOrWhiteSpace(tableName))
{
s_columns[tableName] = tableData;
}
foreach (var rowObj in table.Rows)
{
DataRow row = rowObj as DataRow;
Variable rowVar = new Variable(Variable.VarType.ARRAY);
int i = 0;
foreach (var item in row.ItemArray)
{
DataColumn col = table.Columns[i++];
switch (col.DataType.Name)
{
case "Int32":
rowVar.AddVariable(new Variable((int)item));
break;
case "Int64":
rowVar.AddVariable(new Variable((long)item));
break;
case "Boolean":
rowVar.AddVariable(new Variable((bool)item));
break;
case "Single":
rowVar.AddVariable(new Variable((float)item));
break;
case "Double":
rowVar.AddVariable(new Variable((double)item));
break;
case "String":
rowVar.AddVariable(new Variable((string)item));
break;
case "DateTime":
rowVar.AddVariable(new Variable((DateTime)item));
break;
default:
throw new ArgumentException("Unknown type: " + col.DataType.Name);
}
}
results.AddVariable(rowVar);
}
return(results);
}
