protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var queryStatement = Utils.GetSafeString(args, 0);
var spArgs = Utils.GetSafeVariable(args, 1);
var sp = SQLQueryFunction.GetParameters(spArgs);
int result = 0;
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryStatement, con))
{
if (sp != null)
{
cmd.Parameters.AddRange(sp.ToArray());
}
con.Open();
result = cmd.ExecuteNonQuery();
}
}
return(new Variable(result));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var spName = Utils.GetSafeString(args, 0);
var colTypes = GetSPData(spName);
int result = 0;
SqlCommand sqlcom = new SqlCommand(spName);
sqlcom.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < colTypes.Count && i + 1 < args.Count; i++)
{
var arg = args[i + 1];
var currName = colTypes[i].Key;
var currType = colTypes[i].Value;
if (arg.Type == Variable.VarType.ARRAY && currType is List <KeyValuePair <string, SqlDbType> > )
{
var typeData = currType as List <KeyValuePair <string, SqlDbType> >;
DataTable dt = new DataTable();
foreach (var entry in typeData)
{
var type = SQLQueryFunction.SqlDbTypeToType((SqlDbType)entry.Value);
dt.Columns.Add(new DataColumn(entry.Key, type));
}
for (int j = 0; j < arg.Tuple.Count; j++)
{
var row = arg.Tuple[j];
var objs = row.AsObject() as List <object>;
var dataRow = dt.NewRow();
if (objs != null)
{
for (int k = 0; k < objs.Count; k++)
{
dataRow[typeData[k].Key] = objs[k];
}
}
dt.Rows.Add(dataRow);
}
sqlcom.Parameters.AddWithValue("@" + currName, dt);
}
else
{
sqlcom.Parameters.AddWithValue("@" + currName, arg.AsObject());
}
}
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
sqlcom.Connection = con;
con.Open();
result = sqlcom.ExecuteNonQuery();
}
return(new Variable(result));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 3, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var tableName = Utils.GetSafeString(args, 0).Trim();
var colsStr = Utils.GetSafeString(args, 1).Trim();
var colData = SQLQueryFunction.GetColumnData(tableName);
if (colData == null || colData.Count == 0)
{
throw new ArgumentException("Error: table [" + tableName + "] doesn't exist.");
}
var queryStatement = "INSERT INTO " + tableName + " (" + colsStr + ") VALUES ("; //@a,@b,@c);"
var cols = colsStr.Split(',');
for (int i = 0; i < cols.Length; i++)
{
if (string.IsNullOrWhiteSpace(cols[i]) || !colData.Keys.Contains(cols[i]))
{
throw new ArgumentException("Error: column [" + cols[i] + "] doesn't exist.");
}
queryStatement += "@" + cols[i] + ",";
}
queryStatement = queryStatement.Remove(queryStatement.Length - 1) + ")";
var valsVariable = args[2];
bool oneEntry = valsVariable.Type == Variable.VarType.ARRAY && valsVariable.Tuple.Count >= 1 &&
valsVariable.Tuple[0].Type != Variable.VarType.ARRAY;
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
con.Open();
if (oneEntry)
{
using (SqlCommand cmd = new SqlCommand(queryStatement, con))
{
InsertRow(cmd, colData, valsVariable, cols);
}
}
else
{
for (int i = 0; i < valsVariable.Tuple.Count; i++)
{
using (SqlCommand cmd = new SqlCommand(queryStatement, con))
{
InsertRow(cmd, colData, valsVariable.Tuple[i], cols);
}
}
}
}
return(new Variable(oneEntry ? 1 : valsVariable.Tuple.Count));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var spName = Utils.GetSafeString(args, 0);
return(ExecuteSP(spName, null, args));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var tableName = Utils.GetSafeString(args, 0);
bool namesOnly = Utils.GetSafeInt(args, 1, 0) == 1;
return(GetColsData(tableName, namesOnly));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var query = Utils.GetSafeString(args, 0);
Variable results = GetData(query);
return(results);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var queryStatement = Utils.GetSafeString(args, 0).Trim();
int result = 0;
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryStatement, con))
{
con.Open();
result = cmd.ExecuteNonQuery();
}
}
return(new Variable(result));
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
if (m_mode == Mode.SETUP)
{
var query = Utils.GetSafeString(args, 0);
Variable result = new Variable(ExecuteQuery(query));
return(result);
}
else if (m_mode == Mode.NEXT)
{
var id = Utils.GetSafeInt(args, 0);
return(GetNextRecord(id));
}
else if (m_mode == Mode.CLOSE)
{
var id = Utils.GetSafeInt(args, 0);
Close(id);
return(Variable.EmptyInstance);
}
else if (m_mode == Mode.CURRENT_ROW)
{
var id = Utils.GetSafeInt(args, 0);
SQLQueryObj obj = GetSQLObject(id);
Variable result = new Variable(obj.CurrentRow);
return(result);
}
else if (m_mode == Mode.TOTAL)
{
var id = Utils.GetSafeInt(args, 0);
Variable result = new Variable(GetTotalRecords(id));
return(result);
}
return(Variable.EmptyInstance);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
CSCS_SQL.CheckConnectionString(script, m_name);
switch (m_mode)
{
case Mode.SP_DESC:
Utils.CheckArgs(args.Count, 1, m_name);
var spName = "sp_helptext";
var argName = Utils.GetSafeString(args, 0);
List <KeyValuePair <string, object> > spParams = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("@objname", argName)
};
var results = SQLSPFunction.ExecuteSP(spName, spParams);
if (results.Type == Variable.VarType.ARRAY && results.Tuple.Count >= 1 &&
results.Tuple[0].Type == Variable.VarType.ARRAY && results.Tuple[0].Count >= 1)
{
var r = results.Tuple[0].Tuple[0].AsString();
var res = System.Text.RegularExpressions.Regex.Replace(r, @"\s{2,}", " ");
return(new Variable(res));
}
return(results);
case Mode.TABLES:
return(RemoveListEntries(SQLQueryFunction.GetData("SELECT name FROM sysobjects WHERE xtype = 'U'",
null, null, false)));
case Mode.PROCEDURES:
return(RemoveListEntries(SQLQueryFunction.GetData("SELECT NAME from SYS.PROCEDURES",
null, null, false)));
}
return(Variable.EmptyInstance);
}
protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 1, m_name);
CSCS_SQL.CheckConnectionString(script, m_name);
var arg = Utils.GetSafeString(args, 0).Trim();
string statement = "";
switch (m_mode)
{
case Mode.DROP_DB:
statement = "DROP DATABASE " + arg;
break;
case Mode.CREATE_DB:
statement = "CREATE DATABASE " + arg;
break;
case Mode.DROP_TABLE:
statement = "DROP TABLE " + arg;
break;
}
int result = 0;
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(statement, con))
{
con.Open();
result = cmd.ExecuteNonQuery();
}
}
return(new Variable(result));
}