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));
}
static List <KeyValuePair <string, object> > GetSPData(string spName)
{
var colTypes = new List <KeyValuePair <string, object> >();
var existing = new HashSet <string>();
SqlCommand sqlcom = new SqlCommand("sp_helptext");
sqlcom.CommandType = CommandType.StoredProcedure;
//var query = @"SELECT definition FROM sys.sql_modules WHERE object_id = (OBJECT_ID(N'" + spName + "'))";
var query = @"SELECT definition FROM sys.sql_modules WHERE object_id = (OBJECT_ID(@0))";
List <SqlParameter> sp = new List <SqlParameter>();
sp.Add(new SqlParameter("@0", spName));
var data = SQLQueryFunction.GetData(query, "", sp);
var str = data.AsString().ToLower();
int start = str.IndexOf('@');
while (start > 0)
{
int end1 = str.IndexOf(' ', start + 1);
int end2 = str.IndexOfAny(" \n\t".ToCharArray(), end1 + 1);
if (end1 < 0 || end2 < 0)
{
break;
}
var paramName = str.Substring(start + 1, end1 - start).Trim();
if (existing.Contains(paramName))
{
break;
}
existing.Add(paramName);
var paramType = str.Substring(end1 + 1, end2 - end1).Trim();
try
{
var sqlType = SQLQueryFunction.StringToSqlDbType(paramType);
colTypes.Add(new KeyValuePair <string, object>(paramName, sqlType));
}
catch (Exception)
{
var colData = SQLQueryFunction.GetColumnUserData(paramType);
colTypes.Add(new KeyValuePair <string, object>(paramName, colData));
}
start = str.IndexOf('@', end2 + 1);
}
return(colTypes);
}
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.SqlDbTypeToVariable(varType, values.Tuple[i]);
}
cmd.ExecuteNonQuery();
}
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;
}
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 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)
{
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();
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);
}
public static List <KeyValuePair <string, SqlDbType> > GetColumnUserData(string tableName)
{
List <KeyValuePair <string, SqlDbType> > result = null;
if (s_cache.TryGetValue(tableName, out object tableData) &&
tableData is List <KeyValuePair <string, SqlDbType> > )
{
return(tableData as List <KeyValuePair <string, SqlDbType> >);
}
result = new List <KeyValuePair <string, SqlDbType> >();
var query = @"select t.name [TableTypeName]
,c.name [ColumnName]
,y.name [DataType]
,c.max_length[MaxLength]
from sys.table_types t
inner join sys.columns c on c.object_id = t.type_table_object_id
inner join sys.types y ON y.system_type_id = c.system_type_id
WHERE t.is_user_defined = 1 AND t.is_table_type = 1
AND t.name = '" + tableName + "' order by c.column_id";
var data = GetData(query, tableName);
for (int i = 1; data.Tuple != null && i < data.Tuple.Count; i++)
{
var row = data.Tuple[i];
if (row.Type == Variable.VarType.ARRAY && row.Tuple.Count > 2)
{
var colName = row.Tuple[1].AsString();
var colType = SQLQueryFunction.StringToSqlDbType(row.Tuple[2].AsString());
result.Add(new KeyValuePair <string, SqlDbType>(colName, colType));
}
}
s_cache[tableName] = result;
return(result);
}
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);
}
