Exemplo n.º 1
0
        private static object DataQueryFunction(string functionName, object[] functionArguments)
        {
            var providerConnectionString = functionArguments[0] as string;
            var query          = functionArguments[1] as string;
            var limit          = functionArguments.Length > 2 ? Convert.ToInt32(functionArguments[2]) : 100;
            var outputVariable = functionArguments.Length > 3 ? functionArguments[3] as string : "$data_";

            if (string.IsNullOrWhiteSpace(providerConnectionString))
            {
                throw new Exception("Argument 0: provider connection string is required");
            }
            if (string.IsNullOrWhiteSpace(query))
            {
                throw new Exception("Argument 1: query is required");
            }
            if (limit <= 0)
            {
                throw new Exception("Optional argument 2: limit is invalid");
            }

            if (!IsVariableNameValid(outputVariable, out var errorText))
            {
                throw new Exception("Optional argument 3: output variable " + errorText);
            }

            var tokens           = providerConnectionString.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
            var providerName     = tokens[0];
            var connectionString = tokens[1];

            var dbProviderFactory = DbProviderFactories.GetFactory(providerName);
            var row = 0;

            using (var dbConnection = dbProviderFactory.CreateConnection())
            {
                dbConnection.ConnectionString = connectionString;

                var dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = query;
                for (var ax = 4; ax < functionArguments.Length; ax++)
                {
                    var dbParameter = dbCommand.CreateParameter();
                    dbParameter.ParameterName = "@" + (ax - 4);
                    dbParameter.Value         = functionArguments[ax];
                    dbCommand.Parameters.Add(dbParameter);
                }


                dbConnection.Open();
                using (var dbDataReader = dbCommand.ExecuteReader())
                {
                    //Intentional: create empty arrays to return data
                    var numberOfColumns = dbDataReader.FieldCount;
                    for (var col = 0; col < numberOfColumns; col++)
                    {
                        VariableManager.CreateVariableArray(outputVariable + col, string.Empty, 0);
                    }

                    for (row = 0; dbDataReader.Read() && row < limit; row++)
                    {
                        for (var col = 0; col < numberOfColumns; col++)
                        {
                            var value = dbDataReader.IsDBNull(col) ? string.Empty : dbDataReader.GetValue(col);
                            VariableManager.AppendToVariableArray(outputVariable + col, value);
                        }
                    }
                }
            }

            return(row);
        }