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)); }
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 int ExecuteQuery(string query) { SQLQueryObj newQuery = new SQLQueryObj(); newQuery.Query = GetSQLQuery(query); newQuery.Connection = new SqlConnection(CSCS_SQL.ConnectionString); newQuery.Connection.Open(); newQuery.Command = new SqlCommand(newQuery.Query, newQuery.Connection); newQuery.DataReader = newQuery.Command.ExecuteReader(); newQuery.Table = GetTableName(query); newQuery.Columns = SQLQueryFunction.GetColumnData(newQuery.Table); s_queries.Add(newQuery); return(s_queries.Count - 1);// (int)count; }
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); }