如何在使用NHibernate进行任何插入之前增加ID [英] How to increment ID before any insert with NHibernate
问题描述
似乎NH仅获得一次MAX(ID)，首先插入，然后在内部存储该值，这在其他进程插入数据时给我带来了一些问题.然后，我没有实际的ID，并且抛出了重复的密钥异常.
It looks like NH gets MAX(ID) only once, at first insert and then stores this value internally, this causes me some problems when other processes inserts data. Then I have not actual ID and duplicate key exception is thrown.
假设我们有表Cats
CREATE TABLE Cats(ID int, Name varchar(25))
然后我们用FluentNhibernate完成了相应的映射
Then we have corresponding mapping done with FluentNhibernate
public class CatMap : ClassMap<Cat>
{
public CatMap()
{
Id(m=>m.ID).GeneratedBy.Increment();
Map(m=>.Name);
}
}
我要做的就是在插入之前，先插入SELECT MAX(ID) FROM Cats 插入由NHibernate生成的ID的Cat记录.在执行任何提交操作后执行Session.Flush.我已经使用SQL Server事件探查器进行了一些调查，并且此sql stetement仅执行一次(在第一次插入时)-其他插入并不会强制检索实际的MAX(ID).我知道HiLo之类的其他算法会更好，但我无法取代它.
All I want to achieve is to insert my Cat records with ID's generated by NHibernate using SELECT MAX(ID) FROM Cats before any insert. Executing Session.Flush after any commit dosnt work. I'v done some investigation using SQL Server profiler, and this sql stetement is executed only once (at first insert) - other inserts doesnt force to retreive actual MAX(ID). I know that other algorithms like HiLo are better, but I cant replace it.
推荐答案
如您所知，NHibernate Increment id生成器不适用于多用户环境.您声明使用HiLo生成器不是一种选择，因此您将拥有以下这些选择:
As you found out, the NHibernate Increment id generator was not intended for use in a multi-user environment. You state that using a HiLo generator is not an option so you're left with these options:
-
使用本机生成器并更改id列以使用数据库支持的身份机制
use the Native generator and change the id column to use the database supported identity mechanism
使用Assigned生成器并编写代码以确定下一个有效ID
use the Assigned generator and write code to determine the next valid id
创建一个自定义生成器，在其中实现 IIdentifierGenerator 界面来完成您需要的操作
create a Custom generator where you implement the IIdentifierGenerator interface to do what you need
以下是自定义生成器的示例代码，该生成器使用通用proc获取给定表的ID.这种方法的主要问题是，您必须将代码包装为工作单元模式，以确保"select max(id)..."和插入内容被同一数据库事务覆盖.
Below is sample code for a custom generator that uses a generalized proc to get an ID for a given table. The main issue with this approach is that you must wrap the code in something like a Unit of Work pattern to ensure the 'select max(id) ..." and the insert are covered by the same database transaction. The IIdentifierGenerator link has the XML mapping you need to wire up this custom generator.
using System;
using System.Collections.Generic;
using System.Data;
using NHibernate.Dialect;
using NHibernate.Engine;
using NHibernate.Id;
using NHibernate.Persister.Entity;
using NHibernate.Type;
namespace YourCompany.Stuff
{
public class IdGenerator : IIdentifierGenerator, IConfigurable
{
private string _tableName;
// The "select max(id) ..." query will go into this proc:
private const string DefaultProcedureName = "dbo.getId";
public string ProcedureName { get; protected set; }
public string TableNameParameter { get; protected set; }
public string OutputParameter { get; protected set; }
public IdGenerator()
{
ProcedureName = DefaultProcedureName;
TableNameParameter = "@tableName";
OutputParameter = "@newID";
}
public object Generate(ISessionImplementor session, object obj)
{
int newId;
using (var command = session.Connection.CreateCommand())
{
var tableName = GetTableName(session, obj.GetType());
command.CommandType = CommandType.StoredProcedure;
command.CommandText = ProcedureName;
// Set input parameters
var parm = command.CreateParameter();
parm.Value = tableName;
parm.ParameterName = TableNameParameter;
parm.DbType = DbType.String;
command.Parameters.Add(parm);
// Set output parameter
var outputParameter = command.CreateParameter();
outputParameter.Direction = ParameterDirection.Output;
outputParameter.ParameterName = OutputParameter;
outputParameter.DbType = DbType.Int32;
command.Parameters.Add(outputParameter);
// Execute the stored procedure
command.ExecuteNonQuery();
var id = (IDbDataParameter)command.Parameters[OutputParameter];
newId = int.Parse(id.Value.ToString());
if (newId < 1)
throw new InvalidOperationException(
string.Format("Could not retrieve a new ID with proc {0} for table {1}",
ProcedureName,
tableName));
}
return newId;
}
public void Configure(IType type, IDictionary<string, string> parms, Dialect dialect)
{
_tableName = parms["TableName"];
}
private string GetTableName(ISessionImplementor session, Type objectType)
{
if (string.IsNullOrEmpty(_tableName))
{
//Not set by configuration, default to the mapped table of the actual type from runtime object:
var persister = (IJoinable)session.Factory.GetClassMetadata(objectType);
var qualifiedTableName = persister.TableName.Split('.');
_tableName = qualifiedTableName[qualifiedTableName.GetUpperBound(0)]; //Get last string
}
return _tableName;
}
}
}
这篇关于如何在使用NHibernate进行任何插入之前增加ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
