protected override Variable Evaluate(ParsingScript script)
{
List <Variable> args = script.GetFunctionArgs();
Utils.CheckArgs(args.Count, 3, 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));
}
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 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);
}