Использование хранимых процедур как источника данных

[DmitryRu]

У редактора Стимулсофт (Designer.exe) есть проблемы с использованием хранимых процедур (по крайней мере на MS SQL) в качестве источников данных для отчета.
Описаны они здесь:
http://forum.stimulsoft.com/viewtopic.php?f=8&t=38322
Вкратце
1) Надо устанавливать какую-то опцию в файле Options.xml, причем после этого родной дизайнер начинает сообщать что он не может получить к нему доступ (снова стало проявляться недавно)
2) Не всегда умеет подтягивать список параметров хранимой процедуры
3) Чтобы получить список столбцов dataset'а из хранимой процедуры, приходится использовать танцы с бубном.
4) и это еще не все, всего и не упомнишь

Я набросал утилитку, решающую эти проблемы. Дарю ее сообществу, с примечанием, что это бета-версия.
Это обертка над родным редактором Стимулсофт .NET, которая позволяет задать имя интересующей ХП и считать ее параметры и список столбцов.

Графический интерфейс:
Нажимаем кнопку "Создать новый", вводим имя хранимой процедуры

createnew.png (14.02 КБ) 5419 просмотров

Подтягивается список параметров, причем если установлена галочка, то параметры ХП будут инициализироваться из Stimulsoft Variable с запросом у пользователя. Далее можем задать необходимые для ХП параметры и нажать кнопку "UpdateColumns"

updateColumns.png (15.77 КБ) 5419 просмотров

Будет выполнен запрос на SQL сервере и считан список полей

columnList.png (20.2 КБ) 5419 просмотров

Далее можно нажать кнопку "Редактировать отчет" и откроется стандартный редактор Стимулсофт, с созданным источником данных в виде ХП.

Если Стимулсофт интегрирует что-либо их этого кода в свой, я буду только рад

[DmitryRu]

Основная часть логики:

