Можно ли использовать `SqlDbType.Structured` для передачи табличных параметров в NHibernate?

Я хочу передать коллекцию идентификаторов хранимой процедуре, которая будет отображаться с помощью NHibernate. Этот метод был введен в Sql Server 2008 (дополнительная информация здесь => Табличные параметры). Я просто не хочу передавать несколько идентификаторов в nvarchar параметр, а затем выберите его значение на стороне SQL Server.

Источник

5 ответов

Решение

Моей первой, специальной идеей была реализация моей собственной IType,

public class Sql2008Structured : IType {
    private static readonly SqlType[] x = new[] { new SqlType(DbType.Object) };
    public SqlType[] SqlTypes(NHibernate.Engine.IMapping mapping) {
        return x;
    }

    public bool IsCollectionType {
        get { return true; }
    }

    public int GetColumnSpan(NHibernate.Engine.IMapping mapping) {
        return 1;
    }

    public void NullSafeSet(IDbCommand st, object value, int index, NHibernate.Engine.ISessionImplementor session) {
        var s = st as SqlCommand;
        if (s != null) {
            s.Parameters[index].SqlDbType = SqlDbType.Structured;
            s.Parameters[index].TypeName = "IntTable";
            s.Parameters[index].Value = value;
        }
        else {
            throw new NotImplementedException();
        }
    }

    #region IType Members...
    #region ICacheAssembler Members...
}

Больше методов не реализовано; throw new NotImplementedException(); во всем остальном. Далее я создал простое расширение для IQuery,

public static class StructuredExtensions {
    private static readonly Sql2008Structured structured = new Sql2008Structured();

    public static IQuery SetStructured(this IQuery query, string name, DataTable dt) {
        return query.SetParameter(name, dt, structured);
    }
}

Типичное использование для меня

DataTable dt = ...;
ISession s = ...;
var l = s.CreateSQLQuery("EXEC some_sp @id = :id, @par1 = :par1")
            .SetStructured("id", dt)
            .SetParameter("par1", ...)
            .SetResultTransformer(Transformers.AliasToBean<SomeEntity>())
            .List<SomeEntity>();

Хорошо, но что это "IntTable"? Это имя типа SQL, созданного для передачи аргументов табличных значений.

CREATE TYPE IntTable AS TABLE
(
    ID INT
);

А также some_sp может быть как

CREATE PROCEDURE some_sp
    @id IntTable READONLY,
    @par1 ...
AS
BEGIN
...
END

Конечно, он работает только с Sql Server 2008 и в этой конкретной реализации с одним столбцом DataTable,

var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));

Это только POC, а не полное решение, но оно работает и может быть полезным при настройке. Если кто-то знает лучшее / более короткое решение, сообщите нам.

Источник

Более простым решением, чем принятый ответ, было бы использование ADO.NET. NHibernate позволяет пользователям подключиться IDbCommands в транзакции NHibernate.

DataTable myIntsDataTable = new DataTable();
myIntsDataTable.Columns.Add("ID", typeof(int));

// ... Add rows to DataTable
ISession session = sessionFactory.GetSession();
using(ITransaction transaction = session.BeginTransaction())
{
    IDbCommand command = new SqlCommand("StoredProcedureName");
    command.Connection = session.Connection;
    command.CommandType = CommandType.StoredProcedure;
    var parameter = new SqlParameter();
    parameter.ParameterName = "IntTable";
    parameter.SqlDbType = SqlDbType.Structured;
    parameter.Value = myIntsDataTable;
    command.Parameters.Add(parameter);            
    session.Transaction.Enlist(command);
    command.ExecuteNonQuery();
}
Источник

Вы можете передавать коллекции значений без хлопот.

Пример:

var ids = new[] {1, 2, 3};
var query = session.CreateQuery("from Foo where id in (:ids)");
query.SetParameterList("ids", ids);

NHibernate создаст параметр для каждого элемента.

Источник

В моем случае моя хранимая процедура должна вызываться в середине открытой транзакции. Если есть открытая транзакция, этот код работает, потому что он автоматически повторно использует существующую транзакцию сеанса NHibernate:

NHibernateSession.GetNamedQuery("SaveStoredProc")
    .SetInt64("spData", 500)
    .ExecuteUpdate();

Однако для моей новой хранимой процедуры этот параметр не так прост, как Int64, Это параметр с табличным значением (определяемый пользователем тип таблицы) Моя проблема в том, что я не могу найти правильную функцию Set. Я старался SetParameter("spData", tvpObj), но он возвращает эту ошибку:

Не удалось определить тип для класса: …

В любом случае, после некоторых проб и ошибок, этот подход ниже, кажется, работает. Enlist() функция является ключом в этом подходе. Это в основном говорит SQLCommand использовать существующую транзакцию. Без этого будет ошибка, говорящая

ExecuteNonQuery требует, чтобы команда имела транзакцию, когда назначенное команде соединение находится в ожидающей локальной транзакции…

using (SqlCommand cmd = NHibernateSession.Connection.CreateCommand() as SqlCommand)
{
    cmd.CommandText = "MyStoredProc";
    NHibernateSession.Transaction.Enlist(cmd); // Because there is a pending transaction
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@wiData", SqlDbType.Structured) { Value = wiSnSqlList });
    int affected = cmd.ExecuteNonQuery();
}

Так как я использую SqlParameter класс с таким подходом, SqlDbType.Structured доступен.

Это функция, где wiSnList получает назначение:

private IEnumerable<SqlDataRecord> TransformWiSnListToSql(IList<SHWorkInstructionSnapshot> wiSnList)
{
    if (wiSnList == null)
    {
        yield break;
    }
    var schema = new[]
    {
        new SqlMetaData("OriginalId", SqlDbType.BigInt),           //0
        new SqlMetaData("ReportId", SqlDbType.BigInt),             //1
        new SqlMetaData("Description", SqlDbType.DateTime),        //2
    };

    SqlDataRecord row = new SqlDataRecord(schema);
    foreach (var wi in wiSnList)
    {
        row.SetSqlInt64(0, wi.OriginalId);
        row.SetSqlInt64(1, wi.ShiftHandoverReportId);
        if (wi.Description == null)
        {
            row.SetDBNull(2);
        }
        else
        {
            row.SetSqlString(2, wi.Description);
        }

        yield return row;
    }
}
Источник

Другие получили эту реализацию для работы? Мне нужно передать несколько строк из нескольких столбцов (точнее 5 целых), используя этот метод - я определил свой базовый структурированный тип SQL и изменил ColumnSpan вернуть 5. Единственное другое отличие, которое я вижу, состоит в том, что я не возвращаю никаких значений при выполнении моей хранимой процедуры. Он просто потребляет значения, предоставленные ему.

DataTable dt = ...;

var query = session.CreateSqlQuery("EXEC foo_proc_here @data = :data")
     .SetStructured("data", dt)
     .ExecuteUpdate();

К сожалению, это приводит к появлению сообщения "Не удалось выполнить собственный запрос на массовые манипуляции". Внутренние исключения жалуются на ошибки "Index Out of Range".

Я не совсем уверен, куда еще обратиться, поскольку тема структурированных типов / параметров табличных значений в NHibernate кажется достаточно эзотерической, поэтому я продолжаю останавливаться на этом же посте переполнения стека.

Источник
Другие вопросы по тегам