Возможны ли табличные параметры для процедур CLR в SQL Server 2008?
Эта страница из SQL Server 2008 BOL рассказывает о хранимых процедурах CLR и имеет раздел с надписью "Табличные параметры", в котором говорится о том, как они могут быть полезными. Это здорово - я бы хотел использовать TVP в своих процессах CLR, но, к сожалению, это похоже на единственную ссылку в юниверсе на такую возможность, и в разделе не описывается, что такое синтаксис (а также дальнейшая информация связанный в конце абзаца)
Конечно, я легко могу найти описания того, как использовать TVP из процессов T-SQL или как выполнять CLR-процессы в целом. Но писать CLR proc, который принимает TVP? Ничего. Все это очень неуспешно, поскольку передача многострочных данных в сохраненный процесс является популярной проблемой.
Это заставляет меня задаться вопросом, является ли наличие раздела на этой странице ошибкой. Кто-нибудь, пожалуйста, скажите мне это, и укажите мне больше информации/примеры.
[EDIT]
Я собирался опубликовать это на одном из форумов MS, когда я наткнулся на это, что кажется последним гвоздем в гробу. Похоже, это невозможно.
Ответы
Ответ 1
Я могу найти lot подробнее ссылки. Тем не менее, все они предназначены для передачи табличных параметров в процедуры TSQL, поэтому они мало используются.
Однако я пришел к выводу, что это невозможно. Во-первых, существует список сопоставлений между типами CLR и SQL. Для типов таблиц нет сопоставления, поэтому следующее не работает, например:
[SqlProcedure]
public static void StoredProcedure(DataTable tvp, out int sum)
{
return 42;
}
а затем
CREATE TYPE MyTableType AS TABLE
(
Id INT NOT NULL PRIMARY KEY,
[Count] INT NOT NULL
)
GO
CREATE ASSEMBLY ClrTest FROM '<somePath>'
GO
CREATE PROCEDURE ClrTest
AS EXTERNAL NAME ClrTest.StoredProcedures.StoredProcedure
GO
Какой бы тип вы ни пытались (DataTable
, DbDataReader
, IEnumerable
), вызов CREATE PROCEDURE
продолжает генерировать ошибку 6552: CREATE PROCEDURE для "ClrTest" не удалось, поскольку типы T-SQL и CLR для параметра "@tvp" не соответствуют.
Во-вторых, документация на странице, на которую вы ссылаетесь, говорит: Тип таблицы, определяемый пользователем, не может быть передан в качестве параметра table-valued или возвращен из управляемой хранимой процедуры или функции, выполняемой в процессе SQL Server.
Кажется, я не могу найти нигде, как создать определенный тип таблицы в С#, но это также кажется тупиковым.
Возможно, вы можете задать свой вопрос где-нибудь на форуме Microsoft. По-прежнему странно, что они упоминают табличные параметры на странице CLR sproc, но никогда не объясняют, как это реализовать. Если вы найдете какое-либо решение, я хотел бы знать.
Ответ 2
Вы можете использовать временную таблицу, созданную и заполненную, прежде чем вы вызовете процедуру и прочитаете таблицу внутри процедуры clr.
Ответ 3
Решение состоит в том, чтобы сериализовать ваши табличные данные в строку в формате Json, а затем передать строку в ваш процесс CLR. В вашей процедуре или функции clr вы должны проанализировать json для IEnumerable, list или tableular object. Затем вы можете работать с данными, как и любой другой тип табличных данных.
Я написал несколько утилит, способных сериализовать любую таблицу sql в форматированную строку Json. Я был бы рад поделиться ими с кем-либо, предлагающим их адрес электронной почты. Фил Фактор написал хороший парсер Json T-SQL, который он назвал parseJson. Я адаптировал его решение к clr, которое выполняется намного быстрее. Оба принимают форматированную строку Json и создают таблицу из строки. У меня также есть множество утилит Json, которые я использую как с T-SQL, так и с CLR, способным сериализовать, разбор, вставку, удаление и обновление форматированных строк Json, хранящихся в столбцах sql.
Ответ 4
В то время как похоже, что передача таблиц непосредственно в процедуры CLR в настоящее время невозможна, я получил результат, хотя и не оптимальный:
- определение таблицы TSQL с оценкой UDT FooTable
- определение функции TSQL, которая принимает FooTable как параметр и возвращает XML, используя FOR XML EXPLICIT
- передача результирующего XML в функцию/процедуру CLR вместо самой таблицы
Не идеально, но он немного приближается.
Ответ 5
Если вы используете С# (в отличие от VB, в котором отсутствуют пользовательские итераторы), вы можете написать код ADO.NET для вызова ExecuteNonQuery() и запустить хранимую процедуру с помощью параметра SqlDbType.Structured (т.е., TVP).
Сбор, переданный как значение TVP, должен реализовывать IEnumerable<SqlDataRecord>
. Каждый раз, когда выполняется этот IEnumerable yield return, строка SqlDataRecord "конвейерна к параметру" table ".
Подробнее см. в этой статье.