Код: Выделить всё

    /// <summary>
    /// Data Access Object
    /// </summary>
    public class StiDao
    {
        private readonly SqlConnection _connection;

        private readonly StiMsSqlConnector _typeConverter;

        public StiDao(SqlConnection connection)
        {
            ContractValidator.NotNull(connection, "connection");
            _connection = connection;
            _typeConverter = new StiMsSqlConnector();
        }

        private class ParameterMapping
        {
            private readonly int _nameIndex;
            private readonly int _typeIndex;

            private readonly SqlDataReader _reader;
            private readonly StiMsSqlConnector _typeConverter;

            public ParameterMapping(SqlDataReader r, StiMsSqlConnector typeConverter)
            {
                _typeConverter = typeConverter;
                _reader = r;
                _nameIndex = r.GetOrdinal("name");
                _typeIndex = r.GetOrdinal("type_name");
            }

            public StiDataParameter CreateParameter()
            {
                var paramName = _reader.GetString(_nameIndex);
                var paramType = _reader.GetString(_typeIndex);
                var type = _typeConverter.GetNetType(paramType);
                var stiType = _typeConverter.GetSqlType(type);
                return new StiDataParameter(paramName, stiType, 0);
            }
        }

        private StiSqlSource FillStoredProc(StiSqlSource sp)
        {
            var sql =
                "select     *, type_name(system_type_id) as type_name " +
                "from          sys.parameters "+
                "where        object_id = object_id(@sp)";
            sp.Parameters.Clear();
            using (var command = new SqlCommand(sql, _connection))
            {
                command.Parameters.Add(new SqlParameter("@sp", SqlDbType.Text)).Value = string.Format("dbo.{0}", sp.SqlCommand);
                using (var r = command.ExecuteReader())
                {
                    var mapping = new ParameterMapping(r, _typeConverter);
                    while (r.Read())
                    {
                        sp.Parameters.Add(mapping.CreateParameter());
                    }
                }
            }
            if (sp.Parameters.Count < 1)
            {
                throw new BusinessException(string.Format("Не удалось найти параметры для хранимой процедуры {0}", sp.SqlCommand));
            }
            return sp;
        }

        /// <summary>
        /// Reads DB's schema from SQL server to fetch list of parameters for Stored Procedure with name = <paramref name="name"/>
        /// Fills result's list of parameters
        /// </summary>
        /// <param name="name">name of stored proc in question</param>
        /// <param name="nameInSource">Stimulsoft's terminology</param>
        /// <returns>Created stored procedure</returns>
        public StiSqlSource CreateStoredProcedureAndParameters(string name, string nameInSource)
        {
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }
            if (string.IsNullOrWhiteSpace(name))
            {
                throw new BusinessException("Пожалуйста введите имя ХП");
            }
            if (name.Contains("."))
            {
                throw new BusinessException("Имя не должно содержать символы: '.'");
            }
            var result = new StiSqlSource(nameInSource, name)
            {
                Type = StiSqlSourceType.StoredProcedure,
                SqlCommand = name
            };
            return FillStoredProc(result);
        }

        /// <summary>
        /// Performs 
        /// <code>
        ///   EXEC sp.SqlCommand
        /// </code>
        /// Iterates over receieved GetSchemaTable() to read columns list from returned dataset
        /// Fills <paramref name="sp"/> up with received columns descriptions
        /// </summary>
        /// <param name="sp">stored procedure description</param>
        /// <param name="parameters">parameters to be passed to <paramref name="sp"/></param>
        public void ReadColumnListForStoredProc(StiSqlSource sp, List<OneParameterAndValue> parameters)
        {
            ContractValidator.NotNull(sp, "sp");
            ContractValidator.NotNull(parameters, "parameters");
            using (var command = new SqlCommand(sp.SqlCommand, _connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (var parameterAndValue in parameters)
                {
                    var sqlParam = command.CreateParameter();
                    sqlParam.ParameterName = parameterAndValue.ParamName;
                    sqlParam.Value = parameterAndValue.GetSqlValue();
                    command.Parameters.Add(sqlParam);
                }
                using (var r = command.ExecuteReader())
                {
                    var schema = r.GetSchemaTable();
                    if (schema == null)
                    {
                        throw new InternalApplicationException("schema == null");
                    }
                    sp.Columns.Clear();
                    foreach (DataRow row in schema.Rows)
                    {
                        var name = row["ColumnName"] as string;
                        var dataType = row["DataTypeName"] as string;
                        sp.Columns.Add(name, _typeConverter.GetNetType(dataType));
                    }
                }
            }
            
        }
    }

Тест для этого класса:

Код: Выделить всё

 
       [Test]
        public void ReportHeaderInfoColumnsList()
        {
            var list = new List<OneParameterAndValue>();
            var sp = _dao.CreateStoredProcedureAndParameters("ReportHeaderInfo", "myConnection");
            Assert.AreEqual(4, sp.Parameters.Count);
            var paramVal1 = AddParameterToList(sp.Parameters[0], list, "@Token");
            paramVal1.AssignTextValue("790");

            AddParameterToList(sp.Parameters[1], list, "@PointIDs");
            AddParameterToList(sp.Parameters[2], list, "@ID_TimeSchema");
            AddParameterToList(sp.Parameters[3], list, "@DetailedNames");

            _dao.ReadColumnListForStoredProc(sp ,list);
            Assert.AreEqual(6, sp.Columns.Count);

            var col1 = sp.Columns[0];
            Assert.AreEqual("PointName", col1.Name);
            Assert.AreEqual(typeof(string), col1.Type);

            var col2 = sp.Columns[1];
            Assert.AreEqual("ParameterName", col2.Name);
            Assert.AreEqual(typeof(string), col2.Type);
        }

        private OneParameterAndValue AddParameterToList(StiDataParameter parameter, List<OneParameterAndValue> dest,
            string expectedName)
        {
            Assert.AreEqual(expectedName, parameter.Name);
            var result = new OneParameterAndValue(parameter, new StiMsSqlConnector());
            dest.Add(result);
            return result;
        }

[Aleksey]

Здравствуйте,

Единственная проблема, которая возникает - это хранимые процедуры, которые используют временные таблицы. Для таких процедур и используется дополнительная опция.

Спасибо за присланный пример. Посмотрим, постараемся исправить данные неудобства в будущем.

[Aleksey]

Здравствуйте,

