Возможны ли табличные параметры для процедур 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 ".

Подробнее см. в этой статье.