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));
}
static void InsertRow(SqlCommand cmd, Dictionary <string, SqlDbType> colData, Variable values, string[] cols)
{
if (values.Type != Variable.VarType.ARRAY || values.Tuple.Count < cols.Length)
{
throw new ArgumentException("Error: not enough values (" + values.Tuple.Count +
") given for " + cols.Length + " columns.");
}
for (int i = 0; i < cols.Length; i++)
{
var varName = "@" + cols[i];
var varType = colData[cols[i]];
cmd.Parameters.Add(varName, varType);
cmd.Parameters[varName].Value = SQLQueryFunction.SqlDbTypeToType(varType, values.Tuple[i]);
}
cmd.ExecuteNonQuery();
}
public static Variable ExecuteSP(string spName, List <KeyValuePair <string, object> > spParams = null,
List <Variable> args = null)
{
SqlCommand sqlcom = new SqlCommand(spName);
sqlcom.CommandType = CommandType.StoredProcedure;
int result = 0;
if (spParams != null)
{
for (int i = 0; i < spParams.Count; i++)
{
sqlcom.Parameters.AddWithValue(spParams[i].Key, spParams[i].Value);
}
}
else
{
var colTypes = GetSPData(spName);
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());
}
}
}
DataTable table = new DataTable("results");
using (SqlConnection con = new SqlConnection(CSCS_SQL.ConnectionString))
{
sqlcom.Connection = con;
con.Open();
result = sqlcom.ExecuteNonQuery();
SqlDataAdapter dap = new SqlDataAdapter(sqlcom);
dap.Fill(table);
con.Close();
}
Variable results = SQLQueryFunction.FillWithResults(table);
return(results == null ? new Variable(result) : results);
}