Вернулись к вашему примеру.
Проблема в следующем - для получения списка параметров для хранимой процедуры, вы делаете запрос к системной таблице, но у большинства пользователей данных прав нет, и этот запрос им ничего не вернет.

Спасибо.

[DmitryRu]

Здравствуйте.

у большинства пользователей данных прав нет, и этот запрос им ничего не вернет.

Уточните, пожалуйста, каких именно прав нет у обычных пользователей.
Сейчас подключился к БД, где я не админ. Если у меня есть права на запуск хранимой процедуры (ХП), мой запрос возвращает мне ее описание.
Примерно то же утверждает оригинал:
https://msdn.microsoft.com/ru-ru/librar ... .105).aspx
https://msdn.microsoft.com/ru-ru/librar ... .105).aspx

В SQL Server 2005 и более поздних версиях видимость метаданных ограничивается защищаемыми объектами, которыми пользователь владеет или на которые пользователю были предоставлены разрешения. Например, следующий запрос возвращает строку, если пользователю было предоставлено разрешение SELECT или INSERT на таблицу myTable.

Случай, когда у пользователя нет доступа к ХП, а он пытается построить по ней отчет, считаю вырожденным

Единственная проблема, которая возникает - это хранимые процедуры, которые используют временные таблицы

На мой взгляд, это не единственная проблема.
Во всяком случае, на моей БД ваш редактор умеет считывать список параметров только у первых 20 процедур, а ХП у меня на порядок больше, причем не все ХП со временными таблицами.
Кроме того, моя лень крайне огорчена тем, что для того, чтобы запросить список колонок из БД, надо написать запрос полностью:

Код: Выделить всё

EXEC dbo.MyProc @param1, @param2

потом задать эти параметры через редактор (не заточенный под массовую правку 10 параметров) в какие-то константные величины
потом запросить список колонок

потом переписать запрос на:

Код: Выделить всё

dbo.MyProc

потом снова перезадать параметры через редактор (не заточенный под массовую правку 10 параметров) на те значения, которые используются для построения отчета (а это имена переменных, в моем случае).
Работы конечно не запредельно много, но лентяев она огорчает

[Aleksey]

Здравствуйте,

Случай, когда у пользователя нет доступа к ХП, а он пытается построить по ней отчет, считаю вырожденным

Извините, может не совсем понятно написал, имелось в виду не права на запуск хранимой процедуры, а права на запрос по системным таблицам.
Данный запрос -

Код: Выделить всё

select *, type_name(system_type_id) as type_name from sys.parameters

большинству пользователей ничего не вернет.

Спасибо.

[DmitryRu]

Майкрософт в вышеприведенных ссылках утверждает, что у большинства пользователей запрос вернет не пустой датасет, если у пользователя есть права на запуск БД.
Я проверял подключаясь к трем разным БД, где мне дали только роль public - запрос возвращает список параметров ХП.

Почему Вы полагаете, что датасет будет пустой?
Может у вас DBA что-то с безопасностью перекрутил?

Спасибо.

[DmitryRu]

Т.е., у обычного пользователя есть права на чтение из view sys.parameters
Если админ не закрутил это самостоятельно
Если закрутил, то будет так:

Код: Выделить всё

Msg 229, Level 14, State 5, Line 2 
The SELECT permission was denied on the object 'parameters', database 'mssqlsystemresource', schema 'sys'.

[Aleksey]

Здравствуйте,

Да, после проверки на другом сервере, данный запрос выдал результат. Возможно, что-то с настройками прав пользователя.
В любом случае, постраемся перенести данный метод в наш продукт. О результатах сообщим в данном топике.

Спасибо.

[DmitryRu]

Спасибо за поддержку.
Не подскажете, до кучи, почему в проекте, который выложен в первом посте, не работает автосохранение?

В вашем Custom Editor Sample работает, но вроде он для этого ничего особо не делает.
У меня галочка в опциях редактора установлена, но файл не сохраняется каждые 5 минут.
Правда, я уже допилил редактор так, чтобы при возникновении исключения сохранялась бы временная копия (чего мне не хватает в Вашем редакторе)
Но автосохранение тоже хорошая штука.