Example #1
0
        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));
        }
Example #2
0
        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));
        }
Example #3
0
        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));
        }
Example #4
0
        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);
        }
Example #5
0
        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();
        }
Example #6
0
        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;
        }
Example #7
0
        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);
        }
Example #8
0
        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);
        }
Example #9
0
        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);
        }
Example #10
0
        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);
        }
Example #11
0
        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);
        }
Example #12
0
        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);
        }